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
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
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
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
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
Hi,
I read that views can't be published with SQL Server 2005 replication. Is this planned for the future? If not what alternatives are there for this?
Simple collecting the data needed in a new table ain't a solution for us (memory consuming). And joining the data on the PPC ain't a good solution either (memory and time consuming). We only want to pull the data.
Greets,
Ivo Klerkx
hi,
my sql query is like this
create view table1 with schemabidning
as
select columnname from table1
but it is giving error msg like this
you cannot create view 'Cannot schema bind view'
please help me out how to create a view with binding to underlying table.
Raghu sunkara.
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
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'
: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?
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 RelatedHi 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
Hi Guys,
I tried all the ways ,no good result.
I am really running out of time.
Can somebody please help.
-Anu
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
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
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?
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,
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
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
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?
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
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
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
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.
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?
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...
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
What happens when Network Connectivity between Distributor & Subscriber
fails in Transactional Replication ??
Thanks,
John
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.
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 RelatedGreetings 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 RelatedCan I enable the Non - Clustered Index option set to 'TRUE' at publisher does it affect anything?
Does the Non clustered Indexes are automatically transfer to the subscriber database?
Does it affects database performance when I enable this option!
Hi,
I need a urgent help! The problem is that every synchronization only transfer data from subscriber to publisher, but not the other direction. The publisher is sql server 2005 standard edition, and the subscriber is 2005 express. Is that any stored-procedure to deal with such a problem?
Thanks for any commnet.