Solved

Return values from .NET SMO ExecuteNonQuery

Posted on 2014-12-12
7
390 Views
Last Modified: 2014-12-18
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?
0
Comment
Question by:Steve Endow
  • 4
  • 2
7 Comments
 
LVL 78

Expert Comment

by:David Johnson, CD, MVP
ID: 40497723
do you just assume unconditional success unless an exception is thrown

Yes, but you can add checking to the sp
0
 
LVL 18

Author Comment

by:Steve Endow
ID: 40497997
Hi David,

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

Steve
0
 
LVL 40
ID: 40498231
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 18

Assisted Solution

by:Steve Endow
Steve Endow earned 0 total points
ID: 40498269
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
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 500 total points
ID: 40498331
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
 
LVL 18

Assisted Solution

by:Steve Endow
Steve Endow earned 0 total points
ID: 40498482
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
 
LVL 18

Author Closing Comment

by:Steve Endow
ID: 40506628
Confirmed that the method returns a -1 value for each discrete statement in a DDL script.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now