January 12, 2012 at 2:52 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 12, 2012 at 3:03 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 12, 2012 at 4:05 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 13, 2012 at 4:45 am
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.
January 13, 2012 at 4:49 am
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
January 13, 2012 at 2:39 pm
Brandie Tarvin (1/13/2012)
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.
My pleasure. Took me a few rounds of goofing with it to be able to recreate it, then I went down to root level to determine that the proc itself refused to actually error.
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.
A 0 in that window means it uses the defaults in the sp_configure. It's not an enternal window unless you set your server that way.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 18, 2012 at 12:27 pm
I did not know that information about the timeout. I'll check into it.
Of course, every time I get a moment to work on this issue, another bigger fire comes out of the woodwork. Eventually I'll get this thing working. @=)
EDIT: My server values are...
nameminimummaximumconfig_valuerun_value
remote login timeout (s)021474836472020
Am I looking at this correctly? It seems to be set to the maximum # possible, right? "To Infinity and Beyond" and all that jazz?
January 18, 2012 at 8:10 pm
Brandie Tarvin (1/9/2012)
Craig, I get what you're saying, but I can't figure out how to pass the error outside of the code context to fail the job step itself. Raising an error is, in and of itself, a successful prosecution of the code. And I see no options within the job step window that allow me to set success or failure of that job step.
Try using raiserror with the severity parameter set to 12 or higher in a catch block. That should cause the step to fail.
Tom
January 19, 2012 at 4:59 pm
Brandie Tarvin (1/18/2012)
I did not know that information about the timeout. I'll check into it.Of course, every time I get a moment to work on this issue, another bigger fire comes out of the woodwork. Eventually I'll get this thing working. @=)
EDIT: My server values are...
nameminimummaximumconfig_valuerun_value
remote login timeout (s)021474836472020
Am I looking at this correctly? It seems to be set to the maximum # possible, right? "To Infinity and Beyond" and all that jazz?
Sorry Brandie, I lost track of this thread briefly. You're correct, that's set to infinity and beyond, basically.
I think that's 68 years or so.
These are the three two links you'll care about trying to set the timeout, but that won't help the lack of an error being raised:
http://msdn.microsoft.com/en-us/library/ms186839.aspx
http://msdn.microsoft.com/en-us/library/ms177457.aspx
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 19, 2012 at 5:38 pm
L' Eomot Inversé (1/18/2012)
Brandie Tarvin (1/9/2012)
Craig, I get what you're saying, but I can't figure out how to pass the error outside of the code context to fail the job step itself. Raising an error is, in and of itself, a successful prosecution of the code. And I see no options within the job step window that allow me to set success or failure of that job step.Try using raiserror with the severity parameter set to 12 or higher in a catch block. That should cause the step to fail.
Tom, I think the issue is that there is no error raised. So a catch block won't ever raise an error.
Jared
CE - Microsoft
January 20, 2012 at 8:15 pm
SQLKnowItAll (1/19/2012)
L' Eomot Inversé (1/18/2012)
Brandie Tarvin (1/9/2012)
Craig, I get what you're saying, but I can't figure out how to pass the error outside of the code context to fail the job step itself. Raising an error is, in and of itself, a successful prosecution of the code. And I see no options within the job step window that allow me to set success or failure of that job step.Try using raiserror with the severity parameter set to 12 or higher in a catch block. That should cause the step to fail.
Tom, I think the issue is that there is no error raised. So a catch block won't ever raise an error.
Drat, that's horrible. Surely there is some way of detecting this error?
Tom
January 22, 2012 at 12:22 am
L' Eomot Inversé (1/20/2012)
Drat, that's horrible. Surely there is some way of detecting this error?
No, it appears to be a 'feature' of 2k8 for Linked Servers. I haven't figured out a way to force it to kick one yet. It reports the timeout, but more like a PRINT message then an error occurred. Attempting to detecti it would be frightful.
Here's the stranger part: I can't seem to find anyone else who's run into this AND complained about it on the intertubes anywhere. I'd have figured this would have been sitting in connect somewhere.
However, I don't have R2. My guess is the majority of people running 2k8 are running 2k8 R2 and I can't test that, which would explain the lack of 'hits' on this topic.
As a simple test if someone following this has R2, can you please setup the following:
Grab yourself two instances of SQL. 2k5+ for one, 2k8 R2 as the other. Create a database on the 'called' instance and shove a proc in there to call that simply does a wait for 15 seconds.
From your 2k8 R2 create a linked server to the first instance. Set the remote query timeout in the advanced options to 3 (seconds). Now create a proc that calls the 'waiting' proc on the other server via the linked server.
Now call that proc on the 2k8R2 machine. See if you get a simple message, or an actual error message.
Thanks!
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 23, 2012 at 4:49 am
Evil Kraig F (1/22/2012)
Here's the stranger part: I can't seem to find anyone else who's run into this AND complained about it on the intertubes anywhere. I'd have figured this would have been sitting in connect somewhere.
There's plenty of complaints about the original issue on 2k5. Original Issue meaning the job succeeded even though there was a timeout. I don't think anyone managed to figure out what you did.
July 8, 2012 at 3:14 pm
Hi
use
"SET ANSI_WARNINGS OFF"
in your stored procedure
July 9, 2012 at 4:07 am
mehmet.erbay (7/8/2012)
Hiuse
"SET ANSI_WARNINGS OFF"
in your stored procedure
Can I ask what makes you think this will work?
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply