Sql Server 2000. I had a merge publication setup on one table that replicated to several sql servers (all 2000 SP3). The publication was deleted on the publisher - but somehow - the subscription information is still on the subscribers. How can I remove them? When trying to delete the tables at the subscribers I get error that they are part of replication.
P.S. Other tables in the same database are part of different merge replication.
We have a database we are replicating to about 8 SQL Express subscribers from a SQL 2012 SP2 publisher. The size of the database grew too large for the 10GB license limit for SQL Express and now replication refuses to replicate any of our deletions on the publisher to reduce the size of the database. I've come up with a few options below.
1) Drop one of the larger table indices on the subscriber database to get below the size restriction. Permit the replication to replicate the deleted records and then rebuild the index. (I'm not sure how important an index is to this table. Is it merely performance related?)
2) "Upsize" SQL Express to SQL Standard on the affected boxes. Allow the deletes to replicate. Backup the database, downgrade to SQL Express and restore the database back to SQL a new SQL express instance. This would involve a lot of work on each box. I'd like to avoid it if possible.
I'm working with Schedule Subcription with parameter @startdate in SQL 2005 Standard version. The @startdate is depend on user needs, I've tried to use DayAdd() function in the parameter but don't work. How can I set the parameter in subcription to do it? Thanks!
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
Here i have small problem in transactions.I don't know how it is happaning. Up to my knowldge if you start a transaction in side the transaction if you have DML statements Those statements only will be effected by rollback or commit but in MS SQL SERVER 7.0 and 6.5 It is rolling back all the commands including DDL witch it shouldn't please let me know on that If any one can help this please tell me ...........Please............ For Example begin transaction t1 create table t1 drop table t2
then execute bellow statements select * from t1 this query gives you table with out data
select * from t2 you will recieve an error that there is no object
but if you rollback T1 willn't be there in the database
droped table t2 will come back please explain how it can happand.....................
In article <8e7dqp$lii$1@nnrp1.deja.com>, skhanal@my-deja.com wrote: > Hi, > > I have two servers configured as Immediate updating subscribers to the > publication server. > > Everything was ok for sometime, but now when I try to insert a record > in any table in the published database in one of the subscriber I get > the following error. > > Server: Msg 7415, Level 16, State 1, Line 3 > Ad hoc access to OLE DB provider 'SQLOLEDB' has been denied. You must > access this provider through a linked server. > > Inserting the same record in the other subscriber and publisher does > not have any problem. > > I could not find any documentation for this error. Any help will be > greatly appreciated. > > Thanks
Hi! How is it possible to set up a repl. scenario, where a subscriber of a DB is also the publisher of this DB for other subscribers. i mean 3 level repliction. MAINPUB SUB1 SUB2 SUB3 notebookSUB1...................notebooksubN
SUB1..3 are subscribing to MAINSUB and publishing to a number of notebooks!
Can we use transactional replication with updatable subscriptions when we have more updates and transactional changes at the Subscriber side??
We are using SQL Server 2005 SP1. We will be using 6 publications and 4 subscriptions for each publications, so totally 24 subscriptions. Both the Publisher and the Distributor is the same server. It is set to run in continuous mode. Type : Pull Subscriptions.
I'm a bit confused right now. I am using Sql 2005 merge replication. I have a publisher and a subscriber running. Today, I updated some data in my publisher which caused 300,000 updates to be sent to the subscriber. There was no problem here, it took a while though. Later, I added a new subscriber and when it started initializing I suddenly thought that I should have really created a new snapshot, one that included the 300,000 row update. Because I didn't I was epecting that once the subscription had finished initializing from the old snapshot it would then have to apply the 300,000 row update. But it didn't seem to do this last update! But yet all the data was correct!!
Does Sql replication somehow merge the snapshot with pending updates when it creates a new subscription?
I have a database, MobileApp, on a server called Server03, which is a subscriber to a database Stock on Server01. I create the database as a subscriber on Server01, and it gets replicated to Server03 correctly.
I now want to use MobileApp on Server03 as a publisher to mobile devices. I click to add a new publication, and add all the tables. I get all the way to the end, but when I click on finish, I get an error for each table - 'an article with a different subscriber_upload_options value already exists for object.
Publisher SQL 2000 Snapshot publication timed to run at 23:00 daily All tables & views are published articles.Snapshot agent runs and creates a snapshot of all articles.
Subscriber SQL 2005 Pull subscription set to run continuously Manually run the SQL agent job and I get the following error... 2006-12-11 15:04:35.687 Agent message code 20164. Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber.
I've tried creating the subscription database from a backup so I know all the tables & views exist and also tried just creating a blank empty database - same error.
There are no user stored procedures or functions in the database.
If I'm publishing all tables & views then why should the data & structure fail to load at the subscriber? Do I need two publications, the first containing the metadata and the second containing the data?
I have a clustering between Server A and Server B. There is a clustered SQL Server installed.
Server A is a primary node(oltp production server) and Server B is a secondary node.
i've set up transactional replication betw serer A and B. Server A is Publisher and Server B is subscriber. B also has a distribution database. I use serve B for all the reporting purposes.
Currently both servers have sp1.
Now i want to apply sp2 to the server B first since i use it for reporting purposes (just to make sure there are no issues and then after some days i want to apply sp2 on server A which is my prodction oltp server)
So when i apply sp2 on server B , my publisher will have SP1 and subscriber (& distributor) will have sp2. Will this cause any issues with replication?
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?
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'
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.
I'm reading the MS online docs about replication. there's something not so clear until so far.
I have one central server and 7 client servers. Sometimes new data is entered at one client server. This should be replicated (when client comes online) to the Central Server (merge replication).
Also, the other way, once the central server gets new data (that was replicated from a client), it should be again replicated to all 6 other clients (from the central server).
now, who should i configure as the distributor, publisher and subscriber?
I have 2 server with merge replication the first as distributor and the second as subscriber.
I'll try to make trigger from database replication on distributor. the problem is if any change data on subscriber, i need the user name from subscriber.
right now, i used user_sname to get user name. but on distributor always get name Administrator. it's because when subscriber update or delete data, distributor always change/delete/add data from subscriber as Administrator.
but, we know data change from subscriber it's from user. I need to retrieve user name that who change the data.
someone have any solution.
This is my triggers
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER TRIGGER [tr_insert_jour] ON [dbo].[Jour] AFTER INSERT AS BEGIN SET NOCOUNT ON; declare @date1 datetime, @person varchar(30) , @journal_code char(4), @journal_no char(8) set @date1 = getdate() set @person = user_name() ----this is user name select @journal_code = JRN_CODE from inserted select @journal_no = JRN_NO from inserted insert into log_insert_Jour values (@date1,@person,@Journal_code,@journal_no) END
Hi all, I have a table (Products), and the table is like this:
- Product_ID (Primary Key), Not Null, Identity Specification: Yes, Increment 1, seed 1 - Name ... - Price ...
When i insert in the publisher (PC, Sql Server 2005), it inserts correct ex: |Product_ID | Name | Price| Inserted by| | 1 | P1 | X | Pc | | 2 | P2 | Y | Pc |
Then i replicate to Subscriber(Pocket PC, SqlMobile ), when i insert in Subscriber, it creates strange and big id's, is it normal? is this to avoid conflict inserts?
Ex: |Product_ID | Name | Price| Inserted by|
| 1 | P1 | X | Pc |
| 2 | P2 | Y | Pc | | 3000 | P3 | Z | PPC | | 6000 | P4 | Y | PPC |
I am using merge replication between SQL Server 2005 and SQL Express. Some of the articles in my publication have filter conditions. Changes to subscription databases are being applied to the publication database without regard to these filters.
The easiest way to see this problem is to create a merge publication with a single table article with @subset_filterclause of '0 = 1'. Any inserts to the publication database will not be propagated to the subscription database, but inserts to the subscription database will be propagated to the publication database. Can this be right?
Is it possible to create a trigger on a subscriber table so that when something on the subsriber table is changed a trigger can send some of the changes made to the subscriber table to another table?
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.
I have a problem with merge replication on SQL 2000.
-Publisher is Standard edition, subsriber MSDE
-Both SP4
-It always fails at same table.
- CHECKDB shows no errors
- It worked OK for a quite long period...
-Replication fails with this mesage ('verbose ouput'):
The merge process encountered an unexpected network error. The connection to Subscriber 'SV-LESNOSAOP' is no longer available. Percent Complete: 0 . Repl Agent Status: 6 Percent Complete: 0 Category:NULL Source: Merge Replication Provider Number: -2147200999 Message: The process could not enumerate changes at the 'Subscriber'. Repl Agent Status: 3 Percent Complete: 0 CategoryQLSERVER Source: SV-LESNOSAOP Number: 0 Message: Invalid time format Repl Agent Status: 3 Percent Complete: 0 CategoryQLSERVER Source: SV-LESNOSAOP Number: 0 Message: The merge process timed out while executing a query. Reconfigure the QueryTimeout parameter and retry the operation. Repl Agent Status: 3 Percent Complete: 0 Category:NULL Source: Merge Process Number: -2147199469 Message: The merge process encountered an unexpected network error. The connection to Subscriber 'SV-LESNOSAOP' is no longer available.
Some tables in article are merged succesfully so I don't believe it's a network error.
Anyway I will try to recreate the subscription, but there is a problem that subcriber has more data than publisher, so i have to manualy trasfer (DTS) the data to publisher before the new snapshot is applied. If this also fails i guess i can assume there is a connectivity problem.
Hi ...i am new to sql server replication, im using the sql server 2005.
I was configure the replication n publish a database , i wanna add a new subscription, but i am facing the follow error
You have selected the Publisher as a Subscriber and entered a subscription database that is the same as the publishing database. Select another subscription database.
How do i add another subscription database,since in my sql server has only 1 subscriber?
can anyone tell me if there is a way to determine with SMO or RMO if a database is a subscriber when using merge replication. If only have the Server and database at this point too!!
I want to provide a small app that creates a merge publication but only if the database isn't a subscriber.
I have two different sql servers one two different domains connected by internet. Iam working on Doman-A. Both sql server's are 2005
Domain Name System Name Sql Instance Name Port No
Domain-A SysA(IP:202.187.65.124) SqlA 1215
Domain-B SysB(IP:102.45.68.125) SqlB 1465
Intially I was unable to connect from SysA to SysB through sql management studio. But after giving the connection string as 102.45.68.125SqlB,1465 I was able to connect. Now my main problem is with the replication only. I had configured Publisher and Distrbutor on SysA. I want the subscriber to be SysB. But when I try to configure the subscription in the New Subscription Wizard from SSMS installed on SysA and try to Configure the subscription I get the following error message:
TITLE: New Subscription Wizard ------------------------------
SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'SysBSqlB'. (Replication.Utilities)
I am using Transactional Replication as a backup for our main database. The subscriber database however is smaller than the original database. Is there something that I'm missing? Is the subscriber missing data?
Hello, There are 2 questions that I interested in:Q1: We are planning to scale out SQL Server 2005 with Transactional Replication and use the following topology:NodeA: PublisherNodeB: Publishing Subscriber of NodeANodeC: Subscriber of NodeBIf it is necessary to allow update on NodeC, is it possible to set NodeC as an updatable subscriber? BOL states that Updatable Subscriptions do not support republishing data. But I'm not sure whether it is applicable to these case. Please advice.Q2:In SQL Server 2005 BOL, in claim that "Standard transactional replication assumes read-only Subscribers and is hierarchical in structure: typically a single Publisher publishes data to one or more Subscribers. Standard transactional replication also supports a republishing hierarchy: updates are delivered from a Publisher to a set of republishing Subscribers, who in turn deliver updates to a final set of leaf-node Subscribers. Updating subscriptions offer the ability for Subscribers to push changes back to the Publisher, but the arrangement is still hierarchical because changes follow the hierarchical structure when moving between Subscribers and Publishers. ..." in the topic "Peer-to-Peer Transactional Replication" Could somebody describe how Subscribers push back changes to Publisher in hierarchical structure? Could Republisher setup in the "hierarchical" structure or topology as a middle-node (Not a leaf node nor root node)?Thanks a lot.Terence
