2

I have one of the stored procedures for my replication giving this error

Insert Error: Column name or number of supplied value does not match table definition".

The stored procedure is spInsertsrra and the table is srra, I have put the syntax below for the 2 articles.

       CREATE PROCEDURE SpInsertSrra @num_SessIdSession NUMERIC(10),
                                      @tin_SrraCptAnom1  TINYINT,
                                     @tin_SrraCptAnom2  TINYINT,
                                     @tin_SrraCptAnom3  TINYINT,
                                     @tin_SrraCptAnom4  TINYINT,
                                     @tin_SrraCptAnom5  TINYINT,
                                    @tin_SrraCptAnom6  TINYINT,
                                    @tin_SrraCptAnom7  TINYINT
AS
    INSERT INTO srra
    VALUES      (@num_SessIdSession,
                 @tin_SrraCptAnom1,
                 @tin_SrraCptAnom2,
                 @tin_SrraCptAnom3,
                 @tin_SrraCptAnom4,
                 @tin_SrraCptAnom5,
                 @tin_SrraCptAnom6,
                 @tin_SrraCptAnom7)

GO 

CREATE TABLE [SRRA] (
[SESS_ID_SESSION] [numeric](10, 0) NOT NULL ,
[SRRA_CPT_ANOM1] [tinyint] NULL ,
[SRRA_CPT_ANOM2] [tinyint] NULL ,
[SRRA_CPT_ANOM3] [tinyint] NULL ,
[SRRA_CPT_ANOM4] [tinyint] NULL ,
[SRRA_CPT_ANOM5] [tinyint] NULL ,
[SRRA_CPT_ANOM6] [tinyint] NULL ,
[SRRA_CPT_ANOM7] [tinyint] NULL ,
[rowguid]  uniqueidentifier ROWGUIDCOL  NOT NULL CONSTRAINT
           [DF__SRRA__rowguid__5F94079C] DEFAULT (newid()),
       CONSTRAINT [PK_SRRA] PRIMARY KEY  CLUSTERED 
           (
    [SESS_ID_SESSION]
         )  ON [PRIMARY] ,
         CONSTRAINT [FK_SRRA_CPTRS_ANO_SESS_SAN] FOREIGN KEY 
         (
    [SESS_ID_SESSION]
        ) REFERENCES [SESS_SANTE] (
    [SESS_ID_SESSION]
     )
          ) ON [PRIMARY]
          GO 

closed as off-topic by Jon Seigel, Mark Storey-Smith, Paul White, RolandoMySQLDBA, StanleyJohns Sep 24 '13 at 2:01

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Jon Seigel, Mark Storey-Smith, Paul White, RolandoMySQLDBA, StanleyJohns
If this question can be reworded to fit the rules in the help center, please edit the question.

  • I wondered who voted my question down. What is wrong in the question? – oshaye3 Sep 23 '13 at 12:08
  • 1
    And the definition of srra is? Also, if you have insert triggers, go ahead and post them + the table structure of what they reference – billinkc Sep 23 '13 at 12:19
  • Thanks bilinkc. I am not the developer of the database, but i checked the SP and tables and nothing like srra. I am not an expert on SP, but I have looked for the reference somehow but do not knwo how to get that. – oshaye3 Sep 23 '13 at 12:33
  • @oshaye3 Can you find the definition of srra (the CREATE TABLE statement)? – ypercubeᵀᴹ Sep 23 '13 at 12:46
  • 1
    @oshaye3 - No. Either of the approaches in the two answers will work to give the new row the column default. – Martin Smith Sep 23 '13 at 13:28
3

SRRA has 9 columns, but you're only inserting 8. Specify DEFAULT for your rowguid column.

INSERT INTO srra
    VALUES      (@num_SessIdSession,
                 @tin_SrraCptAnom1,
                 @tin_SrraCptAnom2,
                 @tin_SrraCptAnom3,
                 @tin_SrraCptAnom4,
                 @tin_SrraCptAnom5,
                 @tin_SrraCptAnom6,
                 @tin_SrraCptAnom7,
                 DEFAULT)
  • Thanks db2. It is working and I used the Default has suggested. The DB developer omitted this in their design and so gives insert error during replication. Cheers – oshaye3 Sep 23 '13 at 13:39
2

You aren't supplying a value for all the columns and so need an explicit column list.

INSERT INTO SRRA
            (SESS_ID_SESSION,
             SRRA_CPT_ANOM1,
             SRRA_CPT_ANOM2,
             SRRA_CPT_ANOM3,
             SRRA_CPT_ANOM4,
             SRRA_CPT_ANOM5,
             SRRA_CPT_ANOM6,
             SRRA_CPT_ANOM7)
VALUES      (@num_SessIdSession,
             @tin_SrraCptAnom1,
             @tin_SrraCptAnom2,
             @tin_SrraCptAnom3,
             @tin_SrraCptAnom4,
             @tin_SrraCptAnom5,
             @tin_SrraCptAnom6,
             @tin_SrraCptAnom7) 

ROWGUIDCOL columns don't behave like IDENTITY columns in this respect.

  • Thanks Martin, I understand now. I just used Default and it has worked. This has been affecting replication, but now is working. Cheers! – oshaye3 Sep 23 '13 at 13:36

Not the answer you're looking for? Browse other questions tagged or ask your own question.