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_d ts 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_NA ME VARCHAR(256)
DECLARE @emp_id INT
DECLARE @lastname VARCHAR(70)
IF ( @debug = 1 )
BEGIN
PRINT '=== BEGIN ==='
PRINT 'Stored Procedure dts_calling_sto red_proc'
PRINT 'Begin timestamp: ' + CONVERT(VARCHAR (32), CURRENT_TIMESTA MP,
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
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_d ts 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_NA ME VARCHAR(256)
DECLARE @emp_id INT
DECLARE @lastname VARCHAR(70)
IF ( @debug = 1 )
BEGIN
PRINT '=== BEGIN ==='
PRINT 'Stored Procedure dts_calling_sto red_proc'
PRINT 'Begin timestamp: ' + CONVERT(VARCHAR (32), CURRENT_TIMESTA MP,
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