Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Sep 2010
    Posts
    10

    Question Unanswered: Query Analyzer; transactions and temp tables

    I'm using MS SQL Server 2k with the associated Query Analyzer.

    I'm not sure when exactly this happened, but I recently discovered that I can no longer use transactions or temp tables in a meaningful way within QA. I can, but only in a single execution of code. For instance, if I do this:

    Code:
    select getdate() as col into #temp
    
    select * from #temp
    It runs fine if I execute both statements at once. Run them one at a time, and the temp table is not there. Likewise I can start a transaction, but cannot roll back or commit it outside of the same execution that started it.

    Researching the problem, I find articles are split about 50/50 on temp tables being persistent for the connection vs being persistent for the single execution scope.

    I swear, it was not always like this. I've often used QA for debugging scripts by dumping the output of big queries into a temp table and perusing the contents at my leisure. I've done complicated updates to a database by manually starting a transaction and executing queries one at a time and only committing the changes when I was satisfied with the results. Furthermore, if I use Management Studio 2005 against the same 2k database, it works exactly as it should, with temp tables lasting for the duration of the connection, not just the execution.

    WTF is going on? If need be, I'll just switch over to 2005, but this just bugs me.

    EDIT: I tested it out against 2 other SQL2k servers, and got someone else to do it with the same results. So it's not just me, and it's not just that server.
    Last edited by JaredLessl; 09-24-10 at 11:33.

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    On the two SQL2K8 servers I have access to, the #temp table persists.

    I ran:

    select getdate() as col into #temp

    select * from #temp

    then wiped-across the select * line and ran it alone and it returned the date from the #temp table.


    Don't know why yours is behaving differently--all I can say is that you are not crazy.
    Last edited by PracticalProgram; 09-24-10 at 11:51.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    One of your statements creates #temp, the other reads from #temp.

    You must create the temp table first, then try to access the data in it.

    What's confusing?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    As I understand Jared's problem Pat you are missing his point. That, or I am missing yours.

    Jared - so long since I worked on SS2k with QA that I can't really help (I don't even have a VM with those on these days). I can confirm though that what you expect to happen does happen for me with any combination of SSMS 2k5 and 2k8 querying RTM builds of SS2k8 and SS2k5 Ent, Standard & dev.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Sep 2010
    Posts
    10
    Quote Originally Posted by Pat Phelan View Post
    One of your statements creates #temp, the other reads from #temp.

    You must create the temp table first, then try to access the data in it.
    What's confusing is that I _am_ creating the temp table, and then within the same connection (though not the same execution) attempting to read from it, and it doesn't work. It used to, and other people here confirm that it ought to.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I think I smell an isolation level problem here. Especially where commits and rollbacks may not be working. For the moment, see if you have an open transaction that is messing things up by running
    Code:
    select @@trancount
    and make sure it comes back with a 0.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Actually, it may even be easier than that. Have you accidentally checked off the box in Tools->Options Connections Tab "Disconnect after query executes"?

  8. #8
    Join Date
    Sep 2010
    Posts
    10

    pindat caricature

    No transactions, "Disconnect after query executes" was unchecked.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    How about running a profiler trace, see what QA is submitting to the server? See if it is actually disconnecting?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've tried this on a SQL 2000 server using both osql.exe and Query Analyzer. It works as expected in both cases, both when the whole batch is executed at once and when the individual statements are executed in sequence (which requires an extra GO in osql.exe).

    The fact that you can execute your sample script and other scripts involving transactions as you expect using the SQL 2005 Management Studio makes me suspect that there may be some problem with your Query Analyzer configuration. I don't have enough information to help you figure out what might be awry.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Sep 2010
    Posts
    10
    Here's the trace from running just the SELECT INTO statement. I don't see a logout or anything like that at the end. Probably best just paste this into Excel or some such.

    Code:
    Audit Login	-- network protocol: Named Pipes
    set quoted_identifier on
    set implicit_transactions off
    set cursor_close_on_commit off
    set ansi_warnings on
    set ansi_padding on
    set ansi_nulls on
    set concat_null_yields_null on
    set language us_english
    set dateformat mdy
    set datefirst 7
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.640		
    SQL:BatchStarting	SET TEXTSIZE 64512	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.640		
    SQL:StmtStarting	SET TEXTSIZE 64512	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.640		
    SQL:StmtCompleted	SET TEXTSIZE 64512	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.640		
    SQL:BatchCompleted	SET TEXTSIZE 64512	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.640		
    SQL:BatchStarting	select @@microsoftversion	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.653		
    SQL:StmtStarting	select @@microsoftversion	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.653		
    SQL:StmtCompleted	select @@microsoftversion	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.653		
    SQL:BatchCompleted	select @@microsoftversion	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.653		
    SQL:BatchStarting	SELECT ISNULL(SUSER_SNAME(), SUSER_NAME())	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.670		
    SQL:StmtStarting	SELECT ISNULL(SUSER_SNAME(), SUSER_NAME())	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.670		
    SQL:StmtCompleted	SELECT ISNULL(SUSER_SNAME(), SUSER_NAME())	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.670		
    SQL:BatchCompleted	SELECT ISNULL(SUSER_SNAME(), SUSER_NAME())	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.670		
    SQL:BatchStarting	select @@spid	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.687		
    SQL:StmtStarting	select @@spid	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.687		
    SQL:StmtCompleted	select @@spid	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.687		
    SQL:BatchCompleted	select @@spid	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.687		
    SQL:BatchStarting	set showplan_text off
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.700		
    SQL:StmtStarting	set showplan_text off
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.700		
    SQL:StmtCompleted	set showplan_text off
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.700		
    SQL:BatchCompleted	set showplan_text off
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.700		
    SQL:BatchStarting	SET NOEXEC OFF SET PARSEONLY OFF	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.717		
    SQL:StmtStarting	SET NOEXEC OFF SET PARSEONLY OFF	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.717		
    SQL:StmtCompleted	SET NOEXEC OFF SET PARSEONLY OFF	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.717		
    SQL:BatchCompleted	SET NOEXEC OFF SET PARSEONLY OFF	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.717		
    SQL:BatchStarting	set showplan_all off
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.717		
    SQL:StmtStarting	set showplan_all off
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.717		
    SQL:StmtCompleted	set showplan_all off
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.717		
    SQL:BatchCompleted	set showplan_all off
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.717		
    SQL:BatchStarting	use [POS_WH]	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.733		
    SQL:StmtStarting	use [POS_WH]	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.733		
    SQL:StmtCompleted	use [POS_WH]	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	6	0	0	4104	105	2010-09-24 11:46:25.733		
    SQL:BatchCompleted	use [POS_WH]	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	14	0	0	4104	105	2010-09-24 11:46:25.733		
    SQL:BatchStarting	set nocount off
    set arithabort on
    set concat_null_yields_null on
    set ansi_nulls on
    set cursor_close_on_commit off
    set ansi_null_dflt_on on
    set implicit_transactions off
    set ansi_padding on
    set ansi_warnings on
    set quoted_identifier on
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.763		
    SQL:StmtStarting	set nocount off
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.763		
    SQL:StmtCompleted	set nocount off
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.763		
    SQL:StmtStarting	set arithabort on
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.763		
    SQL:StmtCompleted	set arithabort on
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.763		
    SQL:StmtStarting	set concat_null_yields_null on
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.763		
    SQL:StmtStarting	set concat_null_yields_null on
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.763		
    SQL:StmtCompleted	set concat_null_yields_null on
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.763		
    SQL:StmtStarting	set ansi_nulls on
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.763		
    SQL:StmtCompleted	set ansi_nulls on
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.763		
    SQL:StmtStarting	set cursor_close_on_commit off
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.763		
    SQL:StmtCompleted	set cursor_close_on_commit off
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.763		
    SQL:StmtStarting	set ansi_null_dflt_on on
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.763		
    SQL:StmtCompleted	set ansi_null_dflt_on on
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.763		
    SQL:StmtStarting	set implicit_transactions off
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.763		
    SQL:StmtCompleted	set implicit_transactions off
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.763		
    SQL:StmtStarting	set ansi_padding on
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.763		
    SQL:StmtCompleted	set ansi_padding on
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.763		
    SQL:StmtStarting	set ansi_warnings on
    set quoted_identifier on
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.763		
    SQL:StmtCompleted	set ansi_warnings on
    set quoted_identifier on
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.763		
    SQL:BatchCompleted	set nocount off
    set arithabort on
    set concat_null_yields_null on
    set ansi_nulls on
    set cursor_close_on_commit off
    set ansi_null_dflt_on on
    set implicit_transactions off
    set ansi_padding on
    set ansi_warnings on
    set quoted_identifier on
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.763		
    SQL:BatchStarting	set lock_timeout -1	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.763		
    SQL:StmtStarting	set lock_timeout -1	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.763		
    SQL:StmtCompleted	set lock_timeout -1	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.763		
    SQL:BatchCompleted	set lock_timeout -1	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.763		
    SQL:BatchStarting	select IS_SRVROLEMEMBER ('sysadmin')	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.780		
    SQL:StmtStarting	select IS_SRVROLEMEMBER ('sysadmin')	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.780		
    SQL:StmtCompleted	select IS_SRVROLEMEMBER ('sysadmin')	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.780		
    SQL:BatchCompleted	select IS_SRVROLEMEMBER ('sysadmin')	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	0	0	0	4104	105	2010-09-24 11:46:25.780		
    SQL:BatchStarting	select 1 as col into #temp
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.810		
    SQL:StmtStarting	select 1 as col into #temp
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.810		
    Object:Created		SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl					4104	105	2010-09-24 11:46:25.810		
    SQL:StmtCompleted	select 1 as col into #temp
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	123	1	0	4104	105	2010-09-24 11:46:25.810		
    SQL:BatchCompleted	select 1 as col into #temp
    	SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	123	1	0	4104	105	2010-09-24 11:46:25.810		
    Audit Logout		SQL Query Analyzer	JaredLessl	DOMAIN\JaredLessl	0	324	1	216	4104	105	2010-09-24 11:46:25.640

  12. #12
    Join Date
    Sep 2010
    Posts
    10
    Quote Originally Posted by Pat Phelan View Post
    The fact that you can execute your sample script and other scripts involving transactions as you expect using the SQL 2005 Management Studio makes me suspect that there may be some problem with your Query Analyzer configuration. I don't have enough information to help you figure out what might be awry.

    -PatP
    I agree, which is why I had someone else attempt the same thing as well, and they got the same results with their own QA. If it's not the server (works with 2005 client), and it's not the client (same results by other users), and for the sake of argument we'll say it's not PEBKAC, what's left?

  13. #13
    Join Date
    Sep 2010
    Posts
    10

    korang the

    Well here's a new development. If I run both statements in the same batch, _then_ the temp table is persistent and can be accessed in subsequent executions (still within the same connection, of course).

    Notably this behavior is _not_ shared by other users. They continue to get the "table not found".

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not sure if you have posted this, but have you tried QA against a SQL 2005 db?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Sep 2010
    Posts
    10
    Quote Originally Posted by pootle flump View Post
    Not sure if you have posted this, but have you tried QA against a SQL 2005 db?
    Good idea! ...and same thing.

    Server: Msg 208, Level 16, State 1, Line 1
    Invalid object name '#TEMP'.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •