Results 1 to 7 of 7

Thread: GUIDs

  1. #1
    Join Date
    Dec 2002
    Posts
    2

    Unanswered: GUIDs

    I'm using SQLServer 2000. I have a table with a uniqueIdentifier field (first_guid) that calls newid() to populate with GUID.

    I have a stored procedure whose output parameter is a uniqueIdentifier. Below is the code:

    Create sp_test
    (@firstid int,
    @firstGuid uniqueIdentifier output)

    as

    Select @firstGuid = first_guid from testTable where testTable.first_id= @firstid

    Here is the problem. When I run this storedprocedure I get the following error:

    "Arithmetic overflow error converting expression to datatype nvarchar".
    Does anybody know why I'm getting this error?

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249

    Re: GUIDs

    /*
    Tested on MS SQL 2000 SP2 and works OK, but when I replace
    "@firstGuid UNIQUEIDENTIFIER OUTPUT"
    by
    "@firstGuid NVARCHAR OUTPUT"
    I receive as a matter of course the same error as you did.

    When this query works, then problem is
    invisible in syntax -> rewrite from blank text
    and others
    */
    IF OBJECT_ID('dbo.testTable') IS NOT NULL DROP TABLE dbo.testTable
    GO
    SELECT CONVERT(INT,0) AS first_id, NEWID() AS first_guid INTO testTable
    GO
    IF OBJECT_ID('dbo.sp_test') IS NOT NULL DROP PROC dbo.sp_test
    GO
    CREATE PROC sp_test(
    @firstid INT,
    @firstGuid UNIQUEIDENTIFIER OUTPUT
    ) AS
    SELECT @firstGuid = first_guid FROM testTable WHERE testTable.first_id= @firstid
    GO
    DECLARE @X0 INT
    DECLARE @X1 UNIQUEIDENTIFIER
    SET @X0=0
    EXEC sp_test @X0,@X1 OUTPUT
    SELECT @X1
    --Result: FA4C3E66-F576-4C3A-ADDF-8B32684596A3
    -- or something like that
    Last edited by ispaleny; 12-11-02 at 18:40.

  3. #3
    Join Date
    Oct 2002
    Posts
    369

    Question Re: GUIDs

    RE:
    /* Tested on MS SQL 2000 SP2 and works OK, but when I replace
    "@firstGuid UNIQUEIDENTIFIER OUTPUT"
    by
    "@firstGuid NVARCHAR OUTPUT"
    or
    "@firstGuid SYSNAME OUTPUT"
    I receive as a matter of course the same error as you did.
    The GUID conversion error could not be reproduced as such on a similar system (with additional hotfixes - on a dev version), using "@FirstGuid nVarChar (36) OUTPUT". Assuming that "@FirstGuid nVarChar OUTPUT", Q.V., was used to reproduce the error (functionally equivalent to "@FirstGuid nVarChar (1) OUTPUT")? When run against the same system, both do return an overflow error:
    "Server: Msg 8115, Level 16, State 2, Procedure sp_test, Line 5
    Arithmetic overflow error converting expression to data type nvarchar."

    Q1 I am very curious about the error result obtained with the SysName replacement. Could you confirm that using SysName actually DOES return the same overflow error on your system? (If so, exactly what Sql Server 2k version and edition is running?) The following replacements test fine (run against the dev server described above):

    @FirstGuid nVarChar (128) OUTPUT
    @FirstGuid SysName OUTPUT

  4. #4
    Join Date
    Dec 2002
    Posts
    2

    Re: GUIDs

    1) When using @firstGuid uniqueidentifier OUTPUT, I get the following error:
    Stored Procedure: testdb.dbo.sp_test
    Return Code = 0
    Output Parameter(s):
    Server: Msg 8115, Level 16, State 2, Line 11
    Arithmetic overflow error converting expression to data type nvarchar.
    Return Code = 0




    2) when using @firstGuid NVARCHAR OUTPUT, I get the following error:

    Server: Msg 8115, Level 16, State 2, Procedure sp_test, Line 7
    Arithmetic overflow error converting expression to data type nvarchar.
    Stored Procedure: testdb.dbo.sp_test
    Return Code = -6
    Output Parameter(s):
    @firstGuid = <NULL>


    3) when using @FirstGuid nVarChar (128) OUTPUT, I get the following error:

    Stored Procedure: testdb.dbo.sp_test
    Return Code = 0
    Output Parameter(s):
    Server: Msg 8115, Level 16, State 2, Line 11
    Arithmetic overflow error converting expression to data type nvarchar.
    Return Code = 0


    4) when using @FirstGuid SysName OUTPUT, I get the following error:
    Stored Procedure: testdb.dbo.sp_test
    Return Code = 0
    Output Parameter(s):
    Server: Msg 8115, Level 16, State 2, Line 11
    Arithmetic overflow error converting expression to data type nvarchar.
    Return Code = 0

  5. #5
    Join Date
    Oct 2002
    Posts
    369

    Question Re: GUIDs

    Q1 Are you seeing these errors running the ispaleny code segment (below) or only when running your production code, (or in both)?

    -- ispaleny code segment:
    Use TempDB
    Go
    IF OBJECT_ID('dbo.testTable') IS NOT NULL DROP TABLE dbo.testTable
    GO
    SELECT CONVERT(INT,0) AS first_id, NEWID() AS first_guid INTO testTable
    GO
    IF OBJECT_ID('dbo.sp_test') IS NOT NULL DROP PROC dbo.sp_test
    GO
    CREATE PROC sp_test(
    @firstid INT,
    @firstGuid UNIQUEIDENTIFIER OUTPUT
    ) AS
    SELECT @firstGuid = first_guid FROM testTable WHERE testTable.first_id= @firstid
    GO
    DECLARE @X0 INT
    DECLARE @X1 UNIQUEIDENTIFIER
    SET @X0=0
    EXEC sp_test @X0,@X1 OUTPUT
    SELECT @X1
    -- ispaleny code segment
    Q2 If you are seeing these errors only when running your production code post some additional code (as the source of the error is elsewhere)?

    Q3 If you are seeing these errors in both (when running your production code and when running the ispaleny code segment) could you post some additional system details?
    Last edited by DBA; 12-09-02 at 10:23.

  6. #6
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249

    Re: GUIDs

    Originally posted by DBA
    RE:
    Q1 I am very curious about ...:
    @FirstGuid nVarChar (128) OUTPUT
    @FirstGuid SysName OUTPUT
    I wrote "tested", but I tested only the first variant
    e.g. nvarchar(30) and assumed that sysname (nvarchar(128))
    would behave alike. I am sorry about your vasted time DBA.

  7. #7
    Join Date
    Oct 2002
    Posts
    369

    Thumbs up Re: GUIDs

    RE:
    I wrote "tested", but I tested only the first variant
    e.g. nvarchar(30) and assumed that sysname (nvarchar(128))
    would behave alike. I am sorry about your vasted time DBA.
    No problem, (no apology is necessary either); thank you for verifying that no unusual issue exists with the sysname type.

    Your assumption was actually correct, (at one time sysname was implemented as a 30), it was implemented as an nvarchar (128) in 7.0.

    My concern was that (potentially problematic) changes might have been made to sysname e.g.(in some hotfix(es)), say in regard to compatibility settings, etc., or worse, e.g.(general version or system specific behavior differences for the sysname type). Fortunately, I have not experienced an unexpected issue with the sysname type yet.

    Incidentally, your ordered streams solution for the heartbeat post http://dbforums.com/t589670.html (the one that requires no schema changes) looks pretty efficient (clustering the log data table might improve on run time slightly). It did surprise me that you found that a cursor implementation ran in minutes (my guess would have been more like an hour or so or longer), but perhaps your cursors are better written than average as well.

Posting Permissions

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