Posted by Bill_DBA on 12/11/06 15:43

Thanks Dan,

We had a stored procedure to extract for a fixed field file sending to
Peoplesoft on Oracle. The DTS was to map the resultset into the fixed
field file. I just thought if I could log the debug prints for this DTS
job because we run it every week. Upon further testing, even had I do
the PRINTs after the Select in the stored proc, neither DTS nor the SQL
job scheduler could channel those debug prints from the stored
procedure because there are too many re-direction.

I have decided to use BCP over DTS to create the fixed field file. And
call the stored procedure directly from the job scheduler.

Cursor processing in my sample code was indeed a moot step. It was used
to illustrate the PRINT and CURSOR combo problem.

Bill.

Dan Guzman wrote:
> > As you'd see, I have SET NOCOUNT ON in the code. Has anyone
> > experienced this? Is this a known bug? This occurs in SQL Server 2000
> > running on Windows Server 2003.
>
> I haven't run into this before but I can see how PRINT statements in a
> transformation task could confuse DTS. I did a little experimenting and
> found that DTS was fine as long as the PRINT statements ran after the SELECT
> statement that returned the proc result. If you must have the debug
> functionality, consider saving the messages into local variables for
> printing after the result set is returned.
>
> Separately, I don't understand the purpose of the cursor here. It seems
> tome that the entire body of the proc could be replace with the query below.
>
> SELECT
> EmployeeID AS emp_id,
> lastname
> FROM Employees
> WHERE @cur = 1
>
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP

[Back to original message]