Replication Isues After Upgrading Subscriber To 2005

May 16, 2006

Hi There

A while ago i migrated a 2000 database to a new instance of sql server 2005 and succesffully setup replication from a 2000 publisher.

However to experiment i took an existing 2000 instance with a subscriber databases and upgraded the default instance to 2005. (Upgraded from 2000 Enterprise to 2005 Enterprise)

After the upgrade the setup logs were all successful, i also run upgrade advisor before and made sure there were no issues.

To see what happens i did not drop the subscription before upgrade (is this an absolute neccessity ?)

All i did was stop the distribution agent before upgrade, to see what would happen, i did not expect it to work.

But since the upgrade i cannot delete the subscription on the subscriber after upgrade, i also cannot create a new subscriptions on the upgraded subscriber.

I get the following error:

Sql Server error 207: column job_step_uid not found.

I found the following link :

http://support.microsoft.com/kb/914781/en-us

but it only refers to desktop editions and express, they refer to the msdb database not upgrading, but the upgrades logs are clear i see no evidence of an issue when upgrading msdb.

What exactly is going on? How can i delete the old subsciption and start creating new ones in the upgraded subscriber ?

I have read the Upgrading replicated DB's topic in BOL, no mention of having to drop subscription before upgrade although i suspect so, all it mentions scripting out the replication , changing the scripts , dropping the subscription (no mention of before or after upgrade), and re-creating them. (all of these i am unable to do due to the above error.)

Please assist, thanx

View 6 Replies


ADVERTISEMENT

Replication - DB2 As Publisher - SQL 2005 As Subscriber

Sep 14, 2007



I have been looking for a way to replicate data from a DB2 system running on an AIX machine. I found some information related to doing this with SQL2000, but not with 2005. Can this be done - preferably without writing our own provider.

Thanks in advance

View 5 Replies View Related

2005 Replication With SCHEMABINDING Subscriber Views

Dec 6, 2007

Hi Folks Is there an easy way around this ? One Way Transactional Rep Subscriber needs SCHEMABINDING on the majority of their Views (require View Indexes) which read from Replicated Tables. Main table has 4 Million Rows ReInitialize Subscription Errors with Cannot Drop Table because it is being referenced By Object ..... [schemaBound View] GW

View 6 Replies View Related

SQL Replication: 2005 (Publisher) And A 2000 (Subscriber)???

Sep 25, 2006

Hi,

I was wondering if it is possible to setup replication between a 2005 SQL Publisher and a 2000 SQL Subscriber? Is there any special setup steps I need?

Thanks so much,

David

View 3 Replies View Related

Transactional Replication From SQL 2000 Sp4 Publisher/distributor To SQL 2005 Sp2 Subscriber

Jun 26, 2007

Hello,



Are there any requirements that dictate the SQL Server version for the distribution agent for a SQL 2000 publisher with a transactional push subscription to a SQL 2005 subscriber?



Thanks in advance,



John

View 3 Replies View Related

Merge Replication: Problems Upgrading SQL-2000 To 2005

Dec 25, 2006

Hello!

We have a module in our business-application, that automatically installs merge replication of the business-application's database, both at publisher and subscribers. It's intended for Sql-2000. Now we need it to be applicable also for Sql-2005, so the module requires some changes, because, as we noticed, sql-2005's replication technology differs from Sql-2000's one.

A few questions to experts, familiar with Merge Replication in SQL-2005:

1. Is it possible to create hybrid replication, with publisher running at SQL-2000 (MSDE) and subscribers running at SQL-2005 Express? Merge publication is not supported in SQL 2005 Express, but some users may require option to use the application at free-of-charge database platform.

2. How deep are changes in merge replication implementation at system level? Is it just modified a bit since SQL-2000, or changed entirely? This knowledge is needed, because the module uses some low-level features (executing system sp's, querying replication-specified tables, etc.). For example, when we tried to create subscription of existing publication in SQL 2005 using the module as is, we found out that sp_addmergepullsubscription_agent doesn't use @encrypted_password parameter anymore, and subscription creation process failed.

3. If anybody has experience using merge replication creation/deletion/detection scripts, generated by SQL 2000, in SQL 2005! Please, tell - what more problems may happen?

Thanks!

View 3 Replies View Related

Merge Replication Subscriber

Sep 6, 2001

SQL Server 7.0 SP3

I am using the "Pull Subscription Wizard" to set up merge replication. I go through all the steps of the wizard and then I hit "Finish" at the end. When it gets to the third step (Creating the Subscription), I get the old:

__________________________________________________ _________
mmc.exe has generated errors and will by closed by Windows.
You will need to restart the program.

An error log is being created.
__________________________________________________ _________

This happens whether I try to do this from my PC or from the actual server I am creating the subscription on.

Has anyone else had this issue? Is there a hotfix I need?

Any help is appreciated.

Thanks,
Craig

View 1 Replies View Related

Replication: Cannot Enumerate Changes At Subscriber

Jul 2, 2003

Hi all!

I hope anyone here can help me with this tough problem:

I have a replication enviroment with several subscribers.
Publisher:MSSQL 2000, SP3
Subscribers: MSSQL200, SP3 and MSDE2000, SP3

Since a few weeks, one of the subscribers fails when
synchronizing with the message
"This process could not enumerate changes at the subscriber"

The erroroutput with verboselevel 3 doesn't help me much, I
append it to the end of this posting. (unfortunately german)

I heard that this error can be caused by a "blank" in an image
column or different Service Pack Versions - definitely not
possible in this case!
The only strange detail is that the SQL Server Versionnumber
of this MSDE shown via EM is not the same as the number shown
in controlpanel->software. By the way, is it ok that here are
two lines for the MSDE?
SP3 is definitely installed on all machines!

Any help would be greatly appreciated - i'm already losing hope...
Best Regards, Gert

Microsoft SQL Server-Merge-Agent 7.00.623
Copyright (c) 1998 Microsoft Corporation

Percent Complete: 0
Verbindung mit Abonnent 'MyServerAT074'
Connecting to Abonnent 'MyServerAT074.MyDB'
MyServerAT074.MyDB: {call sp_MSgetversion }
Percent Complete: 0
Verbindung mit Verteiler 'MyServer1s'
Connecting to Verteiler 'MyServer1s.'
MyServer1s.: {call sp_MSgetversion }
MyServer1s.: {call sp_helpdistpublisher (N'MyServer1s') }
MyServer1s.distribution: select datasource, srvid from master..sysservers
where srvname = N'MyServer1s'
MyServer1s.distribution: select datasource, srvid from master..sysservers
where srvname = N'MyServerAT074'
MyServer1s.distribution: {call sp_MShelp_merge_agentid (0, N'MyDB',
N'MyDB', 9, N'MyDB')}
Percent Complete: 0
Initialisiert
MyServer1s.distribution: {call sp_MShelp_profile (19, 4, N'')}
Percent Complete: 1
Verbindung mit Verleger 'MyServer1s'
Connecting to Verleger 'MyServer1s.MyDB'
Connecting to Verleger 'MyServer1s.MyDB'
MyServer1s.MyDB: {call sp_MSgetversion }
Percent Complete: 3
Ruft Publikationsinformationen ab
Percent Complete: 4
Ruft Abonnementinformationen ab
Connecting to Abonnent 'MyServerAT074.MyDB'
Disconnecting from Abonnent 'MyServerAT074'
Percent Complete: 4
Uploadet Datenänderungen zum Verleger
Percent Complete: 5
Ruft die Liste der Löschungen von MyServer1s.MyDB ab
Percent Complete: 5
Verarbeitet Artikel 'First'

......
SNIP
....
Percent Complete: 5
Verarbeitet Artikel 'LastTable'
Percent Complete: 0
Der Prozess konnte die Änderungen auf dem Abonnenten nicht aufzählen.
Percent Complete: 0
Category:SQLSERVER
Source: MyServerAT074
Number: 0
Message: Attributverletzung eingeschränkter Datentypen
Attributverletzung eingeschränkter Datentypen
Disconnecting from Abonnent 'MyServerAT074'
Disconnecting from Verleger 'MyServer1s'
Disconnecting from Verleger 'MyServer1s'
Disconnecting from Verteiler 'MyServer1s'

View 1 Replies View Related

Replication Insert From Subscriber

Jul 8, 2004

:confused:

I want to call a stored procedure to do the insert into my published arcticle/table. Is this possible? I know you can call a stored procedure to insert into the subscriber arctiles.

The problem is the published database has a generated primary key (max +1 )that is calculated via a stored procedure during all inserts. I would bring the stored procedure over to the subscriber but that would not help since the values in the target table will not be 'current' because the subscriber database is only updated nightly.

Thoughts?

View 4 Replies View Related

Replication; What Is: SqlCeReplication.Subscriber?

Feb 24, 2006

I am tring to use SQL Server Mobile 2005 to subscribe to a SQL Server 2005 publication from a pocketPC. In my code I have a SqlCeReplication object. One of the properties that must be set is: Subscriber, but I have no idea what that should be set to. When I was setting up the publication I don't remember specifying anything like that, and the class description of this property doesn't help: "Specifies the name of the Subscriber". Any help will be appreciated.

View 3 Replies View Related

Upgrading To Replication

Mar 5, 2007

I created an application with the MSDE (SQL server 2000 edition).

The application handles the contents of an automatic wharehouse system, on one side it interfaces to the wharehouse hardware (industrial controllers) and on the other side it interfaces to an As400 system using the IBM ODBC driver. Database and application run on WXP proffesional.

As the wharehouse works round the clock, we would like to add a standby system. We do not need a failover system, it is enougth that the reserve system has everything installed and set up and that the database is up to date so the the wharehouse may be restarted using the reserve system.

So we require live database replication.

In order to experiment I took a copy of the database and put it on the desktop and portable in my lab, which are both running W2KP. I could not get the database published, the wizard round up with an error saying that the computer cannot be set up as a distribution server (error 14114).

I realised that I may need a server edition of windows to do this, is this correct?

What would be my best (cheapest) option to upgrade, given that my requirements are very simple and straightforward **except** for the replication. Note that ideally the replication would include everything, so that if e.g. a user changes password, a view of stored procedure gets modified etc, it will all get replicated.

I seem to remember that SQL server 2000 required the enterprise edition for this level of replication, and had to be run on a windows server edition. As far as I can see the 2005 edition offers a limited replication on the standard edition. Would it be better to upgrade to this?

Would the 2005 server edition be able to do the necessary replication on W2KP/XP?

Thanks in advance for any advice!

View 3 Replies View Related

Replication - Sync Subscriber Using Backup

Feb 9, 2004

Hi Guys,

I have tried backing up the replication from Primary Server (Publisher) A and copying over to Subscriber B. When I sync. I am missing the data. I don't know what step I am missing or it is bug on SQL 2K SP3.

I don't know what i am doing wrong.

Can anybody give me a step by step detail how to backup the publisher database and copy to a subscriber and sync by saying the schema and data already available. As we have WAN problem all the times, and when I try the full snapshot of 20 gig it was taking more than 24 hrs and also if the connection fails my snapshot also fails

Please help.


Thanks in Advance,
-Anu

View 7 Replies View Related

Replication - Sync Subscriber Using Backup

Feb 9, 2004

Hi Guys,

I tried all the ways ,no good result.
I am really running out of time.

Can somebody please help.

-Anu

View 4 Replies View Related

Replication Problem - Subscriber Not Dropped

Apr 30, 2001

Hi there,

I installed 2 SQL2000 servers at a client site, the first acting as a publisher and distributor for transactional replication. The second is a push subscription to the first. The client wanted to change the database name, and some other stuff, so I removed the replication and set it up again for the new database name.

All's fine at the publisher / distributor but the subscriber is showing as still having active push subscription to the old (and new) databases

Any idea how to fix this

View 2 Replies View Related

Push Subscriber In Merge Replication

Jul 26, 2004

I am using merge replication with a push subscription type. I am wondering if the updates of the tables on the subscriber side are push to the publisher from the subscriber or pulled from the subscriber by the publisher when the syncronisation takes place. this makes a big diferrence for me and i can't find the answer to this question anywhere...

if anyone could answer it would be really appreciated

Thanks

View 3 Replies View Related

Bi Directional Transactional Replication And Subscriber Name

Oct 19, 2004

Hi,
I am trying to setup bi-directional transactional replication between 2 SQL Servers,
I add the subscription on both the servers using the code below.

EXEC sp_addsubscription @publication = N'test',
@article = N'all', @subscriber = 'AnyServer',
@destination_db = N'test', @sync_type = N'none',
@status = N'active', @update_mode = N'read only',
@loopback_detection = 'true'
GO

I have defined 'AnyServer' on both the servers using cliconfg
(Server1's AnyServer pointed towards Server2 and Server2's AnyServer pointed towards Server1, I need to do that because there is a restriction to run the same code on both the servers),

After inserting a record on server1 in the 'test' database, the changes successfully transfers to the server2, then server2 sends it back to server1 and server1 generates the error of

"Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key in object 'Table1'."

It seems as if loop detection is failing if I keep the same subscriber name on both sides.

It runs fine when I change the subscriber name in the subscription (@subscriber = 'Server2' for server1 and @subscriber = 'Server1' for server2).

Can anybody explains this behavior to me?

View 2 Replies View Related

Replication - Can Subscriber Objects Be Pre-existing?

Aug 15, 2006

My manager does not want to go through the process of moving 40 GB of data to the subscriber database over the network as would occur during the initialization phase of replication (exact method of replication has yet to be determined). The subscriber db is for Web Access and will be read-only. He wants to back up a db and use tapes to restore the db on the subscriber db server, and then activate replication. I have told him that you have to allow SQL Server to create the objects on the subscriber side and you have to allow SQL Server to control the data flow, that manually creating the objects on the subscriber side will not work. He says that I am incorrect.

Who is right? I haven't been able to find an answer from the Microsoft site and news groups.

Thanks,

View 1 Replies View Related

Mirrored Subscriber In Transactional Replication ?

Apr 30, 2008

Hi,

I am trying to setup mirrored subcriber in Transactional Replication in SQL 2005. I am not able to do so as it don't work after failover.However I am able to setup mirrored publisher with distribution property "PublisherFailoverPartner" for snapshot and log reader agent profiles and it works even after failover.

However I need to know, do we have something called "Subscriberfailoverproperty" or any other method to set up mirrored subscriber in Transactional Replication in SQL 2005.


Thanks

Kiran Patil

View 6 Replies View Related

Replication Multiple Subscriber Database

Nov 28, 2007

I have a central server (CS1) with database CDB and a branch server (BR1)
Inside the branch server, there are two database DB1 and DB2.
CDB contains data for DB1 and DB2

I made a subscription in the brancserver named BR1:DB1(this will replicate the data from CDB to DB1)
I also made a subscription for DB2 named BR1:DB2(this will replicate the data from CDB to DB2)

Unfortunately, upon starting the synchronization for BR1:DB1, the data replicated into DB1 also contains data for DB2. same happens with the BR1:DB2..

How do I filter the data that will be replicate per database..Example:only data for DB1 coming from CDB should be replicated into DB1 if that specific subscription(BR1:DB1) is synchronized.

Thank you

View 1 Replies View Related

Merge Replication With Read-only Subscriber

Nov 17, 2007

Using SQL CE 3.1 merge replication. Working fine, but want to make subscription read only. In other words, master/slave pattern.
How is this accomplished?

View 3 Replies View Related

How To Detect At The Subscriber That A Replication Is Being (re)initialized.

Feb 21, 2007

Is there a method/query to discover that the subscriber's databasereplication is being reinitialized?

The problem is that the applications which is connects to this database crashes when the replication is (re)initializing.

Thanx, Ralf

View 1 Replies View Related

Merge Replication Subscriber Status

Sep 22, 2007



Hi.
I have setup an SQL Server 2005 Merge Replication.
Now I need to display the status of the replication programatically from the subscriber side, I have checked the documentation which mentions MergeSubscription & MergeSubscriberMonitor but I couldn't know how to use them!!
Any Help ?
Thanks

View 3 Replies View Related

Subscriber To A SQL Server 2000 Replication

Oct 31, 2006

Hi ..

I know that SQL Server Express 2005 can subscribet to SQL Server 2000 publications. However, it seems that SQL server 2000 cannot perform a push to SQL Express.

Can anyone refer me to any online help or documentation on how a proper replication can be done between SQL 2005 Exress and SQL 2000??

Thanks

View 1 Replies View Related

Deletes On Subscriber In Merge Replication

Oct 20, 2006

Using Merge replication between SQL Server 2000 and SQL Server CE,
is there any way that row deletes could occur on the subscriber without a reinitialize
or explicit delete of row on publisher.

More specifically, if there is a row filter that returns a days worth of data with each
days pull, for example, and the filter looked like
select <columns> from Table where UpdateDate < GETDATE() and UpdateDate >= DATEADD(d,1,GETDATE())
would there be some implicit delete at subscriber each day because data sent changed?

My research indicates this does not happen, but I have a colleague who thinks differently.

View 5 Replies View Related

Merge Replication - Subscriber Error

Apr 20, 2007

Hi,

I have setup merge replication between SQL Server 2005 and SQL Server Express. The setup uses web synchronization. (And the setup uses dynamic filter HOST_NAME)

On some of the client machines, replication fails with the following error (when initializing the subscription):

from subscriber merge agent verbose log:
A dynamic snapshot will be applied from 'D:DOCUME~1JBAUMG~1.CORLOCALS~1TempDB101-EQA-SCL_BQDB_BQ_PUB_BQDB'

Validating dynamic snapshot
2007-04-1819:39:45.434 OLE DB Subscriber 'FRG-BAUMGARTENJSQLEXPRESS': sys.sp_MSregisterdynsnapseqno @snapshot_session_token=N'\XXX.XX.XX.XXSnapshotuncDB101-EQA-SCL_BQDB_BQ_PUB20070417180079dynsnap', @dynsnapseqno='5599F67E-A1A9-4573-A14F-9851F6FE4B51'

(Note: XXX.XX.XX.XX is a proper IP of DB, I have just masked it in the post)

The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file.When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.

I enabled verbose logging for the snapshot agent for the subscriber, but there was no error there. It is obvious from the log info above that the error occurs only after downloading the snapshot to the client and during applying it to the subscriber by the merge agent.

After I got this error, I tried running the subscriber program again and it successfully replicated on the third attempt. But this behavior is not consistent and it fails for most of the time on some of the machines

Any info on this error?

View 6 Replies View Related

Replication :: Publish Database On Subscriber

Aug 13, 2015

I have database (DB) on the server SQL1. This database (DB) is published on SQL1 server for SQL2 server. So, SQL1 is publisher and SQL2 is subscriber for (DB). Can I publish database (DB) on the server SQL2 for the server SQL3? I would like to implement this scheme of Publication/Subscription:

<o:p></o:p>
SQL1 -> SQL2 ->SQL3..

I know, that it is simpler just to publish DB for SQL2 and SQL3 from SQL1, but, because of network connectivity, I can't do that...

View 3 Replies View Related

Reinstating Replication On A Subscriber Automatically

Jun 21, 2007

Hi all



I am setting up a system using SQL Server 2005 replicating to both SQL Express and MSDE clients. My question is this - if a client PC has been rebuilt - i.e. new hard disk etc, how can I automatically make that machine realise that it is already a subscriber of a SQL Server database and for it to automatically get a snapshot?



I hope this makes sense, thanks for your help.

D

View 1 Replies View Related

Upgrading From SQL 6.5 To 7.0 With Replication In Place.

Feb 4, 2000

If I have a publisher, distributor, and subscribers all running SQL 6.5, and I wish to upgrade to SQL 7.0, in what order to I upgrade?

View 1 Replies View Related

Upgrading Replication Environment

Jul 1, 2005

Hi There

View 4 Replies View Related

Network Connectivity Between Distributor && Subscriber - Replication

Aug 25, 2004

What happens when Network Connectivity between Distributor & Subscriber
fails in Transactional Replication ??

Thanks,
John

View 2 Replies View Related

Replication When Publisher And Subscriber Hostname Conflicts?

Sep 23, 2015

We've got x2 data centers in the US:

NYC & SAC

Sadly the hostnames for the boxes I am trying to replicate are similar:

- db1.nyc.mydomain.tld
- db1.sac.mydomain.tld

The servers can talk to each other over all necessary ports however when I generate a push subscription from the publisher, I am asked to add the SQL Server subscriber and it already shows DB1 listed since itself is DB1.nyc. How can I attach the remote subscriber when setting up the subscription from the publisher if the hostname conflicts in my unique scenario?I created a entry on the publishers host file to use db2sac for the SAC IP and entering that alias in as the remote subscriber but no dice.

View 4 Replies View Related

SQL Server 2008 :: Add Subscriber To Ongoing Replication?

Feb 27, 2015

i have replication setup and working, there are 3 subscribers and now i want to add a 4th one. is it ok if i just add the subscriber and reinitialize replication?

View 1 Replies View Related

Replication: Transactional: Multi Publications, One Subscriber.

Jul 20, 2005

Greetings All, I was hoping that a replication sage might be able toanswer a question for me.I want to have one subscriber subscribing to N publishers. Iessentially have a company that has a main headquarters and threesatellite offices. I want each of the satellite offices to push theirdata up to the master database. From what I have read it seems likethis should not be a problem. Some questions that come to mind are:1.)Does the master need to be read only or can it be configured to beupdateable as well?2.)Can the distribution agent on all the publishers be set tocontinuously distribute or should it be staggered so as not to cause aproblem when another distribution agent is running?3.)If the distribution is set to "delay distribution" will this causechanges on the subscriber to be pushed out to the publishers?In this database guid's are used as pk's so the issue of pk collisionsis not a problem.I hope that this question is not too vague. My experience thus farwith replication has been simple one way transactional and simplemerge replication.Regards, Louis Frolio

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved