Using SMO, still no go… ConnectionContext.ExecuteN

问题:

SQL Server 2008

Using all the correct references I dare say:

System.Data.SqlClient;
Microsoft.SqlServer.Management.Smo;
Microsoft.SqlServer.Management.Common;
Microsoft.SqlServer.Management.Sdk.Sfc;

All compiles with no errors.

I have stripped code down to almost zero for easy debugging.

Connecting to server alright and so on.

Excuting following code:

SqlConnection connection = new SqlConnection(sqlConnectionString);
Server server = new Server(new ServerConnection(connection));
server.ConnectionContext.ExecuteNonQuery(sqlDBQuery);

Where sqlDBQuery is a string: USE [master] GO ALTER DATABASE [Cassiopeia] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [Cassiopeia] SET ANSI_NULLS OFF GO

But it doesn't matter what "sqlDBQuery" is, I always get the same error, like

incorrect syntax near GO

.

I was in belief that SMO would take care of this, when I look at my ConnectionContext is says BatchSeparator = "GO"

If I remove GO it's a go... so to speak but I really need to know why my SMO doesn't work.

Everywhere I look it just says "use smo like this and you're off fine". Well... doesn't work for me.

See this post by Jon Galloway for reference: http://weblogs.asp.net/jgalloway/archive/2006/11/07/Handling-_2200_GO_2200_-Separators-in-SQL-Scripts-2D00-the-easy-way.aspx

Regards

回答1:

"GO" is not SQL language

It's a batch separator used by client tools like SSMS (which does not send "GO" to the database engine)

SMO does not parse the script into batches like SSMS would do, so the database engine throws an error.

After comment:

"GO" must be on a separate line by itself

Is your script literally this (copy/paste from when I "edit" the question)

USE [master]  GO  ALTER DATABASE [Cassiopeia] SET ANSI_NULL_DEFAULT OFF  GO  ALTER DATABASE [Cassiopeia] SET ANSI_NULLS OFF  GO

or this correctly formatted?

USE [master]
GO
ALTER DATABASE [Cassiopeia] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [Cassiopeia] SET ANSI_NULLS OFF
GO

回答2:

Error caused by faulty formatting of query text. Quite embarrassing really.

Now solved, thanks!