DbCommand can't run multiple SQL command in one ExecuteNonQuery()

Question

Hello I have the following problem and I can't solve it.

With DbCommand I'm trying execute this SQL statement

Dim strCommnad As String = 
"CREATE DEFAULT [dbo].[DOMAIN_XLibPKID_D] AS (0);" + Environment.NewLine +                                 
"CREATE TYPE [dbo].[XLibPKID] FROM BIGINT NOT NULL;" + Environment.NewLine +
"EXEC sp_bindefault 'DOMAIN_XLibPKID_D', 'XLibPKID';"

command.CommandText = strCommnad              
command.CommandType = CommandType.Text                
command.ExecuteNonQuery()

but I always get this an error message

Incorrect syntax near the keyword 'CREATE'.

But when I run each command from strCommand standalone then everything works fine.

I'm using VS 2010 Professional and SQL Server 2008 R2 Express.

Thanks for any help.

Try to run these three statements together in SQL Server Management Studio Express - it won't work either! You need to separate those out into two or three separate calls - they cannot be called in a single call .... — marc_s, Jun 06 '11 at 12:58
From [CREATE DEFAULT](http://msdn.microsoft.com/en-us/library/ms173565.aspx): "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work" — Damien_The_Unbeliever, Jun 06 '11 at 13:14

marc_s · Answer 1 · 2011-06-06T13:11:34.227

I'm not entirely sure what you're trying to do - but your approach seems overly complicated ...

You appear to be adding a DEFAULT clause to your XLibPKID column - right? This ALTER TABLE statement should do that, too:

command.CommandText = 
   "ALTER TABLE [dbo].[DOMAIN_XLibPKID_D]  " +
   "    ADD CONSTRAINT DF_XLibPKID DEFAULT (0) FOR XLibPKID";
command.ExecuteNonQuery()

This just adds a separate DEFAULT CONSTRAINT to your table.

score -2 · Answer 2 · answered Jun 06 '11 at 12:58

-2

Try adding a GO between each statement:

Dim strCommnad As String =  "CREATE DEFAULT [dbo].[DOMAIN_XLibPKID_D] AS (0);" + Environment.NewLine +
"GO; CREATE TYPE [dbo].[XLibPKID] FROM BIGINT NOT NULL;" + Environment.NewLine + 
"GO; EXEC sp_bindefault 'DOMAIN_XLibPKID_D', 'XLibPKID';"

answered Jun 06 '11 at 12:58

Jaymz

6,140
2
26
30

2

That won't work in a `DbCommand` or `SqlCommand` - GO is **NOT** a valid SQL keyword - it's a SQL Server Management Studio delimiter ONLY – marc_s Jun 06 '11 at 13:00
Ah, bugger me! I'd never tried it outside of SSMS before - sorry. – Jaymz Jun 07 '11 at 11:22

DbCommand can't run multiple SQL command in one ExecuteNonQuery()

2 Answers2