|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Thursday, May 09, 2013 4:13 AM
Points: 103,
Visits: 75
|
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: 2 days ago @ 12:39 PM
Points: 1,662,
Visits: 1,708
|
|
This is a very good back to basics question, thank you.
The behaviour of the exec in this case is identical to the one of the stored proc where the temp table created inside is descoped once the proc bails out. In other words, if create temp table part were moved out then the temp table would be visible to the exec batch and after, i.e.
if object_id('tempdb.dbo.#tmp') is not null drop table #tmp;
create table #tmp (id int);
declare @sql varchar(2000); set @sql = 'insert into #tmp(id) values (1);'; exec (@sql);
select * from #tmp; results in
id ----------- 1 Oleg
|
|
|
|
|
UDP Broadcaster
      
Group: General Forum Members
Last Login: Friday, May 10, 2013 3:15 AM
Points: 1,476,
Visits: 1,943
|
|
Good question but the answers are a bit... unfair. The 2 answers: Throw an error: Invalid object name '#tmp'. Throw an error: Cannot find table #tmp inside TempDB.
Have the same effect (no #tmp table). So you would also have to know the wording of the error SQL would throw in order to get it right... without running it.
/T
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: 2 days ago @ 9:52 AM
Points: 1,277,
Visits: 1,608
|
|
|
|
|
|
SSCrazy Eights
        
Group: General Forum Members
Last Login: Yesterday @ 6:54 AM
Points: 9,364,
Visits: 6,462
|
|
|
|
|
|
UDP Broadcaster
      
Group: General Forum Members
Last Login: Friday, May 10, 2013 3:15 AM
Points: 1,476,
Visits: 1,943
|
|
da-zero (12/10/2010) Nice question, thanks.
(but it surprises me that apparently 78% -for the moment- know the exact error message!)
Could have done what i did... ran the code. Now i knew that it wouldnt find the #tmp table but didnt know the exact error message (like i care about the exact wording of an errormessage). Cheating... maybe maybe not... it depends 
Like i wrote a bit up... the second error message about not being able to find #tmp shouldnt have been there.
/T
|
|
|
|
|
SSCertifiable
       
Group: General Forum Members
Last Login: Today @ 2:01 AM
Points: 5,230,
Visits: 7,020
|
|
da-zero (12/10/2010) (but it surprises me that apparently 78% -for the moment- know the exact error message!) Well, I don't know about others, but for me this is one of the more common errors I see when developing. Or when copying and pasting code snippets from internet support forums. I would not be able to reproduce it out of the blue, but I did recognise the "invalid object name" error, and I have not ever seen a message that even resembles the "cannot find ... inside TempDB" one. (plus, that one is extremely unlikely, as the database for temporary object is called tempdb, not TempDB - but I guess only people who are in the habit of developing on a case sensitive instance would know that).
Hugo Kornelis, SQL Server MVP Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354,
Visits: 1,299
|
|
I got it right but I had to think about this one.
I thought the error was going to be caused by not having a space between the two commands.
Set @strSql = @strSql + 'Create table #tmp (id int)' Set @strSql = @strSql + 'Insert into #tmp(id) values (1)'
This should produce the same as (Notice no spacing between two commands): Set @strSQL = 'Create table #tmp (id int)Insert into #tmp(id) values (1)' I knew this had to cause some error because there's no space or semicolon between the create and insert statements but didn't know the exact error that would be produced. I've never seen any of the other specific errors so I guessed correctly but for the wrong reason.
|
|
|
|
|
SSCertifiable
       
Group: General Forum Members
Last Login: Today @ 2:01 AM
Points: 5,230,
Visits: 7,020
|
|
cengland0 (12/10/2010)
This should produce the same as (Notice no spacing between two commands): Set @strSQL = 'Create table #tmp (id int)Insert into #tmp(id) values (1)' I knew this had to cause some error Except it doesn't. If you copy/paste the above in an SSMS query window and hit execute, you get "Command(s) completed successfully."
Hugo Kornelis, SQL Server MVP Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354,
Visits: 1,299
|
|
Hugo Kornelis (12/10/2010)
Except it doesn't. If you copy/paste the above in an SSMS query window and hit execute, you get "Command(s) completed successfully." Exactly. I try to answer the questions without executing the code so I thought the missing space would cause an error.
Remember, there are so many trick QOTD's so I'm always looking for the trick. I thought I found the trick and it was that a space was missing. The lack of the space actually had nothing to do with the error but I thought it did so I selected the error message I thought would have been generated and got it right but for the wrong reason.
|
|
|
|