Groups | Blog | Home
all groups > sql server replication > may 2006 >

sql server replication : Transformable v. regular subscriptions


Hilary Cotter
5/12/2006 12:00:00 AM
Transformable Subscriptions use a DTS package to transform the data as it
moves from the Publisher to the Subscriber. It is intended to transform the
data when it goes to heterogeneous subscribers. It has been deprecated in
SQL 2005. If you need to transform the data for SQL Server subscribers you
are better to use custom stored procedures.

To answer your question, I take it that you are able to use the DTS package
code outside of the publication and it does not lose data. If so, you should
not be loosing data while using transformable subscriptions. You can always
log to a table where the DTS package runs for debugging purposes.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



[quoted text, click to view]

Oskar
5/12/2006 4:47:02 AM
Hi,
Tell me please, is there any chance to lose or get altered data in a
susbcription database if the character mode format of snapshots and
transformable subscriptions are used in the transactional replication that
was set up between MS SQL Server 2000 SP3 servers?

--Many thanks, Oskar
Oskar
5/12/2006 5:07:02 AM
Forgot to mention that the data in the publication database for the
transactional replication is stored using a legacy multilingual DOS codepage,
which is mapped to a single codepage that can be recognized by the SQL Server
software on the servers.


[quoted text, click to view]
Oskar
5/12/2006 9:10:02 AM
I use transformable subscriptions and the accompanying character format of
snapshots, because, as far as I can see, that's the only way I can "tell" the
snapshot agent to skip the columns that aren't present in the publication
database while data is copied in bulk into the respective table in the
subscription database. If the snapshot agent of the transactional replication
could use the format file in the same way the bcp utility does it, I'd stick
with the native format of snapshots, but since it doesn't I'm left with only
the character format. Is the character format the same as the native format
in respect of transferring the exact copy of published data to the
subscription database? Can you think of any issues, for example, when
transferring data stored using a very outdated codepage ?

-- Many thanks, Oskar

[quoted text, click to view]
Hilary Cotter
5/12/2006 12:56:02 PM
Have a look at a custom sync object. I think this will do what you are
looking for.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



[quoted text, click to view]

Oskar
5/14/2006 10:44:02 PM
You mean filtering the columns? If yes, I've tried that already. I discovered
that the snapshot agent doesn't work when schemas are different at the
subscriber. It seems to be hapenning, because the insert bulk process of the
snapshot agent can't be "told" to skip the excess columns at the subsriber so
it ends up inserting the wrong type of data in those columns.

-- Oskar

[quoted text, click to view]
Oskar
5/14/2006 10:56:02 PM
I thought about it again, maybe I could edit the synchronization views
manually and have some fake values selected for the excess columns. Should
give it try.


[quoted text, click to view]
Oskar
5/15/2006 5:15:02 AM
I modified the synchronization views, but that didn't help. As soon as I add
a fake (NULL) column to the view definition the snapshot agent fails with
this error:

<< Cannot use empty object or column names. Use a single space if necessary.
[quoted text, click to view]

I have an extra timestamp column in each of the subscriber tables, which has
to be skipped or filled with NULLs. Looks like this method doesn't allow the
snapshot agent to export the NULL values for inserting into those extra
columns.

[quoted text, click to view]
Hilary Cotter
5/16/2006 12:00:00 AM
you might want to review this post.

http://groups.google.com/group/microsoft.public.sqlserver.replication/browse_frm/thread/c42a884df89a98fd?tvc=1&q=custom+sync+object

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



[quoted text, click to view]

AddThis Social Bookmark Button