May 31, 2012 at 11:58 am
We are trouble-shooting a nightly job we are having issues with.
The code seems like a fairly straight-forward UPDATE statement. But the Job History shows a mysterious (and un-helpful) error message " failed with the following error: "Unspecified error". Possible failure reasons: Problems with the query, "
So, we tore apart the UPDATE query and turned it into a SELECT query and ran it in a Query window. We saw mixed results. Sometimes it would run to completion, and other times, it would result in this error message "Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded."
So then, I wrote some T-SQL code to put the Id values of the records for the update into a table variable and constructed a cursor loop (don't shoot me!) so that I could print out the Id values of each record and print them out as it ran - thinking that we were looking at a data-related issue.
So I ran my code, and it loops through doing what I expected it to do, and then all of a sudden, on its 2229th iteration, I received this error message: "Msg 208, Level 16, State 1, Line 12
Invalid object name 'LOGIX_SOShipHeader'."
The table exists obviously, because it had already run 2,228 successful iterations before coughing up this error. Does anyone have any idea about why/what might be happening here? Has anyone else seen an error like this?
May 31, 2012 at 12:02 pm
Is it always in the same spot where it fails? Could the target table be getting dropped or renamed? Or the user having access removed?
May 31, 2012 at 12:26 pm
Different spots each time it fails.
I learned in examing a function call that is being used to generate a CustOrderNumber, that the function call refers to db called 'PortalReports'. When I examined that db for the table, i found that 'PortalReports.dbo.New_salesDetail' is actually a synonym which points to a snapshot instance from our CRM system.
Hmmm...snapshots are occuring every 5 minutes, so I'm guessing that there must be a window of time when the db is regenerating the synonym definition to point at a new snapshot.
...must do more homework here...
May 31, 2012 at 12:28 pm
Is there a trigger on the table? Posting the table DLL and the code you were running might help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply