temp table is not created when using sp_executesql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

temp table is not created when using sp_executesql

Author

Topic

chxxangie
Starting Member

16 Posts

Posted - 2012-02-13 : 22:01:26

i need to upload the excel content into temp table. now i am having problem in creating temp table.

in this code, the #tmpxls is able to create.


      SELECT * into #tmpxls
      FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
      'Excel 12.0;Database=C:\ALL.XLS;HDR=Yes',
      'SELECT * FROM [Report 1$]')

However, in this code, the #tmpxls is NOT able to create.


      SET @psExecSql = 
         'SELECT * INTO #tmpxls 
         FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',
         ''Excel 12.0;Database=' + @psFilePath + ';HDR=' + @in_psHeader + ''',
         ''SELECT * FROM [' + @in_psWSheetName + '$]'')'                             
      
      EXEC sp_executesql @psExecSql

Can someone pls help in this? thanks in advance!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-13 : 22:08:34

create the temp table outside of sp_executesql

Doing it inside sp_executesql, it will be created and drop when it is out of scope (out of sp_executesql)

KH
[spoiler]Time is always against us[/spoiler]

chxxangie
Starting Member

16 Posts

Posted - 2012-02-13 : 22:26:05

quote:
Originally posted by khtan

create the temp table outside of sp_executesql

Doing it inside sp_executesql, it will be created and drop when it is out of scope (out of sp_executesql)

KH
[spoiler]Time is always against us[/spoiler]

oh... now i know this RULE!

then i have to go to permanent table and drop it afterward.

thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-13 : 22:37:43

it is not necessary for temp table.

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 10:07:26

quote:
Originally posted by chxxangie

quote:
Originally posted by khtan

create the temp table outside of sp_executesql

Doing it inside sp_executesql, it will be created and drop when it is out of scope (out of sp_executesql)

KH
[spoiler]Time is always against us[/spoiler]

oh... now i know this RULE!
then i have to go to permanent table and drop it afterward.

thanks!

why so?
whats the issue in creating # table outside of dynamic sql?
you dont have to drop it each time as compared to permanent table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources