(from the
SoftwareAppreciation desk...)
Consider the following statement going down an ODBC connection to SQL Server:
[]
select 5 as yo into #Yo
insert into #Yo
select 6 as yo
The ODBC driver uses a special prepare/execute model to help enhance performance. This model was introduced in 6.5, but could be turned off against these databases. Against a 7 or 2k database, however, it's no longer an option, it just is.
The ODBC driver takes the above statement and generates the following SQL:
[]
declare @P1 int
set @P1=NULL
exec sp_prepare @P1 output, NULL, N'
select 5 as yo into #Yo
insert into #Yo
select 6 as yo
', 1
select @P1
exec sp_execute @P1
Well, this yields an error when executed. The sp_prepare fails this because it can't compile the second half of the SQL statement because the temp table #Yo doesn't exist at compile time, even though the first half of the statement will create it.
Unfortunately, the easy workaround, breaking the single statement of two queries into two separate statements fails as well. In this case sp_execute-ing the 2nd statement fails because the temp table no longer exists.
Why is this, though - we're using the same connection, right? And local temp tables have a scope related to the connection. Well, I can't exactly recall the detailed explanation, but because the SELECT INTO statement is actually not run directly, it's passed into the sp_execute stored proc, the scope of the table is limited to the scope of sp_execute, not the ODBC connection. So, when the 1st sp_execute that creates the temp table is done, the table is dropped automatically, causing the 2nd sp_execute to fail as well.
This code illustrates the first problem:
[]
use tempdb
go
declare @P1 int
set @P1=NULL
exec sp_prepare @P1 output, NULL, N'
select 5 as yo into #Yo
insert into #Yo
select 6 as yo
', 1
select @P1
exec sp_execute @P1
-- show user temp tables
select * from sysobjects where xtype = 'U'
This replicates what the ODBC driver does when asking to simply the SQL portion of the sp_prepare call.
But this fails (SQL 7 & 2000):
[]
Server: Msg 208, Level 16, State 1, Line 4
Invalid object name '#Yo'.
Server: Msg 8180, Level 16, State 1, Procedure sp_prepare, Line 4
Statement(s) could not be prepared.
Server: Msg 8179, Level 16, State 1, Procedure sp_execute, Line 12
Could not find prepared statement with handle 0.
“Statement(s) could not be prepared” because the temp table doesn't exist for the insert statement to compile.
What's really interesting is that this tweak to the insert statement causes this to suddenly work (sql 7 sp 3):
[]
use tempdb
go
declare @P1 int
set @P1=NULL
exec sp_prepare @P1 output, NULL, N'
select 5 as yo into #Yo
insert into #Yo values (6)
', 1
select @P1
exec sp_execute @P1
select * from sysobjects where xtype = 'U'
To continue the magic show, change the insert statement by putting the values clause on its own line and it suddenly stops working again:
[]
use tempdb
go
declare @P1 int
set @P1=NULL
exec sp_prepare @P1 output, NULL, N'
select 5 as yo into #Yo
insert into #Yo
values (6)
', 1
select @P1
exec sp_execute @P1
select * from sysobjects where xtype = 'U'
Wow, what a weird glitch.
Now to demonstrate the easy workaround of breaking two queries in a single statement into two separate statements. The thought is the first statement will complete, the temp table will now exist (because the connection creating it hasn't been dropped) and the insert statement can compile and execute:
[]
use tempdb
go
declare @P1 int
set @P1=NULL
exec sp_prepare @P1 output, NULL, N'select 5 as yo into #Yo'
select @P1
exec sp_execute @P1
set @P1=NULL
exec sp_prepare @P1 output, NULL, N'insert into #Yo select 6 as yo'
select @P1
exec sp_execute @P1
select * from sysobjects where xtype = 'U'
Nope - the temp table is dropped after the first sp_execute, so the 2nd statement is still screwed:
[]
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#Yo'.
Server: Msg 8180, Level 16, State 1, Procedure sp_prepare, Line 9
Statement(s) could not be prepared.
This whole mess goes away in SQL 2k. I'm not exactly sure why. It might be because the ODBC driver now uses sp_prepexec combining the two separate calls into one. I thought it might be deferred name resolution, but that seems to have been introduced in SQL 7 and relates to stored procs being created, not parsing the query inside sp_prepare.
Unfortunately, I've got a bunch of SQL written successfully against 2k that I know need to revisit to get it working on SQL 7.
tags: ComputersAndTechnology
