SQL Server Import and Export wizard gives invalid object error on stored procedure with temp table -

SQL Server Import and Export wizard gives invalid object error on stored procedure with temp table -

i created stored proc creates temp table, inserts, selects drops. executing stored proc within sql server management studio works fine , gives expected result.

class="lang-sql prettyprint-override">create procedure usp_temptabletest -- add together parameters stored procedure here @color varchar(10) begin -- set nocount on added prevent result sets -- interfering select statements. set nocount on; create table #tmptable ( color varchar(10) ) insert #tmptable (color) values (@color) select color #tmptable drop table #tmptable end go

however, when creating in import/export tool , using stored proc info source, gives me error:

invalid object name '#tmptable'.

any thought why happen? if alter table variable seems work fine import/export, don't understand why not working temp table.

when run mimicked stored procedure, yours above, in ssms, can info returned mentioned in procedure. however, if seek #tmptable, did, same error because drop table removes it. can tell, import/export final insert process. reason works table variable because info still exist on final insert; in case of drop table, not. instance, when remove drop table, works.

i might wrong here, seems logic when it's import or export in case of above procedure is

insert data

select data

drop data

insert (import/export): generates "invalid object name tmptable'"

with variable (or no drop), it's

insert data

select data

insert (import/export)

in sec case, info still exist. in first case, they're gone. 1 way around if want utilize #tmptable, start code with:

if object_id('tempdb..#tmptable') not null drop table #tmptable

sql sql-server