MS SQL Server 2000 Warning Messages

[-rob-]

Hi all,

Does anyone know of a method to stop SQL from outputting warning messages in a stored procedure called from an ASP page?

I have a table with a Unique ID column that is the Primary Key. I am running a SP that generates a UniqueID and creates a record in the table. In the SP if the Insert generates an error 2627 then the UniqueID already exists, so I wait for a period of time and try creating another Unique ID.

This works fine, and all the records are created with unique IDs. The problem I have is the ASP calling page is showing the 2627 error message.

Without using On Error Resume Next in ASP, how do I suppress these error messages being ouput from SQL to the ASP page?

Thanks all very much in advance.

[MattR]

Why not use identity or GUID?

[-rob-]

Why not use identity or GUID?

Good question, but one which I do not have an answer to. The SP method is the way it is to be done.

[r937]

identity/guid is totally compatible with stored procedures

[-rob-]

identity/guid is totally compatible with stored procedures

r937,

Thank you for your input, but this is not the solution as the whole entire database design uses this Unique ID generating SP. The DB designer will not redesign the whole database as it has been in production for a number of years and has a lot of data already in it.

Is there a DB person in this forum who can actually answer the question I asked? I need to find a solution ASAP.

Again, thank you very much in advance.

[MattR]

As far as I know, there is no way to suppress error messages from SQL Server. You have to use:
On Error Resume Next in ASP

If you guys really want to use this not-so-good approach, why not do something like this in the stored procedure (pseudocode):

CREATE PROCEDURE foo
 @uniqueID VARCHAR( 20 ) OUTPUT
AS
DECLARE @areWeDone BIT
SET @areWeDone = 0
WHILE( @areWeDone <> 1 )
BEGIN
 SET @uniqueID = howeverYouGenerateUniqueIDs
 
 IF NOT EXISTS( SELECT *
				  FROM yourTable
				 WHERE uniqueID = @uniqueID )
 BEGIN
  SET @areWeDone = 1 
 END
END

[r937]

Is there a DB person in this forum who can actually answer the question I asked?

pardon me, but what you asked has nothing to do with the database and everything to do with your scripting language of choice, ASP

perhaps your question would have been better in the ASP forum

[-rob-]

pardon me, but what you asked has nothing to do with the database and everything to do with your scripting language of choice, ASP

perhaps your question would have been better in the ASP forum

r937,

I do not wish to get into a fight with you, and I apologise if I offended you in any way. I did explicitly state the situation in my initial post, and explained exactly what I was looking for. You responded with identity/guid; and while I personally would have used this had I designed the DB, I am unfortunately stuck supporting a database developed by another person.

Additionally these error messages are SQL driven, and appear in QA when running this SP. The only reason I mention ASP is to let readers know the calling platform, ergo this is not an ASP issue.

[-rob-]

As far as I know, there is no way to suppress error messages from SQL Server. You have to use:
On Error Resume Next in ASP

If you guys really want to use this not-so-good approach, why not do something like this in the stored procedure (pseudocode):

CREATE PROCEDURE foo
@uniqueID VARCHAR( 20 ) OUTPUT
AS
DECLARE @areWeDone BIT
SET @areWeDone = 0
WHILE( @areWeDone <> 1 )
BEGIN
SET @uniqueID = howeverYouGenerateUniqueIDs
 
IF NOT EXISTS( SELECT *
				 FROM yourTable
				 WHERE uniqueID = @uniqueID )
BEGIN
SET @areWeDone = 1 
END
END

MattR,

I suggested this method, but I was told it is not an option, because it is too expensive...

I know I could use On Error Resume Next, but unfortunately it does not allow one to properly handle exceptions.

There must be a way in SQL to suppress the error messages, without making it a server global setting.

[MattR]

There is no way to prevent SQL Server from raising error messages in this case.

http://www.algonet.se/~sommar/error-handling-I.html

http://www.algonet.se/~sommar/error-handling-II.html

[-rob-]

MattR,

That is very unfortunate. The SP does a check against @@ERROR and then reiterates a loop if necessary. Unfortunately the error appears before the @@ERROR value is updated with the error.

Do you agree with the previous comment made to me that doing a check for the uniqueID would be too expensive? Does it depend upon the number of records? We are dealing with tables of records from just a few records to a couple of hundred thousand.

Thanks for the links, very interesting.

[MattR]

What is more expensive:
a duck
a chicken



You need to explain exactly how the stored procedure works and how/why it is invoked in order to say if it would be 'more expensive' (well, I agree that it would be 'more expensive' but is it necessarily noticeably so?). Where does this stored procedure fall in the grand scheme of things? How are "unique" (because obviously they are not) identifiers created?

[-rob-]

Well that all depends on whether you're at my market or yours!

Okay, let us pretend I don't have a this DB developers restrictions to work with, and look at this from a different angle. If the DB uses GUID's created by a SP, what would be the best method to handle duplicate GUID's in the following pseudo code:

INSERT into table GUID, color, length
IF @@ERROR = 2627 THEN try again
Else End

I am assuming from comments above that the only method to check for duplicate keys without ADO spitting out an error is to query the table first for the key. Correct? So in essence the original DB's UniqueID generator is not really to relevent in this issue...

[-rob-]

I finally got an answer from the DB person... he creates his own 16 digit unique ID because it is a lot shorter than a GUID and therefore uses a lot less space.

[MattR]

And identities (4 byte ints) use a lot less space than a 16 byte ID.

[r937]

... and therefore uses a lot less space.

and you therefore a lot more advil

[-rob-]

Guys,

This is something I of course did not know, but gives me some great ammunition to go back with. I could never fathom any good reasons for his methods and reasons for creating his own unique ID. I ran some testing on his SP and it iterates over 1,000 times before it is able to spit out a new ID, because it is based on the date and time, to the millisecond. So our server is running the SP over 1,000 within 1 millisecond! That is to me quite phenomenol performance, though I have not much experience in this area.

Thanks for all your help.