Interpreting a dynamic SQL code 2

[JCAD1]

Hi there,

I am new to dynamic SQL but have an urgent problem concerning a job that was created by someone who has since left the organisation. The job is scheduled to run every night but has been failing since the beginning of October. It consists of the script given below and I have no clue as to what the script is supposed to be doing. Could someone please explain to me line by line (if possible) what the script is doing. How can I figure out why it is falling over? Is there another way of writing the same script in a simple stored procedure that I can use?

Thank you very much for your help.

JCAD1

[blue]
DECLARE @ret int, @retMsg varchar(1024)
SET @retMsg = 'None'
Exec @ret = usp_DM_Stream_Process @iStreamName = 'TIARA',
@iProcessName = NULL,
@iReturnMsg = @retMsg OUTPUT

if @@ERROR <> 0 OR @ret<> 0
BEGIN
RAISERROR(@retMsg,16,1) WITH NOWAIT, SETERROR
END
[/blue]

 

[nickdel]

JCAD1, nothing wrong with that code, whats in usp_DM_Stream_Process? Have you tried running usp_DM_Stream_Process manually to see what the error is?

Nick

 

[gmmastros]

I don't consider this dynamic sql (at least not in the classic sense).

This code is simply calling another stored procedure.

[tt][blue]
DECLARE @ret int, @retMsg varchar(1024) [green]-- Declare variables[/green]
SET @retMsg = 'None' [green]-- Set variable value to None[/green]
[green]
-- The next execution line is spread over 3 line in the editor.
-- usp_DM_Stream_Process is a stored procedure that is being called.
-- this procedure has 3 arguments:
-- @iStreamName
-- @iProcessName
-- @iReturnMsg
--
-- @iReturnMsg is special in that it returns
-- a value.
[/green]
Exec @ret = usp_DM_Stream_Process @iStreamName = 'TIARA',
@iProcessName = NULL,
@iReturnMsg = @retMsg OUTPUT

[green]-- Check for error[/green]
if @@ERROR <> 0 OR @ret<> 0
BEGIN
RAISERROR(@retMsg,16,1) WITH NOWAIT, SETERROR
END
[/blue][/tt]

As nickdel pointed out, there is probably nothing failing in this code. But, since this code calls another stored procedure, the error is most likely caused in there.

You say that this is 'failing'. Is there an error message? If no error message, can you explain it what way it is failing? Also, show us the code for the usp_DM_Stream_Process procedure. With this information, we can probably help you determine why the process is failing.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JCAD1]

Thank you nickdel and gmmastros for pointing me in the right direction.

I am now manually running the stored procedure (usp_DM_Stream_Process ) that is being called by the code I posted earlier. If it fails or can't make sense of it, I will send you the code for the procedure.

Once again, thank you for your help.

Jcad1

 

[SQLSister]

DId this code only start failing after the person who created it left? Perhaps the job is running in his or her name and that name no longer has rights to the db.

"NOTHING is more important in a database than integrity." ESquared