December 18, 2009 at 8:45 am
How can i identify where the error is as there is no where defined in the code using 'RAISEERROR' statement. please help..
December 18, 2009 at 8:47 am
Job Failed With Error 50000
December 18, 2009 at 10:20 am
what do you have in sysmessages for that error message?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 18, 2009 at 10:45 am
I know 50000 is a user defined Error, but i dont even find anything in sysmessages-table in Master. What is the best way to debug this Job to find the error. I dont even find the RAISEERROR statement in the code. The job is executing a storedprocedure.
December 18, 2009 at 10:59 am
Per BOL (Books Online, the SQL Server Help System) regarding error message 50000:
When RAISERROR is used with a msg_str instead of a msg_id, the SQL Server error number and native error number returned is 50000.
This means that the RAISERROR statement contained an error message string, so you need to identify the error message returned, then look for that in the stored procedure. If the stored procedure called any other stored procedures, you may have to look through them as well.
December 18, 2009 at 11:04 am
RAISEERROR statement is not in the stored procedure. I have got the error like this:
Executed as user: xxxcorp\sqladmn. ...X] = 'x' Where Client = '4600' And SequenceNumber = '1020' And IsNull([FileReceived],'') <> IsNull((Select [FileReceived] From tmpSeqNum B Where B.FileNumber = GeStatus.FileNumber),'') [SQLSTATE 01000] (Message 0) Update GeStatus Set ChangedRecordX = 'x', [POCDateX] = 'x' Where Client = '4600' And SequenceNumber = '1020' And IsNull([POCDate],'') <> IsNull((Select [POCDate] From tmpSeqNum B Where B.FileNumber = GeStatus.FileNumber),'') [SQLSTATE 01000] (Message 0) Update GeStatus Set ChangedRecordX = 'x', [ReceiveReviewPlanX] = 'x' Where Client = '4600' And SequenceNumber = '1020' And IsNull([ReceiveReviewPlan],'') <> IsNull((Select [ReceiveReviewPlan] From tmpSeqNum B Where B.FileNumber = GeStatus.FileNumber),'') [SQLSTATE 01000] (Message 0) Update GeStatus Set ChangedRecordX = 'x', [OTPDateX] = 'x' Where Client = '4600' And SequenceNumber = '1020' And IsNull([OTPDate],'') <> IsNull((Select [OTPDate] From tmpSeqNum B Where B.F... The step failed.
December 18, 2009 at 11:07 am
sureshdeenu (12/18/2009)
RAISEERROR statement is not in the stored procedure. I have got the error like this:Executed as user: xxxcorp\sqladmn. ...X] = 'x' Where Client = '4600' And SequenceNumber = '1020' And IsNull([FileReceived],'') <> IsNull((Select [FileReceived] From tmpSeqNum B Where B.FileNumber = GeStatus.FileNumber),'') [SQLSTATE 01000] (Message 0) Update GeStatus Set ChangedRecordX = 'x', [POCDateX] = 'x' Where Client = '4600' And SequenceNumber = '1020' And IsNull([POCDate],'') <> IsNull((Select [POCDate] From tmpSeqNum B Where B.FileNumber = GeStatus.FileNumber),'') [SQLSTATE 01000] (Message 0) Update GeStatus Set ChangedRecordX = 'x', [ReceiveReviewPlanX] = 'x' Where Client = '4600' And SequenceNumber = '1020' And IsNull([ReceiveReviewPlan],'') <> IsNull((Select [ReceiveReviewPlan] From tmpSeqNum B Where B.FileNumber = GeStatus.FileNumber),'') [SQLSTATE 01000] (Message 0) Update GeStatus Set ChangedRecordX = 'x', [OTPDateX] = 'x' Where Client = '4600' And SequenceNumber = '1020' And IsNull([OTPDate],'') <> IsNull((Select [OTPDate] From tmpSeqNum B Where B.F... The step failed.
Where did you get this:
Job Failed With Error 50000
December 18, 2009 at 11:18 am
sql server Error: 50000, is found in sql server agent Error log.
December 18, 2009 at 11:32 am
I have an idea, show us the code that is failing instead of just the error message. That really isn't helping much.
December 18, 2009 at 11:54 am
Declare @UpdateText as VarChar(2000)
Set @UpdateText =
'Update GeStatus ' +
'Set ChangedRecordX = ''x'', ' +
'[' + RTrim(@ChangedFieldName) +'] = ''x'' ' +
'Where Client = ''' + RTrim(@Client) + ''' ' +
'And SequenceNumber = ''' + RTrim(Cast(@SeqNum as VarChar)) + ''' ' +
'And IsNull([' + RTrim(@FieldName) + '],'''') <> ' +
' IsNull((Select [' + RTrim(@FieldName) + '] From tmpSeqNum B ' +
' Where B.FileNumber = GeStatus.FileNumber),'''')'
Exec(@UpdateText)
Print @UpdateText
December 18, 2009 at 12:02 pm
sureshdeenu (12/18/2009)
Declare @UpdateText as VarChar(2000)Set @UpdateText =
'Update GeStatus ' +
'Set ChangedRecordX = ''x'', ' +
'[' + RTrim(@ChangedFieldName) +'] = ''x'' ' +
'Where Client = ''' + RTrim(@Client) + ''' ' +
'And SequenceNumber = ''' + RTrim(Cast(@SeqNum as VarChar)) + ''' ' +
'And IsNull([' + RTrim(@FieldName) + '],'''') <> ' +
' IsNull((Select [' + RTrim(@FieldName) + '] From tmpSeqNum B ' +
' Where B.FileNumber = GeStatus.FileNumber),'''')'
Exec(@UpdateText)
Print @UpdateText
Is this the entire code or just a piece of the stored proc? If the latter, please post the entire proc.
December 18, 2009 at 12:18 pm
--New Record, everythings changed(PART OF OTHER STORED PROCEDURE)
UPDATE GeStatus
SET ChangedRecordX = 'x'
,FileReceivedX = 'x'
,POCDateX = 'x'
,ReceiveReviewPlanX = 'x'
,OTPDateX = 'x'
WHERE SUBSTRING(FileNumber, 1, 4) = @Client
AND SequenceNumber = @SeqNum
AND FileNumber NOT IN ( SELECT FileNumber
FROM tmpSeqNum (NOLOCK) )
EXEC sp_ChangeReportProcessField @Client, @SeqNum, 'POCDate',
'POCDateX'
EXEC sp_ChangeReportProcessField @Client, @SeqNum, 'OTPDate',
'OTPDateX'
-----------------------------------------------------------NEXT STORED PROCEDURE
CREATE Proc [dbo].[sp_ChangeReportProcessField](
@ClientVarChar(4),
@SeqNumint,
@FieldNameVarChar(50),
@ChangedFieldNameVarChar(50)
) as
Declare @UpdateText as VarChar(2000)
Set @UpdateText =
'Update GeStatus ' +
'Set ChangedRecordX = ''x'', ' +
'[' + RTrim(@ChangedFieldName) +'] = ''x'' ' +
'Where Client = ''' + RTrim(@Client) + ''' ' +
'And SequenceNumber = ''' + RTrim(Cast(@SeqNum as VarChar)) + ''' ' +
'And IsNull([' + RTrim(@FieldName) + '],'''') <> ' +
' IsNull((Select [' + RTrim(@FieldName) + '] From tmpSeqNum B ' +
' Where B.FileNumber = GeStatus.FileNumber),'''')'
Exec(@UpdateText)
Print @UpdateText
December 18, 2009 at 12:20 pm
Main stored procedure is using cursor to check all the clients update with the new records.
After getting the new records, i think it is failing while updating.
December 18, 2009 at 12:24 pm
Is the child stored procedure able to execute independently of the parent proc?
Has the process ever worked?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 18, 2009 at 12:30 pm
NO, these SP are depend on the Main SP.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply