1

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?

  • You mean different executions of sqlcmd? Then the answer is no, the same session is not used. You got the same session id by luck. – user1443098 Oct 18 at 14:47
4

You should be able to verify that all batches in a single SQLCMD script are run in the same session by creating a local #temp table at the top of the SQLCMD script and trying to reference that temp table in subsequent batches. Since the scope of a local temp table ends when the session ends, if subsequent batches were indeed running in different sessions (even though the id was the same), you would get table not found errors.

create table #test(TestColumn varchar(50))
insert into #test(TestColumn) values('I''m still here!')
GO

SELECT @@SPID
SELECT * FROM #test
GO

SELECT @@SPID
SELECT * FROM #test
GO

SELECT @@SPID
SELECT * FROM #test
GO
  • I should have thought of this from the start. Very nice, thank you. – Andriy M Oct 28 at 12:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.