I have built a stored procedure that returns a result set I want to
use in DTS to populate a table, but on the Transformations tab I see
no Source columns?

The procedure uses data from several tables to populate a temp table,
which is then joined to other tables. Nothing very fancy...

On the source tab, I simply put:
"exec my_proc"

On the Destination tab I chose a Destination table, I go to the
Transformations tab, I have no source columns to choose from.

I know the procedure is OK; it runs in Query Analyzer and with the
Preview button on the Source tab I see my result set.

What could be wrong?

Thanx in advance,

Bart

Make sure the first thing in the proc is

SET NOCOUNT ON

Let us know if this works

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"pipo" <pipo1 (AT) ilse (DOT) nl> wrote

Thanx for your reply.
The nocount was on already.

But I figured it out: the proc put data in a temp table, to get it out
later.
For performance reasons, DTS surrounds the SQL with

SET FMTONLY ON
EXEC my_proc
SET FMTONLY OFF

And if the procedure runs with FMTONLY ON, then there's no temp table
to do a select on...

Regards,
Bart


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote