CODEWITHSUNDEEP
sql-servert-sqltemp-tables
user2970010
user2970010

Reputation: 21

Need help. Message says temp table doesn't exist: 'Invalid object name '#TempCodes'.'

If I run the select statement below by itself (with the table name hard coded in) it runs fine and the temp table is created. If I run it as below it says 'Invalid object name '#TempCodes'' although when I print @Sql1 it looks exactly the same as when I run it by itself (with the table name hard coded in).

Any ideas on what's going on here will be appreciated.

DECLARE @TableName NVARCHAR(50)
SET @TableName = '[My Table Name]'
DECLARE @Sql1 NVARCHAR(MAX);
SET @Sql1 = N'SELECT  AccountNumber,LTRIM(RTRIM(m.n.value(''.[1]'',''varchar(8000)''))) AS mdcodes INTO #TempCodes FROM (SELECT AccountNumber,CAST(''<XMLRoot><RowData>''
    + REPLACE(MD_Results,'','',''</RowData><RowData>'') 
    + ''</RowData></XMLRoot>'' AS XML) AS x FROM  ' + @TableName 
    + N')t CROSS APPLY x.nodes(''/XMLRoot/RowData'')m(n)'

IF OBJECT_ID('tempdb.dbo.#TempCodes', 'U') IS NOT NULL 
BEGIN
   drop table  #TempCodes
END 

EXECUTE sp_executesql @Sql1
Select * from #TempCodes

Upvotes: 0

Views: 1501

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93754

I believe ## is a typo. Even if you fix it to # it will throw same error.

EXECUTE sp_executesql @Sql1

A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished.

In your case sp_executesql is the stored procedure.

the table created inside the dynamic query will be dropped after the exec sp_executesql is completed. That's why you are getting that error.

You need to create table outside and use Insert into table..select syntax inside the dynamic query

IF OBJECT_ID('tempdb.dbo.#TempCodes', 'U') IS NOT NULL 
   drop table  #TempCodes

create table #TempCodes(AccountNumber varchar(100),mdcodes varchar(100))

SET @Sql1 = N'Insert into #TempCodes
              SELECT  AccountNumber,LTRIM(RTRIM(m.n.value(''.
[1]'',''varchar(8000)''))) AS mdcodes FROM (SELECT 
AccountNumber,CAST(''<XMLRoot><RowData>'' + 
REPLACE(MD_Results,'','',''</RowData><RowData>'') + ''</RowData></XMLRoot>'' 
AS XML) AS x FROM  '+@TableName+ N')t CROSS APPLY 
x.nodes(''/XMLRoot/RowData'')m(n)'

Upvotes: 1

VDK
VDK

Reputation: 583

Try using a global temp table ##TempCodes as local temporary tables are only visible in current session.

DECLARE @TableName NVARCHAR(50)
SET @TableName = '[My Table Name]'
DECLARE @Sql1 NVARCHAR(MAX);
SET @Sql1 = N'SELECT  AccountNumber,LTRIM(RTRIM(m.n.value(''.
[1]'',''varchar(8000)''))) AS mdcodes INTO ##TempCodes FROM (SELECT 
AccountNumber,CAST(''<XMLRoot><RowData>'' + 
REPLACE(MD_Results,'','',''</RowData><RowData>'') + ''</RowData></XMLRoot>'' 
AS XML) AS x FROM  '+@TableName+ N')t CROSS APPLY 
x.nodes(''/XMLRoot/RowData'')m(n)'
IF OBJECT_ID('tempdb.dbo.##TempCodes', 'U') IS NOT NULL 
            BEGIN
            drop table  ##TempCodes
            END 
EXECUTE sp_executesql @Sql1

Select * from ##TempCodes

Upvotes: 0

Related Questions