My script contains multiple
GO keywords. I know that SQLCMD interprets
GO as a batch delimiter, so the code is not run as a single block but instead each part between the delimiters (a batch) is run separately. My question is, are all batches executed in the same session?
I tried testing this using a simple script like this:
SELECT @@SPID GO SELECT @@SPID GO SELECT @@SPID GO
I then ran the script from the command line using this command
SQLCMD -S MyServer\Instance -E -i MyScript.sql
and got this output:
------ 62 (1 rows affected) ------ 62 (1 rows affected) ------ 62 (1 rows affected)
It seemed to answer my question in the affirmative, but then I ran the script again and got exactly the same output, i.e. all values were
62 again. Apparently the two separate executions of SQLCMD could not possibly run in the same session, they just happened to receive the same session ID. But that in turn makes me think that the same
@@SPID value return by a single run might not necessarily mean it was the same session either. Each batch could be executed in a different session that just happened to receive the same ID, similar to how different executions of SQLCMD were running in different sessions with the same ID.
Therefore, my question still stands: are multiple batches of the same script executed in SQLCMD in the same session or not? Is there a way to determine this?