I have the following stored procedure that is called from the source of
a transformation in a DTS package. The first parameter turns on PRINT
debug messages. The second, when equals 1, turns on the openning,
fetching, and closing of a cursor. Things are fine if only one of the
two parameters was set to 1.

When run with both parameters turned on, "dba_test_dts 1, 1", DTS
source (tab) preview fails because it thinks no row was returned. "No
rowset was returned from the SQL statement". Understanbly then the
transformation step would also fail with the "Invalid Pointer" error.

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.
-----------------------------------------------------------------------------------------------------------------
CREATE PROC dba_test_dts ( @debug BIT = NULL, @cur BIT = NULL )

AS

-- Always have these 2 options set or unset so DTS would not error out
-- with the Invalid Pointers message.

SET NOCOUNT ON

SET ANSI_WARNINGS OFF

DECLARE @FMT_FILE_NAME VARCHAR(256)
DECLARE @OUTPUT_FILE_NAME VARCHAR(256)

DECLARE @emp_id INT
DECLARE @lastname VARCHAR(70)

IF ( @debug = 1 )
BEGIN
PRINT '=== BEGIN ==='
PRINT 'Stored Procedure dts_calling_stored_proc'
PRINT 'Begin timestamp: ' + CONVERT(VARCHAR(32), CURRENT_TIMESTAMP,
109 )
PRINT 'Server : ' + @@SERVERNAME
PRINT 'Host Name/ID : ' + HOST_NAME() + '/' + CONVERT(VARCHAR(32),
HOST_ID())
PRINT 'Database : ' + DB_NAME()
PRINT 'User/NT User : ''' + USER_NAME() + '''/''' + SYSTEM_USER +
''''
PRINT 'DEBUG FLAG : ' + CONVERT( CHAR(1), @DEBUG )
PRINT '=== BEGIN ==='
PRINT SPACE(1)
END

IF ( EXISTS ( SELECT 1 FROM sysobjects WHERE
id=object_id(N'Employees_temp') ) )
DROP TABLE Employees_temp

CREATE TABLE Employees_temp
(
emp_id INTEGER
, lastname VARCHAR(70)
)

INSERT INTO Employees_temp
(
[emp_id]
,[lastname]
)
SELECT EmployeeID
, lastname
FROM Employees

IF ( @cur = 1 )
BEGIN

DECLARE curEmp CURSOR FOR
SELECT emp_id
, lastname
FROM Employees_temp

OPEN curEmp

FETCH NEXT FROM curEmp
INTO
@emp_id, @lastname

WHILE ( @@FETCH_STATUS = 0 )
BEGIN
FETCH NEXT FROM curEmp
INTO
@emp_id, @lastname
END

CLOSE curEmp
DEALLOCATE curEmp

END

SELECT emp_id
, lastname
FROM Employees_temp

GO

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

"Bill_DBA" <bill8575_1998 (AT) yahoo (DOT) com> wrote

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: