SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Replication (2005)
 Transactional vs snapshot replication
 New Topic  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], t.name [table]
FROM	sys.tables t
On	schema_name(t.schema_id) = c.table_schema
And	t.name = 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  
 New 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.12 seconds. Powered By: Snitz Forums 2000