1

I am having a SQL file(filenameScript) having more than 10k lines of code. Each block of SQL starts with GO and ends with GO. While executing the file from C#, I am getting exception near GO statements. But when I am running the same file in SQL server it is working fine.

con.ConnectionString = sqlconn;
FileInfo file = new FileInfo(filenameScript);
string script = file.OpenText().ReadToEnd();
SqlCommand command = new SqlCommand(script, con);
con.Open();
command.ExecuteNonQuery();
Close();

I think ExecuteNonQuerry is not able to handle so many \n,\r,and \t as the file read is stored in single line with many \n and \r. Is there any other method to do the same? Thanks in advance.

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
Veer
  • 1,575
  • 3
  • 16
  • 40

1 Answers1

3

No, the issue is not the length of the file, nor is it the existence of \r and/or \n characters. It is because executing SQL using that method can only run a single batch, and the script having GO statements causes multiple batches.

One possibility is to split the text on the keyword GO and execute each individual part:

con.ConnectionString = sqlconn;
var commands = File.ReadAllText(filenameScript).Split(new []{"GO"},StringSplitOption.RemoveEmptyEntries);
con.Open();
foreach(var batch in commands)
{
    SqlCommand command = new SqlCommand(batch, con);
    command.ExecuteNonQuery();
}
con.Close()

Additionally, you could wrap that in a Transaction to ensure all batches are executed atomically.

An alternative is also provided in this SO Question: How do I execute a large SQL script (with GO commands) from c#

Community
  • 1
  • 1
Jamiec
  • 133,658
  • 13
  • 134
  • 193
  • `File.ReadAllText(filenameScript).Split("GO");` is showing error **The best overloaded method match for `string.Split(params char[])` has some invalid arguments ** – Veer Apr 18 '13 at 08:47
  • 2
    That's a pretty basic error, Veer. But `string.Split` does have a pretty awful interface. I would just replace it with `Regex.Split(scriptFile, "GO", RegexOptions.IgnoreCase)` instead. – Quick Joe Smith Apr 18 '13 at 09:07
  • @VeerB.Singh - My bad, horrible string split interface. Ive updated it. – Jamiec Apr 18 '13 at 09:11
  • @QuickJoeSmith The Sql Script is getting messed up. The comments are messed too using this. Is there any method to run multiple batches directly including the GO word in a single attempt. – Veer Apr 18 '13 at 09:27
  • @Jamiec : That is crashing my Visual Studio.`Error 1 Internal Compiler Error (0xc0000005 at address 5A7D9D68): likely culprit is 'BIND'. An internal error has occurred in the compiler. To work around this problem, try simplifying or changing the program near the locations listed below. Locations at the top of the list are closer to the point at which the internal error occurred. Errors such as this can be reported to Microsoft by using the /errorreport option. ` – Veer Apr 18 '13 at 09:36
  • @VeerB.Singh - Ok, now you're just not bothering to debug yourself. Im afraid im not in front of your computer to handhold. Use some debugging/research techniques to try to solve the issues you now have (which BTW look completely unrelated to this question) – Jamiec Apr 18 '13 at 09:49