November 28, 2023 at 7:19 am
hi
xyz.bat this is called from microsoft sheduler. the contenct of .bat is followng.
sqlcmd -S abc -U rr -P xyz -Q "use db EXEC usppp @strSourcedb = N'dba',@strTargateDb = N'dbb'"
sqlcmd -S abc -U rr -P xyz -Q "use db EXEC usppp @strSourcedb = N'dba',@strTargateDb = N'dbb'"
so the question is, is it synchronous that mean when I check activity monitor I must not get two session id from the database running same stored procedure's queries.
Q1)that is first sqlcmd call should execute first, in a sessionid @spid then after the complition this spid should go away from my activity monitor then next call of the same stored procedure should execute in diffrent @spid ( that is at one time i must see only one spid in my activity monitor executing a query of the above stored procedure, i know that same spid is given to other new process.)
Q2) can i see two spid of same (instence) stored procedure in above case with two diffrent quries of the same SP at the same time in activity monitor?
Q3) can i see same spid twoice, in activity monitor with diffrent/same query of the same stored procedure(instece) at the same time in activity monitor?
November 28, 2023 at 11:59 am
This is an easy one to test, make a dummy proc which is just a WAITFOR DELAY and call that using the template you have above and note the behaviour you see.
November 28, 2023 at 2:30 pm
I agree with Ant-Green - test it and see. BUT that being said, the way the bat file is written it would be running command 1 then when that sends back a return code, it will start command 2.
Now as for your questions, Q1, the SPID from query 1 may still show up if the connection is still active for any reason. Offhand, I don't remember if sqlcmd logs out when complete or if it leaves the session active but in a suspended state for a set period of time.
Q2, yep - you could see that no problem. if the BAT file is run more than once before the first time completes, then you could have 2 SPIDs for each of the SP's
Q3, yep - you could see the same spid twice (or more) with the same query. When the query goes parallel, this can happen.
Now, my answers above are based on using sp_who2, not activity monitor as I am not a fan of activity monitor, but I am pretty sure activity monitor just calls sp_who2 in the back-end (or a very similar SP).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 1, 2023 at 11:16 am
the case is one bat file runs at a time. kindly answer in this light.
regarding q1) are u saying sqlcmd when completes, it closes the connection or it keeps the connection open?
December 1, 2023 at 2:02 pm
I'm saying I don't remember if it calls a logout or if it leaves the connection open in a suspended state (ie open but doing nothing) when it completes. I'm saying test it. JUST because you see a SPID on the activity monitor doesn't mean the SPID is doing anything.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 2, 2023 at 6:11 am
This was removed by the editor as SPAM
December 2, 2023 at 6:12 am
This was removed by the editor as SPAM
December 12, 2023 at 11:42 am
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply