0

When I use SqlCommand.ExecuteNonQuery() and its CommandText has a GO (commit) it it, I get an SQLException.

I removed it and the SQL worked, but the whole SQL has many statements and I'd like to commit after each of them run. And I don't wanna split them in multiple SqlCommand objects.

Is there a better solution to keep commiting and keep a unique SqlCommand object?

Hikari
  • 3,797
  • 12
  • 47
  • 77

2 Answers2

9

GO is not commit, it is the batch separator. You need to split your command batch into chunks and omit the GO in order to be able to execute them with SqlCommand.ExecuteNonQuery().

Transaction handling is done independently. When you execute multiple commands in a loop, you are free to run them all in the same SqlTransaction, start a new transaction for every iteration, or leave the SqlCommand.Transaction property unassigned.

Cee McSharpface
  • 8,493
  • 3
  • 36
  • 77
  • Thanks. Sorry I didn't understand what you mean. Do you suggest splitting the SQL string in multiple runs, right? I wanted to keep all the operations in a unique string and execute `SqlCommand.ExecuteNonQuery()` once for them all. Is that really impossible? – Hikari Mar 18 '16 at 17:30
  • It is possible, but you need to remove the `GO` lines. `GO` is not the same as a transaction commit. `GO` is interpreted by batch script processors (osql, SQL Studio script) to execute the code that precedes it as a unit, so you can for example chain multiple DDL statements like `CREATE VIEW` which is otherwise not possible. So if you just remove the `GO` you run a risk that the resulting script fails. I recommend a loop. You can still reuse the same `SqlCommand` object, just reassign the `CommandText`, with negligible performance penalty. – Cee McSharpface Mar 18 '16 at 17:36
  • So, aren't logs commited to mdf files when a `GO` command is run? If that's the case, I'll just remove it and so be it. – Hikari Mar 18 '16 at 17:48
  • yes and no :-) `GO` does not imply a transaction. you can execute a `BEGIN TRAN`, then run a batch, and then `ROLLBACK TRAN` to cancel the entire batch (see [here](http://stackoverflow.com/questions/2668529/what-is-a-batch-and-why-is-go-used). But if you don't use a transaction at all, every call to `SqlCommand.Execute()` will persist the results of its data manipulation immediately. In case you concat script parts previously separated by `GO`, be careful that you don't end up with an invalid script - some DDL operations like `CREATE PROCEDURE` cannot occur more than once in a single command). – Cee McSharpface Mar 18 '16 at 20:09
2

Here is my PowerShell solution:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") > $null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") > $null
$cnSQL=New-Object System.Data.SqlClient.SqlConnection("Server=$($env:COMPUTERNAME)\$InstanceName;Database=master;User Id=$User;Password=$Pwd")
$cnServer=New-Object Microsoft.SqlServer.Management.Common.ServerConnection($cnSQL)
$strSQL=Get-Content -Path "sqlFile.txt" | Out-String
$cnServer.ExecuteNonQuery($strSQL) > $null
$cnSQL.Close()
Slogmeister Extraordinaire
  • 2,890
  • 2
  • 25
  • 39
  • Could you please explain how your solution handles the problem of `GO` statements in the SQL script ? – adamency Jun 01 '22 at 15:56
  • You should explain that your solution only works if you have SQL Server installed on your machine btw. – adamency Jun 03 '22 at 13:57