3

In a management studio tab, suppose I run code that creates a temp table.

Now if I re-run this code it gives an error that temp table already exists. I can open a new tab and run this code. This happens because each tab is a new session.

I know that using drop as initial part of the code is a solution. But, is there any way to get a new session (or reset the session) in the same window, so that I don't have to create new tab when re-running the code?

Improve this question
5

Personally, I just put a DROP IF EXISTS at the front of the code. That makes it forever repeatable, and you don't get errors if you haven't created the table yet.

Otherwise, you can disconnect & reconnect the window without closing it. Pain the bottom, but then you don't have to change the code.

Improve this answer
6
  • I am debugging code with around 100 temp tables and table variables (without any drop if exists checks) so was wondering if there is a command that I can place at the top that will reset the session for me.
    – variable
    Jul 14 '21 at 12:30
  • 1
    There no such sql command, since you are talking about having the client app to disconnect and reconnect. The client app being ssms in this case. You could play with running ssms in sqlcmd mode and the :Connect directive, I don't know how much success you'll have, since you lose variables, open transactions and such.
    – Tibor Karaszi
    Jul 14 '21 at 12:43
  • @TiborKaraszi: Could you please elaborate a little on what you mean by losing variables? I'm not sure how working with variables (presumably you mean T-SQL variables) is different between regular mode and sqlcmd mode.
    – Andriy M
    Jul 14 '21 at 12:55
  • I mean that variables, transaction, temp tables and such are cleaned up and dissappear if you reconnect.
    – Tibor Karaszi
    Jul 14 '21 at 19:32
  • @TiborKaraszi: Well yes, you are right, but I don't think that's an issue, since it's what the OP is asking about. They want to lose the temp tables created by their code in order to be able to run the said code again. And they mention as a possible workaround the option of running the code in a new window, so surely they realise that means losing everything else (vars, transactions...) as well. But I agree that it wouldn't make much sense for this to be a SQL command, since connection is something that the client side is establishing, rather than something happening solely on the server side.
    – Andriy M
    Jul 15 '21 at 6:49
1

Depending on what you have in your code, you could wrap it into a stored procedure (normal or temporary) and then execute the procedure. Like so:

CREATE OR ALTER PROCEDURE #p
AS
BEGIN
/*your code here*/
/* remember you cannot have GO statements here*/
END

GO

EXEC #p

That way you won't need to write out all the DROP TABLE statements. That being said I personally just reconnect the query window.

Improve this answer
1

You can right click anywhere inside the Query Window, hover over the Connection option, and then click Disconnect, then do the same again and click Connect. This will log you off and back on, leaving the Query Window open, and ultimately creating a new session, which will effectively drop all your TempTables.

Improve this answer
1
  • 1
    This option has been mentioned by others, albeit without a step-by-step instruction how to do it. I suspect (though could be wrong of course) the OP might actually know how to reconnect a query window, and what they are looking for is a simpler way to do that, like a one-click option or something, without the login popup box or any such thing. At least that's what would make sense to me, since doing all these steps isn't really that much of a trouble, but replacing all of them with a single click would certainly make a difference.
    – Andriy M
    Jul 15 '21 at 7:05

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.