Can I make a query fail without killing the connection?

Question

Post reply

Can I make a query fail without killing the connection?

Henry Treftz

SSCarpal Tunnel

Points: 4207
More actions
May 17, 2010 at 11:57 am

#143155

Is there a way I can send a error message to a running SPID or otherwise make it fail (long running query) without killing the SPID?
Thanks
Henry

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Not sure what you're trying to do but maybe RAISERROR will help (see BOL for details).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Richard M. SSCertifiable Points: 7401 More actions · Answer 2

lmu92 (5/17/2010)
Not sure what you're trying to do but maybe RAISERROR will help (see BOL for details).

... you can't "inject" an error to another SPID....

As far as I know, the only way to make a long running query fail is by killing it......

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

Henry Treftz SSCarpal Tunnel Points: 4207 More actions · Answer 3

It is a currently running query. So what I want to do is basically do send of a raiserror to an existing connection...

LutzM SSC Guru Points: 107049 More actions · Answer 4

Richard M. (5/17/2010)
lmu92 (5/17/2010)
Not sure what you're trying to do but maybe RAISERROR will help (see BOL for details).
... you can't "inject" an error to another SPID....
As far as I know, the only way to make a long running query fail is by killing it......

Let's assume the following (totally made-up and messy) scenario:

One stored procedure containing 10 rather long running statements.

Between two such statements, call a look-up table to see if the sp can continue or should stop. The look-up table can be controlled from the outside. Concept rating: :pinch: :sick:

I can't think of any business case to use such a "concept", but it seems possible (the same way you can hit your thumb with a hammer: is it a good idea: no; is it possible: yes.) 😉

That's why I asked what the OP is trying to do...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Richard M. SSCertifiable Points: 7401 More actions · Answer 5

lmu92 (5/17/2010)
Richard M. (5/17/2010)
lmu92 (5/17/2010)
Not sure what you're trying to do but maybe RAISERROR will help (see BOL for details).
... you can't "inject" an error to another SPID....
As far as I know, the only way to make a long running query fail is by killing it......
Let's assume the following (totally made-up and messy) scenario:
One stored procedure containing 10 rather long running statements.
Between two such statements, call a look-up table to see if the sp can continue or should stop. The look-up table can be controlled from the outside. Concept rating: :pinch: :sick:
I can't think of any business case to use such a "concept", but it seems possible (the same way you can hit your thumb with a hammer: is it a good idea: no; is it possible: yes.) 😉
That's why I asked what the OP is trying to do...

Lutz, that makes perfect sense, but would have required the long running queries to have that logic built in already. besides, if it is ONE statement that is causing this, it wouldn't help anyway......

As a matter of fact, we have such logic built in into our weekly reindexing process, so that the mirroring queue doesn't get too large and the t-log for the database in question doesn't grow enormously big.

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

Richard M. SSCertifiable Points: 7401 More actions · Answer 6

Henry Treftz (5/17/2010)
It is a currently running query. So what I want to do is basically do send of a raiserror to an existing connection...

...doubt that can be done....

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 7

Henry Treftz (5/17/2010)
It is a currently running query. So what I want to do is basically do send of a raiserror to an existing connection...

What exactly do you want to achieve with it other than terminating the session?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Henry Treftz SSCarpal Tunnel Points: 4207 More actions · Answer 8

I want the query to fail and the connection to remain

Leo.Miller SSChampion Points: 13048 More actions · Answer 9

Henry Treftz (5/17/2010)
I want the query to fail and the connection to remain

Why? Is this an application? In general unless you have built in functionality to do this, like a try/catch option any error the app gets back from SQL will close the connection.

If this is being done from SQL 2005 Enterprise Manager killing the SPID isn't an issue as the EM session will reconnect with an error on the next execute, then run in it's original context on the following execute.

Leo

Striving to provide a better service.

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Jeff Moden SSC Guru Points: 1003893 More actions · Answer 10

Henry Treftz (5/17/2010)
I want the query to fail and the connection to remain

Heh... we know that, Henry... why is it important for the connection to remain?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Henry Treftz SSCarpal Tunnel Points: 4207 More actions · Answer 11

A concern about the application failing in such a way that it would require significant time to re-start.

Not 100% sure that is going to be an issue.

Regardless as we started asking the question we realized it is an interesting idea. Force and error or other issue and keep the spid alive.

Leo.Miller SSChampion Points: 13048 More actions · Answer 12

If your application fails because of a SQL Error you need to fix the app. There should be (almost) no unhandled errors within the app, related to its SQL interaction, that cause it to fail and take time to restart.

Leo

Striving to provide a better service.

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Henry Treftz SSCarpal Tunnel Points: 4207 More actions · Answer 13

Yes in the best of all possible worlds this would be addressed via the application.

I am not looking for suggestions on application development.

Richard M. SSCertifiable Points: 7401 More actions · Answer 14

Henry Treftz (5/18/2010)
Yes in the best of all possible worlds this would be addressed via the application.
I am not looking for suggestions on application development.

In that case I guess KILL is your only option....

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]