Why sql server didn't find my temp table

  • 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','',''

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

    John

  • 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/

  • 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

  • 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]

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

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