Return values from .NET SMO ExecuteNonQuery

I am using SQL Management Objects (SMO) in a C# project to run some DDL scripts.  It is the first time I've used SMO.

It appears to work fine, but I can't seem to find a list of possible values returned by the SMO ExecuteNonQuery method.

In my testing, when my DDL script runs, apparently successfully, I get a return value of -13.  In most examples I've seen (almost all copied from MSDN), they don't even bother to check the integer return.  

https://social.msdn.microsoft.com/Forums/en-US/43e8bc3a-1132-453b-b950-09427e970f31/run-a-sql-script-file-in-c?forum=adodotnetdataproviders


Does anyone know of documentation about or a list of the return values from SMO ExecuteNonQuery?  -13 seems a little odd, so I'm assuming there are other possible return values.

Or do you just assume unconditional success unless an exception is thrown?
LVL 18
Steve EndowMicrosoft MVP - Dynamics GPAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jacques Bourgeois (James Burger)Connect With a Mentor PresidentCommented:
Ooops. I had 3 tabs opened in my browser when I answered. I Must have hit the wrong tab when I told you that it does not return a value. Or maybe it's the vodka (my wonderful Suzanne had one, so why not me). Sorry for the confusion.

As for you question.

And since you have 13 statements, the value returned should be 13... If your statements are INSERT, UPDATE or DELETE and they succeeded. Are they?

If not, then maybe the documentation is not clear, and it returns -1 for each statement.

Easy to test.

Also, the way I understand it from the documentation, GO is not always necessary. It is used to send the previous statements (since the last GO) as a batch. This is necessary in some scenarios, but in most cases, GO is useless.
0
 
David Johnson, CD, MVPOwnerCommented:
do you just assume unconditional success unless an exception is thrown

Yes, but you can add checking to the sp
0
 
Steve EndowMicrosoft MVP - Dynamics GPAuthor Commented:
Hi David,

What do you mean "add checking to the sp"?

Steve
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Jacques Bourgeois (James Burger)PresidentCommented:
Which ExecuteNonQuery do you refer to. There are many.

Your message title talks about smo ExecuteNonQuery. There is no ExecuteNonQuery on smo itself, but there is a Smo.Database.ExecuteNonQuery in the Smo namespace. This is probably not the one you are using, because that one does not return a value.

There is also the ServerConnection.ExecuteNonQuery in the Microsoft.SqlServer.Management.Common namespace. This one does no return a value either.

The other one I know of is the SqlCommand (or any other Command object in the System.Data namespace) ExecuteNonQuery. This does not seem to be the one you use either, because this one returns the number of records that were thouched by the query, and that number cannot be negative.

So, which one are you using, and how are you getting -13 for any of these?. Or are you using a fourth one I have never encountered?
0
 
Steve EndowConnect With a Mentor Microsoft MVP - Dynamics GPAuthor Commented:
Hi Jacques,

I am using ServerConnection.ExecuteNonQuery, which does have an integer return value.

Server server = new Server(new ServerConnection(gpConn));
int result = server.ConnectionContext.ExecuteNonQuery(commandText);

http://msdn.microsoft.com/en-us/library/ms199328.aspx

An Int32 array value that specifies the total number of rows affected by each element of the StringCollection object used as the sqlCommands parameter. The return value specifies the total number of rows affected by the Transact-SQL command for UPDATE, INSERT, and DELETE statements. For all other types of statements, the return value is -1.

Hmm, I'm now wondering whether it is returning -13 because my DDL script has 13 statements separated by GO.  

I'll check my code later today and see if that explains it.  If so, I'll modify my DDL script and see if I get a different return just to confirm.
0
 
Steve EndowConnect With a Mentor Microsoft MVP - Dynamics GPAuthor Commented:
Just did some testing and I confirmed that, for a DDL script, the value returned by ServerConnection.ExecuteNonQuery is -1 times the number of GO statements in the script.

You were correct that the GO statements were not required.  I removed them all, and the method returned -1.   When there were 2 GO statements, it returned -2.  4 statements returned -4, etc.

Mystery solved!
0
 
Steve EndowMicrosoft MVP - Dynamics GPAuthor Commented:
Confirmed that the method returns a -1 value for each discrete statement in a DDL script.
0
All Courses

From novice to tech pro — start learning today.