Help: Prob handling SQL error in stored proc

Hi,

I a stored procedure that inserts a record into a table as
below.

The insert works OK, but if the insert violates a unique
indewx constraint on one of the columns, the proc terminates
immediately, and does NOT execute the 'if @.@.ERROR <> 0'
statement.

Am I doing something wrong, or do I need to set an attribute
somewhere?

tia,
Bill

begin tran

insert into Users
(UserName, UserPWD, Lname, Fname, UserDesc)
values (@.userName, @.userPWD, @.lname, @.fname, @.userDesc)

if @.@.ERROR <> 0
begin
rollback tran
set @.returnCode = -2
set @.errMsg = 'SQL error '
+ convert(varchar(6), @.@.ERROR)
+ ' occurred adding user '
+ @.userName
end[posted and mailed, please reply in news]

Bill S. (bill_sheets@.hotmail.com) writes:
> I a stored procedure that inserts a record into a table as
> below.
> The insert works OK, but if the insert violates a unique
> indewx constraint on one of the columns, the proc terminates
> immediately, and does NOT execute the 'if @.@.ERROR <> 0'
> statement.
> Am I doing something wrong, or do I need to set an attribute
> somewhere?
>
> begin tran
> insert into Users
> (UserName, UserPWD, Lname, Fname, UserDesc)
> values (@.userName, @.userPWD, @.lname, @.fname, @.userDesc)
> if @.@.ERROR <> 0
> begin
> rollback tran
> set @.returnCode = -2
> set @.errMsg = 'SQL error '
> + convert(varchar(6), @.@.ERROR)
> + ' occurred adding user '
> + @.userName
> end

First, @.@.error is set after each statement, so @.errMsg will never read
anything but "SQL Error 0 ...". Always save @.@.error in a local variable
before you do anything else with it.

So over to your question. Error handling in SQL Server is a messy topic,
and there are errors you cannot trap like this, because SQL Server
aborts the batch immediately. However, constraint violation as you
mention is not among those - unless the setting SET XACT_ABORT is ON.

So, assuming you are not using XACT_ABORT ON, the error handler should
be executed. But how do you know that it is not? What are @.returnCode
and @.errMsg? Local variables? Output parameters? If they are output
parameters, and run the procedure from Query Analyzer:

DECLARE @.ret int, @.errMsg varchar(200)
EXEC your_sp @.userName, ..., @.ret OUTPUT, @.errMsg OUTPUT
SELECT @.ret, @.errMsg

My guess is that you are running the procedure from some client library
which traps the error, before you get to read the output parameters.

As I mentioned, error handling is really a messy topic, but I have
an article on error handling of my web site that may be of interest,
http://www.sommarskog.se/error-handling-II.html. I don't know exactly
on what level you are on; if you are fairly unexperienced with SQL
Server, you may feel overwhelmed, but you could browse it now, and
save it for later reading.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Help: How to detect inserts, updates, deleted on a table from within C++ application?

Hopefully someone can at least point me in the right direction for more
research (e.g.: correct terminology). My only previous experience was just
dumping data into a database using ODBC, and that was some years ago so now
mostly forgotten.

I need to write an NT Service/Application (in C/C++) that will be getting
data sent to it via SQL Server 2000. The data will arrive in my SQL Server
(read-only access), via replication of tables from another remote SQL
Server.

My application needs know when new row are inserted, or updated so it can to
read this data (needs to be quick/timely so hopefully no polling) to then
interface with other remote proprietary systems.

T.I.A.

PS: If you can recommend appropriate books on SQL Server 2000 that would
also be useful.L,
There isn't an easy way to do this. You don't say how many tables you
need info for, but I think the best way to do this is to create and insert
and update trigger on the rows which would insert the primary key for that
table into another table or tables. This way, your application could poll
the new table and grab the primary keys that were inserted or changed, then
delete all data in the table.
Or, you could add a last_modified field which would be updated by
trigger anytime the row was inserted or updated. This might be a little
slower for your application, but easier to maintain data integrity. The
problem with the first solution is you need to be careful if an insert or
update is happening at the same time that you poll your new table.

I hope this helps.
Better than books on MS SQL Server 2000, you might take a look at our
video series at www.TechnicalVideos.net. In just a few hours, you can go
from newbie to expert. Download and watch the .wmv videos on your computer
and our experts will show you how to do things that we really use; things
you won't find in books. There's nothing like having an expert show you
first hand.

Best regards,
Chuck Conover
www.TechnicalVideos.net

"L. Blunt" <withheld@.my.choice> wrote in message
news:40471e58$0$28281$cc9e4d1f@.news.dial.pipex.com ...
> Hopefully someone can at least point me in the right direction for more
> research (e.g.: correct terminology). My only previous experience was just
> dumping data into a database using ODBC, and that was some years ago so
now
> mostly forgotten.
> I need to write an NT Service/Application (in C/C++) that will be getting
> data sent to it via SQL Server 2000. The data will arrive in my SQL Server
> (read-only access), via replication of tables from another remote SQL
> Server.
> My application needs know when new row are inserted, or updated so it can
to
> read this data (needs to be quick/timely so hopefully no polling) to then
> interface with other remote proprietary systems.
> T.I.A.
> PS: If you can recommend appropriate books on SQL Server 2000 that would
> also be useful.|||L. Blunt (withheld@.my.choice) writes:
> Hopefully someone can at least point me in the right direction for more
> research (e.g.: correct terminology). My only previous experience was
> just dumping data into a database using ODBC, and that was some years
> ago so now mostly forgotten.
> I need to write an NT Service/Application (in C/C++) that will be getting
> data sent to it via SQL Server 2000. The data will arrive in my SQL Server
> (read-only access), via replication of tables from another remote SQL
> Server.
> My application needs know when new row are inserted, or updated so it
> can to read this data (needs to be quick/timely so hopefully no polling)
> to then interface with other remote proprietary systems.

Chuck suggested triggers, and that is about the only way to go in SQL
2000, if you don't want to poll. The trigger would in that case invoke
an exentended stored procedure, or an OLE object to alert your application
that there is data to find.

But there might be a catch, since you are using replication. I don't relly
know what happens with triggers on tables in a subscriber database when
you set up replication. There may be a risk that all at a sudden the
triggers are gone.

So intensive polling might be better. Then there is a question on how to
poll, but knowing nothing about the database, this is a little difficult
to discuss.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

HELP: Delayed Inserts in SQL Server!

Hi all,
I have a small table with the following columns:
Refno (int), CampaignID(int), Value (int)
Sometimes a record takes >30 - 40 seconds to write. Is there a way to
ensure that a record is written in a timely manner?
Other info: I do have a stored procedure which loops the table waiting
for a response:
WHILE ((SELECT COUNT(*) FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO) = 0)
AND @.LOOPCOUNTER < 30
BEGIN
SET @.LOOPCOUNTER = @.LOOPCOUNTER + 1
/* Pause Loop Delay in hh:mm:ss:ms format */
WAITFOR DELAY '00:00:00.200'
END
Also, once a record is read, it is deleted from the table. Could the
deletes be locking the table?
Thank you for ANY help.
--
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/Your deletes and your looping SELECT can both be blocking the inserts.
Can you post DDL for the table, including any indexes and constraints?
Also, how many rows are in the table?
Your loop might be more efficient expressed as:
WHILE NOT EXISTS (SELECT * FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns959B8FF7AC71Dnntprogerscom@.140.99.99.130...
> Hi all,
> I have a small table with the following columns:
> Refno (int), CampaignID(int), Value (int)
> Sometimes a record takes >30 - 40 seconds to write. Is there a way to
> ensure that a record is written in a timely manner?
>
> Other info: I do have a stored procedure which loops the table waiting
> for a response:
> WHILE ((SELECT COUNT(*) FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO) = 0)
> AND @.LOOPCOUNTER < 30
> BEGIN
> SET @.LOOPCOUNTER = @.LOOPCOUNTER + 1
> /* Pause Loop Delay in hh:mm:ss:ms format */
> WAITFOR DELAY '00:00:00.200'
> END
> Also, once a record is read, it is deleted from the table. Could the
> deletes be locking the table?
> Thank you for ANY help.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> http://members.ebay.com/aboutme/coolspot18/|||"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns959B8FF7AC71Dnntprogerscom@.140.99.99.130...
> Hi all,
> I have a small table with the following columns:
> Refno (int), CampaignID(int), Value (int)
> Sometimes a record takes >30 - 40 seconds to write. Is there a way to
> ensure that a record is written in a timely manner?
>
> Other info: I do have a stored procedure which loops the table waiting
> for a response:
> WHILE ((SELECT COUNT(*) FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO) = 0)
> AND @.LOOPCOUNTER < 30
> BEGIN
> SET @.LOOPCOUNTER = @.LOOPCOUNTER + 1
> /* Pause Loop Delay in hh:mm:ss:ms format */
> WAITFOR DELAY '00:00:00.200'
> END
> Also, once a record is read, it is deleted from the table. Could the
> deletes be locking the table?
> Thank you for ANY help.
>
What version of SQL Server are you running? I am assuming 2000.
What indexes are on the table?
Why do you have a WHILE loop running? That is most likely the cause of your
problem. It is probably holding locks on the table while an INSERT is
attempting to grab locks.
Is the looping doing something for you? Some type of notification? Could
you use a trigger on INSERT to accomplish the same task without using the
loop?
Rick Sawtell
MCT, MCSD, MCDBA|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
news:eSEjFmcxEHA.2012@.TK2MSFTNGP15.phx.gbl:
> Can you post DDL for the table, including any indexes and constraints?
> Also, how many rows are in the table?
Approximately 20 - 30 rows at a time. After a record is read, it is deleted
from the table.
There are no indexes and constraints on the table. I am running SQL Server
2000.
CREATE TABLE [dbo].[DialAnswerLookup] (
[campID] [int] NOT NULL ,
[refno] [int] NOT NULL ,
[answeredby] [int] NULL
) ON [PRIMARY]
GO
The While loop is used for notification - An ASP script executes a stored
procedure which loops the table waiting for the insert. Once the insert is
completed, the loop will complete and the value is returned to the ASP
page.
--
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/|||"Rick Sawtell" <quickening@.msn.com> wrote in
news:uoA11mcxEHA.2624@.TK2MSFTNGP11.phx.gbl:
> What version of SQL Server are you running? I am assuming 2000.
Yes, SQL Server 2000.
> What indexes are on the table?
There are no indexes - I dropped all indexes in hopes that the INSERT
query will run faster.
> Why do you have a WHILE loop running? That is most likely the cause
> of your problem. It is probably holding locks on the table while an
> INSERT is attempting to grab locks.
The While loop is used for notification - An ASP script executes a
stored procedure which loops the table waiting for the insert to
complete. Once the insert is completed, the loop will find the record
and the value is returned to the ASP page. If no value is found within X
seconds the SP exits and the default value returned.
> Is the looping doing something for you? Some type of notification?
> Could you use a trigger on INSERT to accomplish the same task without
> using the loop?
Do you know of any other way to pause the execution of a select
statement and wait for a notification? A trigger *would* be nice... but
the problem is that the value must be returned to an external process (a
VoiceXML server which can only call webpages):
Here is the DDL for the table in case it might help you.
CREATE TABLE [dbo].[DialAnswerLookup] (
[campID] [int] NOT NULL ,
[refno] [int] NOT NULL ,
[answeredby] [int] NULL
) ON [PRIMARY]
GO
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/|||You could start by defining a primary key... Refno, perhaps?
And maybe ease up the SELECT loop a bit, and/or use a (NOLOCK) hint to
ensure that it doesn't hold locks on the table...
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns959B95DA8324nntprogerscom@.140.99.99.130...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
> news:eSEjFmcxEHA.2012@.TK2MSFTNGP15.phx.gbl:
> > Can you post DDL for the table, including any indexes and constraints?
> > Also, how many rows are in the table?
> Approximately 20 - 30 rows at a time. After a record is read, it is
deleted
> from the table.
> There are no indexes and constraints on the table. I am running SQL Server
> 2000.
> CREATE TABLE [dbo].[DialAnswerLookup] (
> [campID] [int] NOT NULL ,
> [refno] [int] NOT NULL ,
> [answeredby] [int] NULL
> ) ON [PRIMARY]
> GO
>
> The While loop is used for notification - An ASP script executes a stored
> procedure which loops the table waiting for the insert. Once the insert is
> completed, the loop will complete and the value is returned to the ASP
> page.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> http://members.ebay.com/aboutme/coolspot18/