Hi,
This is sort-of a follow up to a question I posted yesterday about turning
SQL Server error messages off.
After some more looking around I have found reference that if you use
@.@.ERROR to get the last error, this should stop the error being reported bac
k
to the client. However when I try this the error still gets reported in, fo
r
example, query analyser, should this be the case?
My test SP is listed below.
Thanks,
Steve
CREATE PROCEDURE [dbo].[divzero]
AS
DECLARE @.ErrNum int
SELECT @.ErrNum = 100/0
SELECT @.ErrNum = @.@.ERROR
SELECT 'DivZero'
GOYou cannot supress an error in T-SQL. Of course, you can check the @.@.ERROR
and take an action based on the @.@.ERROR value, but the client will still
receive the error message.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Steve Norman" <SteveNorman@.discussions.microsoft.com> wrote in message
news:B06126DE-8D34-4912-8011-42186196F6D2@.microsoft.com...
Hi,
This is sort-of a follow up to a question I posted yesterday about turning
SQL Server error messages off.
After some more looking around I have found reference that if you use
@.@.ERROR to get the last error, this should stop the error being reported
back
to the client. However when I try this the error still gets reported in,
for
example, query analyser, should this be the case?
My test SP is listed below.
Thanks,
Steve
CREATE PROCEDURE [dbo].[divzero]
AS
DECLARE @.ErrNum int
SELECT @.ErrNum = 100/0
SELECT @.ErrNum = @.@.ERROR
SELECT 'DivZero'
GO|||> After some more looking around I have found reference that if you use
> @.@.ERROR to get the last error, this should stop the error being reported
> back
> to the client.
No, using @.@.ERROR will not stop errors from being raised on the client.
Even if you handle errors in Transact-SQL, your client app needs to be aware
that errors will be raised.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Norman" <SteveNorman@.discussions.microsoft.com> wrote in message
news:B06126DE-8D34-4912-8011-42186196F6D2@.microsoft.com...
> Hi,
> This is sort-of a follow up to a question I posted yesterday about turning
> SQL Server error messages off.
> After some more looking around I have found reference that if you use
> @.@.ERROR to get the last error, this should stop the error being reported
> back
> to the client. However when I try this the error still gets reported in,
> for
> example, query analyser, should this be the case?
> My test SP is listed below.
> Thanks,
> Steve
> CREATE PROCEDURE [dbo].[divzero]
> AS
> DECLARE @.ErrNum int
> SELECT @.ErrNum = 100/0
> SELECT @.ErrNum = @.@.ERROR
> SELECT 'DivZero'
> GO
>|||Still it's reported.. with an annoying side-effect : If you are running
some code lauched by the SQL Agent, the job will finished after the first
error is raised, no matter the code you may have written to handle the
error... not that nice so to speak.
Chris
________________________________________
______
It's still better that if it would have been worst, isn't it ?
C'est toujours mieux que si c'etait pire !
"Steve Norman" <SteveNorman@.discussions.microsoft.com> wrote in message
news:B06126DE-8D34-4912-8011-42186196F6D2@.microsoft.com...
> Hi,
> This is sort-of a follow up to a question I posted yesterday about turning
> SQL Server error messages off.
> After some more looking around I have found reference that if you use
> @.@.ERROR to get the last error, this should stop the error being reported
back
> to the client. However when I try this the error still gets reported in,
for
> example, query analyser, should this be the case?
> My test SP is listed below.
> Thanks,
> Steve
> CREATE PROCEDURE [dbo].[divzero]
> AS
> DECLARE @.ErrNum int
> SELECT @.ErrNum = 100/0
> SELECT @.ErrNum = @.@.ERROR
> SELECT 'DivZero'
> GO
>|||So, is there no way of stopping SQL Server from reporting this divide-by-zer
o
error? The problem is that I am running the SP from ASP.NET, and even thoug
h
I handle the error in the SP, the ASP.NET code gets an Exception and errors
before I get the data back.
Any ideas of how to get round this (there is no way of stopping the divide
by zero error in the SP, I have to run SQL from a field in a table which may
or may not cause it)
Thanks,
Steve
"Chris V." wrote:
> Still it's reported.. with an annoying side-effect : If you are running
> some code lauched by the SQL Agent, the job will finished after the first
> error is raised, no matter the code you may have written to handle the
> error... not that nice so to speak.
> Chris
> --
> ________________________________________
______
> It's still better that if it would have been worst, isn't it ?
> C'est toujours mieux que si c'etait pire !
> "Steve Norman" <SteveNorman@.discussions.microsoft.com> wrote in message
> news:B06126DE-8D34-4912-8011-42186196F6D2@.microsoft.com...
> back
> for
>
>|||Well, so far, the way I used is to have a SP calling the SP..
SP_lancher call the SP_active with OSQL, if SP_Active raises an error, it
raises it to OSQL which discards it, hidding it for the client.
I'm not pretending it's an elegant solution..but it's working :)
Chris
________________________________________
______
It's still better that if it would have been worst, isn't it ?
C'est toujours mieux que si c'etait pire !
"Steve Norman" <SteveNorman@.discussions.microsoft.com> wrote in message
news:C0E40943-7BF2-470D-947D-910564408693@.microsoft.com...
> So, is there no way of stopping SQL Server from reporting this
divide-by-zero
> error? The problem is that I am running the SP from ASP.NET, and even
though
> I handle the error in the SP, the ASP.NET code gets an Exception and
errors
> before I get the data back.
> Any ideas of how to get round this (there is no way of stopping the divide
> by zero error in the SP, I have to run SQL from a field in a table which
may
> or may not cause it)
> Thanks,
> Steve
> "Chris V." wrote:
>
first
turning
reported
in,|||The only thing I can think of is if you use ADO.NET in which case you can
trap the error there.
"Steve Norman" wrote:
> So, is there no way of stopping SQL Server from reporting this divide-by-z
ero
> error? The problem is that I am running the SP from ASP.NET, and even tho
ugh
> I handle the error in the SP, the ASP.NET code gets an Exception and error
s
> before I get the data back.
> Any ideas of how to get round this (there is no way of stopping the divide
> by zero error in the SP, I have to run SQL from a field in a table which m
ay
> or may not cause it)
> Thanks,
> Steve
> "Chris V." wrote:
>|||"Steve Norman" <SteveNorman@.discussions.microsoft.com> wrote in message
news:C0E40943-7BF2-470D-947D-910564408693@.microsoft.com...
> So, is there no way of stopping SQL Server from reporting this
divide-by-zero
> error? The problem is that I am running the SP from ASP.NET, and even
though
Sure there is -- make sure you don't have a divide-by-zero error to
begin with.
CASE denominator
WHEN 0.0 THEN 0.0
ELSE numerator / denominator
END
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Apologies about the brain dead reply.
Silly question time...
Why can you have a check on the amount that your dividing by i.e
if @.Amount > 0
Set @.Division = @.Division / @.Amount.
Formally Known as 'Peter The Spate'
"Never ascribe to malice, that which can be explained by incompetence."
Napoleon
"Peter" wrote:
> The only thing I can think of is if you use ADO.NET in which case you can
> trap the error there.
> "Steve Norman" wrote:
>|||I don't think in my case I would be able to do that. Reason being that a
user will enter a formula in an ASP.NET page which can be any equation they
want based upon any number of data columns they choose, my SP will then do
they calculation and return the result.
So unless I do some pretty complicated string manipulation on the formula to
add in case statements, I cannot stop the divide by zero error. However I d
o
handle the error and the SP will continue on and correct the data itself, bu
t
the calling ASP.NET page only reports the error that gets raised by SQL
Server, so I need a way of stopping this error getting back to the ASP.NET
page (SQLClient object).
I'm beginning to think I cannot do it?
Steve
"Adam Machanic" wrote:
> "Steve Norman" <SteveNorman@.discussions.microsoft.com> wrote in message
> news:C0E40943-7BF2-470D-947D-910564408693@.microsoft.com...
> divide-by-zero
> though
> Sure there is -- make sure you don't have a divide-by-zero error to
> begin with.
> CASE denominator
> WHEN 0.0 THEN 0.0
> ELSE numerator / denominator
> END
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>
Showing posts with label error. Show all posts
Showing posts with label error. Show all posts
Tuesday, March 27, 2012
@@ERROR per connection?
Hi
Hoping someone may know this. I am connecting to sql server via
OleDbConnection from C#.
Is the instance of @.@.ERROR that is set in db per connection? What I
was going to do was, after connecting and opening a data reader on one
connection, I wanted to open a 2nd connection and read @.@.ERROR.
But every time I read @.@.ERROR, it is 0, regardlessof the previous
read.
I suspect this is because conenction 2 is not reading the @.@.ERROR set
by connection1.
Is this the case?
Thanks
Jeff"Jeff User" <jeff31162@.hotmail.com> wrote in message
news:6o3ap198n6vs7kb14h80maab9rgd9mfelg@.
4ax.com...
> Hi
> Hoping someone may know this. I am connecting to sql server via
> OleDbConnection from C#.
> Is the instance of @.@.ERROR that is set in db per connection? What I
> was going to do was, after connecting and opening a data reader on one
> connection, I wanted to open a 2nd connection and read @.@.ERROR.
> But every time I read @.@.ERROR, it is 0, regardlessof the previous
> read.
> I suspect this is because conenction 2 is not reading the @.@.ERROR set
> by connection1.
> Is this the case?
Correct. Any time you get an error it propagates back to the client. For
you it will become an OleDbException. BTW why OleDbConnection and not
SqlConnection?
David|||Thanks David
I used OleDb because I read that it was more verstile and my code will
eventually be ported to other databases. Is this incorrect?
Thanks
Jeff
On Mon, 5 Dec 2005 22:34:07 -0600, "David Browne" <davidbaxterbrowne
no potted meat@.hotmail.com> wrote:
>"Jeff User" <jeff31162@.hotmail.com> wrote in message
> news:6o3ap198n6vs7kb14h80maab9rgd9mfelg@.
4ax.com...
>Correct. Any time you get an error it propagates back to the client. For
>you it will become an OleDbException. BTW why OleDbConnection and not
>SqlConnection?
>David
>|||Hmm, do I even need to try to read the SQL server @.@.error or will I
always get the same information in my oleDb error that is raised when
there is a failure?
Thanks again
Jeff
On Mon, 5 Dec 2005 22:34:07 -0600, "David Browne" <davidbaxterbrowne
no potted meat@.hotmail.com> wrote:
>"Jeff User" <jeff31162@.hotmail.com> wrote in message
> news:6o3ap198n6vs7kb14h80maab9rgd9mfelg@.
4ax.com...
>Correct. Any time you get an error it propagates back to the client. For
>you it will become an OleDbException. BTW why OleDbConnection and not
>SqlConnection?
>David
>|||"Jeff User" <jeff31162@.hotmail.com> wrote in message
news:486ap1ppcjptrckbde41oekh84e2srj163@.
4ax.com...
> Hmm, do I even need to try to read the SQL server @.@.error or will I
> always get the same information in my oleDb error that is raised when
> there is a failure?
That's an OleDB implementation detail I don't know. With the SqlClient, the
Error number and message are available.
David|||"Jeff User" <jeff31162@.hotmail.com> wrote in message
news:746ap1dmf2hgclqarvfe2gd219dbajcfvk@.
4ax.com...
> Thanks David
> I used OleDb because I read that it was more verstile and my code will
> eventually be ported to other databases. Is this incorrect?
That's one way. But probably not the preferred way. It isolates the
differences between databases out to a layer you have very little access to,
and forces you to use COM interop in your code. All the .NET data providers
implement a set of common interfaces, so you can support multiple databases
and still use the best client library for each one.
See:
Generic Coding with the ADO.NET 2.0 Base Classes and Factories
http://msdn.microsoft.com/library/d...r />
erics.asp
Data Access Application Block for ADO.NET 1.1
http://msdn.microsoft.com/practices...2/html/daab.asp
David|||OK, I can do some research/testing on this one.
Thanks for both replies and I will look into the sqlClient and check
out the articles.
jeff
On Mon, 5 Dec 2005 23:53:35 -0600, "David Browne" <davidbaxterbrowne
no potted meat@.hotmail.com> wrote:
>"Jeff User" <jeff31162@.hotmail.com> wrote in message
> news:486ap1ppcjptrckbde41oekh84e2srj163@.
4ax.com...
>That's an OleDB implementation detail I don't know. With the SqlClient, th
e
>Error number and message are available.
>David
>
Hoping someone may know this. I am connecting to sql server via
OleDbConnection from C#.
Is the instance of @.@.ERROR that is set in db per connection? What I
was going to do was, after connecting and opening a data reader on one
connection, I wanted to open a 2nd connection and read @.@.ERROR.
But every time I read @.@.ERROR, it is 0, regardlessof the previous
read.
I suspect this is because conenction 2 is not reading the @.@.ERROR set
by connection1.
Is this the case?
Thanks
Jeff"Jeff User" <jeff31162@.hotmail.com> wrote in message
news:6o3ap198n6vs7kb14h80maab9rgd9mfelg@.
4ax.com...
> Hi
> Hoping someone may know this. I am connecting to sql server via
> OleDbConnection from C#.
> Is the instance of @.@.ERROR that is set in db per connection? What I
> was going to do was, after connecting and opening a data reader on one
> connection, I wanted to open a 2nd connection and read @.@.ERROR.
> But every time I read @.@.ERROR, it is 0, regardlessof the previous
> read.
> I suspect this is because conenction 2 is not reading the @.@.ERROR set
> by connection1.
> Is this the case?
Correct. Any time you get an error it propagates back to the client. For
you it will become an OleDbException. BTW why OleDbConnection and not
SqlConnection?
David|||Thanks David
I used OleDb because I read that it was more verstile and my code will
eventually be ported to other databases. Is this incorrect?
Thanks
Jeff
On Mon, 5 Dec 2005 22:34:07 -0600, "David Browne" <davidbaxterbrowne
no potted meat@.hotmail.com> wrote:
>"Jeff User" <jeff31162@.hotmail.com> wrote in message
> news:6o3ap198n6vs7kb14h80maab9rgd9mfelg@.
4ax.com...
>Correct. Any time you get an error it propagates back to the client. For
>you it will become an OleDbException. BTW why OleDbConnection and not
>SqlConnection?
>David
>|||Hmm, do I even need to try to read the SQL server @.@.error or will I
always get the same information in my oleDb error that is raised when
there is a failure?
Thanks again
Jeff
On Mon, 5 Dec 2005 22:34:07 -0600, "David Browne" <davidbaxterbrowne
no potted meat@.hotmail.com> wrote:
>"Jeff User" <jeff31162@.hotmail.com> wrote in message
> news:6o3ap198n6vs7kb14h80maab9rgd9mfelg@.
4ax.com...
>Correct. Any time you get an error it propagates back to the client. For
>you it will become an OleDbException. BTW why OleDbConnection and not
>SqlConnection?
>David
>|||"Jeff User" <jeff31162@.hotmail.com> wrote in message
news:486ap1ppcjptrckbde41oekh84e2srj163@.
4ax.com...
> Hmm, do I even need to try to read the SQL server @.@.error or will I
> always get the same information in my oleDb error that is raised when
> there is a failure?
That's an OleDB implementation detail I don't know. With the SqlClient, the
Error number and message are available.
David|||"Jeff User" <jeff31162@.hotmail.com> wrote in message
news:746ap1dmf2hgclqarvfe2gd219dbajcfvk@.
4ax.com...
> Thanks David
> I used OleDb because I read that it was more verstile and my code will
> eventually be ported to other databases. Is this incorrect?
That's one way. But probably not the preferred way. It isolates the
differences between databases out to a layer you have very little access to,
and forces you to use COM interop in your code. All the .NET data providers
implement a set of common interfaces, so you can support multiple databases
and still use the best client library for each one.
See:
Generic Coding with the ADO.NET 2.0 Base Classes and Factories
http://msdn.microsoft.com/library/d...r />
erics.asp
Data Access Application Block for ADO.NET 1.1
http://msdn.microsoft.com/practices...2/html/daab.asp
David|||OK, I can do some research/testing on this one.
Thanks for both replies and I will look into the sqlClient and check
out the articles.
jeff
On Mon, 5 Dec 2005 23:53:35 -0600, "David Browne" <davidbaxterbrowne
no potted meat@.hotmail.com> wrote:
>"Jeff User" <jeff31162@.hotmail.com> wrote in message
> news:486ap1ppcjptrckbde41oekh84e2srj163@.
4ax.com...
>That's an OleDB implementation detail I don't know. With the SqlClient, th
e
>Error number and message are available.
>David
>
@@Error not trapping
Im having to convert dates for an mainframe file. 3/3/2005 to 03032005. So I
datepart each portion of the date and then add zeros when needed and string
it back together.
When trying to do some of the checks on the dates
set @.Month=convert(varchar(2),DATEPART(month
, @.myDate))
if(@.@.ERROR=0)
begin
--set @.Day
--set @.Year
end
else
begin
set @.NewDate=@.myDate
end
the SQL will blow up on the first line SOMETIMES
Its like it doesnt bother to exec the if(@.@.ERROR) Yet other time even with
the error it keeps on chugging along
Whats the deal?
JP
.NET Software DevelperIn case that @.myDate is type of varchar.
Datepart will raise an error only when you pass a value which cannot be
recognized as valid datetime format. Datepart of null returns null and,
consequently, Conversion of null returns null => no error.
MSDN Quote:
SQL Server recognizes date and time data enclosed in single quotation marks
(') in these formats:
-Alphabetic date formats (for example, 'April 15, 1998')
-Numeric date formats (for example, '4/15/1998', 'April 15, 1998')
-Unseparated string formats (for example, '19981207', 'December 12, 1998')
In your specific case Convert function cannot raise an error.
Regards,
Marko Simic
"JP" wrote:
> Im having to convert dates for an mainframe file. 3/3/2005 to 03032005. So
I
> datepart each portion of the date and then add zeros when needed and strin
g
> it back together.
> When trying to do some of the checks on the dates
> set @.Month=convert(varchar(2),DATEPART(month
, @.myDate))
> if(@.@.ERROR=0)
> begin
> --set @.Day
> --set @.Year
> end
> else
> begin
> set @.NewDate=@.myDate
> end
> the SQL will blow up on the first line SOMETIMES
> Its like it doesnt bother to exec the if(@.@.ERROR) Yet other time even with
> the error it keeps on chugging along
> Whats the deal?
> --
> JP
> .NET Software Develper
datepart each portion of the date and then add zeros when needed and string
it back together.
When trying to do some of the checks on the dates
set @.Month=convert(varchar(2),DATEPART(month
, @.myDate))
if(@.@.ERROR=0)
begin
--set @.Day
--set @.Year
end
else
begin
set @.NewDate=@.myDate
end
the SQL will blow up on the first line SOMETIMES
Its like it doesnt bother to exec the if(@.@.ERROR) Yet other time even with
the error it keeps on chugging along
Whats the deal?
JP
.NET Software DevelperIn case that @.myDate is type of varchar.
Datepart will raise an error only when you pass a value which cannot be
recognized as valid datetime format. Datepart of null returns null and,
consequently, Conversion of null returns null => no error.
MSDN Quote:
SQL Server recognizes date and time data enclosed in single quotation marks
(') in these formats:
-Alphabetic date formats (for example, 'April 15, 1998')
-Numeric date formats (for example, '4/15/1998', 'April 15, 1998')
-Unseparated string formats (for example, '19981207', 'December 12, 1998')
In your specific case Convert function cannot raise an error.
Regards,
Marko Simic
"JP" wrote:
> Im having to convert dates for an mainframe file. 3/3/2005 to 03032005. So
I
> datepart each portion of the date and then add zeros when needed and strin
g
> it back together.
> When trying to do some of the checks on the dates
> set @.Month=convert(varchar(2),DATEPART(month
, @.myDate))
> if(@.@.ERROR=0)
> begin
> --set @.Day
> --set @.Year
> end
> else
> begin
> set @.NewDate=@.myDate
> end
> the SQL will blow up on the first line SOMETIMES
> Its like it doesnt bother to exec the if(@.@.ERROR) Yet other time even with
> the error it keeps on chugging along
> Whats the deal?
> --
> JP
> .NET Software Develper
@@Error not catching error.
Hi all,
If you are on SQL 2005, you need to use TRY-CATCH. If you are using SQL
2000, you first need to upgrade to SQL 2005. In SQL 2000 you cannot detect
this error, because the entire batch is aborted because of the error.
If you want to know more about error handling in SQL 2000, I have an
article on my web site: http://www.sommarskog.se/error-handling-I.html.
If you are on SQL 2005, you need to use TRY-CATCH. If you are using SQL
2000, you first need to upgrade to SQL 2005. In SQL 2000 you cannot detect
this error, because the entire batch is aborted because of the error.
If you want to know more about error handling in SQL 2000, I have an
article on my web site: http://www.sommarskog.se/error-handling-I.html.
I want to catch error in stored procedure and return error message.
I want to catch error 'Syntax error converting the varchar value 'a'
to a column of data type int.' Means error occuring if i enter wrong
value.
Say suppose i have statment like
select * from emp where rowid = 'a'
PRINT @.@.ERROR
print 'reach'
here rowid is integer value so i am getting above mention error.
So what i am expecting is it should print error and then print 'reach'
which is not happening.
can anyone tell me reason behind this and how to overcome this
problem.
thanks in advance.(trialproduct2004@.yahoo.com) writes:
Quote:
Originally Posted by
I want to catch error in stored procedure and return error message.
I want to catch error 'Syntax error converting the varchar value 'a'
to a column of data type int.' Means error occuring if i enter wrong
value.
>
Say suppose i have statment like
>
select * from emp where rowid = 'a'
PRINT @.@.ERROR
print 'reach'
>
here rowid is integer value so i am getting above mention error.
>
So what i am expecting is it should print error and then print 'reach'
which is not happening.
can anyone tell me reason behind this and how to overcome this
problem.
If you are on SQL 2005, you need to use TRY-CATCH. If you are using SQL
2000, you first need to upgrade to SQL 2005. In SQL 2000 you cannot detect
this error, because the entire batch is aborted because of the error.
If you want to know more about error handling in SQL 2000, I have an
article on my web site: http://www.sommarskog.se/error-handling-I.html.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||(trialproduct2004@.yahoo.com) writes:
Quote:
Originally Posted by
I want to catch error in stored procedure and return error message.
I want to catch error 'Syntax error converting the varchar value 'a'
to a column of data type int.' Means error occuring if i enter wrong
value.
>
Say suppose i have statment like
>
select * from emp where rowid = 'a'
PRINT @.@.ERROR
print 'reach'
>
here rowid is integer value so i am getting above mention error.
>
So what i am expecting is it should print error and then print 'reach'
which is not happening.
can anyone tell me reason behind this and how to overcome this
problem.
If you are on SQL 2005, you need to use TRY-CATCH. If you are using SQL
2000, you first need to upgrade to SQL 2005. In SQL 2000 you cannot detect
this error, because the entire batch is aborted because of the error.
If you want to know more about error handling in SQL 2000, I have an
article on my web site: http://www.sommarskog.se/error-handling-I.html.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
@@ERROR logic not working
Hi There
I am having a little trouble with something very simple:
I have a stored procedure with the following sql in it:
UPDATE mfsWS_CustNewCustomer SET Result_Id = 0 WHERE QueryGUID = @.Guid
IF (@.@.ERROR <> 0)
BEGIN
RAISERROR ('Error setting Result_Id', 16, 1)
UPDATE mfsWS_CustNewCustomer SET Result_Id = 1 WHERE QueryGUID = @.Guid
RETURN
END
To test if this works i SET the Result_Id = 'X' this is a smallint column so this should not work.
My issue is that all i get is the error:
cannot convert char to numeric etc etc
However the @.@.ERROR logic is not executed , my error is not raised and the update does nto happen, it si as though the entire sp aborts at the point where i try do the bogus update.
Is there something i should SET at the beginning of my sp or something ? How do i ensure that any error will be passed to the logic that checks @.@.ERROR and performs the appropriate actions ? This is SS2000, so i cannot use try catch.
Thanx
Unfortunately, you can't. There isn't a way to trap any and all kinds of errors in your T-SQL code, since some errors terminates the batch. T-SQL code after the point where the error happens, never gets executed. In your case, a conversion error are of the 'breaking' kind.
You can verify with a small example. Note that the last line never gets printed.
declare @.i int
set @.i = 'x'
print 'Error happened'
go
Server: Msg 245, Level 16, State 1, Line 2
Syntax error converting the varchar value 'x' to a column of data type int.
In order to trap all kinds of errors, you need to trap the at the client (calling side) of the procedure. (if there is a 'client' of some sorts)
You can find more info on errorhandling here.
http://www.sommarskog.se/error-handling-II.html
/Kenneth
|||Thanx Kenneth
So what can i do ?
What is the point of doing a IF (@.@.ERROR) check after a delete update or insert then?
I have to update a certain table to set the error code to 1 if an error occurs, is there no way to do this?
Thanx
|||Well, it's a bit of a tight spot. T-SQL errorhandling possibilites are somewhat limited pre SS2005. The one certain point that all errors go to is to the 'client'. In the case of a procedure, the 'client' is whatever executed it. If this is another proc, then you still have the same problem. If it's some other 'external' program, then that's where all errors will land.
Please take some time and read through the link about errorhandling above. It's well spent time, I assure you. Errorhandling is like any other 'project', it needs afterthought and desicions must be made about what to do in various situations. It needs to be designed with understanding in order to serve it's purpose best - to give us robust and reliable code.
In this case, I don't think that the mission is impossible, but rather not as straightforward as we would like, due to the limitations and behaviour of some errors in T-SQL. But, to grant yourself some insight, and possibly a direction to go on how you want to handle your situattion, http://www.sommarskog.se/error-handling-II.html is a good place to start. =;o)
/Kenneth
|||Thanx Kewin
I will definately read the link.
Yes it is a problem i have an sp that calls an sp, i have written it so that if @.@.ERROR <> 0 i RETURN @.@.ERROR and handle it in the calling sp, but in this case the execution will abort and not return the @.@.ERROR to the calling sp to be handled.
But let me first read the link and i will post again.
Thank You
|||Hi Kewin
Am i correct in saying that my specific test which has to do with char to numeric implicit cast will cause execution to abort BUT if the update or insert for example violated a primary key constriant or something more general that the execution would continue and execute the T-SQL in the IF @.@.ERROR <> 0 logic ?
|||Correct. Not all errors are transaction aborting or batch aborting errors. For example, syntax errors or compile-time errors cannot be caught with exception handling in SQL Server 2005 also. Please check out Erland's home page and the error handling topics in BOL.|||To add a pennies worth, the big twist to error handling (even in 2005, though you have the control of try...catch) is the client. If you have a well behaved client like Query Analyzer or SSMS, then the IF@.@.error logic is very important, because it will always return control after an error if the error is not batch terminating. However, almost every client I see stops executing on an error and ends the batch itself. In those cases it is important to handle the errors with that in mind.
Things like
<DML operation>
if @.@.error <> 0
begin
raiserror ('boom',16,1)
rollback transaction
return -100
end
Look good, but if the client never completes the message and returns control, you can be stuck with an open transaction. And even worse, you probably never reach the raiserror, rollback, or return if the error in the DML operation occurs.
But you have to be ready for it nevertheless...
|||Thanx for all the feedback guys.
I have read the link Kewin and i know most of it but l did learn one or 2 new things, thanx.
I guess what i am really trying to ask is as far as SS2000 goes there is not anything more i can do in an stored procedure than :
IF @.@.ERROR <>0
BEGIN
RASIERROR
ROLLBACK TRAN
RETURN -1
END
These sp's are called by a .Net app i understand completely that the app must be ready for any kind of exception.
What i really want to confirm is that there is nothing more or better i could do from an sp error handling point of view?
Thanx
|||You got it.
Basically, that's what is avaliable to us as far as errorhandling in T-SQL on SS 2000 goes.
A workaround of sorts around these limitations can be done, and when I think about it, that's pretty much what 'scrubbing' is all about. For known 'fatal' errors that you know you can't trap, and possibly could leave you in some sticky situation, it's common to try to ensure that the situation never happens, either by first validating thoroughly, or by different coding techniques.
eg, to avoid a PK violation when inserting a new row, you could first check for the existence, or (perhaps even better) do the insert based on a left joined select. In the latter case, if the PK didn't exist, 1 row would be affected, but should it already exist, 0 rows would be affected. But more importantly, it's not an error to do some DML and have zero rows affected. Approaching a problem from different angles may be of aid to circumvent some of the errorhandling limitations that we have.
/Kenneth
sql@@ERROR is not being populated
Hi All,
I ran the code below in QA, and @.@.ERROR never gets populated with any error code, despite the fact that an error occured. This event is very misleading and errors are never caught. Any advice greatly appreciated.
-- Create the table
create table test_table (
test_field int
)
-- Execute the code
DECLARE @.err_status int, @.row_count int
insert into test_table (
test_field
)
values (
'TEST STRING'
)
select @.err_status = @.@.ERROR, @.row_count = @.@.ROWCOUNT
IF @.err_status <> 0 PRINT @.err_status
-- Results:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'TEST STRING' to a column of data type int.
The reason is that the statements after insert never get executed. Some TSQL errors abort the batch execution or compile-time errors for example. Please check out the links below for more information:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
@@error in vb6
Hi everyone!
How can I read the value of @.@.error from within vb6 source? Or how can I check the success of a transaction (comit/rollback) from within vb6 frontend source?
Thank you!I believe you will have to pass it back as an output parameter from your stored procedure. That's how I do it anyway.|||To capture @.@.error you can either store its value into an output parameter, or return it in the form of a result set.
To trap an execution error and notify your FE of a specific section of BE code that encountered the error use RAISERROR. In your VB code precede the call to SQL with ON ERROR (either RESUME NEXT and check for error right below, or GOTO <LABEL> and do the error trapping there).
EDITED: At the end make sure to turn error-fall-through OFF by issueing ON ERROR GOTO 0. Failure to do so often yields hours of debugging.|||ON ERROR GOTO 0. Failure to do so often yields hours of debugging.
Couldn't agree more. That's why I don't use it. Way too many sleepless nights trying to figure out why the stupid app isn't giving me the results I expect.
On Error Goto BeerFridge
Now that works :)
How can I read the value of @.@.error from within vb6 source? Or how can I check the success of a transaction (comit/rollback) from within vb6 frontend source?
Thank you!I believe you will have to pass it back as an output parameter from your stored procedure. That's how I do it anyway.|||To capture @.@.error you can either store its value into an output parameter, or return it in the form of a result set.
To trap an execution error and notify your FE of a specific section of BE code that encountered the error use RAISERROR. In your VB code precede the call to SQL with ON ERROR (either RESUME NEXT and check for error right below, or GOTO <LABEL> and do the error trapping there).
EDITED: At the end make sure to turn error-fall-through OFF by issueing ON ERROR GOTO 0. Failure to do so often yields hours of debugging.|||ON ERROR GOTO 0. Failure to do so often yields hours of debugging.
Couldn't agree more. That's why I don't use it. Way too many sleepless nights trying to figure out why the stupid app isn't giving me the results I expect.
On Error Goto BeerFridge
Now that works :)
@@ERROR in mult thread environment?
Hi all,
I would like know if I can use @.@.ERROR to error in mult thread environment?
For sample:
If this SP is executed by 2 threads in same time, I wiil have correct value
in @.@.ERROR
Thanks
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'terminal_ativo_sp'
AND type = 'P')
DROP PROCEDURE terminal_ativo_sp
GO
CREATE PROCEDURE terminal_ativo_sp
@.tid uterminalid,
@.ret INT OUTPUT
WITH ENCRYPTION
AS
BEGIN
-- declare vars
-- ----
--
DECLARE @.ativo BIT
-- init vars (assume SYS ERROR)
-- ----
--
SET @.ret = 101
SET @.ativo = 0
-- do work
-- ----
--
SELECT @.ativo = ativo FROM estabelecimentos_terminais WHERE terminal_id =
@.tid
-- check error
-- ----
--
IF (@.@.ERROR <> 0)
BEGIN
SET @.ret = 1
RETURN
END
-- check @.ativo
-- ----
--
IF (@.ativo = 1)
BEGIN
SET @.ret = 0
RETURN
END
ENDIts specific to the database connection and is not affected by any other
connection.
So, yes your code is fine.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:uAcEXet5FHA.1276@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I would like know if I can use @.@.ERROR to error in mult thread
> environment?
> For sample:
> If this SP is executed by 2 threads in same time, I wiil have correct
> value in @.@.ERROR
> Thanks
>
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'terminal_ativo_sp'
> AND type = 'P')
> DROP PROCEDURE terminal_ativo_sp
> GO
> CREATE PROCEDURE terminal_ativo_sp
> @.tid uterminalid,
> @.ret INT OUTPUT
> WITH ENCRYPTION
> AS
> BEGIN
> -- declare vars
> -- ----
--
> DECLARE @.ativo BIT
> -- init vars (assume SYS ERROR)
> -- ----
--
> SET @.ret = 101
> SET @.ativo = 0
> -- do work
> -- ----
--
> SELECT @.ativo = ativo FROM estabelecimentos_terminais WHERE terminal_id =
> @.tid
> -- check error
> -- ----
--
> IF (@.@.ERROR <> 0)
> BEGIN
> SET @.ret = 1
> RETURN
> END
> -- check @.ativo
> -- ----
--
> IF (@.ativo = 1)
> BEGIN
> SET @.ret = 0
> RETURN
> END
> END
>
I would like know if I can use @.@.ERROR to error in mult thread environment?
For sample:
If this SP is executed by 2 threads in same time, I wiil have correct value
in @.@.ERROR
Thanks
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'terminal_ativo_sp'
AND type = 'P')
DROP PROCEDURE terminal_ativo_sp
GO
CREATE PROCEDURE terminal_ativo_sp
@.tid uterminalid,
@.ret INT OUTPUT
WITH ENCRYPTION
AS
BEGIN
-- declare vars
-- ----
--
DECLARE @.ativo BIT
-- init vars (assume SYS ERROR)
-- ----
--
SET @.ret = 101
SET @.ativo = 0
-- do work
-- ----
--
SELECT @.ativo = ativo FROM estabelecimentos_terminais WHERE terminal_id =
@.tid
-- check error
-- ----
--
IF (@.@.ERROR <> 0)
BEGIN
SET @.ret = 1
RETURN
END
-- check @.ativo
-- ----
--
IF (@.ativo = 1)
BEGIN
SET @.ret = 0
RETURN
END
ENDIts specific to the database connection and is not affected by any other
connection.
So, yes your code is fine.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:uAcEXet5FHA.1276@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I would like know if I can use @.@.ERROR to error in mult thread
> environment?
> For sample:
> If this SP is executed by 2 threads in same time, I wiil have correct
> value in @.@.ERROR
> Thanks
>
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'terminal_ativo_sp'
> AND type = 'P')
> DROP PROCEDURE terminal_ativo_sp
> GO
> CREATE PROCEDURE terminal_ativo_sp
> @.tid uterminalid,
> @.ret INT OUTPUT
> WITH ENCRYPTION
> AS
> BEGIN
> -- declare vars
> -- ----
--
> DECLARE @.ativo BIT
> -- init vars (assume SYS ERROR)
> -- ----
--
> SET @.ret = 101
> SET @.ativo = 0
> -- do work
> -- ----
--
> SELECT @.ativo = ativo FROM estabelecimentos_terminais WHERE terminal_id =
> @.tid
> -- check error
> -- ----
--
> IF (@.@.ERROR <> 0)
> BEGIN
> SET @.ret = 1
> RETURN
> END
> -- check @.ativo
> -- ----
--
> IF (@.ativo = 1)
> BEGIN
> SET @.ret = 0
> RETURN
> END
> END
>
@@Error help
Im not really understanding how @.@.Error works. If I run this code:
exec sp_rename 'Table3','Table1'
select @.@.Error
in a DB with no Table3, I get the error message:
Server: Msg 15225, Level 11, State 1, Procedure sp_rename, Line 273
No item by the name of 'Table3' could be found in the current database
'ZipCode', given that @.itemtype was input as '(null)'.
but @.@.Error is still 0. If Im getting an error message, why is @.@.Error 0?
TIA,
ChrisR
run this for fun
create table blah (id int)
declare @.id int
EXEC @.id =sp_rename 'blah', 'blah2'
select @.id
--@.id = 0
--run again
declare @.id int
EXEC @.id =sp_rename 'blah', 'blah2'
select @.id
--@.id = 1
So it looks like it returns 0 when there is no error
http://sqlservercode.blogspot.com/
|||I dont get it... I need to make it fail twice in order to be used?
TIA,
ChrisR
"SQL" wrote:
> run this for fun
> create table blah (id int)
> declare @.id int
> EXEC @.id =sp_rename 'blah', 'blah2'
> select @.id
> --@.id = 0
> --run again
> declare @.id int
> EXEC @.id =sp_rename 'blah', 'blah2'
> select @.id
> --@.id = 1
>
> So it looks like it returns 0 when there is no error
> http://sqlservercode.blogspot.com/
>
|||No
I was just giving you the code so that you could test what the proc
returns
If you run this, the @.id should be 1 (or better not 0)
declare @.id int
exec sp_rename 'Table3','Table1'
select @.id
|||Certain types of errors are not trapped by @.@.ERROR, or the new TRY...CATCH
in SQL Server 2005. Basically, things like syntax errors or invalid object
references can prevent the batch from being successfully compiled and
executed. @.@.ERROR and TRY...CATCH only get invoked if the batch was
successfully compiled and executed. So errors like a WHERE clause that
references and invalid key value or something that does a divide by zero
will generate errors during the execution of the batch that can be processed
by @.@.ERROR or TRY...CATCH. A typo like writing SEELCT instead of SELECT
prevents the batch from being compiled, so the Database Engine simply
returns the syntax error to the calling application.
Alan Brewer [MSFT]
Content Architect, SQL Server Documentation Team
SQL Server Developer Center: http://msdn.microsoft.com/sql
SQL Server TechCenter: http://technet.microsoft.com/sql/
This posting is provided "AS IS" with no warranties, and confers no rights.
|||See http://msdn2.microsoft.com/en-us/library/ms190193.aspx
for more information on how to properly use @.@.ERROR.
/sym.
symbiote
Posted via http://www.codecomments.com
exec sp_rename 'Table3','Table1'
select @.@.Error
in a DB with no Table3, I get the error message:
Server: Msg 15225, Level 11, State 1, Procedure sp_rename, Line 273
No item by the name of 'Table3' could be found in the current database
'ZipCode', given that @.itemtype was input as '(null)'.
but @.@.Error is still 0. If Im getting an error message, why is @.@.Error 0?
TIA,
ChrisR
run this for fun
create table blah (id int)
declare @.id int
EXEC @.id =sp_rename 'blah', 'blah2'
select @.id
--@.id = 0
--run again
declare @.id int
EXEC @.id =sp_rename 'blah', 'blah2'
select @.id
--@.id = 1
So it looks like it returns 0 when there is no error
http://sqlservercode.blogspot.com/
|||I dont get it... I need to make it fail twice in order to be used?
TIA,
ChrisR
"SQL" wrote:
> run this for fun
> create table blah (id int)
> declare @.id int
> EXEC @.id =sp_rename 'blah', 'blah2'
> select @.id
> --@.id = 0
> --run again
> declare @.id int
> EXEC @.id =sp_rename 'blah', 'blah2'
> select @.id
> --@.id = 1
>
> So it looks like it returns 0 when there is no error
> http://sqlservercode.blogspot.com/
>
|||No
I was just giving you the code so that you could test what the proc
returns
If you run this, the @.id should be 1 (or better not 0)
declare @.id int
exec sp_rename 'Table3','Table1'
select @.id
|||Certain types of errors are not trapped by @.@.ERROR, or the new TRY...CATCH
in SQL Server 2005. Basically, things like syntax errors or invalid object
references can prevent the batch from being successfully compiled and
executed. @.@.ERROR and TRY...CATCH only get invoked if the batch was
successfully compiled and executed. So errors like a WHERE clause that
references and invalid key value or something that does a divide by zero
will generate errors during the execution of the batch that can be processed
by @.@.ERROR or TRY...CATCH. A typo like writing SEELCT instead of SELECT
prevents the batch from being compiled, so the Database Engine simply
returns the syntax error to the calling application.
Alan Brewer [MSFT]
Content Architect, SQL Server Documentation Team
SQL Server Developer Center: http://msdn.microsoft.com/sql
SQL Server TechCenter: http://technet.microsoft.com/sql/
This posting is provided "AS IS" with no warranties, and confers no rights.
|||See http://msdn2.microsoft.com/en-us/library/ms190193.aspx
for more information on how to properly use @.@.ERROR.
/sym.
symbiote
Posted via http://www.codecomments.com
@@Error help
Im not really understanding how @.@.Error works. If I run this code:
exec sp_rename 'Table3','Table1'
select @.@.Error
in a DB with no Table3, I get the error message:
Server: Msg 15225, Level 11, State 1, Procedure sp_rename, Line 273
No item by the name of 'Table3' could be found in the current database
'ZipCode', given that @.itemtype was input as '(null)'.
but @.@.Error is still 0. If Im getting an error message, why is @.@.Error 0?
--
TIA,
ChrisRrun this for fun
create table blah (id int)
declare @.id int
EXEC @.id =sp_rename 'blah', 'blah2'
select @.id
--@.id = 0
--run again
declare @.id int
EXEC @.id =sp_rename 'blah', 'blah2'
select @.id
--@.id = 1
So it looks like it returns 0 when there is no error
http://sqlservercode.blogspot.com/|||I dont get it... I need to make it fail twice in order to be used?
--
TIA,
ChrisR
"SQL" wrote:
> run this for fun
> create table blah (id int)
> declare @.id int
> EXEC @.id =sp_rename 'blah', 'blah2'
> select @.id
> --@.id = 0
> --run again
> declare @.id int
> EXEC @.id =sp_rename 'blah', 'blah2'
> select @.id
> --@.id = 1
>
> So it looks like it returns 0 when there is no error
> http://sqlservercode.blogspot.com/
>|||No
I was just giving you the code so that you could test what the proc
returns
If you run this, the @.id should be 1 (or better not 0)
declare @.id int
exec sp_rename 'Table3','Table1'
select @.id|||Certain types of errors are not trapped by @.@.ERROR, or the new TRY...CATCH
in SQL Server 2005. Basically, things like syntax errors or invalid object
references can prevent the batch from being successfully compiled and
executed. @.@.ERROR and TRY...CATCH only get invoked if the batch was
successfully compiled and executed. So errors like a WHERE clause that
references and invalid key value or something that does a divide by zero
will generate errors during the execution of the batch that can be processed
by @.@.ERROR or TRY...CATCH. A typo like writing SEELCT instead of SELECT
prevents the batch from being compiled, so the Database Engine simply
returns the syntax error to the calling application.
--
Alan Brewer [MSFT]
Content Architect, SQL Server Documentation Team
SQL Server Developer Center: http://msdn.microsoft.com/sql
SQL Server TechCenter: http://technet.microsoft.com/sql/
This posting is provided "AS IS" with no warranties, and confers no rights.|||See [url]http://msdn2.microsoft.com/en-us/library/ms190193.aspx[/url]
for more information on how to properly use @.@.ERROR.
/sym.
symbiote
---
Posted via http://www.codecomments.com
---sql
exec sp_rename 'Table3','Table1'
select @.@.Error
in a DB with no Table3, I get the error message:
Server: Msg 15225, Level 11, State 1, Procedure sp_rename, Line 273
No item by the name of 'Table3' could be found in the current database
'ZipCode', given that @.itemtype was input as '(null)'.
but @.@.Error is still 0. If Im getting an error message, why is @.@.Error 0?
--
TIA,
ChrisRrun this for fun
create table blah (id int)
declare @.id int
EXEC @.id =sp_rename 'blah', 'blah2'
select @.id
--@.id = 0
--run again
declare @.id int
EXEC @.id =sp_rename 'blah', 'blah2'
select @.id
--@.id = 1
So it looks like it returns 0 when there is no error
http://sqlservercode.blogspot.com/|||I dont get it... I need to make it fail twice in order to be used?
--
TIA,
ChrisR
"SQL" wrote:
> run this for fun
> create table blah (id int)
> declare @.id int
> EXEC @.id =sp_rename 'blah', 'blah2'
> select @.id
> --@.id = 0
> --run again
> declare @.id int
> EXEC @.id =sp_rename 'blah', 'blah2'
> select @.id
> --@.id = 1
>
> So it looks like it returns 0 when there is no error
> http://sqlservercode.blogspot.com/
>|||No
I was just giving you the code so that you could test what the proc
returns
If you run this, the @.id should be 1 (or better not 0)
declare @.id int
exec sp_rename 'Table3','Table1'
select @.id|||Certain types of errors are not trapped by @.@.ERROR, or the new TRY...CATCH
in SQL Server 2005. Basically, things like syntax errors or invalid object
references can prevent the batch from being successfully compiled and
executed. @.@.ERROR and TRY...CATCH only get invoked if the batch was
successfully compiled and executed. So errors like a WHERE clause that
references and invalid key value or something that does a divide by zero
will generate errors during the execution of the batch that can be processed
by @.@.ERROR or TRY...CATCH. A typo like writing SEELCT instead of SELECT
prevents the batch from being compiled, so the Database Engine simply
returns the syntax error to the calling application.
--
Alan Brewer [MSFT]
Content Architect, SQL Server Documentation Team
SQL Server Developer Center: http://msdn.microsoft.com/sql
SQL Server TechCenter: http://technet.microsoft.com/sql/
This posting is provided "AS IS" with no warranties, and confers no rights.|||See [url]http://msdn2.microsoft.com/en-us/library/ms190193.aspx[/url]
for more information on how to properly use @.@.ERROR.
/sym.
symbiote
---
Posted via http://www.codecomments.com
---sql
@@Error help
Im not really understanding how @.@.Error works. If I run this code:
exec sp_rename 'Table3','Table1'
select @.@.Error
in a DB with no Table3, I get the error message:
Server: Msg 15225, Level 11, State 1, Procedure sp_rename, Line 273
No item by the name of 'Table3' could be found in the current database
'ZipCode', given that @.itemtype was input as '(null)'.
but @.@.Error is still 0. If Im getting an error message, why is @.@.Error 0?
TIA,
ChrisRrun this for fun
create table blah (id int)
declare @.id int
EXEC @.id =sp_rename 'blah', 'blah2'
select @.id
--@.id = 0
--run again
declare @.id int
EXEC @.id =sp_rename 'blah', 'blah2'
select @.id
--@.id = 1
So it looks like it returns 0 when there is no error
http://sqlservercode.blogspot.com/|||I dont get it... I need to make it fail twice in order to be used?
--
TIA,
ChrisR
"SQL" wrote:
> run this for fun
> create table blah (id int)
> declare @.id int
> EXEC @.id =sp_rename 'blah', 'blah2'
> select @.id
> --@.id = 0
> --run again
> declare @.id int
> EXEC @.id =sp_rename 'blah', 'blah2'
> select @.id
> --@.id = 1
>
> So it looks like it returns 0 when there is no error
> http://sqlservercode.blogspot.com/
>|||No
I was just giving you the code so that you could test what the proc
returns
If you run this, the @.id should be 1 (or better not 0)
declare @.id int
exec sp_rename 'Table3','Table1'
select @.id|||Certain types of errors are not trapped by @.@.ERROR, or the new TRY...CATCH
in SQL Server 2005. Basically, things like syntax errors or invalid object
references can prevent the batch from being successfully compiled and
executed. @.@.ERROR and TRY...CATCH only get invoked if the batch was
successfully compiled and executed. So errors like a WHERE clause that
references and invalid key value or something that does a divide by zero
will generate errors during the execution of the batch that can be processed
by @.@.ERROR or TRY...CATCH. A typo like writing SEELCT instead of SELECT
prevents the batch from being compiled, so the Database Engine simply
returns the syntax error to the calling application.
Alan Brewer [MSFT]
Content Architect, SQL Server Documentation Team
SQL Server Developer Center: http://msdn.microsoft.com/sql
SQL Server TechCenter: http://technet.microsoft.com/sql/
This posting is provided "AS IS" with no warranties, and confers no rights.|||See [url]http://msdn2.microsoft.com/en-us/library/ms190193.aspx[/url
]
for more information on how to properly use @.@.ERROR.
/sym.
symbiote
---
Posted via http://www.codecomments.com
---
exec sp_rename 'Table3','Table1'
select @.@.Error
in a DB with no Table3, I get the error message:
Server: Msg 15225, Level 11, State 1, Procedure sp_rename, Line 273
No item by the name of 'Table3' could be found in the current database
'ZipCode', given that @.itemtype was input as '(null)'.
but @.@.Error is still 0. If Im getting an error message, why is @.@.Error 0?
TIA,
ChrisRrun this for fun
create table blah (id int)
declare @.id int
EXEC @.id =sp_rename 'blah', 'blah2'
select @.id
--@.id = 0
--run again
declare @.id int
EXEC @.id =sp_rename 'blah', 'blah2'
select @.id
--@.id = 1
So it looks like it returns 0 when there is no error
http://sqlservercode.blogspot.com/|||I dont get it... I need to make it fail twice in order to be used?
--
TIA,
ChrisR
"SQL" wrote:
> run this for fun
> create table blah (id int)
> declare @.id int
> EXEC @.id =sp_rename 'blah', 'blah2'
> select @.id
> --@.id = 0
> --run again
> declare @.id int
> EXEC @.id =sp_rename 'blah', 'blah2'
> select @.id
> --@.id = 1
>
> So it looks like it returns 0 when there is no error
> http://sqlservercode.blogspot.com/
>|||No
I was just giving you the code so that you could test what the proc
returns
If you run this, the @.id should be 1 (or better not 0)
declare @.id int
exec sp_rename 'Table3','Table1'
select @.id|||Certain types of errors are not trapped by @.@.ERROR, or the new TRY...CATCH
in SQL Server 2005. Basically, things like syntax errors or invalid object
references can prevent the batch from being successfully compiled and
executed. @.@.ERROR and TRY...CATCH only get invoked if the batch was
successfully compiled and executed. So errors like a WHERE clause that
references and invalid key value or something that does a divide by zero
will generate errors during the execution of the batch that can be processed
by @.@.ERROR or TRY...CATCH. A typo like writing SEELCT instead of SELECT
prevents the batch from being compiled, so the Database Engine simply
returns the syntax error to the calling application.
Alan Brewer [MSFT]
Content Architect, SQL Server Documentation Team
SQL Server Developer Center: http://msdn.microsoft.com/sql
SQL Server TechCenter: http://technet.microsoft.com/sql/
This posting is provided "AS IS" with no warranties, and confers no rights.|||See [url]http://msdn2.microsoft.com/en-us/library/ms190193.aspx[/url
]
for more information on how to properly use @.@.ERROR.
/sym.
symbiote
---
Posted via http://www.codecomments.com
---
@@ERROR Handling
To All Gurus:
I am trying to log some information to another table when @.@.Error returns an
error. Below is the sample code to see how @.@.error works. In this sample
code, I am purposely failing INSERT INTO TEST command and want to log
informaiton in TestLog. Even though there is a error but the insert to
TestLog never works. What is the catch here ?
/* Create Sample Tables */
Create Table Test (ID Smallint)
Create Table TestLog (ID Smallint)
/*Fail the Insert statement */
INSERT INTO TEST Values (10, 20)
/*This insert should work because error exists. */
IF @.@.Error <> 0
BEGIN
INSERT INTO TESTLOG Values (10, 20)
END
/* No Records are found*/
Select * from testlog
If I run this code in pieces one by one, the code works. When I highlight
the entire code and run it, it never makes it to TestLog table.
Thanks in advance.Sorry, the second Insert statment was
INSERT INTO TestLog Values (10)
"Mark" wrote:
> To All Gurus:
> I am trying to log some information to another table when @.@.Error returns
an
> error. Below is the sample code to see how @.@.error works. In this sample
> code, I am purposely failing INSERT INTO TEST command and want to log
> informaiton in TestLog. Even though there is a error but the insert to
> TestLog never works. What is the catch here ?
> /* Create Sample Tables */
> Create Table Test (ID Smallint)
> Create Table TestLog (ID Smallint)
> /*Fail the Insert statement */
> INSERT INTO TEST Values (10, 20)
> /*This insert should work because error exists. */
> IF @.@.Error <> 0
> BEGIN
> INSERT INTO TESTLOG Values (10, 20)
> END
> /* No Records are found*/
> Select * from testlog
> If I run this code in pieces one by one, the code works. When I highlight
> the entire code and run it, it never makes it to TestLog table.
> Thanks in advance.
>
>|||Mark,
See Erland's articles:
http://www.sommarskog.se/error-handling-I.html
and
http://www.sommarskog.se/error-handling-II.html
HTH
Jerry
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:87927DFA-D785-42B2-BF22-972996A8F852@.microsoft.com...
> To All Gurus:
> I am trying to log some information to another table when @.@.Error returns
> an
> error. Below is the sample code to see how @.@.error works. In this sample
> code, I am purposely failing INSERT INTO TEST command and want to log
> informaiton in TestLog. Even though there is a error but the insert to
> TestLog never works. What is the catch here ?
> /* Create Sample Tables */
> Create Table Test (ID Smallint)
> Create Table TestLog (ID Smallint)
> /*Fail the Insert statement */
> INSERT INTO TEST Values (10, 20)
> /*This insert should work because error exists. */
> IF @.@.Error <> 0
> BEGIN
> INSERT INTO TESTLOG Values (10, 20)
> END
> /* No Records are found*/
> Select * from testlog
> If I run this code in pieces one by one, the code works. When I highlight
> the entire code and run it, it never makes it to TestLog table.
> Thanks in advance.
>
>|||I read the article but it still didint make it clear as to why Insert into
TestLog never happened when the entire code was highlighted and executed.
Any comments on that one ?
"Mark" wrote:
> To All Gurus:
> I am trying to log some information to another table when @.@.Error returns
an
> error. Below is the sample code to see how @.@.error works. In this sample
> code, I am purposely failing INSERT INTO TEST command and want to log
> informaiton in TestLog. Even though there is a error but the insert to
> TestLog never works. What is the catch here ?
> /* Create Sample Tables */
> Create Table Test (ID Smallint)
> Create Table TestLog (ID Smallint)
> /*Fail the Insert statement */
> INSERT INTO TEST Values (10, 20)
> /*This insert should work because error exists. */
> IF @.@.Error <> 0
> BEGIN
> INSERT INTO TESTLOG Values (10, 20)
> END
> /* No Records are found*/
> Select * from testlog
> If I run this code in pieces one by one, the code works. When I highlight
> the entire code and run it, it never makes it to TestLog table.
> Thanks in advance.
>
>|||The insert failure aborts the batch, not the statement. If you put GO
between the statements, it should work. However you cannot use a
multi-batch technique inside of an object (e.g. stored procedure).
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:52B1E1E2-2422-4728-8447-FA4CB25F576E@.microsoft.com...
>I read the article but it still didint make it clear as to why Insert into
> TestLog never happened when the entire code was highlighted and executed.
> Any comments on that one ?
> "Mark" wrote:
>|||Mark,
I think it has to do with the type of error you're creating. If you add a
CHECK constraint to Test and perform your insert and it violates the insert
then the value IS inserted into the TestLog table.
HTH
Jerry
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:52B1E1E2-2422-4728-8447-FA4CB25F576E@.microsoft.com...
>I read the article but it still didint make it clear as to why Insert into
> TestLog never happened when the entire code was highlighted and executed.
> Any comments on that one ?
> "Mark" wrote:
>|||Thanks to both Jerry and Aaron. You are right, it has to do with what kind o
f
insert error it is. It either rollsback the batch or logs the info based on
the type of error. I created two scenarions and in one case it works and in
other one, it doesnt.
Once again thanks to both
"Jerry Spivey" wrote:
> Mark,
> I think it has to do with the type of error you're creating. If you add a
> CHECK constraint to Test and perform your insert and it violates the inser
t
> then the value IS inserted into the TestLog table.
> HTH
> Jerry
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:52B1E1E2-2422-4728-8447-FA4CB25F576E@.microsoft.com...
>
>|||Mark (Mark@.discussions.microsoft.com) writes:
> Thanks to both Jerry and Aaron. You are right, it has to do with what
> kind of insert error it is. It either rollsback the batch or logs the
> info based on the type of error. I created two scenarions and in one
> case it works and in other one, it doesnt.
As noted in my article, an error can lead to termination on three
different levels:
1) Statement
2) Scope
3) Bacth.
Your error was of the second kind. Scope-aborting errors are, as far as
I know, always compilation errors. What is tricky, is that due to
deferred named resolution, compilation errors can happen at run-time.
Consider:
Create Table Test (ID Smallint)
Create Table TestLog (ID Smallint)
--go
print 'Hello!'
/*Fail the Insert statement */
INSERT INTO Test Values (10, 20)
/*This insert should work because error exists. */
IF @.@.Error <> 0
BEGIN
INSERT INTO TestLog Values (10)
END
go
SELECT * FROM TestLog
This script will print Hello!, but if you uncomment the go, it will not.
This is because in the script as it stands, Test does not exist, so SQL
Server defers compilation of that statement. If you uncomment the go,
the tables exists when the batch is compiled, and thus you get the error
directly, so execution never starts.
Finally, note that if you write:
INSERT INTO Test(ID) Values(10, 20)
the batch always fails to compile, as SQL Server does not need to know
the table definition. Incidently, most people agree that INSERT without
a column list is not good practice.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
I am trying to log some information to another table when @.@.Error returns an
error. Below is the sample code to see how @.@.error works. In this sample
code, I am purposely failing INSERT INTO TEST command and want to log
informaiton in TestLog. Even though there is a error but the insert to
TestLog never works. What is the catch here ?
/* Create Sample Tables */
Create Table Test (ID Smallint)
Create Table TestLog (ID Smallint)
/*Fail the Insert statement */
INSERT INTO TEST Values (10, 20)
/*This insert should work because error exists. */
IF @.@.Error <> 0
BEGIN
INSERT INTO TESTLOG Values (10, 20)
END
/* No Records are found*/
Select * from testlog
If I run this code in pieces one by one, the code works. When I highlight
the entire code and run it, it never makes it to TestLog table.
Thanks in advance.Sorry, the second Insert statment was
INSERT INTO TestLog Values (10)
"Mark" wrote:
> To All Gurus:
> I am trying to log some information to another table when @.@.Error returns
an
> error. Below is the sample code to see how @.@.error works. In this sample
> code, I am purposely failing INSERT INTO TEST command and want to log
> informaiton in TestLog. Even though there is a error but the insert to
> TestLog never works. What is the catch here ?
> /* Create Sample Tables */
> Create Table Test (ID Smallint)
> Create Table TestLog (ID Smallint)
> /*Fail the Insert statement */
> INSERT INTO TEST Values (10, 20)
> /*This insert should work because error exists. */
> IF @.@.Error <> 0
> BEGIN
> INSERT INTO TESTLOG Values (10, 20)
> END
> /* No Records are found*/
> Select * from testlog
> If I run this code in pieces one by one, the code works. When I highlight
> the entire code and run it, it never makes it to TestLog table.
> Thanks in advance.
>
>|||Mark,
See Erland's articles:
http://www.sommarskog.se/error-handling-I.html
and
http://www.sommarskog.se/error-handling-II.html
HTH
Jerry
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:87927DFA-D785-42B2-BF22-972996A8F852@.microsoft.com...
> To All Gurus:
> I am trying to log some information to another table when @.@.Error returns
> an
> error. Below is the sample code to see how @.@.error works. In this sample
> code, I am purposely failing INSERT INTO TEST command and want to log
> informaiton in TestLog. Even though there is a error but the insert to
> TestLog never works. What is the catch here ?
> /* Create Sample Tables */
> Create Table Test (ID Smallint)
> Create Table TestLog (ID Smallint)
> /*Fail the Insert statement */
> INSERT INTO TEST Values (10, 20)
> /*This insert should work because error exists. */
> IF @.@.Error <> 0
> BEGIN
> INSERT INTO TESTLOG Values (10, 20)
> END
> /* No Records are found*/
> Select * from testlog
> If I run this code in pieces one by one, the code works. When I highlight
> the entire code and run it, it never makes it to TestLog table.
> Thanks in advance.
>
>|||I read the article but it still didint make it clear as to why Insert into
TestLog never happened when the entire code was highlighted and executed.
Any comments on that one ?
"Mark" wrote:
> To All Gurus:
> I am trying to log some information to another table when @.@.Error returns
an
> error. Below is the sample code to see how @.@.error works. In this sample
> code, I am purposely failing INSERT INTO TEST command and want to log
> informaiton in TestLog. Even though there is a error but the insert to
> TestLog never works. What is the catch here ?
> /* Create Sample Tables */
> Create Table Test (ID Smallint)
> Create Table TestLog (ID Smallint)
> /*Fail the Insert statement */
> INSERT INTO TEST Values (10, 20)
> /*This insert should work because error exists. */
> IF @.@.Error <> 0
> BEGIN
> INSERT INTO TESTLOG Values (10, 20)
> END
> /* No Records are found*/
> Select * from testlog
> If I run this code in pieces one by one, the code works. When I highlight
> the entire code and run it, it never makes it to TestLog table.
> Thanks in advance.
>
>|||The insert failure aborts the batch, not the statement. If you put GO
between the statements, it should work. However you cannot use a
multi-batch technique inside of an object (e.g. stored procedure).
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:52B1E1E2-2422-4728-8447-FA4CB25F576E@.microsoft.com...
>I read the article but it still didint make it clear as to why Insert into
> TestLog never happened when the entire code was highlighted and executed.
> Any comments on that one ?
> "Mark" wrote:
>|||Mark,
I think it has to do with the type of error you're creating. If you add a
CHECK constraint to Test and perform your insert and it violates the insert
then the value IS inserted into the TestLog table.
HTH
Jerry
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:52B1E1E2-2422-4728-8447-FA4CB25F576E@.microsoft.com...
>I read the article but it still didint make it clear as to why Insert into
> TestLog never happened when the entire code was highlighted and executed.
> Any comments on that one ?
> "Mark" wrote:
>|||Thanks to both Jerry and Aaron. You are right, it has to do with what kind o
f
insert error it is. It either rollsback the batch or logs the info based on
the type of error. I created two scenarions and in one case it works and in
other one, it doesnt.
Once again thanks to both
"Jerry Spivey" wrote:
> Mark,
> I think it has to do with the type of error you're creating. If you add a
> CHECK constraint to Test and perform your insert and it violates the inser
t
> then the value IS inserted into the TestLog table.
> HTH
> Jerry
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:52B1E1E2-2422-4728-8447-FA4CB25F576E@.microsoft.com...
>
>|||Mark (Mark@.discussions.microsoft.com) writes:
> Thanks to both Jerry and Aaron. You are right, it has to do with what
> kind of insert error it is. It either rollsback the batch or logs the
> info based on the type of error. I created two scenarions and in one
> case it works and in other one, it doesnt.
As noted in my article, an error can lead to termination on three
different levels:
1) Statement
2) Scope
3) Bacth.
Your error was of the second kind. Scope-aborting errors are, as far as
I know, always compilation errors. What is tricky, is that due to
deferred named resolution, compilation errors can happen at run-time.
Consider:
Create Table Test (ID Smallint)
Create Table TestLog (ID Smallint)
--go
print 'Hello!'
/*Fail the Insert statement */
INSERT INTO Test Values (10, 20)
/*This insert should work because error exists. */
IF @.@.Error <> 0
BEGIN
INSERT INTO TestLog Values (10)
END
go
SELECT * FROM TestLog
This script will print Hello!, but if you uncomment the go, it will not.
This is because in the script as it stands, Test does not exist, so SQL
Server defers compilation of that statement. If you uncomment the go,
the tables exists when the batch is compiled, and thus you get the error
directly, so execution never starts.
Finally, note that if you write:
INSERT INTO Test(ID) Values(10, 20)
the batch always fails to compile, as SQL Server does not need to know
the table definition. Incidently, most people agree that INSERT without
a column list is not good practice.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
@@error for dynamic SQL
Does anyone know of a simple, correct way to retrieve the value of @.@.error from dynamic SQL? I can't use:SELECT @.cCmd = 'EXECUTE @.i = [' + @.cServer
+ '].master.dbo.sp_password NULL, ''junque'', '''
+ @.cLogin + ''''
EXECUTE (@.cCmd)
SELECT @.@.error -- only shows locally reported error
EXECUTE sp_executesql @.cCmd, N'@.i INT OUTPUT'
, @.iError OUTPUT -- Only shows remotely reported errorI can get the error from a local procedure (one run on this box) using the first example, or on a remote server (via RPC) using the second, but not both. Any suggestions?
-PatPI'm confused as to what your expected output should be since I'm getting errors all over the place (typical results for me).
QUERY:
Set Concat_Null_Yields_Null Off
Declare @.cCmd nvarchar(4000)
Declare @.cServer nvarchar, @.cLogin nvarchar
, @.iError int
SELECT @.cCmd = 'EXECUTE @.i = [' + @.cServer
+ '].master.dbo.sp_password NULL, ''junque'', '''
+ @.cLogin + ''''
Print @.cCmd + N' << The Command' + nchar(10)
EXECUTE (@.cCmd)
SELECT @.@.error As 'Error' -- only shows locally reported error
EXECUTE @.iError = sp_executesql @.cCmd, N'@.i INT OUTPUT'
, @.iError OUTPUT -- Only shows remotely reported error
Select @.iError
RESULTS
EXECUTE @.i = [].master.dbo.sp_password NULL, 'junque', '' << The Command
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@.i'.
Server: Msg 1038, Level 15, State 1, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Error
----
1038
(1 row(s) affected)
Server: Msg 1038, Level 15, State 3, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ''.
----
170
(1 row(s) affected)
What value do you expect for the error codes?|||I'm attempting to dynamically build a password change request, that will be executed on some arbitrary number of servers determined by values in a table. The code that I posted assumes that your current login has the ability to dynamically execute sp_password for a login on both your local server and all of the remote servers.
When it executes sp_password on the local server, the first example works well and the error code is available via the SELECT. When it executes sp_password on a remote server (via RPC, Remote Procedure Call), then the second example works well, but I haven't found any reliable way to retrive the @.@.error result.
At the moment, I'm thinking that I'll just use the 0/1 return code from sp_password and wait until I have time to create an extended stored procedure (or the problem goes away) for a more elegant fix.
-PatP|||Thanks - now I get it.
The only way I could find around this is to put a wrapper around sp_password by creating my_sp_password via hacking sp_password - one of the flaws with this approach is that when sp_password changes, you have to also potentially change the wrapper (my_sp_password).
If you choose to use this hack, you may want to comment out/remove the RAISERRORs. Note: I did not test this on linked servers.
WRAPPER
create procedure my_sp_password
@.old sysname = NULL, -- the old (current) password
@.new sysname, -- the new password
@.loginame sysname = NULL -- user to change password on
as
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @.self int, @.error int
select @.self = CASE WHEN @.loginame is null THEN 1 ELSE 2 END
, @.error = -1
-- RESOLVE LOGIN NAME
if @.loginame is null
select @.loginame = suser_sname()
-- CHECK PERMISSIONS (SecurityAdmin per Richard Waymire) --
IF (not is_srvrolemember('securityadmin') = 1)
AND not @.self = 1
begin
dbcc auditevent (107, @.self, 0, @.loginame, NULL, NULL, NULL)
raiserror(15210,-1,-1)
select @.error = 15210
return (@.error)
end
ELSE
begin
dbcc auditevent (107, @.self, 1, @.loginame, NULL, NULL, NULL)
end
-- DISALLOW USER TRANSACTION --
set implicit_transactions off
IF (@.@.trancount > 0)
begin
raiserror(15002,-1,-1,'sp_password')
select @.error = 15002
return (@.error)
end
-- RESOLVE LOGIN NAME (disallows nt names)
if not exists (select * from master.dbo.syslogins where
loginname = @.loginame and isntname = 0)
begin
raiserror(15007,-1,-1,@.loginame)
select @.error = 15007
return (@.error)
end
-- IF non-SYSADMIN ATTEMPTING CHANGE TO SYSADMIN, REQUIRE PASSWORD (218078) --
if (@.self <> 1 AND is_srvrolemember('sysadmin') = 0 AND exists
(SELECT * FROM master.dbo.syslogins WHERE loginname = @.loginame and isntname = 0
AND sysadmin = 1) )
SELECT @.self = 1
-- CHECK OLD PASSWORD IF NEEDED --
if (@.self = 1 or @.old is not null)
if not exists (select * from master.dbo.sysxlogins
where srvid IS NULL and
name = @.loginame and
( (@.old is null and password is null) or
(pwdcompare(@.old, password, (CASE WHEN xstatus&2048 = 2048 THEN 1 ELSE 0 END)) = 1) ) )
begin
raiserror(15211,-1,-1)
select @.error = 15211
return (@.error)
end
-- CHANGE THE PASSWORD --
exec sp_configure 'allow', 1
Reconfigure
set @.error = @.@.error
if @.error = 0
begin
-- update master.dbo.sysxlogins
-- set password = convert(varbinary(256), pwdencrypt(@.new)), xdate2 = getdate(), xstatus = xstatus & (~2048)
-- where name = @.loginame and srvid IS NULL
exec @.error = sp_password @.old, @.new, @.loginame
end
-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
if @.error = 0
begin
exec('use master grant all to null')
set @.error = @.@.error
end
-- FINALIZATION: RETURN SUCCESS/FAILURE --
if @.error <> 0
return (@.error)
raiserror(15478,-1,-1)
set @.error = 15478
return (@.error) -- sp_password
EXECUTION
Declare @.error int
Select @.error = -1
exec @.error = my_sp_password 'fred', 'ted', 'sam'
Select @.error|||Or
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE ProcessLog(Spid int, ProcessName varchar(255), Row_Count int, Error int, Return_Code int, Add_Dt datetime DEFAULT GetDate())
GO
DECLARE @.cCmd varchar(8000), @.cServer sysname, @.cLogin sysname
SELECT @.cServer = @.@.SERVERNAME, @.cLogin = SYSTEM_USER
SELECT @.cCmd = 'DECLARE @.i int' + CHAR(13)
+ 'EXECUTE @.i = [' + @.cServer
+ '].master.dbo.sp_password NULL, ''test'', '''
+ @.cLogin + ''''+ CHAR(13)
+ 'INSERT INTO ProcessLog(Spid, ProcessName, Row_Count, Error, Return_Code)' + CHAR(13)
+ 'SELECT ' + CONVERT(varchar(15),@.@.SPID) + ', ''sp_password'''+', @.@.ROWCOUNT, @.@.ERROR, @.i' + CHAR(13)
SELECT @.cCmd
EXECUTE (@.cCmd)
SELECT * FROM ProcessLog o
WHERE Spid = @.@.SPID
AND Add_Dt IN ( SELECT MAX(Add_Dt)
FROM ProcessLog i
WHERE i.Spid = o.Spid)
GO
SET NOCOUNT OFF
DROP TABLE ProcessLog
GO|||Brett's example works like my example 1 for the local server, and would make Rube Goldberg giddy with glee in the process. I really want something that works equally well for both local and remote without a whole lot of work to make it fly.
-PatP|||How does your first one work?
Doesn't it return an error?
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@.i'.
?
I didn't test it remotely, are you saying it won't work?|||What I posted is actually an excerpt from a much larger script, only to show the general idea of what I'm doing... I didn't intend for anyone to execute it "as is", but I should have been more clear about that.
The first bit of logic works nicely when the code is being executed on the local server (in other words, when @.cServer = @.@.servername like in your example). The second bit of code works when the dynamic SQL generates an RPC, and it can be elaborated on to return the actual @.@.error value by following the SELECT with a SET command in the dynamic SQL. Because the code is trapped at two different layers within the SQL Engine, and is reported differently depending on who catches what, I can't think of a single, coherant way to trap the actual error code (15087 if I remember correctly) for both cases unless I construct wildly different bits of SQL and test to see which SQL I should use.
As I said, I think this is a design issue in SQL Server that I can't readily fix. I think that the proper answer is to just break down and code it as an extended stored procedure, where I can "put a leash on the monster" and stop fighting it. I just don't have the luxury of the time (probably around 8 hours to write and test, without the enhancements I'd really like to add) needed for that kind of fix at the moment.
-PatP|||DECLARE @.j INT
CREATE TABLE #temp ( id INT IDENTITY (1,1), result VARCHAR(255) )
INSERT INTO #temp (result)
EXEC @.j = master.dbo.xp_cmdshell 'osql -E -Q "EXIT(EXECUTE master.dbo.sp_password NULL, ''junque'', ''test3''; )"'
SELECT @.j
IF @.j = -100
BEGIN
SELECT SUBSTRING(result, CHARINDEX('Msg ', result) + 4, (CHARINDEX(' ',result,CHARINDEX('Msg ', result) + 5) - CHARINDEX('Msg ', result) - 5))
FROM #temp
WHERE id = 1
END
DROP TABLE #temp
NOTE: I don't have a remote server to test it with at home.
Tim S|||I don't think that the query from TimS will get me much further than what I've got right now, but it gives me an idea! I think he's resolved the issue of how to work around the distinctly different way that MS-SQL handles errors on the local SQL Server versus remote SQL servers by using osql.exe to put all servers on a local footing. I'll have to experiment a bit when I get back to a machine with MS-SQL available, but I think it might work to do something like:SET @.cmd = 'osql -E -Q "EXECUTE sp_password NULL, ''abcde'', '''
+ @.cLogin + ''' EXIT(SELECT @.@.error)" -s ' + @.cServer'
EXECUTE @.iRC = master.dbo.xp_cmdshell @.cCmdTo simulate the remote server, just create a second instance of MS-SQL on your machine. As long as the network isn't specifically a part of your test, a second instance works nicely for whatever I've needed to test.
-PatP
+ '].master.dbo.sp_password NULL, ''junque'', '''
+ @.cLogin + ''''
EXECUTE (@.cCmd)
SELECT @.@.error -- only shows locally reported error
EXECUTE sp_executesql @.cCmd, N'@.i INT OUTPUT'
, @.iError OUTPUT -- Only shows remotely reported errorI can get the error from a local procedure (one run on this box) using the first example, or on a remote server (via RPC) using the second, but not both. Any suggestions?
-PatPI'm confused as to what your expected output should be since I'm getting errors all over the place (typical results for me).
QUERY:
Set Concat_Null_Yields_Null Off
Declare @.cCmd nvarchar(4000)
Declare @.cServer nvarchar, @.cLogin nvarchar
, @.iError int
SELECT @.cCmd = 'EXECUTE @.i = [' + @.cServer
+ '].master.dbo.sp_password NULL, ''junque'', '''
+ @.cLogin + ''''
Print @.cCmd + N' << The Command' + nchar(10)
EXECUTE (@.cCmd)
SELECT @.@.error As 'Error' -- only shows locally reported error
EXECUTE @.iError = sp_executesql @.cCmd, N'@.i INT OUTPUT'
, @.iError OUTPUT -- Only shows remotely reported error
Select @.iError
RESULTS
EXECUTE @.i = [].master.dbo.sp_password NULL, 'junque', '' << The Command
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@.i'.
Server: Msg 1038, Level 15, State 1, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Error
----
1038
(1 row(s) affected)
Server: Msg 1038, Level 15, State 3, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ''.
----
170
(1 row(s) affected)
What value do you expect for the error codes?|||I'm attempting to dynamically build a password change request, that will be executed on some arbitrary number of servers determined by values in a table. The code that I posted assumes that your current login has the ability to dynamically execute sp_password for a login on both your local server and all of the remote servers.
When it executes sp_password on the local server, the first example works well and the error code is available via the SELECT. When it executes sp_password on a remote server (via RPC, Remote Procedure Call), then the second example works well, but I haven't found any reliable way to retrive the @.@.error result.
At the moment, I'm thinking that I'll just use the 0/1 return code from sp_password and wait until I have time to create an extended stored procedure (or the problem goes away) for a more elegant fix.
-PatP|||Thanks - now I get it.
The only way I could find around this is to put a wrapper around sp_password by creating my_sp_password via hacking sp_password - one of the flaws with this approach is that when sp_password changes, you have to also potentially change the wrapper (my_sp_password).
If you choose to use this hack, you may want to comment out/remove the RAISERRORs. Note: I did not test this on linked servers.
WRAPPER
create procedure my_sp_password
@.old sysname = NULL, -- the old (current) password
@.new sysname, -- the new password
@.loginame sysname = NULL -- user to change password on
as
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @.self int, @.error int
select @.self = CASE WHEN @.loginame is null THEN 1 ELSE 2 END
, @.error = -1
-- RESOLVE LOGIN NAME
if @.loginame is null
select @.loginame = suser_sname()
-- CHECK PERMISSIONS (SecurityAdmin per Richard Waymire) --
IF (not is_srvrolemember('securityadmin') = 1)
AND not @.self = 1
begin
dbcc auditevent (107, @.self, 0, @.loginame, NULL, NULL, NULL)
raiserror(15210,-1,-1)
select @.error = 15210
return (@.error)
end
ELSE
begin
dbcc auditevent (107, @.self, 1, @.loginame, NULL, NULL, NULL)
end
-- DISALLOW USER TRANSACTION --
set implicit_transactions off
IF (@.@.trancount > 0)
begin
raiserror(15002,-1,-1,'sp_password')
select @.error = 15002
return (@.error)
end
-- RESOLVE LOGIN NAME (disallows nt names)
if not exists (select * from master.dbo.syslogins where
loginname = @.loginame and isntname = 0)
begin
raiserror(15007,-1,-1,@.loginame)
select @.error = 15007
return (@.error)
end
-- IF non-SYSADMIN ATTEMPTING CHANGE TO SYSADMIN, REQUIRE PASSWORD (218078) --
if (@.self <> 1 AND is_srvrolemember('sysadmin') = 0 AND exists
(SELECT * FROM master.dbo.syslogins WHERE loginname = @.loginame and isntname = 0
AND sysadmin = 1) )
SELECT @.self = 1
-- CHECK OLD PASSWORD IF NEEDED --
if (@.self = 1 or @.old is not null)
if not exists (select * from master.dbo.sysxlogins
where srvid IS NULL and
name = @.loginame and
( (@.old is null and password is null) or
(pwdcompare(@.old, password, (CASE WHEN xstatus&2048 = 2048 THEN 1 ELSE 0 END)) = 1) ) )
begin
raiserror(15211,-1,-1)
select @.error = 15211
return (@.error)
end
-- CHANGE THE PASSWORD --
exec sp_configure 'allow', 1
Reconfigure
set @.error = @.@.error
if @.error = 0
begin
-- update master.dbo.sysxlogins
-- set password = convert(varbinary(256), pwdencrypt(@.new)), xdate2 = getdate(), xstatus = xstatus & (~2048)
-- where name = @.loginame and srvid IS NULL
exec @.error = sp_password @.old, @.new, @.loginame
end
-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
if @.error = 0
begin
exec('use master grant all to null')
set @.error = @.@.error
end
-- FINALIZATION: RETURN SUCCESS/FAILURE --
if @.error <> 0
return (@.error)
raiserror(15478,-1,-1)
set @.error = 15478
return (@.error) -- sp_password
EXECUTION
Declare @.error int
Select @.error = -1
exec @.error = my_sp_password 'fred', 'ted', 'sam'
Select @.error|||Or
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE ProcessLog(Spid int, ProcessName varchar(255), Row_Count int, Error int, Return_Code int, Add_Dt datetime DEFAULT GetDate())
GO
DECLARE @.cCmd varchar(8000), @.cServer sysname, @.cLogin sysname
SELECT @.cServer = @.@.SERVERNAME, @.cLogin = SYSTEM_USER
SELECT @.cCmd = 'DECLARE @.i int' + CHAR(13)
+ 'EXECUTE @.i = [' + @.cServer
+ '].master.dbo.sp_password NULL, ''test'', '''
+ @.cLogin + ''''+ CHAR(13)
+ 'INSERT INTO ProcessLog(Spid, ProcessName, Row_Count, Error, Return_Code)' + CHAR(13)
+ 'SELECT ' + CONVERT(varchar(15),@.@.SPID) + ', ''sp_password'''+', @.@.ROWCOUNT, @.@.ERROR, @.i' + CHAR(13)
SELECT @.cCmd
EXECUTE (@.cCmd)
SELECT * FROM ProcessLog o
WHERE Spid = @.@.SPID
AND Add_Dt IN ( SELECT MAX(Add_Dt)
FROM ProcessLog i
WHERE i.Spid = o.Spid)
GO
SET NOCOUNT OFF
DROP TABLE ProcessLog
GO|||Brett's example works like my example 1 for the local server, and would make Rube Goldberg giddy with glee in the process. I really want something that works equally well for both local and remote without a whole lot of work to make it fly.
-PatP|||How does your first one work?
Doesn't it return an error?
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@.i'.
?
I didn't test it remotely, are you saying it won't work?|||What I posted is actually an excerpt from a much larger script, only to show the general idea of what I'm doing... I didn't intend for anyone to execute it "as is", but I should have been more clear about that.
The first bit of logic works nicely when the code is being executed on the local server (in other words, when @.cServer = @.@.servername like in your example). The second bit of code works when the dynamic SQL generates an RPC, and it can be elaborated on to return the actual @.@.error value by following the SELECT with a SET command in the dynamic SQL. Because the code is trapped at two different layers within the SQL Engine, and is reported differently depending on who catches what, I can't think of a single, coherant way to trap the actual error code (15087 if I remember correctly) for both cases unless I construct wildly different bits of SQL and test to see which SQL I should use.
As I said, I think this is a design issue in SQL Server that I can't readily fix. I think that the proper answer is to just break down and code it as an extended stored procedure, where I can "put a leash on the monster" and stop fighting it. I just don't have the luxury of the time (probably around 8 hours to write and test, without the enhancements I'd really like to add) needed for that kind of fix at the moment.
-PatP|||DECLARE @.j INT
CREATE TABLE #temp ( id INT IDENTITY (1,1), result VARCHAR(255) )
INSERT INTO #temp (result)
EXEC @.j = master.dbo.xp_cmdshell 'osql -E -Q "EXIT(EXECUTE master.dbo.sp_password NULL, ''junque'', ''test3''; )"'
SELECT @.j
IF @.j = -100
BEGIN
SELECT SUBSTRING(result, CHARINDEX('Msg ', result) + 4, (CHARINDEX(' ',result,CHARINDEX('Msg ', result) + 5) - CHARINDEX('Msg ', result) - 5))
FROM #temp
WHERE id = 1
END
DROP TABLE #temp
NOTE: I don't have a remote server to test it with at home.
Tim S|||I don't think that the query from TimS will get me much further than what I've got right now, but it gives me an idea! I think he's resolved the issue of how to work around the distinctly different way that MS-SQL handles errors on the local SQL Server versus remote SQL servers by using osql.exe to put all servers on a local footing. I'll have to experiment a bit when I get back to a machine with MS-SQL available, but I think it might work to do something like:SET @.cmd = 'osql -E -Q "EXECUTE sp_password NULL, ''abcde'', '''
+ @.cLogin + ''' EXIT(SELECT @.@.error)" -s ' + @.cServer'
EXECUTE @.iRC = master.dbo.xp_cmdshell @.cCmdTo simulate the remote server, just create a second instance of MS-SQL on your machine. As long as the network isn't specifically a part of your test, a second instance works nicely for whatever I've needed to test.
-PatP
@@error can only capture limited error number?
Hi experts,
I'm quite
with the using of @.@.error in Microsoft SQL 2000. For
instance,
Select * From Table1
If @.@.error <> 0
print 'Got error!!!'
I can have the @.@.error to capture the error number 208 (Invalid object name
'Table1') if the specific table is not found inside database.
Any hint?Read http://www.sommarskog.se/error-handling-I.html and
http://www.sommarskog.se/error-handling-II.html
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"LBT" <LBT@.discussions.microsoft.com> wrote in message
news:A2937AFF-D8E6-4414-A148-5FAD803C75F5@.microsoft.com...
> Hi experts,
> I'm quite with the using of @.@.error in Microsoft SQL 2000. For
> instance,
> Select * From Table1
> If @.@.error <> 0
> print 'Got error!!!'
> I can have the @.@.error to capture the error number 208 (Invalid object
name
> 'Table1') if the specific table is not found inside database.
> Any hint?
I'm quite
with the using of @.@.error in Microsoft SQL 2000. Forinstance,
Select * From Table1
If @.@.error <> 0
print 'Got error!!!'
I can have the @.@.error to capture the error number 208 (Invalid object name
'Table1') if the specific table is not found inside database.
Any hint?Read http://www.sommarskog.se/error-handling-I.html and
http://www.sommarskog.se/error-handling-II.html
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"LBT" <LBT@.discussions.microsoft.com> wrote in message
news:A2937AFF-D8E6-4414-A148-5FAD803C75F5@.microsoft.com...
> Hi experts,
> I'm quite
with the using of @.@.error in Microsoft SQL 2000. For> instance,
> Select * From Table1
> If @.@.error <> 0
> print 'Got error!!!'
> I can have the @.@.error to capture the error number 208 (Invalid object
name
> 'Table1') if the specific table is not found inside database.
> Any hint?
@@Error and @@RowCount
DECLARE @.ErrorNum INT
, @.RowCount INT
DELETE Seller
WHERE SellerId = 9999999999999
SELECT @.ErrorNum = @.@.ERROR
, @.RowCount = @.@.ROWCOUNT
SELECT @.RowCount
, @.ErrorNum
Cases @.ErrorNum @.RowCount
1 0 0
2 0 <> 0
3 <> 0 0
4 <> 0 <> 0
I can simulate the Case 1 and 2 but not case 3 and 4
1 Not able to simulate the problem if the @.ERRORNUM <> 0
2 Checking @.@.ERROR and @.@.ROWCOUNT in the above sample code is correct.
can you please some one help me how to simulate the case 3 and 4
Thanks in Advance
Rajesh.Insert a row into the table & you can simulate #3:
CREATE TABLE Seller ( SellerId BIGINT PRIMARY KEY ) ;
Case #1
-- No rows in the table
DECLARE @.ErrorNum INT, @.RowCount INT
DELETE Seller WHERE SellerId = 9999999999999
SELECT @.ErrorNum = @.@.ERROR, @.RowCount = @.@.ROWCOUNT
SELECT @.RowCount, @.ErrorNum ;
GO
Case #2
DECLARE @.ErrorNum INT, @.RowCount INT
DELETE Seller WHERE SellerId = 9999999999999 /0
SELECT @.ErrorNum = @.@.ERROR, @.RowCount = @.@.ROWCOUNT
SELECT @.RowCount, @.ErrorNum ;
GO
Case #3
INSERT Seller SELECT 9999999999999
DECLARE @.ErrorNum INT, @.RowCount INT
DELETE Seller WHERE SellerId = 9999999999999
SELECT @.ErrorNum = @.@.ERROR, @.RowCount = @.@.ROWCOUNT
SELECT @.RowCount, @.ErrorNum ;
GO
Anith|||Issue resolved.
@.@.Error I am checking for the compliation errors
whereas I need to check for the execution errors
Thanks & Regards
Rajesh.
"Rajesh" wrote:
> DECLARE @.ErrorNum INT
> , @.RowCount INT
> DELETE Seller
> WHERE SellerId = 9999999999999
> SELECT @.ErrorNum = @.@.ERROR
> , @.RowCount = @.@.ROWCOUNT
> SELECT @.RowCount
> , @.ErrorNum
> Cases @.ErrorNum @.RowCount
> 1 0 0
> 2 0 <> 0
> 3 <> 0 0
> 4 <> 0 <> 0
> I can simulate the Case 1 and 2 but not case 3 and 4
> 1 Not able to simulate the problem if the @.ERRORNUM <> 0
> 2 Checking @.@.ERROR and @.@.ROWCOUNT in the above sample code is correct.
>
> can you please some one help me how to simulate the case 3 and 4
> Thanks in Advance
> Rajesh.
>sql
, @.RowCount INT
DELETE Seller
WHERE SellerId = 9999999999999
SELECT @.ErrorNum = @.@.ERROR
, @.RowCount = @.@.ROWCOUNT
SELECT @.RowCount
, @.ErrorNum
Cases @.ErrorNum @.RowCount
1 0 0
2 0 <> 0
3 <> 0 0
4 <> 0 <> 0
I can simulate the Case 1 and 2 but not case 3 and 4
1 Not able to simulate the problem if the @.ERRORNUM <> 0
2 Checking @.@.ERROR and @.@.ROWCOUNT in the above sample code is correct.
can you please some one help me how to simulate the case 3 and 4
Thanks in Advance
Rajesh.Insert a row into the table & you can simulate #3:
CREATE TABLE Seller ( SellerId BIGINT PRIMARY KEY ) ;
Case #1
-- No rows in the table
DECLARE @.ErrorNum INT, @.RowCount INT
DELETE Seller WHERE SellerId = 9999999999999
SELECT @.ErrorNum = @.@.ERROR, @.RowCount = @.@.ROWCOUNT
SELECT @.RowCount, @.ErrorNum ;
GO
Case #2
DECLARE @.ErrorNum INT, @.RowCount INT
DELETE Seller WHERE SellerId = 9999999999999 /0
SELECT @.ErrorNum = @.@.ERROR, @.RowCount = @.@.ROWCOUNT
SELECT @.RowCount, @.ErrorNum ;
GO
Case #3
INSERT Seller SELECT 9999999999999
DECLARE @.ErrorNum INT, @.RowCount INT
DELETE Seller WHERE SellerId = 9999999999999
SELECT @.ErrorNum = @.@.ERROR, @.RowCount = @.@.ROWCOUNT
SELECT @.RowCount, @.ErrorNum ;
GO
Anith|||Issue resolved.
@.@.Error I am checking for the compliation errors
whereas I need to check for the execution errors
Thanks & Regards
Rajesh.
"Rajesh" wrote:
> DECLARE @.ErrorNum INT
> , @.RowCount INT
> DELETE Seller
> WHERE SellerId = 9999999999999
> SELECT @.ErrorNum = @.@.ERROR
> , @.RowCount = @.@.ROWCOUNT
> SELECT @.RowCount
> , @.ErrorNum
> Cases @.ErrorNum @.RowCount
> 1 0 0
> 2 0 <> 0
> 3 <> 0 0
> 4 <> 0 <> 0
> I can simulate the Case 1 and 2 but not case 3 and 4
> 1 Not able to simulate the problem if the @.ERRORNUM <> 0
> 2 Checking @.@.ERROR and @.@.ROWCOUNT in the above sample code is correct.
>
> can you please some one help me how to simulate the case 3 and 4
> Thanks in Advance
> Rajesh.
>sql
@@ERROR and @@ROWCOUNT
Since @.@.ERROR is cleared by the very next statement and @.@.ROWCOUNT is cleare
d by the very next statement, how do you save the @.@.ROWCOUNT and then check
the @.@.ERROR from the same statement?
It appears to me that SET @.HOLD_ERROR=@.@.ERROR is clearing the @.@.ROWCOUNT fro
m the SELECT statement just executed...Try:
declare
@.ERROR int
, @.ROWCOUNT int
select
@.ERROR = @.@.ERROR
, @.ROWCOUNT = @.@.ROWCOUNT
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:684F45E8-4501-4A45-9FE9-A3A5AA9FCB81@.microsoft.com...
Since @.@.ERROR is cleared by the very next statement and @.@.ROWCOUNT is
cleared by the very next statement, how do you save the @.@.ROWCOUNT and then
check the @.@.ERROR from the same statement?
It appears to me that SET @.HOLD_ERROR=@.@.ERROR is clearing the @.@.ROWCOUNT
from the SELECT statement just executed...|||Thanks - that just dawned on me as well. Brain freeze - shouldn't develop
and talk to the customers at the same time...
I'm so set against using SELECT to set variable values - is there a justific
ation for that feeling I have, or did I dream it up...|||Steve Z wrote:
> I'm so set against using SELECT to set variable values - is there a justification
for that feeling I have, or did I dream it up...
I have the same phobia and usually always find myself using SET. The
only time I use SELECT is when I want something from a table.
Aaron Weiker
http://blogs.sqladvice.com/aweiker
http://aaronweiker.com/|||http://www.tkdinesh.com/faq/ans/setorselect.html
No performance penalty or anything like that, but it does violate the
standard (perhaps important if you want to write code that's as portable as
possible).
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:AA0BCA4F-61A1-4C32-8D4E-DB100850EB70@.microsoft.com...
> Thanks - that just dawned on me as well. Brain freeze - shouldn't
develop and talk to the customers at the same time...
> I'm so set against using SELECT to set variable values - is there a
justification for that feeling I have, or did I dream it up...
>|||So, to beat a dead horse here...
You are saying that SET is preferred syntax to assign variables - and I agre
e and follow this logic...
But in the case of @.@.ERROR and @.@.ROWCOUNT, if you want to save both of those
values at the same time, you must use SELECT.
The higher generation language you go, the looser the rules - I remember the
old days of VAX-11 MACRO - how simple and sweet (and verbose, and complicat
ed and hard to debug)...|||Personally, I prefer SET as I find it easier to read (primary reason) and it
is also ANSI SQL compliant. The one exception is when I have to or really
want to do several assignments in one statement. Your @.@.ERROR and @.@.ROWCOUNT
is one example. Below is another:
SELECT @.fname = au_fname, @.lname = au_lname, @.whatever = whatever
FROM tbl
WHERE pkcol = @.pk
Note for above, if more than one row is returned (bug in design) you will
not get an error, the variables will contain values from the last row
processed. Also, if no row is returned, you the variables will go unchanged.
I know of other programmers who prefer to be consistent, and because of that
always use SELECT for all variable assignments. I'm not one of them, but I
respect that viewpoint.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:9D3418CE-03E2-4A23-836B-8288C110BEF9@.microsoft.com...
> So, to beat a dead horse here...
> You are saying that SET is preferred syntax to assign variables - and I
agree and follow this logic...
> But in the case of @.@.ERROR and @.@.ROWCOUNT, if you want to save both of
those values at the same time, you must use SELECT.
> The higher generation language you go, the looser the rules - I remember
the old days of VAX-11 MACRO - how simple and sweet (and verbose, and
complicated and hard to debug)...|||>> But in the case of @.@.ERROR and @.@.ROWCOUNT,
SELECT.
Not necessarily. I have some examples in this article:
http://vyaskn.tripod.com/difference..._and_select.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:9D3418CE-03E2-4A23-836B-8288C110BEF9@.microsoft.com...
So, to beat a dead horse here...
You are saying that SET is preferred syntax to assign variables - and I
agree and follow this logic...
But in the case of @.@.ERROR and @.@.ROWCOUNT, if you want to save both of those
values at the same time, you must use SELECT.
The higher generation language you go, the looser the rules - I remember the
old days of VAX-11 MACRO - how simple and sweet (and verbose, and
complicated and hard to debug)...|||Vyas,
That you for the interesting article and that neat trick to save both values
at once.
I think, though, in the few cases where the @.@.ROWCOUNT and @.@.ERROR values ar
e important to me, I will use SELECT to save them both.
Steve
-- Narayana Vyas Kondreddi wrote: --
SELECT.
Not necessarily. I have some examples in this article:
http://vyaskn.tripod.com/difference..._and_select.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:9D3418CE-03E2-4A23-836B-8288C110BEF9@.microsoft.com...
So, to beat a dead horse here...
You are saying that SET is preferred syntax to assign variables - and I
agree and follow this logic...
But in the case of @.@.ERROR and @.@.ROWCOUNT, if you want to save both of those
values at the same time, you must use SELECT.
The higher generation language you go, the looser the rules - I remember the
old days of VAX-11 MACRO - how simple and sweet (and verbose, and
complicated and hard to debug)...
d by the very next statement, how do you save the @.@.ROWCOUNT and then check
the @.@.ERROR from the same statement?
It appears to me that SET @.HOLD_ERROR=@.@.ERROR is clearing the @.@.ROWCOUNT fro
m the SELECT statement just executed...Try:
declare
@.ERROR int
, @.ROWCOUNT int
select
@.ERROR = @.@.ERROR
, @.ROWCOUNT = @.@.ROWCOUNT
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:684F45E8-4501-4A45-9FE9-A3A5AA9FCB81@.microsoft.com...
Since @.@.ERROR is cleared by the very next statement and @.@.ROWCOUNT is
cleared by the very next statement, how do you save the @.@.ROWCOUNT and then
check the @.@.ERROR from the same statement?
It appears to me that SET @.HOLD_ERROR=@.@.ERROR is clearing the @.@.ROWCOUNT
from the SELECT statement just executed...|||Thanks - that just dawned on me as well. Brain freeze - shouldn't develop
and talk to the customers at the same time...
I'm so set against using SELECT to set variable values - is there a justific
ation for that feeling I have, or did I dream it up...|||Steve Z wrote:
> I'm so set against using SELECT to set variable values - is there a justification
for that feeling I have, or did I dream it up...
I have the same phobia and usually always find myself using SET. The
only time I use SELECT is when I want something from a table.
Aaron Weiker
http://blogs.sqladvice.com/aweiker
http://aaronweiker.com/|||http://www.tkdinesh.com/faq/ans/setorselect.html
No performance penalty or anything like that, but it does violate the
standard (perhaps important if you want to write code that's as portable as
possible).
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:AA0BCA4F-61A1-4C32-8D4E-DB100850EB70@.microsoft.com...
> Thanks - that just dawned on me as well. Brain freeze - shouldn't
develop and talk to the customers at the same time...
> I'm so set against using SELECT to set variable values - is there a
justification for that feeling I have, or did I dream it up...
>|||So, to beat a dead horse here...
You are saying that SET is preferred syntax to assign variables - and I agre
e and follow this logic...
But in the case of @.@.ERROR and @.@.ROWCOUNT, if you want to save both of those
values at the same time, you must use SELECT.
The higher generation language you go, the looser the rules - I remember the
old days of VAX-11 MACRO - how simple and sweet (and verbose, and complicat
ed and hard to debug)...|||Personally, I prefer SET as I find it easier to read (primary reason) and it
is also ANSI SQL compliant. The one exception is when I have to or really
want to do several assignments in one statement. Your @.@.ERROR and @.@.ROWCOUNT
is one example. Below is another:
SELECT @.fname = au_fname, @.lname = au_lname, @.whatever = whatever
FROM tbl
WHERE pkcol = @.pk
Note for above, if more than one row is returned (bug in design) you will
not get an error, the variables will contain values from the last row
processed. Also, if no row is returned, you the variables will go unchanged.
I know of other programmers who prefer to be consistent, and because of that
always use SELECT for all variable assignments. I'm not one of them, but I
respect that viewpoint.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:9D3418CE-03E2-4A23-836B-8288C110BEF9@.microsoft.com...
> So, to beat a dead horse here...
> You are saying that SET is preferred syntax to assign variables - and I
agree and follow this logic...
> But in the case of @.@.ERROR and @.@.ROWCOUNT, if you want to save both of
those values at the same time, you must use SELECT.
> The higher generation language you go, the looser the rules - I remember
the old days of VAX-11 MACRO - how simple and sweet (and verbose, and
complicated and hard to debug)...|||>> But in the case of @.@.ERROR and @.@.ROWCOUNT,
SELECT.
Not necessarily. I have some examples in this article:
http://vyaskn.tripod.com/difference..._and_select.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:9D3418CE-03E2-4A23-836B-8288C110BEF9@.microsoft.com...
So, to beat a dead horse here...
You are saying that SET is preferred syntax to assign variables - and I
agree and follow this logic...
But in the case of @.@.ERROR and @.@.ROWCOUNT, if you want to save both of those
values at the same time, you must use SELECT.
The higher generation language you go, the looser the rules - I remember the
old days of VAX-11 MACRO - how simple and sweet (and verbose, and
complicated and hard to debug)...|||Vyas,
That you for the interesting article and that neat trick to save both values
at once.
I think, though, in the few cases where the @.@.ROWCOUNT and @.@.ERROR values ar
e important to me, I will use SELECT to save them both.
Steve
-- Narayana Vyas Kondreddi wrote: --
SELECT.
Not necessarily. I have some examples in this article:
http://vyaskn.tripod.com/difference..._and_select.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:9D3418CE-03E2-4A23-836B-8288C110BEF9@.microsoft.com...
So, to beat a dead horse here...
You are saying that SET is preferred syntax to assign variables - and I
agree and follow this logic...
But in the case of @.@.ERROR and @.@.ROWCOUNT, if you want to save both of those
values at the same time, you must use SELECT.
The higher generation language you go, the looser the rules - I remember the
old days of VAX-11 MACRO - how simple and sweet (and verbose, and
complicated and hard to debug)...
Sunday, March 25, 2012
@@ERROR & CREATE TABLE
Hi
I've been looking at scripting some create tables, but want to know if the table created successfully. I've been doing the following:
DECLARE @.ERRCODE INT
CREATE TABLE x
SET @.ERRCODE = @.@.ERROR
This works ok, if there is no erroor. However if I run this again, then obviously I get the error "This object already exists" and the script stops executing.
Is there a way that I can capture the error using @.@.ERROR and still let the script run ?
Thanks in advance
MicksterWell, you shouldn't. "On Error Resume Next" is not present in T-SQL. What you should do is perform a validation for presence of object before attempting to create it.if objectproperty(object_id('dbo.x'), 'isusertable')=1 drop table dbo.x
create table dbo.x (f1 int, ...)|||Thanks, but I already understand that I should check for the existance of the object - as stated in my last mail. I want to check the table is being created correctly incase of other events, like permissions, file full, etc.
I've been looking at scripting some create tables, but want to know if the table created successfully. I've been doing the following:
DECLARE @.ERRCODE INT
CREATE TABLE x
SET @.ERRCODE = @.@.ERROR
This works ok, if there is no erroor. However if I run this again, then obviously I get the error "This object already exists" and the script stops executing.
Is there a way that I can capture the error using @.@.ERROR and still let the script run ?
Thanks in advance
MicksterWell, you shouldn't. "On Error Resume Next" is not present in T-SQL. What you should do is perform a validation for presence of object before attempting to create it.if objectproperty(object_id('dbo.x'), 'isusertable')=1 drop table dbo.x
create table dbo.x (f1 int, ...)|||Thanks, but I already understand that I should check for the existance of the object - as stated in my last mail. I want to check the table is being created correctly incase of other events, like permissions, file full, etc.
@@error & @bErrors variables from Index Tuning Wizard
The Index Tuning Wizard recommended these indexes:
CREATE NONCLUSTERED INDEX [pairs5] ON [dbo].[pairs] ([hit] A
SC ) IF( @.@.error
<> 0 ) SET @.bErrors = 1
CREATE NONCLUSTERED INDEX [pairs6] ON [dbo].[pairs] ([query]
ASC ) IF(
@.@.error <> 0 ) SET @.bErrors = 1
What are @.@.error and @.bErrors?
Thanks,
JimHi
@.@.ERROR is a system function and returns the error number of the last comman
d
http://msdn.microsoft.com/library/d...r />
_4hk5.asp
@.bError is a local variable and should be declared earlier in the script. It
seems to be used as a flag to indicate that something has failed somewhere!
John
"Jim" wrote:
> The Index Tuning Wizard recommended these indexes:
> CREATE NONCLUSTERED INDEX [pairs5] ON [dbo].[pairs] ([hit]
ASC ) IF( @.@.error
> <> 0 ) SET @.bErrors = 1
> CREATE NONCLUSTERED INDEX [pairs6] ON [dbo].[pairs] ([quer
y] ASC ) IF(
> @.@.error <> 0 ) SET @.bErrors = 1
> What are @.@.error and @.bErrors?
> Thanks,
> Jim
>
>|||Thanks, John!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9FDF1C1D-E05D-4224-9154-975D53979E5B@.microsoft.com...[vbcol=seagreen]
> Hi
> @.@.ERROR is a system function and returns the error number of the last
> command
> http://msdn.microsoft.com/library/d.../>
te_4hk5.asp
> @.bError is a local variable and should be declared earlier in the script.
> It
> seems to be used as a flag to indicate that something has failed
> somewhere!
> John
>
> "Jim" wrote:
>sql
CREATE NONCLUSTERED INDEX [pairs5] ON [dbo].[pairs] ([hit] A
SC ) IF( @.@.error
<> 0 ) SET @.bErrors = 1
CREATE NONCLUSTERED INDEX [pairs6] ON [dbo].[pairs] ([query]
ASC ) IF(
@.@.error <> 0 ) SET @.bErrors = 1
What are @.@.error and @.bErrors?
Thanks,
JimHi
@.@.ERROR is a system function and returns the error number of the last comman
d
http://msdn.microsoft.com/library/d...r />
_4hk5.asp
@.bError is a local variable and should be declared earlier in the script. It
seems to be used as a flag to indicate that something has failed somewhere!
John
"Jim" wrote:
> The Index Tuning Wizard recommended these indexes:
> CREATE NONCLUSTERED INDEX [pairs5] ON [dbo].[pairs] ([hit]
ASC ) IF( @.@.error
> <> 0 ) SET @.bErrors = 1
> CREATE NONCLUSTERED INDEX [pairs6] ON [dbo].[pairs] ([quer
y] ASC ) IF(
> @.@.error <> 0 ) SET @.bErrors = 1
> What are @.@.error and @.bErrors?
> Thanks,
> Jim
>
>|||Thanks, John!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9FDF1C1D-E05D-4224-9154-975D53979E5B@.microsoft.com...[vbcol=seagreen]
> Hi
> @.@.ERROR is a system function and returns the error number of the last
> command
> http://msdn.microsoft.com/library/d.../>
te_4hk5.asp
> @.bError is a local variable and should be declared earlier in the script.
> It
> seems to be used as a flag to indicate that something has failed
> somewhere!
> John
>
> "Jim" wrote:
>sql
@@ERROR
Hi Folks,
I try to save the error number when a restore fails. In case a device error
I'd like to save the error number into a table. Let's say I have a RESTORE
as below
RESTORE LOG xxxxx
FROM DISK='xxxxxxxxxxx.bak'
PRINT @.@.ERROR
Server: Msg 3201, Level 16, State 2, Line 3
Cannot open backup device 'xxxxxxxxx'. Device error or device off-line. See
the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE LOG is terminating abnormally.
If a device errors I want to get the error number 3201, which is more
specific about the cause of failure. However @.@.ERROR is always 3013 which
doesn't help much. How do I get it right as expected?
Thank you.Hmm, this may be tricky.
@.@.ERROR, as I understand it, always has the last immediate error that
occured. So, if you're printing @.@.error after the backup fails, it's always
going to be the last error, which is 3013 in this case.
I don't really see a way to just grab the error directly, other than logging
it to an output file in the job or writing to the event log. It seems to me
the event log is a more approprate place to put the entire message. You
could then import the event log into a table and do a search on that
specific error number.
"Flicker" <hthan@.superioraccess.com> wrote in message
news:ePQlImsUDHA.1276@.TK2MSFTNGP09.phx.gbl...
> Hi Folks,
> I try to save the error number when a restore fails. In case a device
error
> I'd like to save the error number into a table. Let's say I have a
RESTORE
> as below
> RESTORE LOG xxxxx
> FROM DISK='xxxxxxxxxxx.bak'
> PRINT @.@.ERROR
>
> Server: Msg 3201, Level 16, State 2, Line 3
> Cannot open backup device 'xxxxxxxxx'. Device error or device off-line.
See
> the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE LOG is terminating abnormally.
> If a device errors I want to get the error number 3201, which is more
> specific about the cause of failure. However @.@.ERROR is always 3013 which
> doesn't help much. How do I get it right as expected?
> Thank you.
>
I try to save the error number when a restore fails. In case a device error
I'd like to save the error number into a table. Let's say I have a RESTORE
as below
RESTORE LOG xxxxx
FROM DISK='xxxxxxxxxxx.bak'
PRINT @.@.ERROR
Server: Msg 3201, Level 16, State 2, Line 3
Cannot open backup device 'xxxxxxxxx'. Device error or device off-line. See
the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE LOG is terminating abnormally.
If a device errors I want to get the error number 3201, which is more
specific about the cause of failure. However @.@.ERROR is always 3013 which
doesn't help much. How do I get it right as expected?
Thank you.Hmm, this may be tricky.
@.@.ERROR, as I understand it, always has the last immediate error that
occured. So, if you're printing @.@.error after the backup fails, it's always
going to be the last error, which is 3013 in this case.
I don't really see a way to just grab the error directly, other than logging
it to an output file in the job or writing to the event log. It seems to me
the event log is a more approprate place to put the entire message. You
could then import the event log into a table and do a search on that
specific error number.
"Flicker" <hthan@.superioraccess.com> wrote in message
news:ePQlImsUDHA.1276@.TK2MSFTNGP09.phx.gbl...
> Hi Folks,
> I try to save the error number when a restore fails. In case a device
error
> I'd like to save the error number into a table. Let's say I have a
RESTORE
> as below
> RESTORE LOG xxxxx
> FROM DISK='xxxxxxxxxxx.bak'
> PRINT @.@.ERROR
>
> Server: Msg 3201, Level 16, State 2, Line 3
> Cannot open backup device 'xxxxxxxxx'. Device error or device off-line.
See
> the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE LOG is terminating abnormally.
> If a device errors I want to get the error number 3201, which is more
> specific about the cause of failure. However @.@.ERROR is always 3013 which
> doesn't help much. How do I get it right as expected?
> Thank you.
>
@@error
Got a tiny issue that needs help. I have this little
statement:
IF (SELECT CLSRECORD_ACCESSEDID FROM CLSRECORD_ACCESSED = CLSSTATUS.CONTACTID)
Basically what we want to do is to ensure that if the
result from CLSRECORD_ACCESSEDID equals what’s found in
the CLSSTATUS.CONTACTID, to do a rollback transaction.
Probably should be prefaced with an @.@.error...
Just need the correct, clean syntax on the above.DDL would help. It is not clear what tables are involved and what columns
are used. A potential statement is:
if exists (select * from my_table where mycolumn = 'what’s')
... logic for finding
else
... logic for not finding
"Wheel" <neal.pawlowski@.loanpro.com> wrote in message
news:0fa201c38d9e$d7aa5780$a101280a@.phx.gbl...
> Got a tiny issue that needs help. I have this little
> statement:
> IF (SELECT CLSRECORD_ACCESSEDID FROM CLSRECORD_ACCESSED => CLSSTATUS.CONTACTID)
> Basically what we want to do is to ensure that if the
> result from CLSRECORD_ACCESSEDID equals what’s found in
> the CLSSTATUS.CONTACTID, to do a rollback transaction.
> Probably should be prefaced with an @.@.error...
> Just need the correct, clean syntax on the above.
>|||Do you mean:
IF EXISTS(SELECT NULL FROM CLSRECORD_ACCESSED WHERE CLSRECORD_ACCESSEDID =CLSSTATUS.CONTACTID)
?
--
Jacco Schalkwijk
SQL Server MVP
"Wheel" <neal.pawlowski@.loanpro.com> wrote in message
news:0fa201c38d9e$d7aa5780$a101280a@.phx.gbl...
> Got a tiny issue that needs help. I have this little
> statement:
> IF (SELECT CLSRECORD_ACCESSEDID FROM CLSRECORD_ACCESSED => CLSSTATUS.CONTACTID)
> Basically what we want to do is to ensure that if the
> result from CLSRECORD_ACCESSEDID equals what’s found in
> the CLSSTATUS.CONTACTID, to do a rollback transaction.
> Probably should be prefaced with an @.@.error...
> Just need the correct, clean syntax on the above.
>
statement:
IF (SELECT CLSRECORD_ACCESSEDID FROM CLSRECORD_ACCESSED = CLSSTATUS.CONTACTID)
Basically what we want to do is to ensure that if the
result from CLSRECORD_ACCESSEDID equals what’s found in
the CLSSTATUS.CONTACTID, to do a rollback transaction.
Probably should be prefaced with an @.@.error...
Just need the correct, clean syntax on the above.DDL would help. It is not clear what tables are involved and what columns
are used. A potential statement is:
if exists (select * from my_table where mycolumn = 'what’s')
... logic for finding
else
... logic for not finding
"Wheel" <neal.pawlowski@.loanpro.com> wrote in message
news:0fa201c38d9e$d7aa5780$a101280a@.phx.gbl...
> Got a tiny issue that needs help. I have this little
> statement:
> IF (SELECT CLSRECORD_ACCESSEDID FROM CLSRECORD_ACCESSED => CLSSTATUS.CONTACTID)
> Basically what we want to do is to ensure that if the
> result from CLSRECORD_ACCESSEDID equals what’s found in
> the CLSSTATUS.CONTACTID, to do a rollback transaction.
> Probably should be prefaced with an @.@.error...
> Just need the correct, clean syntax on the above.
>|||Do you mean:
IF EXISTS(SELECT NULL FROM CLSRECORD_ACCESSED WHERE CLSRECORD_ACCESSEDID =CLSSTATUS.CONTACTID)
?
--
Jacco Schalkwijk
SQL Server MVP
"Wheel" <neal.pawlowski@.loanpro.com> wrote in message
news:0fa201c38d9e$d7aa5780$a101280a@.phx.gbl...
> Got a tiny issue that needs help. I have this little
> statement:
> IF (SELECT CLSRECORD_ACCESSEDID FROM CLSRECORD_ACCESSED => CLSSTATUS.CONTACTID)
> Basically what we want to do is to ensure that if the
> result from CLSRECORD_ACCESSEDID equals what’s found in
> the CLSSTATUS.CONTACTID, to do a rollback transaction.
> Probably should be prefaced with an @.@.error...
> Just need the correct, clean syntax on the above.
>
Subscribe to:
Posts (Atom)