Output parameter returning NULL. Randomly.
-
The procedure I've posted below raises a job in our job control system. The job number is generated by an IDENTITY field, which is returned to the procedure by SCOPE_IDENTITY(). The procedure uses this as an INSERT to a couple of tables, using @JobNumber (the output parameter), which inserts the correct job number into these tables.
However, the calling code (VBScript in ASP using an ADODB Command object), is receiving NULL back for @JobNumber. Sometimes. Just started happening. Randomly. Other times it returns the job number without a problem.
As the proc is getting the correct value from SCOPE_IDENTITY() (I've verified this by checking data in OpenLink_JCOAJ and OpenLink_JCOAL), my initial thought was it was a problem in the ASP script, but this hasn't changed, worked right through test and fine on live up until now.
I did, however, change the procedure. I added the code to do the OpenLink inserts at the end (from and including IF @CustOrderNum = '' to the end of the proc). This is when the problem started.
Any ideas?
CREATE PROCEDURE dbo.sp_Control_Insert
@MaincdeCde varchar(4),
@SubcodeCde varchar(4),
@SiteRef varchar(4),
@CustOrderNum varchar(15),
@JobChgeable int,
@JobDsc varchar(131),
@JobStatus char(2),
@JobType char(2),
@InvoiceType char(2),
@ChargeoutRate decimal(6,2),
@QuoteRate decimal(8,2),
@QuoteDays decimal(5,2),
@Originator varchar(50),
@AddProd varchar(13),
@JobNumber int OUTPUT
AS
IF @CustOrderNum = 'noproject'
SELECT @CustOrderNum = ''
INSERT ControlTable (MaincdeCde,SubcodeCde,SiteRef,CustOrderNum,JobChgeable,JobDsc,
JobStatus,JobType,InvoiceType,ChargeoutRate,QuoteRate,QuoteDays,
Originator,QtdDevPeriod,QtdAvailDte,QtdSubDte,AddProd,ModemAmt,ReqdDte,JCUpdated)
VALUES (@MaincdeCde,@SubcodeCde,@SiteRef,@CustOrderNum,@JobChgeable,@JobDsc,
@JobStatus,@JobType,@InvoiceType,@ChargeoutRate,@QuoteRate,@QuoteDays,
@Originator,'','01/01/1900','01/01/1900',@AddProd,0,'01/01/1900',0)
SELECT @JobNumber = SCOPE_IDENTITY()
IF @CustOrderNum = ''
SELECT @CustOrderNum = dbo.ToFour(@SiteRef) + '/'
INSERT OpenLink_JCOAJ (Workstage, Abbname, Name)
VALUES (@JobNumber, LEFT( REPLACE(@JobDsc,';',''), 15), LEFT( REPLACE(@JobDsc,';',''), 30))
INSERT OpenLink_JCOAL (ProjCode, Workstage)
VALUES (@CustOrderNum, @JobNumber)
GO
-
I don't see anything that could make this fail.
What about the application side of the code?
Have you reran the proc with the same parameters when you had a failure?
Maybe there's a trigger somewhere that rollsback the whole thing... (making the scope_identity() null)
-
I've checked the application side, but that hasn't changed and has worked right throughout test, and up until when I made the change to the stored procedure. It's the fact that I changed the SP and nothing else that's made me look at the SP.
It's not rolling back as all the transactions complete successfully. Just when the calling app reads the return parameter, it gets a NULL.
I'll try running it in Query Analyzer (i.e. cut out the app) and see if I get the same results.
-
Can we see the before and after procs?
-
This is the before proc. Current proc as per first post.
I'm running it from QA now so I'll see what happens.
CREATE PROCEDURE dbo.sp_Control_Insert
@MaincdeCde varchar(4),
@SubcodeCde varchar(4),
@SiteRef varchar(4),
@CustOrderNum varchar(15),
@JobChgeable int,
@JobDsc varchar(131),
@JobStatus char(2),
@JobType char(2),
@InvoiceType char(2),
@ChargeoutRate decimal(6,2),
@QuoteRate decimal(8,2),
@QuoteDays decimal(5,2),
@Originator varchar(50),
@AddProd varchar(13),
@JobNumber int OUTPUT
AS
IF @CustOrderNum = 'noproject'
SELECT @CustOrderNum = ''
INSERT ControlTable (MaincdeCde,SubcodeCde,SiteRef,CustOrderNum,JobChgeable,JobDsc,
JobStatus,JobType,InvoiceType,ChargeoutRate,QuoteRate,QuoteDays,
Originator,QtdDevPeriod,QtdAvailDte,QtdSubDte,AddProd,ModemAmt,ReqdDte,JCUpdated, UserRef)
VALUES (@MaincdeCde,@SubcodeCde,@SiteRef,@CustOrderNum,@JobChgeable,@JobDsc,
@JobStatus,@JobType,@InvoiceType,@ChargeoutRate,@QuoteRate,@QuoteDays,
@Originator,'','01/01/1900','01/01/1900',@AddProd,0,'01/01/1900',0,'')
SELECT @JobNumber = SCOPE_IDENTITY()
GO
-
I just don't see it... What about the query analyser results?
-
Found it! Here's the QA results:
(1 row(s) affected)
Server: Msg 208, Level 16, State 1, Procedure sp_Control_Insert, Line 33
Invalid object name 'dbo.ToFour'.
It would seem then, that the error in SQL Server doesn't result in an error being returned to the ASP script via the ADODB Command object. Conversely, if you get an error through a Recordset or Connection it returns an error in the script.
The correct object name, BTW is dbo.fnToFour().
Thanks for the time & the push in the right direction to re-run the proc outside the app.
-
It was your idea first... can't take the full credit.
-
I would also add SET NOCOUNT ON for the 1st line in your proc and SET NOCOUNT OFF as the last line, I have seen this cause inconsistant output parameter problems when using the proc from ADO.
Andy
-
Also is it possible that this stored proc is called from more than one place?
Because it would look like their were 2 different plans being used for this proc...
-
Yes it does...
-
Then I think you will benefit from reading this small article. It talks about cache miss that is caused by calling the same proc with different syntaxe. This is not a great performance booster, but every little bit can help on a busy server... But as you can see it can be the cause of other problems
. -
Thanks, Remi; very interesting.
In this case the procedure isn't called very regularly, and my code is kept case-sensitive for consistency anyway. But certainly useful nonetheless. And learnt something new.
-
Ya I wasn't too sure that it could be the case in this situation... But I thaught: what else can be causing the server to fail/succeed when using the same proc : only 2 different plans can cause this and this is the only way I know to have multiple plans for one proc
.Glad you learned something new.
-
That part came from a serious lack of coffee on my part yesterday afternoon:
It failed only when there was no @CustOrderNum (i.e. the value 'noproject' was passed to the PROC), because that was the only time it tried to run the wrongly named function. Because the script didn't return an error, and it processed as far as the INSERT to ControlTable (and didn't roll back), it seemed from watching the script run that nothing was wrong (it wasn't erroring out and jobs were being raised), and sometimes it returned a job number and sometimes it didn't.
I checked the OpenLink tables by chance on one execution that had @CustOrderNum values passed, so it seemed the whole procedure was working (this was the lack of coffee part: I didn't properly run through every scenario). Not until you'd suggested I re-run it with the same parameters as one that had returned a NULL output parameter, and I decided to do this in Query Analyzer, did I actually see the error present itself which made the problem obvious.
Don't you always seem to find it all boils down to the seemingly silliest of little things?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply