Replication and Timestamp

Hi,
I have a database that has tables with Timestamp field. I am trying to do a
transactional replication on this db. The wizard changed my "Subscription"
table's timestamp field to Binary format.
Now, the problem is that a report application (we didn't write it) require
this TimeStamp field as Timestamp datatype for it to work. We create the
replication mainly because of this application (we don't want it to take up
any real cpu cycle on the live server). Switching this field back to
timestamp breaks the replication.
Help...
thanks,
Alex
Hi Alex,
You may want to consider using merge replication or
transactional replication with the queued updating
option. For these replications, when articles contain a
timestamp column, the timestamp column is replicated,
but the literal timestamp values are not. The timestamp
values are regenerated when applying the initial
snapshot rows at the Subscriber. This allows timestamp
to continue using optimistic concurrency control (a
frequent usage). For snapshot and transactional
publications, and publications that allow immediate
updating, the literal values for a timestamp column are
replicated, but the data type for the replicated values
is changed to binary (8) on the Subscriber.
This information can be found at the "Data Needs and
Characteristics" topic in BOL:
http://msdn.microsoft.com/library/en...lsql/replplan_
5jub.asp
If you have any further questions, please feel free to
let me know.
Sincerely,
William Wang
Microsoft Online Partner Support
Get Secure! - <www.microsoft.com/security>
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Reply-To: "Smartikat" <Smartikat@.online.nospam>
>From: "Smartikat" <Smartikat@.online.nospam>
>Subject: Replication and Timestamp
>Date: Thu, 9 Dec 2004 16:20:43 -0800
>Lines: 19
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>Message-ID: <#JaVY4k3EHA.1396@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: 63.171.237.106
>Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFT NGP08.p
hx.gbl!tk2msftngp13.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.replication:57801
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>Hi,
>I have a database that has tables with Timestamp field.
I am trying to do a
>transactional replication on this db. The wizard
changed my "Subscription"
>table's timestamp field to Binary format.
>Now, the problem is that a report application (we
didn't write it) require
>this TimeStamp field as Timestamp datatype for it to
work. We create the
>replication mainly because of this application (we
don't want it to take up
>any real cpu cycle on the live server). Switching this
field back to
>timestamp breaks the replication.
>Help...
>thanks,
>Alex
>
>
|||Well, it works to a point...
Here is the problem I am faced with using this new method (Queued Update
setup).
1) Snapshot works fine.
2) When Transactional Replication happens, it will error out with "Syntax
Error/Access Denied Error".
3) Error happens because the Replication Wizard created a bunch of SPs on
the subscriber. And all these SPs contain space " ", "," and "$" in its
name. Main reason is that my Table names where these SPs based on contain "
", "," and "$".
4) Before switching to Queued Update method, I have a Tab named "Command"
under the Article setup screen. Now it is gone.
If there is a way for me to change the SPs name and tell the server to use
them, I am set. Or if there is a way for me to disable all these SPs, I
will be set too...
Please Help.
Alex
"William Wang[MSFT]" <v-rxwang@.online.microsoft.com> wrote in message
news:XI86lfo3EHA.3956@.cpmsftngxa10.phx.gbl...
> Hi Alex,
> You may want to consider using merge replication or
> transactional replication with the queued updating
> option. For these replications, when articles contain a
> timestamp column, the timestamp column is replicated,
> but the literal timestamp values are not. The timestamp
> values are regenerated when applying the initial
> snapshot rows at the Subscriber. This allows timestamp
> to continue using optimistic concurrency control (a
> frequent usage). For snapshot and transactional
> publications, and publications that allow immediate
> updating, the literal values for a timestamp column are
> replicated, but the data type for the replicated values
> is changed to binary (8) on the Subscriber.
> This information can be found at the "Data Needs and
> Characteristics" topic in BOL:
> http://msdn.microsoft.com/library/en...lsql/replplan_
> 5jub.asp
> If you have any further questions, please feel free to
> let me know.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> Get Secure! - <www.microsoft.com/security>
> ================================================== ===
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --
> cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFT NGP08.p
> hx.gbl!tk2msftngp13.phx.gbl
> microsoft.public.sqlserver.replication:57801
> I am trying to do a
> changed my "Subscription"
> didn't write it) require
> work. We create the
> don't want it to take up
> field back to
>
|||Hi Alex,
A BUG has been filed for the type of problem you are
having with replication.
Q306608 BUG: Distribution Agent May Fail with Incorrect
Syntax
http://support.microsoft.com/?id=306608
The only workaround was to rename the tables without the
special characters.
The problem is that when Logreader generates the command
it does not use delimiters. There is no way to configure
it work differently. You may consider creating the
article differently. On the publication properties,
click the ... button and uncheck the "replace
insert/update/delete..." checkboxes to force the use of
straight SQL to deliver the commands. This is also
available with scripting as options on sp_addarticle
where @.ins_cmd/upd_cmd/del_cmd='SQL'. Replication will
perform slower but it should work. Please give this a
try and let me know how it works for you.
Sincerely,
William Wang
Microsoft Online Partner Support
Get Secure! - <www.microsoft.com/security>
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Reply-To: "Smartikat" <Smartikat@.online.nospam>
>From: "Smartikat" <Smartikat@.online.nospam>
>References: <#JaVY4k3EHA.1396@.tk2msftngp13.phx.gbl>
<XI86lfo3EHA.3956@.cpmsftngxa10.phx.gbl>
>Subject: Re: Replication and Timestamp
>Date: Fri, 10 Dec 2004 16:13:22 -0800
>Lines: 116
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <OT5w6Yx3EHA.3236@.TK2MSFTNGP15.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: 63.171.237.106
>Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFT NGP08.p
hx.gbl!TK2MSFTNGP15.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.replication:57831
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>Well, it works to a point...
>Here is the problem I am faced with using this new
method (Queued Update
>setup).
>1) Snapshot works fine.
>2) When Transactional Replication happens, it will
error out with "Syntax
>Error/Access Denied Error".
>3) Error happens because the Replication Wizard
created a bunch of SPs on
>the subscriber. And all these SPs contain space " ",
"," and "$" in its
>name. Main reason is that my Table names where these
SPs based on contain "
>", "," and "$".
>4) Before switching to Queued Update method, I have a
Tab named "Command"
>under the Article setup screen. Now it is gone.
>If there is a way for me to change the SPs name and
tell the server to use
>them, I am set. Or if there is a way for me to disable
all these SPs, I
>will be set too...
>Please Help.
>Alex
>
>"William Wang[MSFT]" <v-rxwang@.online.microsoft.com>
wrote in message[vbcol=seagreen]
>news:XI86lfo3EHA.3956@.cpmsftngxa10.phx.gbl...
a[vbcol=seagreen]
timestamp[vbcol=seagreen]
are[vbcol=seagreen]
values[vbcol=seagreen]
http://msdn.microsoft.com/library/en...lsql/replplan_[vbcol=seagreen]
and[vbcol=seagreen]
V6.00.2900.2180[vbcol=seagreen]
cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFT NGP08.p[vbcol=seagreen]
field.[vbcol=seagreen]
this
>
>
|||William...
Thanks for the info... But unfortunately, I cannot change the Table Name at
the source. It is because the Source tables are from Navision (Another
Microsoft Product). And Navision uses Company Name + '$" to prefix the
table names.
Any chance for there is a hot fix for it?
Thanks,
Alex
"William Wang[MSFT]" <v-rxwang@.online.microsoft.com> wrote in message
news:nLxDm8P4EHA.1512@.cpmsftngxa10.phx.gbl...
> Hi Alex,
> A BUG has been filed for the type of problem you are
> having with replication.
>
> Q306608 BUG: Distribution Agent May Fail with Incorrect
> Syntax
> http://support.microsoft.com/?id=306608
>
> The only workaround was to rename the tables without the
> special characters.
> The problem is that when Logreader generates the command
> it does not use delimiters. There is no way to configure
> it work differently. You may consider creating the
> article differently. On the publication properties,
> click the ... button and uncheck the "replace
> insert/update/delete..." checkboxes to force the use of
> straight SQL to deliver the commands. This is also
> available with scripting as options on sp_addarticle
> where @.ins_cmd/upd_cmd/del_cmd='SQL'. Replication will
> perform slower but it should work. Please give this a
> try and let me know how it works for you.
>
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> Get Secure! - <www.microsoft.com/security>
> ================================================== ===
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --
> <XI86lfo3EHA.3956@.cpmsftngxa10.phx.gbl>
> cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFT NGP08.p
> hx.gbl!TK2MSFTNGP15.phx.gbl
> microsoft.public.sqlserver.replication:57831
> method (Queued Update
> error out with "Syntax
> created a bunch of SPs on
> "," and "$" in its
> SPs based on contain "
> Tab named "Command"
> tell the server to use
> all these SPs, I
> wrote in message
> a
> timestamp
> are
> values
> http://msdn.microsoft.com/library/en...lsql/replplan_
> and
> V6.00.2900.2180
> cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFT NGP08.p
> field.
> this
>
|||If there isn't a hotfix, as a work-around you might want to consider
transactionally replicating an indexed view of the table. If the subscriber
has to use the name as the tablename - ie with the hyphen, then a 'normal'
view on the subscriber could be used at that end.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Alex,
I'm sorry that I could not find other workarounds
regarding this issue, and there is not a hotfix for now.
However, I'm glad to tell you that this issue is planned
to be fixed in the next version of SQL Server. Currently
you may consider using Snapshot replication.
Feel free to let me know if I could explain anything
further.
Sincerely,
William Wang
Microsoft Online Partner Support
Get Secure! - <www.microsoft.com/security>
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Reply-To: "Smartikat" <Smartikat@.online.nospam>
>From: "Smartikat" <Smartikat@.online.nospam>
>References: <#JaVY4k3EHA.1396@.tk2msftngp13.phx.gbl>
<XI86lfo3EHA.3956@.cpmsftngxa10.phx.gbl>
<OT5w6Yx3EHA.3236@.TK2MSFTNGP15.phx.gbl>
<nLxDm8P4EHA.1512@.cpmsftngxa10.phx.gbl>
>Subject: Re: Replication and Timestamp
>Date: Mon, 13 Dec 2004 10:41:13 -0800
>Lines: 223
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <uoTwTNU4EHA.2592@.TK2MSFTNGP09.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: 63.171.237.106
>Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFT NGP08.p
hx.gbl!TK2MSFTNGP09.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.replication:57875
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>William...
>Thanks for the info... But unfortunately, I cannot
change the Table Name at
>the source. It is because the Source tables are from
Navision (Another
>Microsoft Product). And Navision uses Company Name +
'$" to prefix the
>table names.
>Any chance for there is a hot fix for it?
>Thanks,
>Alex
>
>"William Wang[MSFT]" <v-rxwang@.online.microsoft.com>
wrote in message[vbcol=seagreen]
>news:nLxDm8P4EHA.1512@.cpmsftngxa10.phx.gbl...
Incorrect[vbcol=seagreen]
the[vbcol=seagreen]
command[vbcol=seagreen]
configure[vbcol=seagreen]
of[vbcol=seagreen]
and[vbcol=seagreen]
V6.00.2900.2180[vbcol=seagreen]
cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFT NGP08.p[vbcol=seagreen]
disable[vbcol=seagreen]
contain[vbcol=seagreen]
replicated,[vbcol=seagreen]
timestamp[vbcol=seagreen]
http://msdn.microsoft.com/library/en...lsql/replplan_[vbcol=seagreen]
to[vbcol=seagreen]
================================================== ===[vbcol=seagreen]
via[vbcol=seagreen]
================================================== ===[vbcol=seagreen]
6.00.2900.2180[vbcol=seagreen]
cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFT NGP08.p
>
>