Solved

Return values from .NET SMO ExecuteNonQuery

Posted on 2014-12-12
7
406 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
A short film showing how OnPage and Connectwise integration works.

919 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

16 Experts available now in Live!

Get 1:1 Help Now