Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

SQL Agent Job succeeds even though step should fail Expand / Collapse
Author
Message
Posted Wednesday, January 11, 2012 4:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 7,197, Visits: 6,342
I'll give the suggestion a try and see what happens. Thanks,

Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1233883
Posted Wednesday, January 11, 2012 1:11 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, August 30, 2014 1:34 PM
Points: 2,693, Visits: 3,384
Brain fart today... Erasing irrelevant comment...

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1234325
Posted Wednesday, January 11, 2012 1:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 6,251, Visits: 7,411
Brandie, do you have any extra information on what type of timeout it is? Is it a lock timeout, a remote query lockout, a network timeout, a login timeout?

I'm trying to recreate the issue in my sandboxes today out of curiousity and it would help if I knew which exact one to muck with the sandboxes.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1234339
Posted Wednesday, January 11, 2012 1:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 6,251, Visits: 7,411
To add to that, it depends on the timeout. For example, a lock timeout:



Definately will fail the step (and thus the job).

I did this by trying to update the same record via a proc called from the job step as I had in a hanging transaction in another query window.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA


  Post Attachments 
TimoutTest.jpg (195 views, 102.83 KB)
Post #1234345
Posted Thursday, January 12, 2012 5:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 7,197, Visits: 6,342
Evil Kraig F (1/11/2012)
Brandie, do you have any extra information on what type of timeout it is? Is it a lock timeout, a remote query lockout, a network timeout, a login timeout?


I think it's a remote query timeout.

It ran over the weekend and I didn't notice the problem until two days later because the job didn't fail:

OLE DB provider "SQLNCLI10" for linked server "MyServer" returned message "Query timeout expired". [SQLSTATE 01000] Job 'XXXX Report Run' : Step 2, 'Run XXX Proc' : Began Executing 2012-01-02 04:40:02

EDIT: I've been fighting other fires, so haven't had a chance to test a deliberate failure of a job. As soon as I put these out, I'm going to go back to testing the job itself.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1234692
Posted Thursday, January 12, 2012 2:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 6,251, Visits: 7,411
Brandie Tarvin (1/12/2012)
Evil Kraig F (1/11/2012)
Brandie, do you have any extra information on what type of timeout it is? Is it a lock timeout, a remote query lockout, a network timeout, a login timeout?


I think it's a remote query timeout.


That did it. I can see what you see now. I created a secondary linked server and set the connection timeout and query timeouts to 1 and then built a passthrough proc to call the proc on the foreign server. Said foreign proc was nothing more than a waitfor delay '00:00:15'.

Successful completion of a failure, but that's primarily because the proc doesn't actually fail, which is just... wierd. Try/Catch is also useless, as it's not an actual failure. Blasted strange. The workaround will not function. Hmmmmm.

My Google Fu does fail me now.

By chance, are you also connecting from a 2k8 machine running the agent and the local DB with the connection attached to a 2k5 machine like I am? I've reviewed some errors for this and it appears others have gotten an actual error to occur from this.




- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1235261
Posted Thursday, January 12, 2012 3:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 6,251, Visits: 7,411
Additional information. SQL 2k8 R1 SP1 talking to SQL 2k5 does not fail properly.

SQL 2k5 talking to 2k8 R1 SP1 DOES fail properly. I'm going to upgrade to SQL 2k8 SP3 and see if this got cured.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1235264
Posted Thursday, January 12, 2012 4:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 6,251, Visits: 7,411
Confirmed. 2k8 R1 SP3 does NOT fail the query on query timeout through to a SQL 2k5 server. I do not currently have two 2k8 instances to play with. I'm asking one of my DBAs to go and test if this is a source or destination version issue.

This is more a linked server than a job issue. The proc itself won't error, so the job is clueless there was a problem.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1235296
Posted Friday, January 13, 2012 4:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 7,197, Visits: 6,342
Evil Kraig F (1/12/2012)
Successful completion of a failure, but that's primarily because the proc doesn't actually fail, which is just... wierd. Try/Catch is also useless, as it's not an actual failure. Blasted strange. The workaround will not function. Hmmmmm.


That's kind of what I expected to happen, so I'm not surprised.


By chance, are you also connecting from a 2k8 machine running the agent and the local DB with the connection attached to a 2k5 machine like I am? I've reviewed some errors for this and it appears others have gotten an actual error to occur from this.


2k8 to 2k8. One is an app server, the other is my db server. SP1.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1235516
Posted Friday, January 13, 2012 4:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 7,197, Visits: 6,342
Evil Kraig F (1/12/2012)
This is more a linked server than a job issue. The proc itself won't error, so the job is clueless there was a problem.


When you put it that way, it actually makes sense what's going on. Thank you for that translation.

So, I wonder if this is a linked server "bug" or if it's deliberate behavior with an unintended side effect.

My linked server uses the SQL Server Native Client 10.0 provider, is set up using a specific security context (the last option), and has Query Timeout set to 0 (which I thought was no timeout). The other options are listed below.

Collation Compatible: False
Data Access: True
RPC: True
RPC Out: True
Use Remote Collation: True
Collation Name: <blank>
Connection Timeout: 0
Distributor: False
Publisher: False
Subscriber: False
Lazy Schema Validation: False
Enable Promotion of Distributed Transactions: True


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1235520
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse