SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Zealot -rob-'s Avatar
    Join Date
    Mar 2003
    Location
    Earth
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MS SQL Server 2000 SP Warning Messages

    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.
    Last edited by -rob-; May 7, 2004 at 10:05.
    Cheers,

    -rob-

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why not use identity or GUID?

  3. #3
    SitePoint Zealot -rob-'s Avatar
    Join Date
    Mar 2003
    Location
    Earth
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MattR
    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.
    Cheers,

    -rob-

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    identity/guid is totally compatible with stored procedures
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot -rob-'s Avatar
    Join Date
    Mar 2003
    Location
    Earth
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    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.
    Cheers,

    -rob-

  6. #6
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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):
    Code:
    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

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by -rob-
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot -rob-'s Avatar
    Join Date
    Mar 2003
    Location
    Earth
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    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.
    Cheers,

    -rob-

  9. #9
    SitePoint Zealot -rob-'s Avatar
    Join Date
    Mar 2003
    Location
    Earth
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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):
    Code:
    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.
    Cheers,

    -rob-

  10. #10
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  11. #11
    SitePoint Zealot -rob-'s Avatar
    Join Date
    Mar 2003
    Location
    Earth
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Last edited by -rob-; May 11, 2004 at 13:03.
    Cheers,

    -rob-

  12. #12
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  13. #13
    SitePoint Zealot -rob-'s Avatar
    Join Date
    Mar 2003
    Location
    Earth
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    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...
    Cheers,

    -rob-

  14. #14
    SitePoint Zealot -rob-'s Avatar
    Join Date
    Mar 2003
    Location
    Earth
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Cheers,

    -rob-

  15. #15
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And identities (4 byte ints) use a lot less space than a 16 byte ID.

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by -rob-
    ... and therefore uses a lot less space.
    and you therefore a lot more advil

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Zealot -rob-'s Avatar
    Join Date
    Mar 2003
    Location
    Earth
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Cheers,

    -rob-


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •