NESTED EXEC CAUSES FOREIGN KEY INSERT ERROR

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

NESTED EXEC CAUSES FOREIGN KEY INSERT ERROR

Author

Topic

jonesy
Starting Member

2 Posts

Posted - 2009-05-08 : 22:51:14

I'm using Microsoft SQL Server 2008 Studio Express

1. I have a stored procedure called m1Addresses_Create used to store street/building addresses. If I call the stored procedure directly (from VB.Net or the Management Console, it works. The stored procedure looks like this.

===================================================================
USE [CrewManager_Dev]
GO
/****** Object: StoredProcedure [dbo].[m1Address_Create] Script Date: 05/09/2009 10:04:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[m1Address_Create]

@Street dbo.stdEntityOtherName,
@Building dbo.stdEntityOtherName,
@Local dbo.stdEntityOtherName,
@District dbo.stdEntityOtherName,
@TMC dbo.stdEntityOtherName,
@Province dbo.stdEntityOtherName,
@Region dbo.stdEntityOtherName,
@State dbo.stdEntityOtherName,
@CountryID dbo.stdCountryFK,
@ZipCode dbo.stdZipCode,

@EditorID dbo.stdUserID,
@ApplicationID dbo.stdApplicationID,

@Result dbo.stdEnumeration OUTPUT,
@ErrMsg1 dbo.stdProcMessage OUTPUT,
@ErrMsg2 dbo.stdProcMessage OUTPUT,
@LastKey dbo.stdPKey OUTPUT

AS BEGIN

DECLARE @NewID stdPKey

BEGIN TRANSACTION

SET @NewID = NewID()

SET @Result = 0
SET @ErrMsg1 = ''
SET @ErrMsg2 = ''

--
-- #1 Validate Supplied Data
--

If @Street = '' AND
@Building = '' AND
@Local = '' AND
@District = '' AND
@TMC = '' AND
@Province = '' AND
@Region = '' AND
@State = '' AND
@CountryID = '' AND
@ZipCode = '' BEGIN

SET @Result = 1
SET @ErrMsg2 = '* All Address Fields Empty '

END
--
-- Do Other Field Validations Here
--

IF @Result = 1 BEGIN

SET @ErrMsg1 = 'The following information were not supplied:'
GOTO OnError

END

--
-- #2 Check for Duplicates
--

SELECT AddressID
FROM dbo.m1Address
WHERE Street = @Street AND
Building = @Building AND
[Local] = @Local AND
District = @District AND
TMC = @TMC AND
Province = @Province AND
Region = @Region AND
[State] = @State AND
ZipCode = @ZipCode AND
Country = @CountryID

IF @@RowCount > 0 BEGIN

SET @Result = 2
SET @ErrMsg1 = 'An exact duplicate of this address already exists in the database'
SET @ErrMsg2 = ''

GOTO OnError

END

--
-- #3 Create Record
--

INSERT INTO m1Address (AddressID,
Street, Building, [Local],
District, TMC, Province,
Region, [State], Country,
ZipCode,
MachineID, TransactionStamp,
LastEditBy, ApplicationID
)
VALUES (@NewID,
@Street, @Building, @Local,
@District, @TMC, @Province,
@Region, @State, @CountryID,
@ZipCode,
HOST_NAME(), GETDATE(),
@EditorID, @ApplicationID)

IF (@@ERROR <> 0) BEGIN

SET @Result = 4

SET @ErrMsg1 = 'An unspecified error occurred while attempting to create the new entry.'
SET @ErrMsg2 = 'Please try sending the create request again.'

GOTO onError

END

COMMIT TRANSACTION

SET @Result = 0

SET @ErrMsg1 = ''
SET @ErrMsg2 = ''

SET @LastKey = @NewID

RETURN

OnError:

SET @ErrMsg2 = @ErrMsg2 + ' (1000.01)'
ROLLBACK TRANSACTION
SET @LastKey = Null
RETURN

END

===================================================================

The table it updates looks like this:

===================================================================

USE [CrewManager_Dev]
GO
/****** Object: Table [dbo].[m1Address] Script Date: 05/09/2009 10:17:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[m1Address](
[AddressID] [dbo].[stdPKey] NOT NULL,
[Street] [dbo].[stdEntityOtherName] NULL,
[Building] [dbo].[stdEntityOtherName] NULL,
[Local] [dbo].[stdEntityOtherName] NULL,
[District] [dbo].[stdEntityOtherName] NULL,
[TMC] [dbo].[stdEntityOtherName] NULL,
[Province] [dbo].[stdEntityOtherName] NULL,
[Region] [dbo].[stdEntityOtherName] NULL,
[State] [dbo].[stdEntityOtherName] NULL,
[Country] [dbo].[stdCountryFK] NULL,
[ZipCode] [dbo].[stdZipCode] NULL,
[MachineID] [dbo].[stdMachineID] NOT NULL,
[TransactionStamp] [dbo].[stdTransactionStamp] NOT NULL,
[LastEditBy] [dbo].[stdUserID] NOT NULL,
[ApplicationID] [dbo].[stdApplicationID] NOT NULL,
CONSTRAINT [PK_mAddress] PRIMARY KEY CLUSTERED
(
[AddressID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[m1Address] WITH CHECK ADD CONSTRAINT [FK_m1Address_m1Countries] FOREIGN KEY([Country])
REFERENCES [dbo].[m1Countries] ([CountryID])
GO
ALTER TABLE [dbo].[m1Address] CHECK CONSTRAINT [FK_m1Address_m1Countries]

===================================================================

Which is dependent on the Countries Table

===================================================================

USE [CrewManager_Dev]
GO
/****** Object: Table [dbo].[m1Countries] Script Date: 05/09/2009 10:18:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[m1Countries](
[CountryID] [dbo].[stdCountryFK] NOT NULL,
[CountryName] [dbo].[stdEntityName] NOT NULL,
[Nationality] [dbo].[stdEntityOtherName] NULL,
[CurrencyID] [dbo].[stdFKey] NULL,
[MachineID] [dbo].[stdMachineID] NOT NULL,
[TransactionStamp] [dbo].[stdTransactionStamp] NOT NULL,
[LastEditBy] [dbo].[stdUserID] NOT NULL,
[ApplicationID] [dbo].[stdApplicationID] NOT NULL,
CONSTRAINT [PK_mCountries] PRIMARY KEY CLUSTERED
(
[CountryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

====================================================================

2. If I call the same stored procedure from inside another stored procedure using EXEC (in my case, m6School_Create, it generates an error message that said the INSERT command failed.

3. To find out what's wrong, I opened a New Query Window in the Management Console, call m1Address_Create directly, then called the other stored procedure that calls m1Address_Create, like so:

Use CrewManager_Dev

EXEC m1Address_Create @Street = 'Street Name',
@Building = 'Buidling Name'
...
go

EXEC m6School_Create (parameters here)...
go

4. I passed two different addresses to the m1Address_Create and m6School_Create.

5. m1Address_Create executes normally.
6. m6School_Create terminates with the following error:

Msg 547, Level 16, State 0, Procedure m1Address_Create, Line 90
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_m1Address_m1Countries". The conflict occurred in database "CrewManager_Dev", table "dbo.m1Countries", column 'CountryID'.
The statement has been terminated.

6. I'm sure that the CountryID being inserted is in the m1Countries database, because I just used it in the direct call to m1Address_Create

7. What's wrong?

8. m6Schools Create Looks like this:

====================================================================

USE [CrewManager_Dev]
GO
/****** Object: StoredProcedure [dbo].[m6Schools_Create] Script Date: 05/09/2009 10:29:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[m6Schools_Create]

@SchoolID dbo.stdFKey,
@SchoolName dbo.stdEntityName,
@OrganisationType dbo.stdEnumeration,
@Remarks dbo.stdMemo,

@OrganisationUpdateMode dbo.stdEnumeration,

@PreferredList dbo.stdYesNo,
@PreSchool dbo.stdYesNo,
@Primary dbo.stdYesNo,
@Intermediate dbo.stdYesNo,
@Elementary dbo.stdYesNo,
@Secondary dbo.stdYesNo,
@JuniorHigh dbo.stdYesNo,
@MiddleSchool dbo.stdYesNo,
@Gymnasium dbo.stdYesNo,
@Tertiary dbo.stdYesNo,
@TechVoc dbo.stdYesNo,
@Finishing dbo.stdYesNo,

@AdddressUpdateMode dbo.stdEnumeration,
@sAddressID dbo.stdFKey,
@sBldg dbo.stdEntityOtherName,
@sStreet dbo.stdEntityOtherName,
@sLocality dbo.stdEntityOtherName,
@sDistrict dbo.stdEntityOtherName,
@sTMC dbo.stdEntityOtherName,
@sProvince dbo.stdEntityOtherName,
@sRegion dbo.stdEntityOtherName,
@sState dbo.stdEntityOtherName,
@sZipCode dbo.stdZipCode,
@sCountryID dbo.stdCountryFK,

@PhoneUpdateMode dbo.stdEnumeration,
@sPhoneID dbo.stdFKey,
@sCountryCode dbo.m1PhoneCountryCode,
@sAreaCode dbo.m1PhoneAreaCode,
@sPhoneNumber dbo.m1PhoneNumber,
@sEndNumber dbo.m1PhoneNumber,
@sCallMode dbo.stdEnumeration,
@sLineType dbo.stdEnumeration,

@FaxUpdateMode dbo.stdEnumeration,
@sPhoneID_F dbo.stdFKey,
@sCountryCode_F dbo.m1PhoneCountryCode,
@sAreaCode_F dbo.m1PhoneAreaCode,
@sPhoneNumber_F dbo.m1PhoneNumber,
@sEndNumber_F dbo.m1PhoneNumber,
@sCallMode_F dbo.stdEnumeration,
@sLineType_F dbo.stdEnumeration,

@EmailUpdateMode dbo.stdEnumeration,
@sEmailID dbo.stdFKey,
@sEmailAddress dbo.stdEmailAddress,

@URLUpdateMode dbo.stdEnumeration,
@sURLID dbo.stdFKey,
@sURLName dbo.stdURL,

@EditorID dbo.stdUserID,
@ApplicationID dbo.stdApplicationID,

@Result dbo.stdEnumeration OUTPUT,
@ErrMsg1 dbo.stdProcMessage OUTPUT,
@ErrMsg2 dbo.stdProcMessage OUTPUT,
@LastKey dbo.stdPKey OUTPUT

AS BEGIN

BEGIN TRANSACTION

DECLARE @tResult dbo.stdEnumeration
DECLARE @tErrMsg1 dbo.stdProcMessage
DECLARE @tErrMsg2 dbo.stdProcMessage

DECLARE @tAddress dbo.stdPKey
DECLARE @tPhone dbo.stdPKey
DECLARE @tFax dbo.stdPKey
DECLARE @tEmail dbo.stdPKey
DECLARE @tWeb dbo.stdPKey
DECLARE @tOrg dbo.stdPkey

--
-- Add / Update the Address File Provided
--
SET @tResult = 0
SET @tErrMsg1 = ''
SET @tErrMsg2 = ''

IF @AdddressUpdateMode = dbo.std_UpdateMode_AddNew()

EXEC dbo.m1Address_Create

@Street = @sStreet,
@Building = @sBldg,
@Local = @sLocality,
@District = @sDistrict,
@TMC = @sTMC,
@Province = sProvince,
@Region = @sRegion,
@State = @sState,
@ZipCode = @sZipCode,
@CountryID = @sCountryCode,

@EditorID = @EditorID,
@ApplicationID = @ApplicationID,

@Result = @tResult output,
@ErrMsg1 = @tErrMsg1 output,
@ErrMsg2 = @tErrMsg2 output,
@LastKey = @tAddress output

ELSE

IF @AdddressUpdateMode =
dbo.std_UpdateMode_EditExisting() BEGIN

EXEC dbo.m1Address_Update

@AddressID = @sAddressID,
@Street = @sStreet,
@Building = @sBldg,
@Local = @sLocality,
@District = @sDistrict,
@TMC = @sTMC,
@Province = @sProvince,
@Region = @sRegion,
@State = @sState,
@ZipCode = @sZipCode,
@CountryID = @sCountryCode,

@EditorID = @EditorID,
@ApplicationID = @ApplicationID,

@Result = @tResult output,
@ErrMsg1 = @tErrMsg1 output,
@ErrMsg2 = @tErrMsg2 output

SET @tAddress = @sAddressID

END

ELSE

SET @tAddress = NULL

---
--- Check If the Address Add/Update Routine Failed
---

IF @tResult <> 0 BEGIN

SET @Result = @tResult
SET @ErrMsg1 = @tErrMsg1
SET @ErrMsg2 = @tErrMsg2

GOTO OnError

END

-- Add / Update the Landline File Provided
--- *** CODE DELETED

-- Add / Update the Fax File Provided
--- *** CODE DELETED

-- Add / Update the Email File Provided
--- *** CODE DELETED

-- Add / Update the School's Base Organisation
--- *** CODE DELETED

-- #3 Create the Record
--- *** Code Deleted

COMMIT TRANSACTION

SET @Result = 0

SET @ErrMsg1 = ''
SET @ErrMsg2 = ''

SET @LastKey = @SchoolID

RETURN

OnError:

SET @ErrMsg2 = @ErrMsg2 + ' (6000.01)'

ROLLBACK TRANSACTION
SET @LastKey = Null
RETURN

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-09 : 05:04:56

i cant spot a step in both the procedures where you're inserting to m1Countries table.where exactly is that insertion happening?

jonesy
Starting Member

2 Posts

Posted - 2009-05-10 : 02:50:20

Hi All,

Sorry for the post.

Found a bug in MY code.

In m6School_Create, I was passing the wrong parametre to m1Address_Create.

Instead of passing @CountryID, I was passing @CountryCode to it, which was meant for another stored procedure for creating phone directory entries.

Mea Culpa Maxima.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-10 : 03:29:04

No Probelms...Glad that you spotted it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources