Hello,

I am having difficulty working out how you can set any messages returned
from a stored proc to a variable declared in another one.
There doesn't seem to be a @@Error equivalent for print messages and there
is apparently no system functions to retrieve them either. Does anyone
have any idea how this can be done. Any suggestions would be appreciated...

Thanks in advance.

Report this post to a moderator

If you're talking about catching the error message (rather than the number),
it can't be done within T-SQL, unfortunately.

It is possible to catch error numbers within parent stored procs, but keep
in mind that you generally can't catch all errors within T-SQL, because
T-SQL implements "fatal" errors which terminate all code execution
(including error handling routines) unconditionally, which effectively
renders any error handling logic you write in T-SQL as unable to catch all
errors. Try SELECT'ing from a table that doesn't exist and you'll see what I
mean.

Generally speaking, this means that error handling - especially catching
messages - is best done in the client application because it can't be relied
upon within T-SQL.

Another thing to keep in mind is that error messages are NOT returned to the
client if the client is on a different language locale to the SQL Server.
This includes different version of English (eg Australian / American /
British) - a very common scenario. In such a scenario only the number,
severity etc are returned and the message is blank.

HTH

Cheers,
Greg Linwood

"Any old Joe" <joe@starseaford.co.uk> wrote in message
news:Xns92D37CB16D759joestarse
afordcouk@207.46.230.185...
> Hello,
>
> I am having difficulty working out how you can set any messages returned
> from a stored proc to a variable declared in another one.
> There doesn't seem to be a @@Error equivalent for print messages and there
> is apparently no system functions to retrieve them either. Does anyone
> have any idea how this can be done. Any suggestions would be
appreciated...
>
> Thanks in advance.

Report this post to a moderator

Greg,

I'm a bit surprised by your below statement:

> Another thing to keep in mind is that error messages are NOT returned to the
> client if the client is on a different language locale to the SQL Server.
> This includes different version of English (eg Australian / American /
> British) - a very common scenario. In such a scenario only the number,
> severity etc are returned and the message is blank.

SET language svenska
GO
SELECT * FROM jkljlkj

I tried above (from QA) using English locale on my machine as well as with Swedish locale. I
tried several languages in SET. I also tried configuring QA for "display results using regional
settings". I always get the us_english message text.
My experience is that for a message (like sp_addmessage) you always have to first create the
us_english message. Then you can add other languages for the same message number. If the TSQL
code is running with a language for which the message doesn't exist, then the us_english is
returned.

I'm looking at this mainly from SQL Server's perspective, so there might be other (client
side/API/programming language) factors involved as well, I assume...

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver

Report this post to a moderator

"Greg Linwood" <g_linwood@hotmail.com> wrote in
news:#9hgeThlCHA.1912@tkmsftngp04:

> If you're talking about catching the error message (rather than the
> number), it can't be done within T-SQL, unfortunately.
>
> It is possible to catch error numbers within parent stored procs, but
> keep in mind that you generally can't catch all errors within T-SQL,
> because T-SQL implements "fatal" errors which terminate all code
> execution (including error handling routines) unconditionally, which
> effectively renders any error handling logic you write in T-SQL as
> unable to catch all errors. Try SELECT'ing from a table that doesn't
> exist and you'll see what I mean.
>
> Generally speaking, this means that error handling - especially
> catching messages - is best done in the client application because it
> can't be relied upon within T-SQL.
>
> Another thing to keep in mind is that error messages are NOT returned
> to the client if the client is on a different language locale to the
> SQL Server. This includes different version of English (eg Australian
> / American / British) - a very common scenario. In such a scenario
> only the number, severity etc are returned and the message is blank.
>
> HTH
>
> Cheers,
> Greg Linwood
>
> "Any old Joe" <joe@starseaford.co.uk> wrote in message
> news:Xns92D37CB16D759joestarse
afordcouk@207.46.230.185...
>> Hello,
>>
>> I am having difficulty working out how you can set any messages
>> returned from a stored proc to a variable declared in another one.
>> There doesn't seem to be a @@Error equivalent for print messages and
>> there is apparently no system functions to retrieve them either.
>> Does anyone have any idea how this can be done. Any suggestions would
>> be
> appreciated...
>>
>> Thanks in advance.
>
>

Thanks for that Greg...

What I was trying to do was to capture any PRINT message rather than
error ones in particular - as you can do from a client connection (for
example with the InfoMsg event on a SQLConnection object) However, I
presume that the above applies to all messages SQL Server emits....
drat!!!

Report this post to a moderator

Sorry about that - I definitely should have stated this is ADO related..

Cheers,
Greg

"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@cornerstone.se>
wrote in message news:OcxICphlCHA.2032@tkmsftngp02...
> Greg,
>
> I'm a bit surprised by your below statement:
>
> > Another thing to keep in mind is that error messages are NOT returned to
the
> > client if the client is on a different language locale to the SQL
Server.
> > This includes different version of English (eg Australian / American /
> > British) - a very common scenario. In such a scenario only the number,
> > severity etc are returned and the message is blank.
>
> SET language svenska
> GO
> SELECT * FROM jkljlkj
>
> I tried above (from QA) using English locale on my machine as well as with
Swedish locale. I
> tried several languages in SET. I also tried configuring QA for "display
results using regional
> settings". I always get the us_english message text.
> My experience is that for a message (like sp_addmessage) you always have
to first create the
> us_english message. Then you can add other languages for the same message
number. If the TSQL
> code is running with a language for which the message doesn't exist, then
the us_english is
> returned.
>
> I'm looking at this mainly from SQL Server's perspective, so there might
be other (client
> side/API/programming language) factors involved as well, I assume...
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
>
>

Report this post to a moderator

Ah, OK. Interesting...
Thanks for the update.

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver


"Greg Linwood" <g_linwood@hotmail.com> wrote in message news:#pH5tCllCHA.2840@tkmsftngp04...
> Sorry about that - I definitely should have stated this is ADO related..
>
> Cheers,
> Greg
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@cornerstone.se>
> wrote in message news:OcxICphlCHA.2032@tkmsftngp02...
> > Greg,
> >
> > I'm a bit surprised by your below statement:
> >
> > > Another thing to keep in mind is that error messages are NOT returned to
> the
> > > client if the client is on a different language locale to the SQL
> Server.
> > > This includes different version of English (eg Australian / American /
> > > British) - a very common scenario. In such a scenario only the number,
> > > severity etc are returned and the message is blank.
> >
> > SET language svenska
> > GO
> > SELECT * FROM jkljlkj
> >
> > I tried above (from QA) using English locale on my machine as well as with
> Swedish locale. I
> > tried several languages in SET. I also tried configuring QA for "display
> results using regional
> > settings". I always get the us_english message text.
> > My experience is that for a message (like sp_addmessage) you always have
> to first create the
> > us_english message. Then you can add other languages for the same message
> number. If the TSQL
> > code is running with a language for which the message doesn't exist, then
> the us_english is
> > returned.
> >
> > I'm looking at this mainly from SQL Server's perspective, so there might
> be other (client
> > side/API/programming language) factors involved as well, I assume...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=...ublic.sqlserver
> >
> >
>
>

Report this post to a moderator