December 18, 2009 at 12:59 pm
So, you have no method or test cases or values for which you could use to test these procs?
Having valid test data to go with these procs would also help to see where you might be getting an error.
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 3:52 pm
I dont have anything to test them..
December 18, 2009 at 4:38 pm
Please post the "master" proc.
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 4:49 pm
----------------------------------SP3
USE [DATABASE]
GO
/****** Object: StoredProcedure [dbo].[sp_ChangeReportProcessField] Script Date: 12/18/2009 15:28:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
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 4:49 pm
Can somebody pls findout why is this job failing?
December 18, 2009 at 5:21 pm
How about the sql for the job that runs this monster?
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 5:24 pm
and the udf's
udf_GenBatchID
udf_GetApplicationID
And, has the job ever worked?
Anything change that would have caused it to start failing?
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 6:11 pm
The job was running till Dec16 2009, it was started failing from dec 17th. pls help wht is causing the problem.
December 18, 2009 at 8:18 pm
sureshdeenu (12/18/2009)
The job was running till Dec16 2009, it was started failing from dec 17th. pls help wht is causing the problem.
Job worked on the 16th, failed on the 17th. What changed? And please, don't say nothing, as something had to have changed between the tme it last ran okay and when it failed. It could be anything, so please think hard and check any change logs you may have. Check the code that is running against the code that ran the 16th (you may have to restore a copy of the database to a test directory, but if you have to in order to identify the error, then that's what you need to do.
December 18, 2009 at 8:28 pm
nothing has been changed in the code, we were using a third party tool Tidal-scheduler to automate this job. The automate job was failing with the error I have given. The thing is even I saw the job properties that ran successfully that has the same message as the job failed. i feel like there is something happening with set ANSI_NULLS ON.
December 18, 2009 at 8:34 pm
Something changed between the 16th and the 17th. Working code doesn't suddenly stop working for no reason. Something in the environment, the data, or the code changed. You need to figure out what changed. I've run the offending code using my own variables, and the dynamic sql generates a valied sql statement.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply