October 14, 2008 at 9:24 am
Hi,
I have a temp table (#TempTable)
Why I created this is to insert and update the rows with different select queries.
IF I use SELECT * FROM #TempTable, its working well.
I want to append searchString variable to it.
So I have taken a variable to store the sql query and appending the searchstring.
@SqlQry = 'SELECT * FROM #TempTable ' + @SearchString
its giving error "Invalid object name #TempTable"
Whats the problem. What is the resolution.
thanks
October 14, 2008 at 1:53 pm
I take it you are trying to execute the SQL String using sp_execute or simply the EXECUTE() command? In this case I believe it opens a new connection and therefore cannot see your temp table because it's defined as a local scope (Other connections cannot see it).
you either need to create the table in your SQL string you are building, or change the name of the temp table to have 2 pound symbols, which makes it global to all connections, like CREATE TABLE ##tablename.
Which you choose to use depends on what kind of scope is desired in your temp table.
October 14, 2008 at 2:49 pm
I do not think that that is right, Adam. Try executing the following:SET NOCOUNT ON
create table #temp(foo int)
Insert into #temp select 13
Declare @sql Nvarchar(max)
SET @sql = 'SELECT * FROM #temp '
EXEC(@sql)
EXEC sp_ExecuteSql @sql
GO
drop table #temp
It returns the following output:foo
-----------
13
foo
-----------
13
Which seems to indicate that the #Temp table is visible and usable form both the EXEC(string) and sp_ExecuteSql() subordinate batches.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 14, 2008 at 2:50 pm
a2zwd:
Please post your actual code that is failing so that we can test it.
Thanks,
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 14, 2008 at 3:28 pm
good catch, I was under the assumption it used a new connection, which is obviously wrong. Generally when I see these errors (object not found on a temp table) I assume it is out of scope, because it has been the leading cause of those errors when I see them.
Yes, Please post your code so we can see what is being executed.
October 15, 2008 at 2:45 am
Hi,
Thanks for the replies. I am posting the code where I got error
CREATE TABLE #TempTable
(
Demand NUMERIC(8, 0),
Capacity NUMERIC(8, 0),
MonthAndYear VARCHAR(20)
)
SET @SqlQry = N'INSERT INTO #TempTable(Demand, Capacity, MonthAndYear)
SELECT COUNT(RECVD_DTTM) AS Demand, NULL,
CAST(DATENAME(m, RECVD_DTTM) AS VARCHAR(3)) + ''-'' + CAST(YEAR(RECVD_DTTM) AS VARCHAR(4)) AS MonthAndYear FROM temp_18ww_DemandCapacity ' + @WhereSearchString + '
GROUP BY CAST(DATENAME(m, RECVD_DTTM) AS VARCHAR(3)) + ''-'' + CAST(YEAR(RECVD_DTTM) AS VARCHAR(4)) '
EXEC sp_executesql @SqlQry
-- Get the total pending referrals and update the local table
SET @SqlQry = N'UPDATE tmp SET Capacity = t.TotalCapacity FROM #TempTable tmp INNER JOIN
(
SELECT COUNT(RECVD_DTTM) AS TotalCapacity,
CAST(DATENAME(m, RECVD_DTTM) AS VARCHAR(3)) + ''-'' + CAST(YEAR(RECVD_DTTM) AS VARCHAR(4)) AS MonthAndYear
FROM temp_18ww_DemandCapacity_WaitList
WHERE Wait_List_Type = ''PENDING'' ' + @SearchString + '
GROUP BY CAST(DATENAME(m, RECVD_DTTM) AS VARCHAR(3)) + ''-'' + CAST(YEAR(RECVD_DTTM) AS VARCHAR(4))
) t ON t.MonthAndYear = tmp.MonthAndYear'
EXEC sp_executesql @SqlQry
SELECT * FROM #TempTable
I am getting error where ever I used #TempTable except at CREATE TABLE statement.
whats the problem. Is there any other way to achieve this?
thanks
October 15, 2008 at 2:59 am
That's interesting, I also assumed it took a new connection.
If you create the temp table before executing the string, then (as Barry's code shows) the table is visible to the session taken by the executing string. But if the executing string creates the temp table, then the temp table is not visible to the original session:
[font="Courier New"]SET NOCOUNT ON
DECLARE @Sql NVARCHAR(100)
SET @Sql = 'SELECT TOP 1 [collation] INTO #temp FROM master.dbo.syscolumns'
EXEC(@Sql)
EXEC sp_executesql @Sql
SELECT * FROM #temp
"Server: Msg 208, Level 16, State 1, Line 9
Invalid object name '#temp'."
[/font]
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 15, 2008 at 3:51 am
Hi,
Thanks for the replies. I did with global temporary table.
Any how the SP will execute only once and as a job.
So, global temporary table is fair enough.
thanks again for the replies.
cheers
October 15, 2008 at 4:02 am
Code is working fine. No need for go global temp. table.
October 15, 2008 at 5:09 am
Chris Morris (10/15/2008)
That's interesting, I also assumed it took a new connection.If you create the temp table before executing the string, then (as Barry's code shows) the table is visible to the session taken by the executing string. But if the executing string creates the temp table, then the temp table is not visible to the original session:
[font="Courier New"]SET NOCOUNT ONDECLARE @Sql NVARCHAR(100)
SET @Sql = 'SELECT TOP 1 [collation] INTO #temp FROM master.dbo.syscolumns'
EXEC(@Sql)
EXEC sp_executesql @Sql
SELECT * FROM #temp
"Server: Msg 208, Level 16, State 1, Line 9
Invalid object name '#temp'."
[/font]
Cheers
ChrisM
In this case it should be visible on the same scope
DECLARE @sql NVARCHAR(100)
SET @sql = 'SELECT TOP 1 [collation] INTO #temp FROM master.dbo.syscolumns SELECT * from #temp'
EXEC(@Sql)
EXEC sp_executesql @sql
Failing to plan is Planning to fail
October 15, 2008 at 5:23 am
Madhivanan (10/15/2008)
Chris Morris (10/15/2008)
That's interesting, I also assumed it took a new connection.If you create the temp table before executing the string, then (as Barry's code shows) the table is visible to the session taken by the executing string. But if the executing string creates the temp table, then the temp table is not visible to the original session:
[font="Courier New"]SET NOCOUNT ONDECLARE @Sql NVARCHAR(100)
SET @Sql = 'SELECT TOP 1 [collation] INTO #temp FROM master.dbo.syscolumns'
EXEC(@Sql)
EXEC sp_executesql @Sql
SELECT * FROM #temp
"Server: Msg 208, Level 16, State 1, Line 9
Invalid object name '#temp'."
[/font]
Cheers
ChrisM
In this case it should be visible on the same scope
DECLARE @sql NVARCHAR(100)SET @sql = 'SELECT TOP 1 [collation] INTO #temp FROM master.dbo.syscolumns SELECT * from #temp'
EXEC(@Sql)
EXEC sp_executesql @sql
Which is functionally equivalent to missing the temp table out altogether:
DECLARE @sql NVARCHAR(100)
SET @sql = 'SELECT TOP 1 [collation] FROM master.dbo.syscolumns'
EXEC(@Sql)
EXEC sp_executesql @sql
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 15, 2008 at 7:21 am
a2zwd (10/15/2008)
Hi,Thanks for the replies. I did with global temporary table.
Any how the SP will execute only once and as a job.
So, global temporary table is fair enough.
Well, Global Temp tables are visible to all other processes and hang around until you explicitly drop them or you reboot, so they can cause a lot of problems and side-effects.
You really should be able to use normal Temp tables, and not being able to is a huge restriction. Could you please do us all a favor and try the script below and then tell us what you get? if you set your output mode to text, you should be able to just cut and paste the results in. Thanks:
SET NOCOUNT ON
create table #temp(TestFromTemp int)
Insert into #temp select 13
Print '
Base process/batch:'
Select @@PROCID
Select CAST(DB_NAME() as NVarchar(25)) as [DB_Name], *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME like '%Temp%'
UNION ALL Select 'TempDB' as [DB_Name], *
FROM TempDB.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME like '%Temp%'
Declare @sql Nvarchar(max)
SET @sql = ' Print ''ExecuteSQL Batch:''
Select @@PROCID
Select CAST(DB_NAME() as NVarchar(25)), *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME like ''%Temp%''
UNION ALL Select ''TempDB'' as [DB_Name], *
FROM TempDB.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME like ''%Temp%''
'
EXEC sp_ExecuteSql @sql
GO
drop table #temp
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply