I am currently trying to setup Transformation on a Transactional Publication.
When When I finish setting up all the transformation for the package and
click finish to save the DTS package I get the Message "SQL Server Enterprise
manager could not complete this operation." this message comes up when it is
in the "Adding task 1 of 1" phase in the Creating the DTS package for
replication wizard.
Any help would be greatly appriciated.
Thanks in advance.
Jeff Stokes
Hi Jeff:
Right click on Server name and click Properties. In the properties window,
click on Connections tab and check if "Enforce Distributed Transactions"
Checkbox is checked. If so, uncheck it and then recreate your publication.
HTH
"Jeff Stokes" wrote:
> I am currently trying to setup Transformation on a Transactional Publication.
> When When I finish setting up all the transformation for the package and
> click finish to save the DTS package I get the Message "SQL Server Enterprise
> manager could not complete this operation." this message comes up when it is
> in the "Adding task 1 of 1" phase in the Creating the DTS package for
> replication wizard.
> Any help would be greatly appriciated.
> Thanks in advance.
> Jeff Stokes
|||Mark,
Thanks for your response. I checked the server properties and "Enforce
Distributed Transactions" is unchecked. Any otherthoughts?
Jeff
Jeff Stokes
"Mark" wrote:
[vbcol=seagreen]
> Hi Jeff:
> Right click on Server name and click Properties. In the properties window,
> click on Connections tab and check if "Enforce Distributed Transactions"
> Checkbox is checked. If so, uncheck it and then recreate your publication.
> HTH
>
> "Jeff Stokes" wrote:
sql
Showing posts with label setting. Show all posts
Showing posts with label setting. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Replication Subscription Error
Hi,
I tried setting a server up as the publisher, distributor and subscriber.
When I tried to create either a push or pull subscription I got the following
message:
Error 14053: Cannot load the DLL replincrementlsn extended procedure, or one
of the DLLs it references. Reason: 126(error not found).
The subscription could not be updated at this time.
The subscription could not be created.
SQL Server Enterprise Manager could not create a pull subscription to
publication XXXX
Error 14053: Cannot load the DLL replincrementlsn extended procedure, or one
of the DLLs it references. Reason: 126(error not found).
The subscription could not be updated at this time.
The subscription could not be created.
Has anyone seen this before?
Server running SQL 2000 on Windows 2000 Server
Thanks
Something is not installed correctly. Reapply your latest service pack.
Hilary Cotter
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
"DavidW" <DavidW@.discussions.microsoft.com> wrote in message
news:895B9996-9D97-4CAA-84F2-FF314F842E6F@.microsoft.com...
> Hi,
> I tried setting a server up as the publisher, distributor and subscriber.
> When I tried to create either a push or pull subscription I got the
> following
> message:
> Error 14053: Cannot load the DLL replincrementlsn extended procedure, or
> one
> of the DLLs it references. Reason: 126(error not found).
> The subscription could not be updated at this time.
> The subscription could not be created.
> SQL Server Enterprise Manager could not create a pull subscription to
> publication XXXX
> Error 14053: Cannot load the DLL replincrementlsn extended procedure, or
> one
> of the DLLs it references. Reason: 126(error not found).
> The subscription could not be updated at this time.
> The subscription could not be created.
> Has anyone seen this before?
> Server running SQL 2000 on Windows 2000 Server
> Thanks
>
|||Thank you Hilary, that has resolved the problem!
"Hilary Cotter" wrote:
> Something is not installed correctly. Reapply your latest service pack.
> --
> Hilary Cotter
> 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
> "DavidW" <DavidW@.discussions.microsoft.com> wrote in message
> news:895B9996-9D97-4CAA-84F2-FF314F842E6F@.microsoft.com...
>
>
I tried setting a server up as the publisher, distributor and subscriber.
When I tried to create either a push or pull subscription I got the following
message:
Error 14053: Cannot load the DLL replincrementlsn extended procedure, or one
of the DLLs it references. Reason: 126(error not found).
The subscription could not be updated at this time.
The subscription could not be created.
SQL Server Enterprise Manager could not create a pull subscription to
publication XXXX
Error 14053: Cannot load the DLL replincrementlsn extended procedure, or one
of the DLLs it references. Reason: 126(error not found).
The subscription could not be updated at this time.
The subscription could not be created.
Has anyone seen this before?
Server running SQL 2000 on Windows 2000 Server
Thanks
Something is not installed correctly. Reapply your latest service pack.
Hilary Cotter
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
"DavidW" <DavidW@.discussions.microsoft.com> wrote in message
news:895B9996-9D97-4CAA-84F2-FF314F842E6F@.microsoft.com...
> Hi,
> I tried setting a server up as the publisher, distributor and subscriber.
> When I tried to create either a push or pull subscription I got the
> following
> message:
> Error 14053: Cannot load the DLL replincrementlsn extended procedure, or
> one
> of the DLLs it references. Reason: 126(error not found).
> The subscription could not be updated at this time.
> The subscription could not be created.
> SQL Server Enterprise Manager could not create a pull subscription to
> publication XXXX
> Error 14053: Cannot load the DLL replincrementlsn extended procedure, or
> one
> of the DLLs it references. Reason: 126(error not found).
> The subscription could not be updated at this time.
> The subscription could not be created.
> Has anyone seen this before?
> Server running SQL 2000 on Windows 2000 Server
> Thanks
>
|||Thank you Hilary, that has resolved the problem!
"Hilary Cotter" wrote:
> Something is not installed correctly. Reapply your latest service pack.
> --
> Hilary Cotter
> 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
> "DavidW" <DavidW@.discussions.microsoft.com> wrote in message
> news:895B9996-9D97-4CAA-84F2-FF314F842E6F@.microsoft.com...
>
>
Replication Standard or Enterprise
In setting up replication between databases do both sql installations have to be enterprise or can it be done with both sql server standard edition.
thanks,Replication is included in Standard Edition.|||Is there a difference in using enterprise... or standard in regards to replication... I can not find much information stating the difference in choosing an edition...
here @. work ... the prod server they want to replicate has already been built using standard... but other... admins... say it would have to be enterprise...|||Is there a difference in using enterprise... or standard in regards to replication... I can not find much information stating the difference in choosing an edition...
here @. work ... the prod server they want to replicate has already been built using standard... but other... admins... say it would have to be enterprise...
Tell the other ...admins ... to take a hike:
SQL Server 2000 Standard Edition
This is a more affordable option for small-sized and medium-sized organizations that do not require the advanced scalability, availability, performance, or analysis features of SQL Server 2000 Enterprise Edition. Standard Edition can be used on symmetric multiprocessing systems with up to 4 CPUs and 2 GB of RAM.
Standard Edition includes the core functionality needed for non-mission-critical e-commerce, data warehousing, and line-of-business solutions. For instance, all of the XML features present in Enterprise Edition are also included in Standard Edition. And while a handful of advanced OLAP features are reserved for Enterprise Edition, all data mining features and the core OLAP functionality are included in SQL Server 2000 Analysis Services in Standard Edition. Similarly, components that other database vendors charge for as separate add-on products for their highest-end editions are included in Standard Edition:
Data Transformation Services (DTS)
Replication (snapshot, transactional, and merge)
Full-Text Search
English Query
Stored procedure development and debugging tools
SQL Profiling and performance analysis tools
Before choosing Standard Edition, make sure you review "Features Supported by the Editions of SQL Server 2000" in SQL Server Books Online.|||Kindly ask the "other admins" for proof of this. Perhaps, if you build the replication into the QA system (a good idea, anyway), you can prove to them it is possible.|||I appreciate the feedback... and will do a lot more research... any experienced suggestions or tips that a tutorial or book might not mention .... thanks again guys...|||Or maybe they would like to read the 2005 features for themselves:
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx|||I have a solution that not only replicates the data (standard or enterprise, 2000 or 2005) but also load balances read-only statements and provides effective means of resynchronization. I work for a company, PCTI (Parallel Computers Technology Inc), out of the Philadelphia area. We have a product, DBx, that does Parallel Synchronous Real Time Replication (up to 16 servers, local or remote) guaranteeing zero-loss of transactions. Anyone looking for more information should visit http://www.pcticorp.com or send me a private message via dbforums.com.
thanks,Replication is included in Standard Edition.|||Is there a difference in using enterprise... or standard in regards to replication... I can not find much information stating the difference in choosing an edition...
here @. work ... the prod server they want to replicate has already been built using standard... but other... admins... say it would have to be enterprise...|||Is there a difference in using enterprise... or standard in regards to replication... I can not find much information stating the difference in choosing an edition...
here @. work ... the prod server they want to replicate has already been built using standard... but other... admins... say it would have to be enterprise...
Tell the other ...admins ... to take a hike:
SQL Server 2000 Standard Edition
This is a more affordable option for small-sized and medium-sized organizations that do not require the advanced scalability, availability, performance, or analysis features of SQL Server 2000 Enterprise Edition. Standard Edition can be used on symmetric multiprocessing systems with up to 4 CPUs and 2 GB of RAM.
Standard Edition includes the core functionality needed for non-mission-critical e-commerce, data warehousing, and line-of-business solutions. For instance, all of the XML features present in Enterprise Edition are also included in Standard Edition. And while a handful of advanced OLAP features are reserved for Enterprise Edition, all data mining features and the core OLAP functionality are included in SQL Server 2000 Analysis Services in Standard Edition. Similarly, components that other database vendors charge for as separate add-on products for their highest-end editions are included in Standard Edition:
Data Transformation Services (DTS)
Replication (snapshot, transactional, and merge)
Full-Text Search
English Query
Stored procedure development and debugging tools
SQL Profiling and performance analysis tools
Before choosing Standard Edition, make sure you review "Features Supported by the Editions of SQL Server 2000" in SQL Server Books Online.|||Kindly ask the "other admins" for proof of this. Perhaps, if you build the replication into the QA system (a good idea, anyway), you can prove to them it is possible.|||I appreciate the feedback... and will do a lot more research... any experienced suggestions or tips that a tutorial or book might not mention .... thanks again guys...|||Or maybe they would like to read the 2005 features for themselves:
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx|||I have a solution that not only replicates the data (standard or enterprise, 2000 or 2005) but also load balances read-only statements and provides effective means of resynchronization. I work for a company, PCTI (Parallel Computers Technology Inc), out of the Philadelphia area. We have a product, DBx, that does Parallel Synchronous Real Time Replication (up to 16 servers, local or remote) guaranteeing zero-loss of transactions. Anyone looking for more information should visit http://www.pcticorp.com or send me a private message via dbforums.com.
Monday, March 26, 2012
replication re-try setting
Hi. We have replication setup from a sql server 200 database to a
sybase database on Solaris. Every now and then, the sql server
replication process will run into a deadlock with other processes on
the sybase side and be chosen as the rollback victim. The rollback
causes the entire replication to go down, and we have to manually
bring it back up. Does anyone know how to set the re-try parameter for
the replication so that upon being rolled back, it will automatically
re-connect to sybase to continue with the replication ? Thanks.
Steve,
if you are using merge replication, restarting the merge agent should fix
the issue. If you want this to happen automatically, you can force the merge
agent's steps to run in a continuous loop (step 3 -> step 2).
HTH,
Paul Ibison
|||loop back to step 1. I believe that in order for for detection of non logged
agent shutdown you must loopback to step one.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23$lpmAdZEHA.3092@.tk2msftngp13.phx.gbl...
> Steve,
> if you are using merge replication, restarting the merge agent should fix
> the issue. If you want this to happen automatically, you can force the
merge
> agent's steps to run in a continuous loop (step 3 -> step 2).
> HTH,
> Paul Ibison
>
|||Thanks Hilary,
step 3 -> step 1 is what I should have written.
Regards,
Paul
sybase database on Solaris. Every now and then, the sql server
replication process will run into a deadlock with other processes on
the sybase side and be chosen as the rollback victim. The rollback
causes the entire replication to go down, and we have to manually
bring it back up. Does anyone know how to set the re-try parameter for
the replication so that upon being rolled back, it will automatically
re-connect to sybase to continue with the replication ? Thanks.
Steve,
if you are using merge replication, restarting the merge agent should fix
the issue. If you want this to happen automatically, you can force the merge
agent's steps to run in a continuous loop (step 3 -> step 2).
HTH,
Paul Ibison
|||loop back to step 1. I believe that in order for for detection of non logged
agent shutdown you must loopback to step one.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23$lpmAdZEHA.3092@.tk2msftngp13.phx.gbl...
> Steve,
> if you are using merge replication, restarting the merge agent should fix
> the issue. If you want this to happen automatically, you can force the
merge
> agent's steps to run in a continuous loop (step 3 -> step 2).
> HTH,
> Paul Ibison
>
|||Thanks Hilary,
step 3 -> step 1 is what I should have written.
Regards,
Paul
Friday, March 23, 2012
Replication question
Dear Sir/Madam,
I am replicating 5 tables from Server A to Server B. On my first setting, I
included those 5 tables on a single publisher. After that, I recognize when a
table schema changes, I needed to drop and re-create the publishication,
means all 5 tables needed to re-synchronize again, is that true? Will it be
better to create 5 different publishication with each for one table only, so
during recreate publishication, only one table needed to synchronize, more
flexible? What the disadvantage for that?
Another question is, during recreate of one publishication, what is the
sequence of action? Is that stop log reader first, then recreate the one of
the publishication, generate snapshot, distribute the snapshot and finially
restart the log reader? Would this action sequence can make sure the
synchonize of those 5 tables?
Waiting to hearing from you soon. Many thanks!
Yours sincerely,
Henry
I would use sp_repladdcolumn or sp_repldropcolumn for this. SQL 20005 has
better options to replication schema changes seemelessly, otherwise if you
have a lot of changes to our schema you might want to think about log
shipping.
Splitting each article out into a seperate publication is a good idea when
sp_repladdcolumn and sp_repldropcolumn can't do the changes you are
looking for - for instance modifiying pk's. It is also a good idea when
your tables are large as if your snapshot fails on one table you will have
to replicate all tables again.
In general you shoudl group your tables into their own publications by DRI.
The way replication works is that the publication tables are locked and
then the log reader agent starts. Users can't make changes to tables until
the data is bcp'd out. Then when the data is bcp'd out the lock is
released and users changes are written to the tlog (as always) and the log
reader will read the transactions in the log and build commands to
replicate these transactions to the subscriber.
This way all the transactions are replicated and nothing is missed.
Hilary Cotter
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
I am replicating 5 tables from Server A to Server B. On my first setting, I
included those 5 tables on a single publisher. After that, I recognize when a
table schema changes, I needed to drop and re-create the publishication,
means all 5 tables needed to re-synchronize again, is that true? Will it be
better to create 5 different publishication with each for one table only, so
during recreate publishication, only one table needed to synchronize, more
flexible? What the disadvantage for that?
Another question is, during recreate of one publishication, what is the
sequence of action? Is that stop log reader first, then recreate the one of
the publishication, generate snapshot, distribute the snapshot and finially
restart the log reader? Would this action sequence can make sure the
synchonize of those 5 tables?
Waiting to hearing from you soon. Many thanks!
Yours sincerely,
Henry
I would use sp_repladdcolumn or sp_repldropcolumn for this. SQL 20005 has
better options to replication schema changes seemelessly, otherwise if you
have a lot of changes to our schema you might want to think about log
shipping.
Splitting each article out into a seperate publication is a good idea when
sp_repladdcolumn and sp_repldropcolumn can't do the changes you are
looking for - for instance modifiying pk's. It is also a good idea when
your tables are large as if your snapshot fails on one table you will have
to replicate all tables again.
In general you shoudl group your tables into their own publications by DRI.
The way replication works is that the publication tables are locked and
then the log reader agent starts. Users can't make changes to tables until
the data is bcp'd out. Then when the data is bcp'd out the lock is
released and users changes are written to the tlog (as always) and the log
reader will read the transactions in the log and build commands to
replicate these transactions to the subscriber.
This way all the transactions are replicated and nothing is missed.
Hilary Cotter
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
Wednesday, March 21, 2012
Replication problems
I am still having difficulties setting up replication, so here is what I am
doing and tell me if I am missing something
1. all 3 sites are not on a domain, they are on the same vpn
2. Configured idential account and password on all machines
3. set the SQL server and SQL Sqerver Agent to run under this account at
start up
4. added entries for each site to LMHOSTS of the others for lookup
5. Enabled SQL Server for TCP/IP
6. Shared replData folder on Publisher/Distributer
7. Setup a table on central computer as a publisher
8. created pull subscriptions on hosts
I am able to get one host to transfer data but not the other
Steve Hall
What error message are you getting? Can you ping the publisher from the
failed host?
Hilary Cotter
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
"Steve Hall" <SteveHall@.discussions.microsoft.com> wrote in message
news:11C2AE7D-8A1A-444B-B607-1A33B64D53B6@.microsoft.com...
>I am still having difficulties setting up replication, so here is what I am
> doing and tell me if I am missing something
> 1. all 3 sites are not on a domain, they are on the same vpn
> 2. Configured idential account and password on all machines
> 3. set the SQL server and SQL Sqerver Agent to run under this account at
> start up
> 4. added entries for each site to LMHOSTS of the others for lookup
> 5. Enabled SQL Server for TCP/IP
> 6. Shared replData folder on Publisher/Distributer
> 7. Setup a table on central computer as a publisher
> 8. created pull subscriptions on hosts
> I am able to get one host to transfer data but not the other
>
> Steve Hall
|||The message I am getting is
"The merge process detected a mismatch while evaluating the subscriber
partition"
I also noticed that the subscribers cannot add rows to the subscribed article.
Site1 says - "Pull" "Succeeded" "No Data needed to be merged. The Step
Suceeded"
Site 2 says - - "Pull" "Succeeded" "The merge process detected a mismatch
while evaluating the subscriber partition"
CentralSite says - Site1 - "Pull" "Active"
CentralSite says - Site2 - "Pull" "Deactivated"
I am trying to get each subscriber to add data to the article and the
central site to collate this in its table. I have the publication set to
dynamically filter on HOST_NAME()
Steve
"Hilary Cotter" wrote:
> What error message are you getting? Can you ping the publisher from the
> failed host?
> --
> Hilary Cotter
> 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
>
> "Steve Hall" <SteveHall@.discussions.microsoft.com> wrote in message
> news:11C2AE7D-8A1A-444B-B607-1A33B64D53B6@.microsoft.com...
>
>
sql
doing and tell me if I am missing something
1. all 3 sites are not on a domain, they are on the same vpn
2. Configured idential account and password on all machines
3. set the SQL server and SQL Sqerver Agent to run under this account at
start up
4. added entries for each site to LMHOSTS of the others for lookup
5. Enabled SQL Server for TCP/IP
6. Shared replData folder on Publisher/Distributer
7. Setup a table on central computer as a publisher
8. created pull subscriptions on hosts
I am able to get one host to transfer data but not the other
Steve Hall
What error message are you getting? Can you ping the publisher from the
failed host?
Hilary Cotter
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
"Steve Hall" <SteveHall@.discussions.microsoft.com> wrote in message
news:11C2AE7D-8A1A-444B-B607-1A33B64D53B6@.microsoft.com...
>I am still having difficulties setting up replication, so here is what I am
> doing and tell me if I am missing something
> 1. all 3 sites are not on a domain, they are on the same vpn
> 2. Configured idential account and password on all machines
> 3. set the SQL server and SQL Sqerver Agent to run under this account at
> start up
> 4. added entries for each site to LMHOSTS of the others for lookup
> 5. Enabled SQL Server for TCP/IP
> 6. Shared replData folder on Publisher/Distributer
> 7. Setup a table on central computer as a publisher
> 8. created pull subscriptions on hosts
> I am able to get one host to transfer data but not the other
>
> Steve Hall
|||The message I am getting is
"The merge process detected a mismatch while evaluating the subscriber
partition"
I also noticed that the subscribers cannot add rows to the subscribed article.
Site1 says - "Pull" "Succeeded" "No Data needed to be merged. The Step
Suceeded"
Site 2 says - - "Pull" "Succeeded" "The merge process detected a mismatch
while evaluating the subscriber partition"
CentralSite says - Site1 - "Pull" "Active"
CentralSite says - Site2 - "Pull" "Deactivated"
I am trying to get each subscriber to add data to the article and the
central site to collate this in its table. I have the publication set to
dynamically filter on HOST_NAME()
Steve
"Hilary Cotter" wrote:
> What error message are you getting? Can you ping the publisher from the
> failed host?
> --
> Hilary Cotter
> 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
>
> "Steve Hall" <SteveHall@.discussions.microsoft.com> wrote in message
> news:11C2AE7D-8A1A-444B-B607-1A33B64D53B6@.microsoft.com...
>
>
sql
Friday, March 9, 2012
Replication Of Tables Not Full Database
Hi,
first let me explain the situation. I hav a database which contains more
than 500 tables ... now we are setting up another server and the requirment
is like i want to replicate the data of 5 tables with the new server. i dont
want to replicate the entire database ... plz guide me for this situation ...
plz dont say NO coz i know that there will be some way to achive this ...
waiuting for ur response ...
thanks in advance
Deepson Thomas
Deepson,
this is possible using replication. Is the flow of data in one direction
only? If so, then transactional replication would almost certainly be the
route to go down. Have a look at the book in my signature below and BOL for
further details.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
first let me explain the situation. I hav a database which contains more
than 500 tables ... now we are setting up another server and the requirment
is like i want to replicate the data of 5 tables with the new server. i dont
want to replicate the entire database ... plz guide me for this situation ...
plz dont say NO coz i know that there will be some way to achive this ...
waiuting for ur response ...
thanks in advance
Deepson Thomas
Deepson,
this is possible using replication. Is the flow of data in one direction
only? If so, then transactional replication would almost certainly be the
route to go down. Have a look at the book in my signature below and BOL for
further details.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Replication of SP's mistake
When setting up replication of Stored Procedures & Views I made a mistake, I did not change the properties to tell replication to replicate the execution of the stored procedures.
How can I change this?
Originally, I just selected publish all & now when I look at Articles on the publication this is not checked, once the snapshot ran. I'm a bit confused.....
I want to the stored procedures to execute on my reporting/replicated db.
Thanx!
That's really not a very good option for me, is there any other way?
If I drop this publication, then I will need to reinitialize for >1000
tables only to get the stored procedures & views replicated? Not really
what I want to do, any other options available?
JUDE
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:023b01c4abcd$4d80f980$a301280a@.phx.gbl...
> The easiest way is to script out the publication then
> drop it.
> Change the scripts so
> @.type = N'proc exec'
> @.status = 0
> Then recreate the publication and initialize.
> HTH,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||JLS,
I replicate my views and stored procedures in a separate publication to
avoid snapshotting the tables when my views change - perhaps this would be
an easier option for you?
Alternatively you could drop the subscription to each article and remove the
article eg:
sp_dropsubscription @.publication = 'northwindxxx'
, @.article = 'region'
, @.subscriber = 'pll-lt-16'
sp_droparticle @.publication = 'northwindxxx'
, @.article = 'region'
The readd the article, run the snapshot agent and synchronize.
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||A separate publication sounds good, these views & SP's seem to be ever
changing at times.
I think I will script the publication, pull out the views & sp's, drop
subscriptions & articles, then setup a separate publication for the views &
sp's.
Another question...
What's the benefit of replicating the view execution (Actually, is there
such a thing?)
When I replicate views & SP's, can it be both schema & execution being
replicated to the reporting server, or an either or situation?
Thanx!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ORXYiI%23qEHA.896@.TK2MSFTNGP12.phx.gbl...
> JLS,
> I replicate my views and stored procedures in a separate publication to
> avoid snapshotting the tables when my views change - perhaps this would be
> an easier option for you?
> Alternatively you could drop the subscription to each article and remove
the
> article eg:
> sp_dropsubscription @.publication = 'northwindxxx'
> , @.article = 'region'
> , @.subscriber = 'pll-lt-16'
> sp_droparticle @.publication = 'northwindxxx'
> , @.article = 'region'
> The readd the article, run the snapshot agent and synchronize.
> Rgds,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||JLS,
for views you don't get the same option.
For each stored proc article you get the options listed as the @.type options
in sp_addarticle. So, the 'structure' or 'structure and execution' are the
main options. If you select the latter, the schema will go down as well as
the execution, and it is not possible to select just the latter.
BTW,
I also
(a) apply a post-snapshot script to apply permissions.
(b) have a separate publication for udfs to force them to go first
(c) sometimes resort to sp_addscriptexec when the dependencies get mixed up.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
How can I change this?
Originally, I just selected publish all & now when I look at Articles on the publication this is not checked, once the snapshot ran. I'm a bit confused.....
I want to the stored procedures to execute on my reporting/replicated db.
Thanx!
That's really not a very good option for me, is there any other way?
If I drop this publication, then I will need to reinitialize for >1000
tables only to get the stored procedures & views replicated? Not really
what I want to do, any other options available?
JUDE
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:023b01c4abcd$4d80f980$a301280a@.phx.gbl...
> The easiest way is to script out the publication then
> drop it.
> Change the scripts so
> @.type = N'proc exec'
> @.status = 0
> Then recreate the publication and initialize.
> HTH,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||JLS,
I replicate my views and stored procedures in a separate publication to
avoid snapshotting the tables when my views change - perhaps this would be
an easier option for you?
Alternatively you could drop the subscription to each article and remove the
article eg:
sp_dropsubscription @.publication = 'northwindxxx'
, @.article = 'region'
, @.subscriber = 'pll-lt-16'
sp_droparticle @.publication = 'northwindxxx'
, @.article = 'region'
The readd the article, run the snapshot agent and synchronize.
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||A separate publication sounds good, these views & SP's seem to be ever
changing at times.
I think I will script the publication, pull out the views & sp's, drop
subscriptions & articles, then setup a separate publication for the views &
sp's.
Another question...
What's the benefit of replicating the view execution (Actually, is there
such a thing?)
When I replicate views & SP's, can it be both schema & execution being
replicated to the reporting server, or an either or situation?
Thanx!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ORXYiI%23qEHA.896@.TK2MSFTNGP12.phx.gbl...
> JLS,
> I replicate my views and stored procedures in a separate publication to
> avoid snapshotting the tables when my views change - perhaps this would be
> an easier option for you?
> Alternatively you could drop the subscription to each article and remove
the
> article eg:
> sp_dropsubscription @.publication = 'northwindxxx'
> , @.article = 'region'
> , @.subscriber = 'pll-lt-16'
> sp_droparticle @.publication = 'northwindxxx'
> , @.article = 'region'
> The readd the article, run the snapshot agent and synchronize.
> Rgds,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||JLS,
for views you don't get the same option.
For each stored proc article you get the options listed as the @.type options
in sp_addarticle. So, the 'structure' or 'structure and execution' are the
main options. If you select the latter, the schema will go down as well as
the execution, and it is not possible to select just the latter.
BTW,
I also
(a) apply a post-snapshot script to apply permissions.
(b) have a separate publication for udfs to force them to go first
(c) sometimes resort to sp_addscriptexec when the dependencies get mixed up.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Saturday, February 25, 2012
Replication issues with MSDE and SQL Server Enterprise
Hello--
I'm am having difficulty setting up what I thought would
be something relatively simple to accomplish. I have two
client computers running XP, each using a local copy of
MSDE2000 to store data. I also have a server running
Windows 2000 AS and SQL Server Enterprise.
What I'm trying to do is have the data that's logged on
the client computers replicate to the server. I have run
into a variety of issues, most seem to be related to
security problems. I have all SQL Server instances set up
to use TCP/IP and named pipes, and all set for mixed mode
authentication. I am not using the server as a domain
controller, but I have created matching user accouts with
administrator priviliges on each of the three computers in
question.
When I create a publication on one of the two client
computers, sometimes a snaphot is generated immediately,
sometimes it isn't. When I try to create a pull
subscription on the server, I typically get an error about
being unable to connect to the distributor (I presume
security-releated problems).
I feel as if I'm missing something obvious, because this
seem like a very straightforward setup. What am I doing
wrong?
Thanks in advance for any help.
Chris Langston
exactly how are you connecting to your Publisher? ActiveX, SQM DMO?
If you are using ActiveX controls you have to define the network method you
are using to connect to your publisher/distributor by using
PublisherAddress/PublisherNetwork and DistributorAddress/DistributorNetwork.
If you are pulling a subscription using UNC you will have to ensure that the
SQL Server agent account on the subscriber has rights to the network share
on the publisher. The easiest way to do this is to make sure that the SQL
Server agent account the subscribers run under is the same one that it runs
under on the Publisher and this account is in the local admin group. If you
can't make them the same domain account, give them the same name and
password.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Chris Langston" <clangst@.vectorcorporation.com> wrote in message
news:512901c4807f$6260a7c0$a401280a@.phx.gbl...
> Hello--
> I'm am having difficulty setting up what I thought would
> be something relatively simple to accomplish. I have two
> client computers running XP, each using a local copy of
> MSDE2000 to store data. I also have a server running
> Windows 2000 AS and SQL Server Enterprise.
> What I'm trying to do is have the data that's logged on
> the client computers replicate to the server. I have run
> into a variety of issues, most seem to be related to
> security problems. I have all SQL Server instances set up
> to use TCP/IP and named pipes, and all set for mixed mode
> authentication. I am not using the server as a domain
> controller, but I have created matching user accouts with
> administrator priviliges on each of the three computers in
> question.
> When I create a publication on one of the two client
> computers, sometimes a snaphot is generated immediately,
> sometimes it isn't. When I try to create a pull
> subscription on the server, I typically get an error about
> being unable to connect to the distributor (I presume
> security-releated problems).
> I feel as if I'm missing something obvious, because this
> seem like a very straightforward setup. What am I doing
> wrong?
> Thanks in advance for any help.
> Chris Langston
>
>
I'm am having difficulty setting up what I thought would
be something relatively simple to accomplish. I have two
client computers running XP, each using a local copy of
MSDE2000 to store data. I also have a server running
Windows 2000 AS and SQL Server Enterprise.
What I'm trying to do is have the data that's logged on
the client computers replicate to the server. I have run
into a variety of issues, most seem to be related to
security problems. I have all SQL Server instances set up
to use TCP/IP and named pipes, and all set for mixed mode
authentication. I am not using the server as a domain
controller, but I have created matching user accouts with
administrator priviliges on each of the three computers in
question.
When I create a publication on one of the two client
computers, sometimes a snaphot is generated immediately,
sometimes it isn't. When I try to create a pull
subscription on the server, I typically get an error about
being unable to connect to the distributor (I presume
security-releated problems).
I feel as if I'm missing something obvious, because this
seem like a very straightforward setup. What am I doing
wrong?
Thanks in advance for any help.
Chris Langston
exactly how are you connecting to your Publisher? ActiveX, SQM DMO?
If you are using ActiveX controls you have to define the network method you
are using to connect to your publisher/distributor by using
PublisherAddress/PublisherNetwork and DistributorAddress/DistributorNetwork.
If you are pulling a subscription using UNC you will have to ensure that the
SQL Server agent account on the subscriber has rights to the network share
on the publisher. The easiest way to do this is to make sure that the SQL
Server agent account the subscribers run under is the same one that it runs
under on the Publisher and this account is in the local admin group. If you
can't make them the same domain account, give them the same name and
password.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Chris Langston" <clangst@.vectorcorporation.com> wrote in message
news:512901c4807f$6260a7c0$a401280a@.phx.gbl...
> Hello--
> I'm am having difficulty setting up what I thought would
> be something relatively simple to accomplish. I have two
> client computers running XP, each using a local copy of
> MSDE2000 to store data. I also have a server running
> Windows 2000 AS and SQL Server Enterprise.
> What I'm trying to do is have the data that's logged on
> the client computers replicate to the server. I have run
> into a variety of issues, most seem to be related to
> security problems. I have all SQL Server instances set up
> to use TCP/IP and named pipes, and all set for mixed mode
> authentication. I am not using the server as a domain
> controller, but I have created matching user accouts with
> administrator priviliges on each of the three computers in
> question.
> When I create a publication on one of the two client
> computers, sometimes a snaphot is generated immediately,
> sometimes it isn't. When I try to create a pull
> subscription on the server, I typically get an error about
> being unable to connect to the distributor (I presume
> security-releated problems).
> I feel as if I'm missing something obvious, because this
> seem like a very straightforward setup. What am I doing
> wrong?
> Thanks in advance for any help.
> Chris Langston
>
>
Tuesday, February 21, 2012
Replication Identity Ranges
Hello,
We have set up Merge replication, using sql 2000. We have one publisher and
four subscribers. When setting up the articles on the publisher, we manually
set an identity range for one of the tables. The range size at the publisher
and the subscriber is set to 5000, and the assign new range is set to 95%.
When looking at the design of the table and ckecking the replication
constraint, on the first subscriber, the range is 103000 - 108000. The next
subscriber has a range of 105000 - 111000. All the subscribers seem to have
a range that overlap with each other. The table has an identity column and
it is set to Not for Replication.
It is my understanding that each subscriber should have a range that do not
overlap. I have checked other tables on the subscribers and they each have
ranges that do not overlap.
I hope I have given enough information, and I hope someone could shed some
light on this.
Thanks in advance
Identity ranges should never overlap.
Did you use the same snapshot for each subscriber?
|||Hello,
we used the same snapshot for each subscriber. This problem has now been
corrected. We manually modified the MSrepl_identity_range table on each
subscriber, then forced replication. This fixed the problem.
"Jim Breffni" wrote:
> Identity ranges should never overlap.
> Did you use the same snapshot for each subscriber?
>
We have set up Merge replication, using sql 2000. We have one publisher and
four subscribers. When setting up the articles on the publisher, we manually
set an identity range for one of the tables. The range size at the publisher
and the subscriber is set to 5000, and the assign new range is set to 95%.
When looking at the design of the table and ckecking the replication
constraint, on the first subscriber, the range is 103000 - 108000. The next
subscriber has a range of 105000 - 111000. All the subscribers seem to have
a range that overlap with each other. The table has an identity column and
it is set to Not for Replication.
It is my understanding that each subscriber should have a range that do not
overlap. I have checked other tables on the subscribers and they each have
ranges that do not overlap.
I hope I have given enough information, and I hope someone could shed some
light on this.
Thanks in advance
Identity ranges should never overlap.
Did you use the same snapshot for each subscriber?
|||Hello,
we used the same snapshot for each subscriber. This problem has now been
corrected. We manually modified the MSrepl_identity_range table on each
subscriber, then forced replication. This fixed the problem.
"Jim Breffni" wrote:
> Identity ranges should never overlap.
> Did you use the same snapshot for each subscriber?
>
Subscribe to:
Posts (Atom)