Why sql server didn't find my temp table

Question

Post reply

Why sql server didn't find my temp table

bkshn

Right there with Babe

Points: 794
More actions
December 3, 2013 at 6:53 am

#301043

Hello
I define a temp table to fill with execution of stored procedure, but I have an error that
Invalid object name '##UserMidListTable'
is my syntax in temp table wrong?
insert into ##UserMidListTable(Id ,Mid ,ValueMid ,CatParent ,[Enabled],LastUpdate ,Company)
exec UserMidList @userRkey,'20','0','0','10','1','2','',''

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

That means that that table doesn't exist. Please will you post the statement that you used to create the table?

John

Sean Lange SSC Guru Points: 286573 More actions · Answer 2

Did you mean to use a global temp table?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

samalex SSCertifiable Points: 5428 More actions · Answer 3

bkshn (12/3/2013)
Hello
I define a temp table to fill with execution of stored procedure, but I have an error that
Invalid object name '##UserMidListTable'
is my syntax in temp table wrong?
insert into ##UserMidListTable(Id ,Mid ,ValueMid ,CatParent ,[Enabled],LastUpdate ,Company)
exec UserMidList @userRkey,'20','0','0','10','1','2','',''

Are you creating the table first? If not just an 'Insert Into' won't create it for you, so it's saying the table doesn't exist. You can also query the TempDB database to verify the table is there:

SELECT * FROM TempDB.INFORMATION_SCHEMA.Tables where Table_Name = '##UserMidListTable'

If nothing is returned then whatever process you have that may be creating the table isn't working, or you have to add such a process if it doesn't exist.

HTH,

Sam

Aram Koukia SSCommitted Points: 1521 More actions · Answer 4

bkshn (12/3/2013)
Hello
I define a temp table to fill with execution of stored procedure, but I have an error that
Invalid object name '##UserMidListTable'
is my syntax in temp table wrong?
insert into ##UserMidListTable(Id ,Mid ,ValueMid ,CatParent ,[Enabled],LastUpdate ,Company)
exec UserMidList @userRkey,'20','0','0','10','1','2','',''

If you dont have the Temp Table created, you can use OPENROWSET to create the temp table with the returned rows from the Stored Procedure like this:

SELECT * INTO #YourTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;', 'EXEC YourStoredProc');

SELECT * FROM #YourTempTable

[font="Courier New"]Aram Koukia: http://www.koukia.ca[/font]