Trouble inserting Uniqueidentifier data into foreign key field using stored procedure

Question

Trouble inserting Uniqueidentifier data into foreign key field using stored procedure

SQLAJ

Ten Centuries

Points: 1359
More actions
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

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

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

jcdyntek Ten Centuries Points: 1153 More actions · Answer 1

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

SQLAJ Ten Centuries Points: 1359 More actions · Answer 2

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

jcdyntek Ten Centuries Points: 1153 More actions · Answer 3

can you post the stored procedure CUSTOMERS_PKG$SAVE

SQLAJ Ten Centuries Points: 1359 More actions · Answer 4

/****** 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

jcdyntek Ten Centuries Points: 1153 More actions · Answer 5

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.

SQLAJ Ten Centuries Points: 1359 More actions · Answer 6

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

Thanks again for the reply(s)

______________________________
AJ Mendo | @SQLAJ

SQLAJ Ten Centuries Points: 1359 More actions · Answer 7

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

jcdyntek Ten Centuries Points: 1153 More actions · Answer 8

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.