Solved

SQL Server msrepl_tran_version inserts fail

Posted on 2010-09-08
13
2,123 Views
Last Modified: 2012-05-10
I have recently set up a replication with updatable subscribers on a couple of remote SQL Server DB's. The column msrepl_tran_version was auto created in all my tables. Inserts fail on some tables, while they go right through on others.

The failures in Management Studio generate...
Cannot insert the value NULL into column 'msrepl_tran_version' table [TABLE NAME]; column does not allow nulls. INSERT fails.

The same query being run through an ASP document produces...
Column name or number of supplied values does not match table definition

I am able to get the inserts to go through if I explicitly list the msrepl_tran_version column and insert (newid()) as the value, but this is not a good solution as I would have to go through thousands of lines of code and add it to each and every query. I'm hoping to find a DB level solution.

The one thing I notice between a couple tables that fail, and the one that works, is that the working one has an identity (auto incrementing) primary key column. Could this have anything to do with the problem on the others? Any other suggestions as to how to fix the issue?
0
Comment
Question by:dale_abrams
  • 7
  • 6
13 Comments
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33630155
when you insert value in table using your ASP page, dont try to write value of AutoIncrement Id.

example, if you have a field MyInc as AutoIncrement Id in a table MyTable, lets assume it has other files like AB, CD, EF

write

INSERT INTO MyTable (AB, CD, EF) VALUES ('','','')

so here dont try to insert value for MyInc.

Is it ok?

0
 
LVL 1

Author Comment

by:dale_abrams
ID: 33630187
I am not writing to any auto incrementing columns on any of the tables. The table that allows inserts has an identity column, but I don't explicitly set a value for it.
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33630217
ok can you please provide code where you are getting error for mismatch columns?
0
 
LVL 1

Author Comment

by:dale_abrams
ID: 33630273
Here is one example


INSERT INTO Products
      (Prod_ID, Prod_Name, Prod_Description, Prod_Price, Prod_Category, Prod_Type, Prod_DescriptionLink)
VALUES
      (@Prod_ID,@Prod_Name,@Prod_Description,@Prod_Price,@Prod_Category,@Prod_Type,@Prod_DescriptionLink)
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33630287
What is the type of Prod_ID field?

is it Identity/AutoIncrement Column?
0
 
LVL 1

Author Comment

by:dale_abrams
ID: 33630344
As I mention above, of the tables I tested, the ones that fail do not have identity auto increment fields.

the Prod_ID is an Int PK NOT NULL field.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33630410
Ok, So according to you your primary key is not set for setting for auto-increment/identity.

Have you tryied debugging your code? any value that you are trying to insert in table (@Prod_Name,@Prod_Description,@Prod_Price,@Prod_Category,@Prod_Type,@Prod_DescriptionLink) may have (') single quote or any other special variable that causing termination of statement and making it as multiple value

like assume your @Prod_DescriptionLink has 'hello's'
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33630464
do you have "msrepl_tran_version" field in your product table?
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33630473
If yes, what is the defination of this field, like int/varchar, null/not null etc..?
0
 
LVL 1

Author Comment

by:dale_abrams
ID: 33631289
The sql code we have has been working for some time. Only now that we have implemented replication does it break. So quotes and things of that nature are not an issue.

I have an auto generated msrepl_tran_version field in all the tables. They get added to all articles when you set up replication. I'm a little unclear on where your going with the questions your asking.

My solution will require changing some database setting as I have already figured out how to fix the problem in my queries by adding the msrepl_tran_version field to them. This is not a solution for me as it would require editing thousands of lines of code. Is this making sense?
0
 
LVL 1

Accepted Solution

by:
dale_abrams earned 0 total points
ID: 33631921
I figured it out. Not all the msrepl_tran_version fields that the replication was supposed to set with the default value of newid() got set. So we just had to set them in SQL Server and now its working.
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33633273
thats is what is asked in my last question:

htttp:#a33630464
do you have "msrepl_tran_version" field in your product table?
0
 
LVL 1

Author Comment

by:dale_abrams
ID: 33637898
There is a big difference between asking whether the field exists after I have stated that it exists in all tables, and finding that the default value did not get set on all fields.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now