Post reply

Trouble inserting Uniqueidentifier data into foreign key field using stored procedure

  • January 27, 2010 at 8:16 am

    #214895

    I have two table. Site and Customers. I am trying to insert the Site.SiteId primary key (uniqueidentifier)

    data into the foreign key field Customers.SiteId. This should be straight forward.

    The simple insert works:

    INSERT INTO matms.customers

    VALUES(

    NEWID()

    , 'B088BEB8-3785-4261-9AFA-0F350DE0AA0C'

    , 'Test Customer Code'

    , 'Test Customer Name'

    , '2FCC1A88-12CC-448A-9918-F188E7B3A346'

    , 'Tester'

    , GETDATE()

    , Null

    , Null

    )

    But this does not.

    DECLARE @rc int

    DECLARE @P_ID uniqueidentifier

    DECLARE @P_ORGANIZATION_ID uniqueidentifier

    DECLARE @P_CUSTOMER_CODE nvarchar(max)

    DECLARE @P_NAME nvarchar(max)

    DECLARE @P_SITE_ID uniqueidentifier

    DECLARE @P_CREATED_BY nvarchar(max)

    set @P_ID = NULL

    set @P_ORGANIZATION_ID = (SELECT top 1 id from matms.ORGANIZATIONS)

    set @P_CUSTOMER_CODE = 'test0'

    set @P_NAME = 'test022'

    set @P_SITE_ID = (select top 1 id from [matms].[SITE])

    set @P_CREATED_BY = 'Tester'

    EXECUTE @rc = [matms].[matms].[CUSTOMERS_PKG$SAVE]

    @P_ID OUTPUT

    ,@P_ORGANIZATION_ID

    ,@P_CUSTOMER_CODE

    ,@P_NAME

    ,@P_SITE_ID

    ,@P_CREATED_BY

    I get this error:

    Msg 547, Level 16, State 0, Procedure CUSTOMERS_PKG$SAVE, Line 20

    The INSERT statement conflicted with the FOREIGN KEY constraint "SYS_C0010585". The conflict occurred in database "matms", table "matms.SITE", column 'ID'.

    The statement has been terminated.

    The site.id data is there. I just inserted it. I think it has to do with the way the data is being returned but I am at a loss. Any ideas?

    Thanks

    ______________________________
    AJ Mendo | @SQLAJ

  • January 27, 2010 at 9:10 am

    #1109368

    i am not sure if using Select Top 1 is getting you the results you expect. Before trying to use the variable in the exec statement why dont you print out the value of the variable P_SITE_ID first

  • January 27, 2010 at 9:32 am

    #1109384

    I am doing that. I just did not show it in the post. The site id is there. I think it has something to do with the formatting of the return query. I have tried converting to varchar(255) and varbinary. This has no effect since they are implicitly converted to uniqueidentifier.

    Thanks for the reply.

    ______________________________
    AJ Mendo | @SQLAJ

  • January 27, 2010 at 9:49 am

    #1109393

    can you post the stored procedure CUSTOMERS_PKG$SAVE

  • January 27, 2010 at 10:02 am

    #1109403

    /****** Object: StoredProcedure [matms].[CUSTOMERS_PKG$SAVE] Script Date: 01/27/2010 11:00:53 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[matms].[CUSTOMERS_PKG$SAVE]') AND type in (N'P', N'PC'))

    BEGIN

    EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE [matms].[CUSTOMERS_PKG$SAVE]

    @P_ID uniqueidentifier OUTPUT,

    @P_ORGANIZATION_ID uniqueidentifier,

    @P_CUSTOMER_CODE nvarchar(max),

    @P_NAME nvarchar(max),

    @P_SITE_ID uniqueidentifier,

    @P_CREATED_BY nvarchar(max)

    AS

    /*

    * Generated by SQL Server Migration Assistant for Oracle.

    * Contact ora2sql@microsoft.com or visit http://www.microsoft.com/sql/migration for more information.

    */

    BEGIN

    SET NOCOUNT ON;

    EXECUTE sysdb.ssma_oracle.db_check_init_package ''MATMS'', ''MATMS'', ''CUSTOMERS_PKG''

    IF @P_ID IS NULL OR @P_ID = ''{00000000-0000-0000-0000-000000000000}''

    BEGIN

    SET @P_ID = newid()

    INSERT matms.CUSTOMERS(

    ID,

    ORGANIZATION_ID,

    CUSTOMER_CODE,

    NAME,

    SITE_ID,

    CREATED_BY,

    CREATED_DATE)

    VALUES (

    @P_ID,

    @P_ORGANIZATION_ID,

    @P_CUSTOMER_CODE,

    @P_NAME,

    @P_SITE_ID,

    @P_CREATED_BY,

    sysdatetime())

    END

    ELSE

    BEGIN

    UPDATE matms.CUSTOMERS

    SET

    ORGANIZATION_ID = @P_ORGANIZATION_ID,

    CUSTOMER_CODE = @P_CUSTOMER_CODE,

    NAME = @P_NAME,

    SITE_ID = @P_SITE_ID,

    UPDATED_BY = @P_CREATED_BY,

    UPDATED_DATE = sysdatetime()

    WHERE CUSTOMERS.ID = @P_ID

    END

    SELECT @P_ID

    END

    '

    END

    ______________________________
    AJ Mendo | @SQLAJ

  • January 27, 2010 at 10:29 am

    #1109422

    The statements appear to be ok, the error is definitely not related to the return code. I would try the following

    Check the foreign key just to make sure the referencing table and columns are correct.

    I am assuming that the columns on both tables are setup as uniqueidentifers.

    Hard code the value of the site id in the stored procedure insert statement or update statement(whichever you are having trouble with, instead of the variable of P_site.

    If that works then the value that you are passing into the variable has to be the problem.

  • January 27, 2010 at 11:19 am

    #1109466

    Been there done all that and more. That's some of the issue.

    Thanks again for the reply(s)

    ______________________________
    AJ Mendo | @SQLAJ

  • January 27, 2010 at 2:44 pm

    #1109602

    I figured out the issue. I am a DUMBASS!

    Looking at the code I displayed. I have a matmsdev database I was working in.

    But calling the stored proc in the matms (production) database.

    That's why the Site.Id was not there!

    Oh I figgin' hate it when I am stupid like that.

    Thanks for the help!

    Cheers!

    ______________________________
    AJ Mendo | @SQLAJ

  • January 27, 2010 at 2:50 pm

    #1109607

    no problem, to be honest, I looked at the three part identifier and I asked myself if you were referencing two different databases. Not sure why I didnt say it out loud. sorry, sure I could of saved you some time.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply