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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Steve EndowMicrosoft 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
Jacques Bourgeois (James Burger)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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve EndowMicrosoft 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.