Running migration scripts using SqlCmd.exe
Published 13 December 2022
Microsoft provides a command-line utility called SqlCmd.exe with SQL Server, but this utility will not run a SQL script over 500MB in size, and will return the following when this condition is encountered:
Sqlcmd: Error: Scripting error
The way around this is to switch off transactions while doing SQL Data Compare and obtain the script in the usual way. You then have to parse the script in an application, written in, for example, Visual Basic or Perl that will divide the queries into smaller batches. As you will not have any transactional integrity you will have to take a backup before you attempt to run the migration SQL script.
The following example contains VB script that injects a "GO" every 100 lines:
http://www.red-gate.com/MessageBoard/viewtopic.php?t=8109
Other workarounds might be to use a WHERE clause to restrict the amount of data being deployed, or to select fewer objects for the deployment and then run several deployments.