Issue with SQL Server Stored Procedure
Posted: Fri Sep 12, 2008 2:54 pm
Greetings-
I have a DataStage Server job that I created that calls a stored procedure in MS SQL Server 2005. For some reason none of us can figure out, the Procedure acts differently when executed from MS SQL Server Management Studio versus from DataStage - the same account was used in both instances.
The SP is fairly simple, it does the following:
1, Creates a cursor from a simple select statement
2, Opens the cursor
3, Fetchs a record from the cursor into local variables
4, Truncates a log table
5, Loops over the records in the cursor
5a, Updates a column in a table based on the value from the cursor
5b, Immediately after the update statement, it saves @@ERROR into a local variable
5c, Tests the local variable to see if the value is NOT EQUAL to zero
If True
Inserts a record into the log table
Calls SQL Server RAISERROR
5d, Fetch the next record
6, Close the cursor
To test the error handling of the SP, we altered the column the SP is attempting to update (5a above) by decreasing its size so that some records would fail due to truncation errors. When I execute the SP from MS SQL Server Management Studio, the SP detects the records that would be truncated, inserts a record into the log table and the RAISERROR event occurs. However, when the SP is run from DataStage no records are written to the log table - it's as if the SP never goes into the error handling block - its as if the errors don't exist!
In the DataStage Job the first stage is a Dynamic RDBMS stage. In the "Before" SQL tab, I have a call to the MS SQL Server Stored Procedure. In the User-defined tab, I have a select count statement which checks to see if we have any records in the error log table. If so, it passes the record count to a sequential file stage.
I initially had the DataStage job setup to call the SP using an ODBC stage using "Update action"-"Call Stored Procedure" but that produced the same results so we tried changing it and calling the SP from a Dynamic RDBMS stage
I'm hoping someone hear has run across this and can provide some guidance because I really need to get this working for an upcoming release. If anyone has any questions, or wants to see the source code, please let me know!
Thanks!
I have a DataStage Server job that I created that calls a stored procedure in MS SQL Server 2005. For some reason none of us can figure out, the Procedure acts differently when executed from MS SQL Server Management Studio versus from DataStage - the same account was used in both instances.
The SP is fairly simple, it does the following:
1, Creates a cursor from a simple select statement
2, Opens the cursor
3, Fetchs a record from the cursor into local variables
4, Truncates a log table
5, Loops over the records in the cursor
5a, Updates a column in a table based on the value from the cursor
5b, Immediately after the update statement, it saves @@ERROR into a local variable
5c, Tests the local variable to see if the value is NOT EQUAL to zero
If True
Inserts a record into the log table
Calls SQL Server RAISERROR
5d, Fetch the next record
6, Close the cursor
To test the error handling of the SP, we altered the column the SP is attempting to update (5a above) by decreasing its size so that some records would fail due to truncation errors. When I execute the SP from MS SQL Server Management Studio, the SP detects the records that would be truncated, inserts a record into the log table and the RAISERROR event occurs. However, when the SP is run from DataStage no records are written to the log table - it's as if the SP never goes into the error handling block - its as if the errors don't exist!
In the DataStage Job the first stage is a Dynamic RDBMS stage. In the "Before" SQL tab, I have a call to the MS SQL Server Stored Procedure. In the User-defined tab, I have a select count statement which checks to see if we have any records in the error log table. If so, it passes the record count to a sequential file stage.
I initially had the DataStage job setup to call the SP using an ODBC stage using "Update action"-"Call Stored Procedure" but that produced the same results so we tried changing it and calling the SP from a Dynamic RDBMS stage
I'm hoping someone hear has run across this and can provide some guidance because I really need to get this working for an upcoming release. If anyone has any questions, or wants to see the source code, please let me know!
Thanks!