Transactional vs snapshot replication

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

All Forums

SQL Server 2005 Forums

Replication (2005)

Transactional vs snapshot replication

Author

Topic

csaha
Yak Posting Veteran

52 Posts

Posted - 2012-08-07 : 17:04:41

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.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-08-08 : 00:14:29

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
LEFT JOIN
	INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
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;

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-08-08 : 04:18:22

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.

http://nosqlsolution.blogspot.co.uk/

csaha
Yak Posting Veteran

52 Posts

Posted - 2012-08-09 : 12:12:21

Thanks for all the responses. Does that mean that I have to use snapshot replication?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources