SQL Server ExecuteNonQuery() state?

[stanl]

I have written a script which creates a linked server to SQL Server (2008 Express R2) with an Access Table using the CLR. It works and is quite fast, and would like to post it.

When I wrote a similar script in COM, after Executing the linked server SQL I put in a while loop based on the command state.

I looked at MSDN OLEDB connection and command classes, there is no state property for the command class, and the connection state is either open or close. I assumed there would be states like executing, or fetching like in COM.

Therefore the script runs (cm = the command object)

cm.ExecuteNonQuery

cm.dispose()

Then closes the connection and exits the script. Like I said it works, but isn't there a danger if the script exits before the link is completed, or does it run on its own thread independent of WB?

[td]

The documentation for the ExecuteNonQuery does not state that it is asynchronous.  The MSFT FCL documentation convention is to state this fact when a method processes asynchronously.  Also, FCL asynchronous method names usually start with the word 'Begin'.  These two facts make it fairly safe to assume that the function is synchronous and not asynchronous.   The class does provide a 'CommandTimeout' property that can be used to cause an exception which will be returned as an error by WinBatch, if the command is not completed within the allotted time.

[stanl]

The documentation for the ExecuteNonQuery does not state that it is asynchronous.  The MSFT FCL documentation convention is to state this fact when a method processes asynchronously.  Also, FCL asynchronous method names usually start with the word 'Begin'.  These two facts make it fairly safe to assume that the function is synchronous and not asynchronous.   The class does provide a 'CommandTimeout' property that can be used to cause an exception which will be returned as an error by WinBatch, if the command is not completed within the allotted time.

Understood. But the question is more about what does WB do between

cm.ExecuteNonQuery()

cm.dispose()

The CommandTimeOut defaults to 30 seconds, so does WB put up a 30 sec TimeDelay, or wait for an error?

[td]

As I said, the the method's MSFT documentation indicates that it is synchronous.  That should be sufficient information to answer the question "what does WB do...?" but apparently not.  I recommend gaining an understanding of the difference between synchronous and asynchronous method calls. It is fundamental.

[td]

Here is a link to MSFT's asynchronous programming model discussion:

http://msdn.microsoft.com/en-us/library/jj152938(v=vs.110).aspx

Of course, this does not apply to your method since it is synchronous like the majority of CLR and COM methods, and almost all WinBatch functions and operators.

[DAG_P6]

I've watched ExecuteNonQuery run in the CLR debugger, and it ran synchronously; the statement pointer didn't advance until the query completed.