Using API to run script file

ghis

Using the SqlDataCompare API I was able to build a synchronization script file that is meant to be used on a DB that is not accesible at the time of build.

So i want to execute that script file on the other DB...

At first I tried running the script using the Microsoft SQLServer Management Studio, but it failed with a "There is insufficient memory to run this query" error. I also tried with osql, with a .NET SqlCommand and with the SqlDataCompare API and it all failed with the same insufficient memory error.

So my question is, is there a way to run a huge script file using your API without getting the memory error?

Here's a snippet of the code I tried

FileStream fs = File.Open(myScript, FileMode.Open);
StreamReader sr = new StreamReader(fs, Encoding.Unicode);
BlockExecutor exec = new BlockExecutor();
ExecutionBlock block = new ExecutionBlock();

block.AddBatch(sr.ReadToEnd());
exec.ExecuteBlock(block, strServer, strDatabase);
block.Dispose();
sr.Close();
fs.Close();

Any ideas ?
Thanks in advance

Ghis

p.s.: Building multiple script files or separating the script file is not an option.

chris.buckingham

The only way to run a very large SQL script in SSMS is to break up the script into small sections with COMMIT statements. This means that you have to parse the script with a utility and insert COMMIT<return>BEGIN TRANSACTION lines.

As transactional integrity may be compromised then you will have to take full backup before running the script of course.