7 Comments

1. Mahendra says:

   June 9, 2011 at 3:04 am

   Going by the line “Temp tables that are created within a SPID are accessible from any proceeding execution in that SPID”,
   1. Create a stored procedure that creates a #temp table.
   2. Open a new Query Window in SSMS.
   3. Execute the stored procedure and then from outside the stored procedure try to excute the following statement “Select * From #temp” it will not get executed.

   My question is when the @@SPID is same why the temp table created in a stored procedure is not accessible outside the stored procedure.

   Reply to this comment
   • Anonymous says:

     September 29, 2011 at 3:45 pm

     The New Query window is a separate connection and therefore spid.

     Reply to this comment
     • Anonymous says:

       May 7, 2012 at 5:35 am

       New query window was opened to execute procedure and run select statment. Both in the same query window. Both on same spid.

       Reply to this comment
2. Err: Changes to the state or options of database ‘databasename’ cannot be made at this time. The database is in single-user mode, and a user is currently connected to it. « R. Suharta's Blog says:

   May 26, 2011 at 10:04 pm

   [...] is you need to kill the sql session which is locking the database. Firstly you need to query which spid is locking the [...]

   Reply to this comment
3. Err: Changes to the state or options of database ‘databasename’ cannot be made at this time. The database is in single-user mode, and a user is currently connected to it. « R. Suharta's Blog says:

   May 26, 2011 at 10:02 pm

   [...] is you need to kill the sql session which is locking the database. Firstly you need to query which spid which is locking the [...]

   Reply to this comment
4. Merete Wrangell says:

   May 23, 2011 at 1:17 am

   Have problems with spid -2 in sqlserver 2005. How do I kill these processes?

   Reply to this comment
5. Tanuj Bhargava says:

   May 11, 2011 at 6:10 am

   Thanks a lot for the accurate information.

   Reply to this comment