Using DatabaseRetrieve / DatabaseGet w/stored proc MS SQL

I have JAL code that looks like:

Dim jobId string "@V"job_id""
Dim jobName string "@V"job_name""
Dim msg string
Dim sql string
Dim dbResult number
//Open database connection
DatabaseConnect "HRoffice"
DatabaseExecute "SET ANSI_NULLS ON" dbResult
DatabaseExecute "SET ANSI_WARNINGS ON" dbResult
//Do JCT employees
Set sql "EXEC xJCT_SyncEmployees 6,6"
DatabaseRetrieve sql dBResult
DatabaseGet 1 1 msg
ConcatEx "JCT sync complete :: " msg msg
LogAddMessageEx "INFO" jobId jobName msg
//Close database connection
DatabaseDisconnect

As you can see, I am using DatabaseRetrieve to run a stored procedure. The stored procedure is of the type that returns a rowset and the lone column in its output is a status message (e.g. "324 records updated; 2 records inserted"). So I use DatabaseGet to retrieve the message

From the internal logging produced by the stored procedure, it appears that both the DatabaseRetrieve and the DatabaseGet cause the stored procedure to be run. (The stored procedure inserts records into a log table that notes the elapsed time of each run of the procedure.)

Is there anyway to get the stored procedure to run just once and still retrieve the status message from its output rowset?

I think its DatabaseRetrieve that might be causing the double run, first one is caused by the executing the code and the second is by opening some internal cursor to get the data.

I suggest to try modifying the code in such a way that execute and retrieve become separate operation, using the following logic

DatabaseExecute "CREATE TABLE #temp (msg varchar(2000)); INSERT INTO #temp EXEC xJCT_SyncEmployees 6,6", dBResult
DatabaseRetrieve "SELECT msg FROM #temp", dBResult
DatabaseGet 1, 1, msg
DatabaseExecute "DROP TABLE #temp", dBResult

BTW, please use comma-separated parameter lists in your code. Space-separated parameters is a legacy syntax which likely won't be supported in future versions of JAL.

Thanks for the speedy reply! I'll try the workaround you suggest.

And thanks for the heads-up about the space-separated parameters. In the early days (5 or 6 years ago) when I used 24x7, I used to use comma-separated params because things just looked weird otherwise. But then I got used to the convienience/laziness of space-separated params. When the time comes that space-separated lists are no longer okay, I hope you will supply a conversion tool that will upgrade existing code as we have hundreds of lines of space-separated params!

Okay. With one more modification, it worked. Because the stored proc being called contained an INSERT...EXEC, I was getting an error:

Sync_EM001 An error occurred while executing automation script: Line 38: An INSERT EXEC statement cannot be nested.

So I changed the INSERT statement slightly--to use a linked server that points to the local database. This fools SQL and lets the insert happen!

DatabaseExecute "INSERT INTO #temp (msg) SELECT StatusMsg FROM openquery(LOCAL_HR,'SET FMTONLY OFF EXEC xJCT_SyncEmployees 9,9')",dbResult

Great! Thanks for the tip. The described solution might help other people facing similar issues..

One more thing--can you edit the Subject of the thread to say "Using DatabaseRetrieve..."? Looks like I got my fingers tangled up in the initial posting--or maybe something happened with the javascript engine in the Chrome browser I am using. Even here as I type, I see occassional missing characters that later appear. Don't know what's up with that, but I've never seen that behavior with IE.

Thanks again for your help today!

I corrected the first word in the subject. Thank you.