I've got 2 servers and they are using replication to synch the data from one to other.
I've got a Full Text Catalog on a table on both servers.
- I repopulate the catalogs completely on both servers(now remember that the base tables of those catalogs are under replication and are exactly the same!!)
-When I run the exact same qquery against the 2 databases using the Full Text Catalogs I'd just finished rebuilding, I get a different number of results returned in my result set on one then I do on the other...
How the heck could this be possible?
Hello AllI was wondering if there's a way to monitor/measure data-transferbeing taking place between 2 serves in a replicated environment.I cannot see any counters, etc. to monitor this..??thanksSunit
I have a website where members can add new messages to the site. But now I put somoe restrictions that for each member can add messages only the specified time intervals. For eg:- for one member, I put the time interval is from 1 to 10 (its in hour format and datatype is varchar).Means this member can add messages in between the time 1 AM and 10 AM. Now how to check whether current time is in between 1 and 10.
I have been tasked with an interesting problem using SQL 2005. Simplified it looks like this: I have two tables both well over a million rows, totalling unrelated keys between the tables. I need to find records in table one that do not exist in table two, the twist is this, I need to find rows where the invoice number in table 1 does not exist at all in table 2 or where the invoice number exists but the timestamp associated with the invoice in table 2 is more than 10secs different than the invoice/timestamp in table 1. I'm fairly new to sql and, although I can do the really simple stuff, this one is over my head I'm afraid. Any help at all would be greatly appreciated. I would also like to hear any suggestions regarding books that might give examples of sql code at around this level of difficulty. Thanks in advance for your assistance! Paul
When the database is configured for mirroring and you want to do partitioning on that database, How can we do? Is this similar process or any variation there while adding file groups and files? The partition will reflect in the mirroring database also?
Unfortunately, data wasn't filtered prior to getting inserted into this table. Now I am stuck with cleaning it up. I have thought about writing a query to update all the values, but there are just too many variations, including spelling mistakes, so I've ruled that out as a possible solution.
I have a table which has a Country field but the values per record vary. For example US, U.S., USA, United States, UK, United Kingdom, Canada, Can, etc. I'm trying to find the percent of records per country.
Sample table data: mytable Id Name Country 1 John US 2 James UK 3 Jane United States 4 Mary Canada 5 Jack U.S. 6 Tony United Kingdom 7 Jeff US 8 Tom Canada 9 Beth UK 10 Mark USA
I would like to show US: 50% --> (includes any variation of US ncluding US, U.S., USA, United States) UK: 30% CAN: 20%
I've made several attempts myself with no luck. Thanks in advance.
I have to create a table like this across a bunch of servers. I'm thinking that I'm overlooking something with needing two additional CTEs, but maybe not. I have it at 17 seconds, which isn't much faster than a while loop solution that's currently in place.
I have a report that was designed using SQL Reporting Services that sits on a SQL reporting server. It's nothing too exciting, it is essentially a three page application with legal jumbo on pages 2 and 3 and applicant data in fields on page 1.
We use rectangles to force page breaks to page 2 and to page 3.
When running the report on the report server, it shows and prints fine.
When running the report from the QA website internally, it shows and prints just fine.
When running the report from the production website from a machine internally, it shows and prints just fine.
When running the report from outside of the company network, the report is jacked. It obliterates large chunks of text, crams text together, and creates blank pages.
I need help in determining where I even begin with trouble shooting this!
can anyone tell me if they know of a way to automate the update process from development servers to live server, with little interference from an administrator
I have a development team that are constantly updating their databases along with their ASP code, and want to publish changes an a weekly basis. They have asked me for a way to take their new structures, tables, procedures etc, and copy them to the live servers, but NOT to interfere with existing customer data.
Funny I know – and I hate the idea btw :(
Any references, contacts, 3rd party tool recommendations welcome,
I am in the middle of a major migraton project, moving from x86 SQL 2000 to IA64 SQL 2005. I have a business need to link to several legacy servers. I have a number of problems I am trying to solve.
1) Linking a Kerberos server to a non-Kerberos server. 2) Linking x64 or IA64 servers to x86 servers. 3) Linking SQL 2005 to SQL 2000.
Two of the errors I am encountering are: ------------------------------ TCP Provider: An existing connection was forcibly closed by the remote host. Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. OLE DB provider "SQLNCLI" for linked server "SCDC250DB" returned message "Communication link failure". (Microsoft SQL Server, Error: 10054) ------------------------------ And ------------------------------ The OLE DB provider "SQLNCLI" for the linked server "SCDC250DB" reported an error. Authentication failed. Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "SCDC250DB". OLE DB provider "SQLCLI" for linked server "SCDC250DB" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399)
If someone has worked through these problems before, I would appreciate it if you could direct me to the relevant documentation to resolve these issues.
Thanks!
Brandon Forest
Database Administrator
Data & Web Services Team
Sutter Connect Information Technologyforesb@sutterhealth.org
Is there anyway to tell when records have been replicated to another device from SQL Server? I realize I can see it by looking in the distribution table, but I want to do it from a different database, so I cant access the distribution database from that DB. Any help would be much appreciated. Thanks, Nick
In our environment (SQL 2005) we have a database that uses Transactional Replication to sync data between two SQL 2005 servers. There is a web app that reads/writes data to the publisher server and the other server (that gets the replicated data) is used by some other internal applications.
At times, there is a need to delete some data from the publisher server...but this can ONLY happen once the data has been successfully replicated to the second server. Is there any way to determine if a row has been replicated successfully?
I have an application that uses web-based merge replication. My publisher is SQL 2005 and my subscriber is SQL 2005 Express. I control the replication with RMO code. If I make changes to the data in both databases using SQL Server Management Studio Express, my RMO code correctly syncs the two databases. However if I make changes to the data at the subscription through my application, these changes are not picked up by the replication process, even though the changes are present if you check the tables through Management Studio. What would cause these changes to not be recognized? Any ideas would be appreciated.
I am trying to figure out a way to link a MySQL database (running on Linux) to my MSSQL7 database. My ultimate goal is to synchronize inserted/updated/deleted data in the MSSQL7 DB to the MySQL DB.
I have tried a few things and have had a few ideas. They are:
1) Create table triggers in the MSSQL7 DB that will synchronize the data to the MySQL DB. I can't figure out how to run Transact/SQL statements (INSERT, UPDATE, DELETE, etc.) into an ODBC DSN pointing to the MySQL DB. I think this could be done with a linked server (see next item).
2) Add a linked server under Security->Linked Servers in the MSSQL7 Enterprise Manager. I have managed to get the remote MySQL server to link in here. I can even see the table names in the server browser, so that's encouraging! However, I cannot figure out the syntax of the fully-qualified table names in my SELECT statements. I have tried this:
SELECT * from LinuxBox..MyDB.MyTable -and- SELECT * from LinuxBox.MyDB.MyDB.MyTable
but I get:
Server: Msg 7312, Level 16, State 1, Line 1 Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.
This statement:
SELECT * from LinuxBox...MyTable;
gives me:
Server: Msg 7313, Level 16, State 1, Line 1 Invalid schema or catalog specified for provider 'MSDASQL'.
Since I can see the table names in the linked server browser in Enterprise Manager, though, some kind of connection must be active. I just need to figure out how to refer to the remote tables.
A big problem is that when I supply a catalog name while defining the linked server, I get an error message from the Enterprise Manager:
Error 7399: OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure.
Therefore, I can't define a catalog with the linked server. I think a catalog is required for the "4-part name" to work in the SELECT statement above.
3) Write an extended stored procedure that uses the ODBC function library to make a connection to the MySQL DSN and have a table trigger call the ESP. Hoping to be able to do something simpler than this!
Hi, We have to move two of our servers in US hosting SQL Server 2000 database to different location. These servers are clustered using MSCS and uses SAN to store database data. The database on this cluster hosts publisher and distributor. The other two sites in Europe host updateable subscribers. We are using transactional replication. The downtime will be around 24 hours during which servers will be physically moved from one location to other. During this time we will keep the two subscribers running and let the changes queued on subscribers. I have listed following steps based on my limited knowledge of SQL Server. Could somebody please advise if this approach is valid and if I have missed any steps?
1. Make sure no users are accessing database 2. Backup database (In case of emergency need) 3. Stop snapshot, Log Reader, Queue Reader and Distributor Agents 4. Stop SQL Server Service and SQL Agent 5. Take OS backup 5. Shutdown the server 6. Ship the servers and OS backup to new location 7. Setup servers at new location and restore OS backup 8. Start SQL Server Service and SQL Agent 9. Start agents listed in step 3
I got Server A with 30 Databases and I was asked to move 30 databases to Server B… But server A is having 2 databases as Publication and Distribution to two different servers….. and Server A is a subscription for 6 Databases from other servers….
Once I move the databases from Server A to Server B…… what is the best way to get the Replications too…. I mean Server B should have 2 Db’s as Pub & Distr and 6 Db’s as Subscription…
Is there any way I can script it out in Server A and run the same script in Server B with some modification…. so that the Replication work…..??
I have Jobs and Maintenance plans…. Which should also move from Server A to Server B…. I know using DTS...... is there any better way..??
Dear All, i've made repliaction stup using the information guide below. all the things are done axactly. now i've inserted a row in the publisher. but i'm not getting the record at the subscriber database. please let me know where i'm missing
Hope to be my last question. I used Transacational with update sub method. When adding new column to replicated table. Do I need to generate new snapshot again? Just want to know how can I apply the new schema to subsciber DB without doing all regenerate snapshot, recreate all tables and bulk copy.. Please help
I hosed myself, please help me somebody.We have a development server (SQL7) where the database is a restored copy ofproduction (where we use replication, but not in the developmentenvironment).While we rebuild the server recently (so we could move off of NT onto 2000),everything looked to be working fine.But I recently, messed up and did a massive update filling the transactionlog of the development server. Now I can't update data in development.I tried to do my usual:BACKUP LOG LAMS WITH Truncate_onlyonly to get a message:The log was not truncated because records at the beginning of the log arepending replication. Ensure the Log Reader Agent is running or usesp_repldone to mark transactions as distributed.Did a little research into BOL and tried to call the following two procs:exec sp_repldone@xactid = null ,@xact_seqno = null ,@reset = 1exec sp_repltransBoth of which gave me an error of :The database is not published.Which it isn't as we don't run replication in the development environment.I stopped and restarted the SQL Service (as that has cleared issues in thepast), but didn't reboot the server.How can I make the database not think It's replicated (or that its caughtup?).I was thinking, would I be even be able to do another restore of a morerecent copy of Production?Sincerely ...Depressed in Delaware
Hi,I am using Merge Replication for synchronizing a bunch of laptopsrunning MSDE with a central SQL Server 2000 production server.This database is used by an application that needs to know whichrecords have not yet been replicated, at any givem time.Here's a simple scenario:User A in laptop X starts his copy of the application. He syncs hisMSDE database through the application, to match the current centralservers' data. He then proceeds to add new records through theapplication and, after he is done, he synchronizes his database withthe central server, through the application.At any given time, the user might kill the application. He might addsome records today, a few more tomorrow or in a week and should be ableto change and delete records that have not yet been merged into theproduction server.The problem is that SQL Server does not have an easy way to tell whichrecords in the subscription tables have been inserted but not yetreplicated. I have been using the following query:SELECT rowguidFROM [MyTable]INNER JOIN (SELECT rowguid AS rep_idFROM msmerge_contentsWHERE generation = joinchangegen AND generation = (SELECT MAX(generation)FROM msmerge_genhistory )) dtableON rowguid_rep = dtable.rep_idThis query works for most cases but fails to identify the first recordcreated immediatly after a synchronization event.What I would like to know is if there is a correct way of identifyingrecords which are "not-yet-replicated" on a given table that is part ofa merge replication process like the one described above.Thanks in advance.
In Merge Replication SQL Server2005, what is the easiest way of finding out wether a row on the publisher database has ever been replicated to subscribers?
Hi, I have a table published on the SQL2K Merger Replication publisher. But when I add an index to the table it's not replicated to the device. I have checked the snapshot folder and the newly added index is already in the .dri file. Any ideas?
I have a number of databases that are being transactionally replicated from SQL 2000 Enterprise edition publisher to SQL 2005 Enterprise edition subscriber. I have included indexes in the replication. The subscriber database is then accessed and the data de-normalised and aggregated for reporting purposes.
My question is this: I want to periodically re-build the indexes on the publisher and subscriber via an automated task. If I rebuild the indexes on the publisher, will that automatically replicate to the subscriber? Will there be a problem with the "snapshot being out of date", and therefore replication stopping? I run a new snapshot once a day in the small hours of the morning. If there is likely to be a problem with the rebuild throwing the replication out, would it be wise to have the rebuild job running just before the new snapshot is taken?
Our company is considering using replication to synchronize data between handheld devices and SQL Server 2005. One of our requirements is the ability to retrieve a summary of all updated records in the Tags table (only on the server) each time data is retrieved from one of the handhelds.
I have merge replication set up between a sql 2005 server and a device that has sql ce database. My original publication had only tables and this has been working fine, as far as getting data from the server (publisher) to the device (subscriber). Recently, I had a view (joins between 3 tables) that I wanted to add to the publication. When I run the replication now (even on a brand new sql ce datbase) only the tables (and their data) get replicated, the view does not get replicated to the device/sql ce database.
Is there something else I need to do to get the view to replicate to the device?
I just completed creating a replication job between a server at my location, where I have SA privileges, to a subscription server at a location where I have db_owner rights (no SA). When replication runs, the objects get created on the subscriber as userid.object rather then dbo.object. The subscriber permissions were set by clicking on "Replication", right clicking on "Publication", clicking on "Configure Publishing, Subscribers and Distribution...", clicking on Subscribers tab, choosing the subscriber and selecting "Use SQL Server Authentication". How do I cause replication to create the objects as dbo.object if I connect to the subscriber using an id defined as db_owner?
