We have a development server (SQL7) where the database is a restored copy of
production (where we use replication, but not in the development
environment).
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 transaction
log of the development server. Now I can't update data in development.
I tried to do my usual:
BACKUP LOG LAMS WITH Truncate_only
only to get a message:
The log was not truncated because records at the beginning of the log are
pending replication. Ensure the Log Reader Agent is running or use
sp_repldone to mark transactions as distributed.
Did a little research into BOL and tried to call the following two procs:
DECLARE @UserEmail as Varchar(50) DECLARE @UserZipPassword as Varchar(50) DECLARE @CoupMember as int DECLARE @member As Bit --Check if Email Exists…. If yes Then -- SELECT @RewardsMember = COUNT(*), @member =CASE M.EMAIL WHEN ((LEN(EMAIL)) <> 1) THEN 1 WHEN (LEN(EMAIL)) IS NULL THEN 0 END FROM MemberTable M Trying to get information from table for User based on data provided. What I am trying to do up there is check to see if User provided correct Email, if User provided right password and if User provided right zip code. Basically, I need to return bit values to my client application from this. Ran into some problem with my SELECT CASE statement up there. Need help please. Thanks in Advance.
I'm upgrading an application that uses a VBScript/ASP front-end and MS Access Backend. The application has many points expecting fields to be true or false..
E.g. SELECT * FROM MyTable WHERE Active=True
However, since SQL Server requires 0 or 1 for bit fields, this query keeps failing. SQL Server (2005 Express) thinks True is a column name and is not automatically converting "True" to 1.
It would be so difficult to re-write the application to do this at the VB level, is there a way to make SQL server do this conversion automatically ?
I have a large SQL script that creates about 20 stored procedures. It worked fine up until our dev SQL server died and was rebuilt. Now, when I try and run the entire script, it complains that I am redeclaring the parameters multiple times in a batch:
The variable name has already been declared. Variable names must be unique within a query batch or stored procedure. (see snippet below)
My feeling is it is a setting on the server but I didn't find anything on Google. Anyone know what it might be? How can it be matching 2 parameters in DIFFERENT stored Procedures?
Thanks in advance.
-------------------SNIPPET------------------
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO
CREATE PROCEDURE sp_PartyInviteMessage_Create @PartyInviteID int, @PartyInviteTypeID int, @PartyInviteRecipientTypeID int, @PartyID int, @TemplateSetID int, @FromEmail varchar(255), @ToEmail varchar(255), @Subject nvarchar(255), @Body ntext, @Enabled bit AS BEGIN INSERT INTO PartyInviteMessage (PartyInviteID, PartyInviteTypeID, PartyInviteRecipientTypeID, PartyID, TemplateSetID, FromEmail, ToEmail, Subject, Body, Enabled) VALUES (@PartyInviteID, @PartyInviteTypeID, @PartyInviteRecipientTypeID, @PartyID, @TemplateSetID, @FromEmail, @ToEmail, @Subject, @Body, @Enabled) END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO
CREATE PROCEDURE sp_PartyInviteMessage_Delete @PartyInviteID int, @PartyInviteTypeID int, @PartyInviteRecipientTypeID int, @PartyID int AS BEGIN DELETE FROM PartyInviteMessage where PartyInviteID = @PartyInviteID AND PartyInviteTypeID = @PartyInviteTypeID AND PartyInviteRecipientTypeID = @PartyInviteRecipientTypeID AND PartyID = @PartyID END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
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..
I'm writing some code to create stored procedures in a database. In order to test it out, I deleted a stored procedure (right clicking in SQL Server 2008 and clicking on delete) and then ran my code to see if it would create it.
My code looks like this:
Code: SqlCommand command = new SqlCommand(); SqlConnection conn = database.TypeLibraryDatabaseConnection; command.Connection = conn; // create the command to create the stored procedure command.CommandText = database.GetProcedure(node.Name); // create the stored proc in the database try { command.ExecuteNonQuery(); } catch { } command.Dispose();
database.GetProcedure(node.name) basically gets a string containing the SQL script to create the stored procedure.
command.ExecuteNonQuery() throws an SqlException that says: "There is already an object named 'SecuritySession_DeleteSessionById' in the
database." But I deleted it! Why does it think it's still there?
I am attempting to move some SQL 2000 databases to SQL 2005. My main production database does not seem to want to move. When I use the SQL 2005 GUI the .bak backup file is marked 'Incomplete'. When I attempt to restore the backup file I get a 'RESTORE detected an error on page (0:0) in database' message. I saw a thread in the SQL Express forum suggesting trying to restore from the T-SQL level to get the GUI out of the picture and I get the same 'error on page (0:0)' message. However when I take the same file and use SQL 2000 Enterprise Manager it restores with no problems.
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.
Dear All, i'm in the transactional replication environment. we need to add one new table to the publisher. it is sql server 2005 environment. please explain me the steps
and in another table, i need to change the data type of a table.
please guide me
Arnav Even you learn 1%, Learn it with 100% confidence.
Can Express edition be a publisher for transactional replication with SQL-DMO? Does scripting out and editing it work? Does stored procedure work for this purpose?
Right i have two sql 2005 standard edition boxes within a Windows 2003 R2 active directory environment. All updates sp1 etc.
I have an mssql service account setup in AD which all the mssql services on both servers startup with.
I went to setup replication between the two servers for one db.
Firstly i setup distribution no problems no errors.
Then i went to setup the publication, all went well no errors in management studio gui but in the application event logs i got the following:
Event Type: Error Event Source: MSSQLSERVER Event Category: (2) Event ID: 14151 Date: 05/10/2006 Time: 12:37:43 User: DNETWORKmssqlservice Computer: SGC Description: Replication-Replication Transaction-Log Reader Subsystem: agent SGC-MerakDB-2 failed. Executed as user: dnetworksqlrep. A required privilege is not held by the client. The step failed.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. Data: 0000: 47 37 00 00 12 00 00 00 G7...... 0008: 04 00 00 00 53 00 47 00 ....S.G. 0010: 43 00 00 00 0d 00 00 00 C....... 0018: 64 00 69 00 73 00 74 00 d.i.s.t. 0020: 72 00 69 00 62 00 75 00 r.i.b.u. 0028: 74 00 69 00 6f 00 6e 00 t.i.o.n. 0030: 00 00
Event Type: Error Event Source: MSSQLSERVER Event Category: (2) Event ID: 14151 Date: 05/10/2006 Time: 12:37:51 User: DNETWORKmssqlservice Computer: SGC Description: Replication-Replication Snapshot Subsystem: agent SGC-MerakDB-Merak Mail DB-2 failed. Executed as user: dnetworksqlrep. A required privilege is not held by the client. The step failed.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. Data: 0000: 47 37 00 00 12 00 00 00 G7...... 0008: 04 00 00 00 53 00 47 00 ....S.G. 0010: 43 00 00 00 0d 00 00 00 C....... 0018: 64 00 69 00 73 00 74 00 d.i.s.t. 0020: 72 00 69 00 62 00 75 00 r.i.b.u. 0028: 74 00 69 00 6f 00 6e 00 t.i.o.n. 0030: 00 00
I've tried all sorts to get this to work, the sqlrep user is db_owner for the distro db, ive tried the sqlrep user as domain admin to see if it was a system priv issue, no luck :(
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.
This may seem like an odd question, but is it possible to setup replication between SQL Server and Microsoft Access having SQL as the subscriber and Access as the publisher?
I know this seems backwards, but we are just throwing some ideas around for a new project and if this is possible, it could potentially save us and our client alot of time and money.
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 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..??
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?
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
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?
