Posted by Bill_DBA on 12/08/06 15:44

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

[Back to original message]