Have two servers, one production server and one backup server. Both are Windows 2003 Server having SQL Server 2000 installed.
I have created replication on these servers using a wizard. I then generated SQL scripts to delete and create the replication. These scripts work well, but to test to remove the replication on both servers I used sp_removedbreplication 'DBNAME' on both servers. Not all were removed and on Replication Monitor and sub items plus Log Reader Agent there were added an icon, a red 'X' as in error. The replication seems to work.
Does anyone have any idea on why this happened and what I can do about it. It is not very nice to have errors in replication on a customers servers.
I have setup transactional replication everything on one box. later(two or three weeks later), Replication monitor is show red X Under my publishers (publications is disconnected). this is SQL2005.
When Replication monitor shows an error icon, I removed all the publication jobs completedly. After I reconfigured publishing, subscriber,Distribution, why the replication monitor still shows an error icon even though there is no replication job scheduled?
We have SQL Server 7.0 running on a NT 4 sp6 server and non of its items track in performance monitor. Under the SQL program tab, when performance monitor is clicked, it comes up and has items for SQL, but they all track as zero. You can "add" other items such as processor items and they track fine. Any ideas???
Thanks in advance!
Ray Peterson EPA Seattle 206-553-1682 peterson.ray@epa.gov
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, 'xxxxxx'. (Replication.Utilities)
I'm experiencing some very odd behavior in Enterprise Manager.I have a process that performs the following:1.) Restores a published database from Server A onto Server B. Thedatabasesare identical and they are both published.2.) I run a script that then deletes the subscriptions to thepublication onServer B. I do this via SP_DROPSUBSCRIPTION.3.) I run a script that then deletes the publication. I do this viaSP_DROPPUBLICATION.4.) Deletes the distribution database.5.) Deletes the old jobs.6.) Creates the publication from pregenerated scripts.7.) Adds the subscriber.8.) Adds the subscription.9.) Generates the snapshot and applies it.What's happening is that I'm seeing the job name that's assigned to thedistribution job increment by 1 each time this process is run. Also,I'mseeing ten instances of the publication showing up in the tree view.Everything appears to be rtunning just fine, transactions beingreplicated,etc. I'm just wondering is there something I'm missing in my steps.Plus,after two or three months of this, might not look so good having 90publications showing up in the tree view.Thanks!
There seems to be a problem with the replication monitor correctly telling you the progress of applying database schema.
Here is my setup:
Running SQL 2005 SP2. Publishing with merge replication (push) via a VPN. Approx 100 tables with lots of filtering, using data partitions filtered on host_name()
When you create and initialize a new subscription if you go to the Replication Monitor and open the Sync History window for that new subscription. You will see that it is setting up the schema in the subscriber but it does not update the status at the bottom of the window it just continually says:
If it helps you can see an image of this below you will notice that it is already 15 minutes into a process that will probibly take 2 hours but it still says 0% complete.
If you require any further info or have any suggestions as to why this is not a bug (just me being dumb) please let me know.
You can see an image of this problem here: https://applications.ccusa.com/2/images/snychistory.jpg
I have to write a little vb6 app to monitor sql 2000 replication. The app must run on the same desktop as the subscriber.
I've tried the samples that comes with sql (replsampl) but that is to do the replication, I need to just monitor replication that was set up in sql 2000.
With monitor I mean to show a red light when replication is busy, so that the user doesn't disconnect from the network while replication is in progress.
I have developed a smart device application using SqlServerCE that replicates data to the backend server. I need to provide to the user a status of this replication process. I read about the "MergeSubscriberMonitor" in SQL Server but can't find it in SqlServerCe. Is this possible? Can someone please point me in the right direction?
We have some mobile devices that are setup to replicate with our sql 2005 server via web sync. It seems that the devices are added multiple times within replication monitor. Is there a way to purge the bogus items. For example we have a mobile unit labeled as Device12 we have it listed under the subscriptions 5x. I would like to purge four of them as they are not currently being the replicated one.
I have a wired situation..!I set up transactional replication on one of my development server (SQL2000 Dev Edition with sp4).It is running fine without any issues and all of a sudden, i noticed inmy repication monitor tab under Publisher where I usually see thepublication is empty now.I do see the snapshot agent, log reader and distribution agent under myagents inside the replication Monitor. But its usefull to see all 3agents in one window under publisher before. What happend? Is there anyway to get that inside that monitor? Has someone encountered thissitation before? Please advise....After that I tried to create a new set of replication on differentdatabase on the same server and i dont see those either underReplication Monitor - Publishers....All it says is (No Items)....I would appreciate any help to correct this issue... Thanks in advance..
We have a database on the live server which powers a website. Then our client has a copy of the database at their office. Their is Merge replication set up between the two running every half a hour.
Now they want to be able to look at the merge replication history. With my little knowledge, I believe that the place to get this data from is the distribution database on the publishing machine which is the live server. Now for obvious reasons we cannot replicate the distrubution database to their side so we need to come up with some way of giving them the data. They also want a history of replication. So the way I was thinking of fixing it was to have a job that copies some of the last days data from the distrubution database onto the main database and add that table to the replication so they got it every day. Also writing a ASP page so they can look at the last days data straight from the distrubution database.
Here are my questions:
Question 1: How long does the replication store history for?
Question 2: Is thier any simpler way of allowing them to see the replication history?
Question 3: Are their any other things I should be worried about?
--> Cursor for fetching all publications in all databases DECLARE curPublishedDatabases CURSOR LOCAL FAST_FORWARD FOR SELECT [name] FROM sys.databases WHERE is_published > 0
OPEN curPublishedDatabases FETCH curPublishedDatabases INTO @PublishedDBName WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = N'SELECT ''' + @PublishedDBName + ''', [name] FROM ' + @PublishedDBName + '.dbo.syspublications' INSERT INTO @Publications (PublicationDatabase, PublicationName) EXEC sp_executesql @SQL
FETCH NEXT FROM curPublishedDatabases INTO @PublishedDBName END
CLOSE curPublishedDatabases DEALLOCATE curPublishedDatabases
--> Cursor for posting tracer tokens DECLARE curPublications CURSOR LOCAL FAST_FORWARD FOR SELECT PublicationPK, PublicationDatabase, PublicationName FROM @Publications
OPEN curPublications FETCH curPublications INTO @PublicationPK, @PublicationDatabase, @PublicationName WHILE @@FETCH_STATUS = 0 BEGIN SET @Parmameters = N'@PublicationName varchar(200), @TracerTokenID_OUT int OUTPUT'; SET @SQL = N'EXEC ' + @PublicationDatabase + '.sys.sp_posttracertoken @publication = @PublicationName, @tracer_token_id = @TracerTokenID_OUT OUTPUT' EXEC sp_executesql @SQL, @Parmameters, @PublicationName = @PublicationName, @TracerTokenID_OUT = @TracerTokenID OUTPUT
UPDATE @Publications SET TracerTokenID = @TracerTokenID, TracerTokenPostTime = GETDATE() WHERE PublicationPK = @PublicationPK
FETCH NEXT FROM curPublications INTO @PublicationPK, @PublicationDatabase, @PublicationName END CLOSE curPublications DEALLOCATE curPublications
--> Wait two minutes for all tokens to be commited at all subscribers WAITFOR DELAY '000:02:00.000'
--> Then check the results for each posted token DECLARE curTokens CURSOR LOCAL FAST_FORWARD FOR SELECT PublicationPK, PublicationDatabase, PublicationName, TracerTokenID FROM @Publications
OPEN curTokens FETCH curTokens INTO @PublicationPK, @PublicationDatabase, @PublicationName, @TracerTokenID WHILE @@FETCH_STATUS = 0 BEGIN --> Insert token history for each token SET @SQL = N'EXEC ' + @PublicationDatabase + '.dbo.sp_helptracertokenhistory @publication = ''' + @PublicationName + ''', @tracer_id = ' + CAST(@TracerTokenID AS VARCHAR(50)) + '' INSERT INTO @Latency (DistributorLatency, Subscriber, SubscriberDB, SubscriberLatency, OverallLatency) EXEC sp_executesql @SQL
--> Make sure that the PublicationPK is added to the token history UPDATE @Latency SET PublicationPK = @PublicationPK WHERE PublicationPK IS NULL
--> Clean up the tracer token SET @SQL = N'EXEC ' + @PublicationDatabase + '.sys.sp_deletetracertokenhistory @publication = ''' + @PublicationName + ''', @tracer_id = ' + CAST(@TracerTokenID AS VARCHAR(50)) + '' EXEC sp_executesql @SQL
FETCH NEXT FROM curTokens INTO @PublicationPK, @PublicationDatabase, @PublicationName, @TracerTokenID END CLOSE curTokens DEALLOCATE curTokens
SELECT b.PublicationDatabase, b.PublicationName, a.Subscriber, a.SubscriberDB, a.OverallLatency, b.TracerTokenPostTime FROM @Latency a INNER JOIN @Publications b ON a.PublicationPK = b.PublicationPK
when I look into the replication monitor of our sql 2005 server I see a lot of subscriptions that are no longer used.
I wrote a program for a smartphone using the sql compact edition (sql ce db). Just in case there are some problems i do not want to mentioned here, i use an preconfigured sql ce db, reinitialize it and afterwards synchronize it. Then the old subscription is no longer used.
The problem is that up to now I did find a way to delete this unused subscriptions at the publisher database. As a hint: there is no way to get access to the old sql ce db for removement issues. The dbs are gone.
So, is there a way to solve my problem?
We are using sql 2005 server as publisher and are using merge replication.
I have setup sql 2005 transactional replication system with three machines (publisher, distributor and subscriber). Couple weeks later, publisher with publications is disconnected from replication monitor (under My Publishers show X on the publisher machine name). I didnt change any things. Can you help for this please?
Both pcs are connected to each other via the internet as i am using Aliases ,they can both access and see each other using SQL Auth.
I want to implement Merge Replication to Synchronize TestDB ,
I have NO DOMAIN connnecting them.
I Created a new publication ,under security i asked the agent to use the Sa account to connect
When launching the rep monitor i get the following error:
"Replication Monitor could not connect to Distributor 'laptop'."
"Login failed for user ''. The user is not associated with a trusted SQL Server connection. (.Net SqlClient Data Provider)"
Both Publisher and Distributer are on my Laptop ,why cant they connect to each other?
Another Question : could i implement Traditional Merge replication to synchronize my DBs or do i need to configure Web Synch in Merge rep to enable connecting via the internet?!
To monitor the status of replication in SQL 2000 we insert the resuts of sp_MShelp_replication_status into a temp table and review the status. In SQL 2005 this stored procedure has changed and now does it's own insert into execute command. Since insert execute commands can not be nested we must find another way to programically monitor replication using TSQL. We could call sys.sp_replmonitorhelppublisherhelper but we can't seem to find it in the distribution database. It must be hidden somehow. Any ideas?
We're testen a simple lan SQL 2005 SP2 one way transactional replication config. Data is replicated ok, but in the Replication Monitor we found some strange issues:
- The subscription is giving a latency warning. When we insert a tracer tocken the total latency is 5 secs. Is there a way to alter the warning level for a subscription??
- Undistributed Commands tab Changes do arrive at the client, but the distributed commands counter shows an increase in value for every (as far as we can tel succesfull) change
If we do insert a tracer token -wich arrives within 5secs.- the the counter is reset to zero.
Hi guys does anyone know of a quick solution to remove the replication monitor icon within Enterprise Manager please I have tried all sorts but it is still appearing and starting to irritate me as I don't use replication on this server. cheers
The client production server CPU starts thrashing. Task manager indicates that SQL server is gobbling CPU cycles. Having a look at the replication monitor, it is obvious that indivual synchronisations to the mobile devices are taking significantly longer expected.
Observing an indivual synchronisation attempt, the Upload changes to Publisher rows are very quickly resolved.
The Download changes to Subscriber seems to take up a very long time.
Along the way, the estimated completion does a few interesting things, like going from 100% complete with no estimated time to complete, back to seomthing like 77% with 2 minutes left to complete.
This sort of behaviour occurs when there are only a hundred rows to download.
Synchronisations for minimal amounts of data suddenly taking anywhere from 2 to 15 minutes. Totally unacceptable form the client perspective but seems that 2005 behaves quite different from 2000 and the tricks are yet to reveal themselves.
Note - It is a server hardware issue as there is in excess of 3 GB ram, the database is on a SAN and there are 4 3Ghz CPUS in operation.
Any possible help appreciated as this issue is beginning to drag on.
We have a publisher that got red-crossed(Run to problem). I decide to remove and recreate it. However, after remove it, the publisher still stays in Replication Monitor. The remain thing has no distributor and logreader, but only snapshot agent. When to check the property of the agent, we got a error message basically say the job does not exist, which makes sense.
Now, it does not show up in any places, except Replication Monitor. Well, it cannot be removed from Replication Monitor. Can any one tell us how to clear it from Replication Monitor?
Thank you.
More Infor: The replication was set up on 2000.(2000 pub, 2000 dis and 2000 sub) with 2005 Management Studio, and the publication was removed with 2005 Management Studio.
We are using SQL Server 2005 64 bit standard edition on both ends (subscriber distributor and publisher ) publisher is its own distributor. Now i want to investigate the replication performance and delayed in replication for this purpose i found that tracer tokens can be settedup but when i open replication monitor but unable to see tracer token tab.
Plz let me know something.why i am unable to see this tab, is there some problem of rights ? , permission, or installtion.
During an upgrade, we had problems with PK errors on sp_MSget_repl_commands. I recreated the SP with the varbinary characteristic as we have seen in many posts and it was sucessful in starting. However, the sp now appears as a 'user' sp, not a 'system' sp, however I an not sure that is a real issue.
The problem I am seeing is that none of the agents are appearing in replication monitor under their agent folders. Replication appears to be working fine and the publications are showing on the distributor in the Replication Monitor - Publication folder under the appropriate publisher. But all of the agent specific folders (i.e. Snapshot agent, distribution agent, etc) are empty.
Another interesting point, is that if replication fails, the failure 'X' shows up on the Agents folder as well as the publisher, but again, no sign of the agent in the folder.
I Want to monitor Replication count of object (Table )if it is not equal to Publication (Table ) and subscriber (Table ), It have to send mail with count difference.
I set up a sql server 2005 database as publisher for about 50 sql mobile subscriptions. Yet in the replication monitor windows, it shows in subscription columns is 26549. Does it mean I have 26549 subsriptions or it means 26549 synchronizations?
We use Merge (pull) replication to get data from the "mother ship" updated to the laptops of 15 sales reps before they head out into the field, where they work disconnected. At end of day, they all reconnect and all the data is once again shared.
We've been having problems with the replication, but that's another topic.
This morning, I logged onto the server to check the replication monitor, and found that all of the subscriber names for that particular subscription are blank! They show the open/close bracket ( [] ) followed by the database name. This is under the My Publishers >> [server name] >> [publication name] node in the explorer tree. If I try to double-click on one of the subscribers to get their detail, I get tne message, "Replication Monitor could not open the Detail Window." and "Specified cast is not valid".
If I look at the publication in SQL Server Management Studio, all of the subscriber names show up just fine. But they do NOT show up under Replication Monitor.
No one touched the database over the weekend, and this was working just fine on Friday afternoon.
We have an issue with our replication configuration when viewed through replication monitor. Parameterised Filters and joined filters don't appear in the gui. However, when we script the publication all the filters are present.
This issue only seems to occur when we have a remote distributor.
I should also point out that we have a merge push topology that uses a custom RMO synchronisation component on a separate server to either the publisher or the distributor. Also all the databases in the topology are called the same name. This has caused us other issues relating to this topology in particular so I raise it here as well although I don't expect it to be the case in this instance.
Any help would be greatly appreciated in clarifying this matter.
If I'm on a remote machine, meaning a computer not in the WSFC cluster, and I open SSMS 2014, point it to a SQL Instance, and open activity monitor:
1. I get all the panes and charts except % Processor Time.
2. Then, if I authenticate to the cluster's domain by mapping a drive with valid domain credentials, I'm free to put performance counters in the Perfmon - - - but SQL Activity Monitor shuts down with“The Activity Monitor is unable to execute queries against server SQL-V01INSTANCE1..Activity monitor for this instance will be placed into a paused state.Use the context menu in the overview pane to resume the activity monitor.
Additional information: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))(Mscorlib)”
3. Of course, the Activity monitor can't be resumed via the context menu. Removing counters and closing the perfmon do not work. I dropped the mapped drive and rebooted the machine. That brought back 95% of the information in the Activity monitor.
4. Further experimentation showed that any mapping of drive shares present on the SQL Server to the computer running SSMS cut off functionality of the 'overview' pane in the remote machine's SQL Activity monitor -- the monitor that had been trying to watch the server offering the shares.
Hi: I'm try to create a stored procedure where I sum the amounts in an invoice and then store that summed amount in the Invoice record. My attempts at this have been me with the error "The multi-part identifier "items.TAX" could not be bound"Any help at correcting my procedure would be greatly appreciate. Regards,Roger Swetnam ALTER PROCEDURE [dbo].[UpdateInvoiceSummary] @Invoice_ID intAS DECLARE @Amount intBEGIN SELECT Invoice_ID, SUM(Rate * Quantity) AS Amount, SUM(PST) AS TAX FROM InvoiceItems AS items GROUP BY Invoice_ID HAVING (Invoice_ID = @Invoice_ID) Update Invoices SET Amount = items.Amount WHERE Invoice_ID =@Invoice_IDEND
I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:
SetIdItemCode 1A 1B 24 28 26 310 312 410
[code]....
You can see that there are some sets that have the same members:
- 1 and 10 - 2 and 11 - 7, 8 & 9
What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.
