January 9, 2003 at 10:42 am
Once again I come begging ...
If I run the following query from query ananlyzer:
INSERT INTO Prspct
(initl_cntct_mrktng_evnt_id,
prspct_type_cd ,
prspct_mail_name,
hsehold_addr1_name,
hsehold_addr2_name,
hsehold_city_name,
hsehold_state_cd,
hsehold_postal_zone_cd)
VALUES
('0258',
'Household',
'Joe Blow',
'222 Main Street',
'',
'Gorham',
'ME',
'04038')
the table Prpsct is updated succesfully.
If I run:
EXECUTE proc_CallCenterAbacusNewPrspct
'225',
'Household',
'Joe Blow',
'222 Main Street',
'',
'Gorham',
'ME',
'04038'
Where proc_CallCenterAbacusNewPrspct is:
CREATE PROCEDURE proc_CallCenterAbacusNewPrspct
@intIntlCntct_id int,
@strType varchar,
@strMailName varchar,
@strAddr1 varchar,
@strAddr2 varchar,
@strCity varchar,
@strState varchar,
@strZip varchar
AS
INSERT INTO Prspct
(initl_cntct_mrktng_evnt_id,
prspct_type_cd ,
prspct_mail_name,
hsehold_addr1_name,
hsehold_addr2_name,
hsehold_city_name,
hsehold_state_cd,
hsehold_postal_zone_cd)
VALUES
(@intIntlCntct_id,
@strType,
@strMailName,
@strAddr1,
@strAddr2,
@strCity,
@strState,
@strZip)
I get the error:
Server: Msg 547, Level 16, State 1, Procedure proc_CallCenterAbacusNewPrspct, Line 14
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'Prspct_FK05'. The conflict occurred in database 'DirMail_test', table 'State_Table', column 'state_cd'.
The statement has been terminated.
Its true that hsehold_state_cd is a foreign key in Prspct but how am I violating it with the SP???
Thanks once again!
Jonathan
January 9, 2003 at 11:01 am
In your stored procedure definition you have listed the parameters as being of type VARCHAR. Since you have not supplied the optional size (e.g., VARCHAR(10)), it is defaulting to a size of one. This means that the parameters passed in are getting truncated to a single character and, hence, the foreign key no longer matches what in the parent table.
HTH
Steve Hendricks
MCSD, MCDBA
AFS Consulting Group
(949) 588-9800 x15
Steve Hendricks
MCSD, MCDBA
Data Matrix
shendricks@afsconsulting.com
(949) 588-9800 x15
January 9, 2003 at 11:20 am
Thank you Steve, you are absolutely correct of course!
Jonathan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply