Job does not wait for stored procedure to finish

dataservices job design
1

I have imported an sql server 2008 stored procedure as a function. I am able to call the function in a script, and I can see from the log (and from trace) that the stored procedure executes. At some point during execution, data services thinks the stored procedure has finished and completes the job. I can tell from specific tables in the datastore that the stored procedure has not finished, and it never does finish.

Data services is returning ACTA_SP_OK for the return status and NULL for the error message. As far as data services knows, everything is fine.

I have tried calling the stored procedure two different ways:

.DBO.<sp_name>(null, $return_cd, $error);

and

sql(‘datastore’, ‘DECLARE @return_value int; DECLARE @param1 nvarchar(50); EXEC @return_value = dbo.sp_name @param1 = NULL; SELECT ‘Return Value’ = @return_value;’);

Both methods have the same result. I have confirmed with an ODBC trace that the stored procedure is running. I have confirmed that the user/pass combo I am using in the datastore has permissions to fully execute the stored procedure. I have created other stored procedures similar to the one in question and successfully run them in data services.

Anyone seen this kind of issue before? I’m going a little crazy. Thanks.

chadbrogan@ccbcu.com (BOB member since 2012-07-11)

2

Is it possible that the stored procedure is returning multiple result sets and DS thinks the stored procedure is complete after the first result set?

eganjp :us: (BOB member since 2007-09-12)

3

We figured it out.

The developer was printing massive SQL statements to the message window so he could troubleshoot the SP quickly. This worked fine inside a native SQL client, but the ODBC driver was bombing out as soon as the SP tried to print a 200 line sql statement.

I had the developer remove all the messages and the SP started working perfectly.

Of course, I had to do a SQL trace to figure all this out. My apologies for forgetting about this post. Thanks for the reply.

chadbrogan@ccbcu.com (BOB member since 2012-07-11)

4

Thanks for the update. Glad to hear you resolved the issue.

eganjp :us: (BOB member since 2007-09-12)