Adding rowguid column broke this Stored Procedure?

Question

I have added a bounty to this as I have, as yet been able to figure this out and time is out.

---

The below stored procedure will not allow me to add it to Modify it. When attempting to modify it I get the following error -->

Msg 213, Level 16, State 1, Procedure spPersonRelationshipAddOpposing, Line 51 Insert Error: Column name or number of supplied values does not match table definition.

Also, Since the DB was set up for Merge Rep (a rowguid column has been added) this stored procedure now no longer works properly.

Do I need to chang the way the columns are listed? One of the warnings when setting up Merge Rep was this -->

Adding Guid Column MAY Cause INSERT Statements without column lists to Fail

What does that mean? Ideas on how I fix this?

USE [Connect]
GO
/****** Object:  StoredProcedure [dbo].[spPersonRelationshipAddOpposing]    Script Date: 07/15/2009 08:14:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spPersonRelationshipAddOpposing]
@ExistingRelationshipID INT 
AS
BEGIN
--Declare local variables
DECLARE @PersonID INT  --PersonID of established relarionship
DECLARE @RelatedID INT  --RelatedID of established relarionship
DECLARE @Relationship VARCHAR(4)  --Established relarionship
DECLARE @RelatedSex as VARCHAR(1)  
DECLARE @OpposingRelationship VARCHAR(4)
DECLARE @OpposingRelationshipID INT
--Fill variables from existing relationship
SELECT @PersonID = PersonID, @RelatedID = RelatedID, @Relationship=PersonRelationshipTypeID
FROM tblPersonRelationship where PersonRelationshipID = @ExistingRelationshipID
--Get gender of relative for finding opposing relationship type
SELECT @RelatedSex = (SELECT Gender FROM tblPerson WHERE PersonID = @PersonID)
--get opposing relationship types
IF (@RelatedSex='M')
    BEGIN
    SELECT @OpposingRelationship = (SELECT OpposingMaleRelationship 
                                    From tblAdminPersonRelationshipType 
                                    WHERE PersonRelationshipTypeID = @Relationship)
    END
ELSE IF (@RelatedSex='F')
    BEGIN
    SELECT @OpposingRelationship = (SELECT OpposingFemaleRelationship 
                                    From tblAdminPersonRelationshipType 
                                    WHERE PersonRelationshipTypeID = @Relationship)
    END
--check for existing opposing relationship
SELECT @OpposingRelationshipID = (SELECT MAX(PersonRelationshipID) FROM tblPersonRelationship WHERE PersonID = @RelatedID AND RelatedID = @PersonID)
--if an opposing relationship was found

IF (@OpposingRelationship IS NOT NULL)
    BEGIN
--if there is a relationship, update it
    IF ISNUMERIC(@OpposingRelationshipID)=1 
        BEGIN
            UPDATE tblPersonRelationship
            SET PersonRelationshipTypeID = @OpposingRelationship,
                MarriageDate = (SELECT MarriageDate FROM tblPersonRelationship WHERE PersonRelationshipID = @ExistingRelationshipID),
                ResidesWithPersonFlag = (SELECT ResidesWithPersonFlag FROM tblPersonRelationship WHERE PersonRelationshipID = @ExistingRelationshipID),
                UpdateDateTime = (SELECT UpdateDateTime FROM tblPersonRelationship WHERE PersonRelationshipID = @ExistingRelationshipID),
                UpdateProgram = (SELECT UpdateProgram FROM tblPersonRelationship WHERE PersonRelationshipID = @ExistingRelationshipID),
                UpdateUserID = (SELECT UpdateUserID FROM tblPersonRelationship WHERE PersonRelationshipID = @ExistingRelationshipID) 
            WHERE PersonRelationshipID = @OpposingRelationshipID
        END
--otherwise add record
    ELSE IF (@OpposingRelationship IS NOT NULL)
        BEGIN
            INSERT INTO tblPersonRelationship 
                SELECT @RelatedID, @OpposingRelationship, @PersonID,
                       MarriageDate, NULL, NULL, 
                       ResidesWithPersonFlag, NULL, UpdateDateTime, UpdateProgram,
                       UpdateUserID, UpdateDateTime, UpdateProgram, 
                       UpdateUserID, NULL FROM tblPersonRelationship WHERE PersonRelationshipID = @ExistingRelationshipID
        END
    END
END

Answer 1

I bleieve you may need to "push" the schema changes from the Publisher, as opposed to attempt to pull them from the client.

Follow the process for modifying an Article, for your specific replication topology, from the Books Online instruction here:

http://msdn.microsoft.com/en-us/library/ms152493(SQL.90).aspx

Let me know how you get on.

Answer 2

Are you replicating all tables and views which the stored procedure references?

Answer 3

You should always specify explicitly the list of columns when doing the INSERT. Rewrite your code like this:

INSERT INTO tblPersonRelationship (RelatedID, PersonRelationshipID, PersonID, ...)
SELECT @RelatedID, @OpposingRelationship, @PersonID, ...

A guid column was implicitly added to support merge replication, that's why you got the error about column lists mismatch.

Answer 4

This error message (Insert Error: Column name or number of supplied values does not match table definition) appears during an INSERT operation when either the number of supplied column names or the number of supplied values does not match the table definition.

I think you are dealing with the latter -- the number of supplied values does not match the table definition. That's my hunch based on the fact that you noted a rowguid column has been added to one of your tables.

Let's take your insert into tblPersonRelationship, for example. Based on the SELECT, one should assume tblPersonRelationship has 15 non-defaulted columns. If you added a rowguid column, it now needs to be represented in the SELECT in the proper position.

INSERT INTO tblPersonRelationship                 
SELECT 
@RelatedID, @OpposingRelationship, @PersonID,                       
MarriageDate, NULL, NULL,                        
ResidesWithPersonFlag, NULL, UpdateDateTime, 
UpdateProgram, UpdateUserID, UpdateDateTime, 
UpdateProgram, UpdateUserID, NULL,
newid()  
FROM tblPersonRelationship 
WHERE PersonRelationshipID = @ExistingRelationshipID

You may also get this error when dealing with computed columns. I don't believe this to be your issue, but you may find more here.

Answer 5

Don't write stored procedures...

I'll get my coat.

Answer 6

At the very end of your procedure you have the following INSERT:

    INSERT INTO tblPersonRelationship 
        SELECT @RelatedID, @OpposingRelationship, @PersonID,
               MarriageDate, NULL, NULL, 
               ResidesWithPersonFlag, NULL, UpdateDateTime, UpdateProgram,
               UpdateUserID, UpdateDateTime, UpdateProgram, 
               UpdateUserID, NULL FROM tblPersonRelationship WHERE PersonRelationshipID = @ExistingRelationshipID

This code will break if you add one more column to tblPersonRelationship. So you need to replace this code with the following:

    INSERT INTO tblPersonRelationship(explicit list of columns you are providing values for)
        SELECT @RelatedID, @OpposingRelationship, @PersonID,
               MarriageDate, NULL, NULL, 
               ResidesWithPersonFlag, NULL, UpdateDateTime, UpdateProgram,
               UpdateUserID, UpdateDateTime, UpdateProgram, 
               UpdateUserID, NULL FROM tblPersonRelationship WHERE PersonRelationshipID = @ExistingRelationshipID

by "list of columns you are providing values for" I mean the list of values in your SELECT:

@RelatedID, @OpposingRelationship, @PersonID,
               MarriageDate, NULL, NULL, 
               ResidesWithPersonFlag, NULL, UpdateDateTime, UpdateProgram,
               UpdateUserID, UpdateDateTime, UpdateProgram, 
               UpdateUserID, NULL

Because you do not provide a value for your rowguid, do not include it in the list of columns.

Answer 7

scope_identity() for a Guid column? No you have to do a lot more work to get the new guid.

First you have to create a table variable:

DECLARE @outputIdTbl TABLE (ID uniqueidentifier)

Then you have to put that in the middle of your insert statement.

INSERT INTO [dbo].[Orders]
           ([AccountId]
           ,[InvoiceDate]
           ,[LastUpdate]
           ,[UserId]
           ,[Sent]
          ,IsCredit
           ,[Status]
          ,Note2)
    output INSERTED.id into @outputIdTbl 
    VALUES
           (@AccountId
           ,@InvoiceDate
           ,GetDate()
           ,@UserId
           ,@Sent
          ,@IsCredit
           ,@Status
          ,@Note2)

Then you can retrieve the value from the table variable.

select @OrderId = id from @outputIdTbl