Hi,
Our replication topology (currently in design) will use transactional
replication to replicate from a OLTP server (publisher) to a Reporting server
(subscriber) in hope of near realtime results. Not all of the tables require
this though and the plan would be to use snapshot replication once a day for
these less updated tables. We’re then going to use log shipping to ship
tranaction log files out to remote servers with the databases originating
from the Reporting server.
My question is what does the distribution agent do to the existing
subscriber tables when it applies the new snapshot files? I guess I’m
looking for information as to whether it TRUNCATES the table, DELETES the
data, or DROPS the table prior to importing the BCP files from the new
snapshot.
SQL 2005 SP1 exclusively will be used in this environment.
Any information would be appreciated.
It depends on what option you set in the article property.
The property you should look for is in the article property under the
destination objects and Action if name is in use
The options, are keep data, drop table, truncate and delete filter data.
"dgcull" <dgcull@.discussions.microsoft.com> wrote in message
news:4A2EAA92-0353-4E83-AAC0-546BD5E0C5B1@.microsoft.com...
> Hi,
> Our replication topology (currently in design) will use transactional
> replication to replicate from a OLTP server (publisher) to a Reporting
> server
> (subscriber) in hope of near realtime results. Not all of the tables
> require
> this though and the plan would be to use snapshot replication once a day
> for
> these less updated tables. We're then going to use log shipping to ship
> tranaction log files out to remote servers with the databases originating
> from the Reporting server.
> My question is what does the distribution agent do to the existing
> subscriber tables when it applies the new snapshot files? I guess I'm
> looking for information as to whether it TRUNCATES the table, DELETES the
> data, or DROPS the table prior to importing the BCP files from the new
> snapshot.
> SQL 2005 SP1 exclusively will be used in this environment.
> Any information would be appreciated.
>
|||Gopal is right, but just to add - the default is to drop the existing table
on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Showing posts with label design. Show all posts
Showing posts with label design. Show all posts
Friday, February 24, 2012
Friday, February 17, 2012
Distributed Transaction Error
I have two SQL Servers (Standard), one running on Windows Server 2003 sp1,
and the other on Windows 2000 sp4. Both are SQL Server sp4.
When I design a view on the 2003 machine, using a linked server (Win2k
system), I can get results by running the view. However, when I try to save
it, I get "The operation could not be performed because the OLE DB provider
'SQLOLEDB was unable to begin a distributed transaction', and 'OLE/DB
provider returned message: New transaction cannot enlist in the specified
transaction coordinator' messages.
I followed the instructions in KB816701, but this did not solve the problem.
There was no option on the Win2k system to set up Application Server: Enable
network DTC access.
Both systems have the Distributed Transaction Coordinator service running.
It seems strange that I could run the unsaved view, but not save it from the
designer. However, creating it via Create View does work, i.e. creating the
view and saving it.
Any assistance would be greatly appreciated.
Thanks,
TomT
Hello,
The following articles are for your reference:
306212 INF: Troubleshooting Linked Server Error 7391
http://support.microsoft.com/?id=306212
827805 MSDTC fails to mutually authenticate when computers do not run in the
http://support.microsoft.com/?id=827805
I hope the information is helpful.
Sophie Guo
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.
|||Sophie,
Thanks for your response. However, I think there may be an issue between the
two platforms (Win2k and 2003). I had been planning to upgrade the Win2k
system to 2003, and did so today.
Once that process was completed, the issue I encountered went away. I.e., a
view created in the designer on one server using a linked server table, can
now be saved - no error messages.
Thanks,
TomT
"Sophie Guo [MSFT]" wrote:
> Hello,
> The following articles are for your reference:
> 306212 INF: Troubleshooting Linked Server Error 7391
> http://support.microsoft.com/?id=306212
> 827805 MSDTC fails to mutually authenticate when computers do not run in the
> http://support.microsoft.com/?id=827805
> I hope the information is helpful.
> Sophie Guo
> 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.
>
and the other on Windows 2000 sp4. Both are SQL Server sp4.
When I design a view on the 2003 machine, using a linked server (Win2k
system), I can get results by running the view. However, when I try to save
it, I get "The operation could not be performed because the OLE DB provider
'SQLOLEDB was unable to begin a distributed transaction', and 'OLE/DB
provider returned message: New transaction cannot enlist in the specified
transaction coordinator' messages.
I followed the instructions in KB816701, but this did not solve the problem.
There was no option on the Win2k system to set up Application Server: Enable
network DTC access.
Both systems have the Distributed Transaction Coordinator service running.
It seems strange that I could run the unsaved view, but not save it from the
designer. However, creating it via Create View does work, i.e. creating the
view and saving it.
Any assistance would be greatly appreciated.
Thanks,
TomT
Hello,
The following articles are for your reference:
306212 INF: Troubleshooting Linked Server Error 7391
http://support.microsoft.com/?id=306212
827805 MSDTC fails to mutually authenticate when computers do not run in the
http://support.microsoft.com/?id=827805
I hope the information is helpful.
Sophie Guo
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.
|||Sophie,
Thanks for your response. However, I think there may be an issue between the
two platforms (Win2k and 2003). I had been planning to upgrade the Win2k
system to 2003, and did so today.
Once that process was completed, the issue I encountered went away. I.e., a
view created in the designer on one server using a linked server table, can
now be saved - no error messages.
Thanks,
TomT
"Sophie Guo [MSFT]" wrote:
> Hello,
> The following articles are for your reference:
> 306212 INF: Troubleshooting Linked Server Error 7391
> http://support.microsoft.com/?id=306212
> 827805 MSDTC fails to mutually authenticate when computers do not run in the
> http://support.microsoft.com/?id=827805
> I hope the information is helpful.
> Sophie Guo
> 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.
>
Tuesday, February 14, 2012
Distributed Transaction Error
I have two SQL Servers (Standard), one running on Windows Server 2003 sp1,
and the other on Windows 2000 sp4. Both are SQL Server sp4.
When I design a view on the 2003 machine, using a linked server (Win2k
system), I can get results by running the view. However, when I try to save
it, I get "The operation could not be performed because the OLE DB provider
'SQLOLEDB was unable to begin a distributed transaction', and 'OLE/DB
provider returned message: New transaction cannot enlist in the specified
transaction coordinator' messages.
I followed the instructions in KB816701, but this did not solve the problem.
There was no option on the Win2k system to set up Application Server: Enable
network DTC access.
Both systems have the Distributed Transaction Coordinator service running.
It seems strange that I could run the unsaved view, but not save it from the
designer. However, creating it via Create View does work, i.e. creating the
view and saving it.
Any assistance would be greatly appreciated.
Thanks,
TomTHello,
The following articles are for your reference:
306212 INF: Troubleshooting Linked Server Error 7391
http://support.microsoft.com/?id=306212
827805 MSDTC fails to mutually authenticate when computers do not run in the
http://support.microsoft.com/?id=827805
I hope the information is helpful.
Sophie Guo
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.|||Sophie,
Thanks for your response. However, I think there may be an issue between the
two platforms (Win2k and 2003). I had been planning to upgrade the Win2k
system to 2003, and did so today.
Once that process was completed, the issue I encountered went away. I.e., a
view created in the designer on one server using a linked server table, can
now be saved - no error messages.
Thanks,
TomT
"Sophie Guo [MSFT]" wrote:
> Hello,
> The following articles are for your reference:
> 306212 INF: Troubleshooting Linked Server Error 7391
> http://support.microsoft.com/?id=306212
> 827805 MSDTC fails to mutually authenticate when computers do not run in t
he
> http://support.microsoft.com/?id=827805
> I hope the information is helpful.
> Sophie Guo
> 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
.
>
and the other on Windows 2000 sp4. Both are SQL Server sp4.
When I design a view on the 2003 machine, using a linked server (Win2k
system), I can get results by running the view. However, when I try to save
it, I get "The operation could not be performed because the OLE DB provider
'SQLOLEDB was unable to begin a distributed transaction', and 'OLE/DB
provider returned message: New transaction cannot enlist in the specified
transaction coordinator' messages.
I followed the instructions in KB816701, but this did not solve the problem.
There was no option on the Win2k system to set up Application Server: Enable
network DTC access.
Both systems have the Distributed Transaction Coordinator service running.
It seems strange that I could run the unsaved view, but not save it from the
designer. However, creating it via Create View does work, i.e. creating the
view and saving it.
Any assistance would be greatly appreciated.
Thanks,
TomTHello,
The following articles are for your reference:
306212 INF: Troubleshooting Linked Server Error 7391
http://support.microsoft.com/?id=306212
827805 MSDTC fails to mutually authenticate when computers do not run in the
http://support.microsoft.com/?id=827805
I hope the information is helpful.
Sophie Guo
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.|||Sophie,
Thanks for your response. However, I think there may be an issue between the
two platforms (Win2k and 2003). I had been planning to upgrade the Win2k
system to 2003, and did so today.
Once that process was completed, the issue I encountered went away. I.e., a
view created in the designer on one server using a linked server table, can
now be saved - no error messages.
Thanks,
TomT
"Sophie Guo [MSFT]" wrote:
> Hello,
> The following articles are for your reference:
> 306212 INF: Troubleshooting Linked Server Error 7391
> http://support.microsoft.com/?id=306212
> 827805 MSDTC fails to mutually authenticate when computers do not run in t
he
> http://support.microsoft.com/?id=827805
> I hope the information is helpful.
> Sophie Guo
> 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
.
>
Distributed Transaction Error
I have two SQL Servers (Standard), one running on Windows Server 2003 sp1,
and the other on Windows 2000 sp4. Both are SQL Server sp4.
When I design a view on the 2003 machine, using a linked server (Win2k
system), I can get results by running the view. However, when I try to save
it, I get "The operation could not be performed because the OLE DB provider
'SQLOLEDB was unable to begin a distributed transaction', and 'OLE/DB
provider returned message: New transaction cannot enlist in the specified
transaction coordinator' messages.
I followed the instructions in KB816701, but this did not solve the problem.
There was no option on the Win2k system to set up Application Server: Enable
network DTC access.
Both systems have the Distributed Transaction Coordinator service running.
It seems strange that I could run the unsaved view, but not save it from the
designer. However, creating it via Create View does work, i.e. creating the
view and saving it.
Any assistance would be greatly appreciated.
Thanks,
TomTHello,
The following articles are for your reference:
306212 INF: Troubleshooting Linked Server Error 7391
http://support.microsoft.com/?id=306212
827805 MSDTC fails to mutually authenticate when computers do not run in the
http://support.microsoft.com/?id=827805
I hope the information is helpful.
Sophie Guo
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.|||Sophie,
Thanks for your response. However, I think there may be an issue between the
two platforms (Win2k and 2003). I had been planning to upgrade the Win2k
system to 2003, and did so today.
Once that process was completed, the issue I encountered went away. I.e., a
view created in the designer on one server using a linked server table, can
now be saved - no error messages.
Thanks,
TomT
"Sophie Guo [MSFT]" wrote:
> Hello,
> The following articles are for your reference:
> 306212 INF: Troubleshooting Linked Server Error 7391
> http://support.microsoft.com/?id=306212
> 827805 MSDTC fails to mutually authenticate when computers do not run in the
> http://support.microsoft.com/?id=827805
> I hope the information is helpful.
> Sophie Guo
> 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.
>
and the other on Windows 2000 sp4. Both are SQL Server sp4.
When I design a view on the 2003 machine, using a linked server (Win2k
system), I can get results by running the view. However, when I try to save
it, I get "The operation could not be performed because the OLE DB provider
'SQLOLEDB was unable to begin a distributed transaction', and 'OLE/DB
provider returned message: New transaction cannot enlist in the specified
transaction coordinator' messages.
I followed the instructions in KB816701, but this did not solve the problem.
There was no option on the Win2k system to set up Application Server: Enable
network DTC access.
Both systems have the Distributed Transaction Coordinator service running.
It seems strange that I could run the unsaved view, but not save it from the
designer. However, creating it via Create View does work, i.e. creating the
view and saving it.
Any assistance would be greatly appreciated.
Thanks,
TomTHello,
The following articles are for your reference:
306212 INF: Troubleshooting Linked Server Error 7391
http://support.microsoft.com/?id=306212
827805 MSDTC fails to mutually authenticate when computers do not run in the
http://support.microsoft.com/?id=827805
I hope the information is helpful.
Sophie Guo
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.|||Sophie,
Thanks for your response. However, I think there may be an issue between the
two platforms (Win2k and 2003). I had been planning to upgrade the Win2k
system to 2003, and did so today.
Once that process was completed, the issue I encountered went away. I.e., a
view created in the designer on one server using a linked server table, can
now be saved - no error messages.
Thanks,
TomT
"Sophie Guo [MSFT]" wrote:
> Hello,
> The following articles are for your reference:
> 306212 INF: Troubleshooting Linked Server Error 7391
> http://support.microsoft.com/?id=306212
> 827805 MSDTC fails to mutually authenticate when computers do not run in the
> http://support.microsoft.com/?id=827805
> I hope the information is helpful.
> Sophie Guo
> 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.
>
Subscribe to:
Posts (Atom)