问题描述:

I created a stored proc that creates a temp table, inserts, selects then drops. Executing the stored proc within SQL Server Management Studio works fine 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 in the Import/Export tool and using that stored proc as the data source, it gives me the error:

Invalid object name '#tmptable'.

Any idea why this would happen? If I change it to a table variable it seems work fine with Import/Export, but I don't understand why it is not working with a temp table.

网友答案:

When I run a mimicked stored procedure, like yours above, in SSMS, I can get the data returned like you mentioned in the procedure. However, if I try the #tmptable, like you did, I also get the same error because the DROP TABLE removes it. From what I can tell, the import/export is basically a final INSERT process. The reason it works with the table variable is because the data still exist on the final insert; in the case of the DROP TABLE, it does not. For instance, when I remove the DROP TABLE, it works.

I might be wrong here, but it seems the logic when it's an import or export in the case of the above procedure is

INSERT data

SELECT data

DROP data

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

With the variable (or no DROP), it's

INSERT data

SELECT data

INSERT (import/export)

In the second case, the data still exist. In the first case, they're gone. One way around it if you want to use the #tmptable, start your code with:

IF OBJECT_ID('tempdb..#tmptable') IS NOT NULL DROP TABLE #tmptable
网友答案:

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

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

相关阅读:
Top