Re: Mapping Output Parameters to DTS Global Variables

#1

Also I have found that this error happens when you don't have SET
NOCOUNT ON. So add SET NOCOUNT ON to the top of the SQL Execution Step.

Dick Campbell wrote:

Quote:

I have some Stored Procedures that insert and update data but return
no data
as such. I have added a select statement at the end of them to return
one
row, one column with the number of rows processed (Select @@RowCount
as
RowsProcessed).

This was intended to allow DTS to map the values to Global Variables
for
logging but it just doesn't seem to work.

If I have a very simple SP that simply returns a value in a rowset,
DTS
seems to be able to map the value to a GV. When I insert the code
that does
the work in front of this, I see a single row/column with the
rowcount in
Query Analyzer but DTS doesn't map the value to a GV.

I have tried using an Output Parameter in the SP but that didn't work
either. The Execute SQL Task doco on Output Parameters is actually
quite
confusing. It seems to regard the columns of a returned rowset as
Output
Parameters.

I have also tried returning the entire rowset to a GV and using VBS
to
retrieve it, but even using the code suggested in BOL, I get an error
saying
that the recordset is not an object. Something <not displayable> does
get
written to the GV.

Very frustrating

Any hints are welcome.

Dick Campbell

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Mapping Output Parameters to DTS Global Variables

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: Mapping Output Parameters to DTS Global Variables - 01-04-2005 , 05:37 PM