Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Return values from .NET SMO ExecuteNonQuery

Posted on 2014-12-12
7
Medium Priority
?
531 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 83

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
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…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

688 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