Issue with SQL Server Stored Procedure

msacks · Post by **msacks** » 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!

msacks · Post by **msacks** » Mon Sep 15, 2008 9:39 am

Wow, I feel like the Economics Teacher in Ferris Bueller Day off. Anyone? Anyone? Have I stumped the guru's here?

chulett · Post by **chulett** » Mon Sep 15, 2008 9:55 am

Sorry, don't know beans about SQL Server. And I don't see how a difference in calling 'tools' can alter the behaviour of the proc, assuming they are called in the same manner by the same user.

shamshad · Post by **shamshad** » Mon Sep 15, 2008 11:01 am

I am assuming the Stored Procedure doesn't take any input parameter. If that is the case then

Open the ODBC stage and click on OUTPUT > GENEARAL > STORED PROCEDURE. Select the stored procedure using the imported definition from Manager. First run it successfull and dump data in a txt file or peek it.

Also, after selecting the Stored Procedure there will be other tabs under OUTPUT, check the DDL generated for the SP within Data Stage and check to see the "Error Code" Table.

(1)Is the Stored Procedure returning any values that says that SP
successfully ran without errors?
(2) Are any exception being caught and returned to SP when it executes,
so that the error code can be trapped under "warning" and / or "fatal
error" in ODBC Stage?

msacks · Post by **msacks** » Mon Sep 15, 2008 11:50 am

Hi Shamshad-

I had the SP initially setup so it accepted one input parameter which was just a dummy parameter. The way I setup the Server Job was to first have a Dynamic RDBMS stage perform a dummy select statement on the MS SQL Server as follows: "SELECT 'X' " It then passed the result of this query ('X') thru a transformer and into the ODBC stage as an input parameter. So basically it called the SP, passed the "X" to it, but the SP never uses the passed value.

I've tried rewriting the Server job as you outlined so that the first stage is the ODBC stage calling the SP, but I can't get it to work. I keep getting the error "SQL statement has incorrect number of result columns.". I'm sure I'm doing something wrong, but since the SP doesn't return anything I'm not sure why DataStage is getting this error. If I call the SP from within MS SQL Server Management Studio and don't pass it a value it runs as expected.

Can you provide any insight as to how to setup the ODBC job so I can get past the error above?

chulett · Post by **chulett** » Mon Sep 15, 2008 12:01 pm

If the proc doesn't return anything, then I don't believe you can use it as a 'source'. If you just need to call it, then create a job that starts with a Transformer and point it to the ODBC or DRS stage that calls the proc.

You'll need to create a 'dummy' stage variable in the transformer so the job will compile, that and contraint it to only output 1 record (@OUTROWNUM=1) but that's where you can put your 'X' value that gets plugged into the proc, if needed.

msacks · Post by **msacks** » Mon Sep 15, 2008 1:17 pm

Hi Chulett-

That's how I had it setup, but based on shamshad's post I thought maybe I had it setup wrong.

Here's the output from running the job from SQL Server Management Studio:

Msg 8152, Level 16, State 13, Procedure CMI_Business_Unit, Line 43
String or binary data would be truncated.
The statement has been terminated.

(1 row(s) affected)
Msg 8152, Level 16, State 13, Procedure CMI_Business_Unit, Line 43
String or binary data would be truncated.
The statement has been terminated.

(1 row(s) affected)
Msg 8152, Level 16, State 13, Procedure CMI_Business_Unit, Line 43
String or binary data would be truncated.
The statement has been terminated.

(1 row(s) affected)

Msg 8152, Level 16, State 13, Procedure CMI_Business_Unit, Line 43
String or binary data would be truncated.
The statement has been terminated.

(1 row(s) affected)
Msg 50000, Level 18, State 1, Procedure CMI_Business_Unit, Line 59
8152
Msg 50000, Level 18, State 1, Procedure CMI_Business_Unit, Line 59
8152
Msg 50000, Level 18, State 1, Procedure CMI_Business_Unit, Line 59
8152
Msg 50000, Level 18, State 1, Procedure CMI_Business_Unit, Line 59
8152

Here's the output from running the job from DataStage:
Starting Job Exec_CMI_Business_Unit_Procedure.
CMI_TGT_DB = CMI-Dev
CMI_TGT_USR = dsadm
CMI_TGT_PWD = *******

Environment variable settings:
(I'm leaving all these off unless anyone wants/needs to see them)

Exec_CMI_Business_Unit_Procedure..Transformer_86: DSD.StageRun
Active stage starting, tracemode = 0.

Exec_CMI_Business_Unit_Procedure..SOURCE: Connected to Microsoft
SQL Server, version 09.00.64523

Exec_CMI_Business_Unit_Procedure..SOURCE: Executing SQL
statement 'SELECT 'X''

Exec_CMI_Business_Unit_Procedure..ODBC_91: DSD.BCIConnect call to SQLConnect returned informational message.

Exec_CMI_Business_Unit_Procedure..Transformer_86: DSD.StageRun Active stage finishing.
1 rows read from DSLink88
1 rows written to DSLink89
0.040 CPU seconds used, 2.000 seconds elapsed.
Finished Job Exec_CMI_Business_Unit_Procedure.

Above in the section where I have the SQL Server Management Studio output, you'll see the message (1 row(s) affected) - that's where the SP is writing to the error log table. When I run the SP via DataStage, no records are ever written to the error log table.

This is very strange and I can't explain it.

chulett · Post by **chulett** » Mon Sep 15, 2008 1:22 pm

Neither can I.

msacks wrote:Exec_CMI_Business_Unit_Procedure..ODBC_91: DSD.BCIConnect call to SQLConnect returned informational message.

Seems that you may need to find out what the 'informational message' was. It may very well be the "String or binary data would be truncated" message noted in your post. Not quite sure how you do that, however.