SQL Server Import and Export Wizard gives an invalid object error for a stored procedure by using the temp table

Question

I created a stored procedure that creates a temporary table, inserts, then selects, then crashes. Running a stored procedure in SQL Server Management Studio works great and gives the expected result.

CREATE PROCEDURE usp_TempTableTest -- Add the parameters for the stored procedure here @color VARCHAR(10) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; CREATE TABLE #tmptable ( color VARCHAR(10) ) INSERT INTO #tmptable (color) VALUES (@color) SELECT color FROM #tmptable DROP TABLE #tmptable END GO 

However, when creating the Import / Export tool and using the saved proc as a data source, this gives me an error:

Invalid object name '#tmptable'.

Any idea why this will happen? If I change it to a table variable, it seems to work fine with Import / Export, but I don’t understand why it does not work with the temporary table.

Answer 1

When I run a matched stored procedure, for example, above, in SSMS, I can get the data, as you mentioned in the procedure. However, if I try #tmptable , just like you, I also get the same error, because DROP TABLE removes it. From what I can say, import / export is basically the final INSERT process. The reason it works with a table variable is because the data still exists in the last insert; in the case of DROP TABLE this is not so. For example, when I delete a DROP TABLE , it works.

Maybe I'm wrong, but it’s logical when importing or exporting in the case of the above procedure

INSERT data

SELECT data

DROP data

INSERT (import / export): this generates "Invalid tmptable object name" "

With a variable (or no DROP ), this

INSERT data

SELECT data

INSERT (import / export)

In the second case, the data still exists. In the first case, they disappeared. In one case, if you want to use #tmptable, run your code with

 IF OBJECT_ID('tempdb..#tmptable') IS NOT NULL DROP TABLE #tmptable 

Answer 2

Put "SET FMTONLY OFF"; right above "SET NOCOUNT ON"

http://msdn.microsoft.com/en-us/library/ms173839.aspx