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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Replication (2005)
 Transactional vs snapshot replication
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yak Posting Veteran

52 Posts

Posted - 08/07/2012 :  17:04:41  Show Profile  Reply with Quote
I am using one database to setup publication but I see variation in the tables primary key when trying to use snapshot and transactional replication. When I choose snapshot replication all tables in the database has primary key, but when I use transactional database some tables in the database does not have primary key. Why is this different?I wish I could post pictures of the publications.


5072 Posts

Posted - 08/08/2012 :  00:14:29  Show Profile  Visit russell's Homepage  Reply with Quote
No. Either the tables have primary keys or they don't.

This query will show you tables that don't have Primary Keys

SELECT	schema_name(t.schema_id) [schema], [table]
FROM	sys.tables t
On	schema_name(t.schema_id) = c.table_schema
And = c.table_name
And	c.constraint_type = 'PRIMARY KEY'
WHERE	c.table_name is null;

Edited by - russell on 08/08/2012 00:31:59
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 08/08/2012 :  04:18:22  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
A table that doesn't have a primary key cannot be used for transactional replication. (you can't set it up)

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Go to Top of Page

Yak Posting Veteran

52 Posts

Posted - 08/09/2012 :  12:12:21  Show Profile  Reply with Quote
Thanks for all the responses. Does that mean that I have to use snapshot replication?
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000