using store procedures with temp tables on dts -- sql server dts

Russell Fields
4/12/2007 2:13:47 PM

Roy,

Not enough information to understand your problem. You might at least tell
us what error message you are getting.

Also, you imply that the 'export' produces the error message. What are you
actually doing when the message is raised?

Sharing your code (or a reasonable portion of it) can also be helpful to the
readers.

RLF
[quoted text, click to view]

ns_nataly
4/24/2007 5:44:02 AM

Hi Russell,

I'm using Stored Procedure as a source in Transform Data Task (TDT).
This SP supposed to return recordset (it's do if called from Query Analyser) .

declare @res int
exec debttrac.dbo.GetCampaignMailList_sms_lst 10, @res OUTPUT

When i click "Parse Query" in DTS TDT - i'm getting error : Invalid object
name #t
and it doesn't return anything if clicked "Preview"

Can you advise anything?

Thanks,
Natalia

[quoted text, click to view]

Russell Fields
4/24/2007 2:29:10 PM

Natalia,

I assume that GetCampaignMailList_sms_lst internally creates the #t table,
then makes use of it in further functions. Despite the parser error, it
will work fine in this case.

For example, my stored procedure:
create procedure UseTempTable
as
create table #q (a int)
insert into #q select id from sysobjects
select * from #q
return

When I parse this from a Execute SQL Task in DTS it complains that there is
no #q table. But when I run it, it runs fine. Since the #q table does not
exist until the code runs, the parser has nothing to match with it. But at
run time, the #q table is created and used.

RLF

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server dts

sql server dts : using store procedures with temp tables on dts