Posted by Dan Guzman on 12/09/06 18:10

> 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

"Bill_DBA" <bill8575_1998@yahoo.com> wrote in message
news:1165592642.882620.279480@n67g2000cwd.googlegroups.com...
>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]