-
HELP - Temp table names
I am using 7.0 and trying to use global temp tables to help create a cross tab query. The problem I am finding is that when the table table name contains a 0 in the name it will not build dynamic sql statements.
Example output from Trace files.
These are the same exact code with the same parameters called for both.
set @s_tab1 =@s_tab + '7'
Event Class Text Database ID Object ID Server Name Binary Data Start Time
+SP:StmtStarting exec('select IDENTITY (int, 1, 1) AS rownum,* into ' + @s_tab1 + ' from ' + @s_tab) InternetReports it_BusCatCity IRDEV 15:55:49.270
THIS CASE DOES NOT WORK, FOR SOME REASON IT DOES NOT EXECUTE THE STATEMENT
+SP:StmtStarting exec('drop table ' + @s_tab) set InternetReports it_BusCatCity IRDEV 15:55:49.270
+SP:Starting Dynamic SQL InternetReports IRDEV 15:55:49.270
+SP:StmtStarting drop table ##MJP5570 InternetReports 131366573 IRDEV 15:55:49.270
Event Class Text Database ID Object ID Server Name Binary Data Start Time
+SP:StmtStarting @s_temp ='select * into ' + @s_tab + ' from ' + @s_tab1 + ' where rownum >' + str(@i_firstrec) + ' and rownum < '+ str(@i_lastrec) InternetReports it_BusCatCity IRDEV 16:01:01.940
+SP:StmtStarting exec(@s_temp) InternetReports it_BusCatCity IRDEV 16:01:01.940
+SP:Starting Dynamic SQL InternetReports IRDEV 16:01:01.950
+SP:StmtStarting select * into ##MJP1287 from ##MJP1287MJ where rownum > 0 and rownum < 16 InternetReports 831921532 IRDEV 16:01:01.950
+SP:Completed Dynamic SQL InternetReports IRDEV 16:01:02.210
+SP:StmtStarting exec('drop table ' + @s_tab1) InternetReports it_BusCatCity IRDEV 16:01:02.210
+SP:Starting Dynamic SQL InternetReports IRDEV 16:01:02.210
+SP:StmtStarting drop table ##MJP1287MJ InternetReports 566890076 IRDEV 16:01:02.210
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|