I have a distributed inventory control database that I am going to
migrate from Access to SQL Server. I am going to use SQL Server
Replication to keep the data current.
There will one SQL Server 2000 database at HeadOffice, and about 200
branches will each have MSDE 2000.
For tables that are the same for each branch (e.g. StockItems,
Suppliers), I'm going to use Transactional replication with the
HeadOffice as Publisher. There is no need for Merge replication,
because only HeadOffice will be changing this data.
For tables that are updated at the branches (e.g. deliveries, sales),
there shouldn't be any
conflicts either, as each branch will only update data that is
specific to it. So, transactional
replication with the Branches as Publishers seems like the answer.
BUT, MSDE 2000 only handles merge replication as a publisher, so it
looks like I'm going to have to use Merge Replication if I'm am going
to use MSDE.
Question: Do you think that it worth buying a full SQL Server licenses
for each branch, so that I can use Transactional replication with the
branches as publishers. Or, should I stick with MSDE 2000 and Merge
replication. Also, are there any other reasons why MSDE won't be
adequate as the database at the branches.
Summary: Failed to insert detail rows in master-detail scenario during merge replication. Not always, sometimes. Topology: I've got 5 servers running MSDE, one of them is central publisher/distributor for merge type of replication. There is no row or column filtering: all subscribers have all data. Central publisher resolve conflicts with default revolvers. Some tables have relations: master-detail (i.e. orders-ordersDetails, etc). Relations are defined in tables as FK. My application which fills data use datasets with relations between tables defined the same way as in the sqlserver. So app first work with dataset which is unable to receive details without master record. When data updated to msde, it is done without errors, means master and details table updated correctly (tables at msde has relations too) Applications running on 4 different locations and fill data to local db (subscriber to central publisher). Sync occurs every 15 minutes in the following order (merge agents run at publisher/distributor): subscriber1: every 15 minutes, starts at 00.00h subscriber2: every 15 minutes, starts at 00.03h subscriber3: every 15 minutes, starts at 00.06h subscriber4: every 15 minutes, starts at 00.09h Sync lasts for average 15 sec, never 3 min. Problem details: Message: The row was inserted at 'CentPub.myDB' but could not be inserted at 'Sub2.MyDB'. INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_OrdersDetails_Orders'. The conflict occurred in database 'MyDB', table 'Orders', column 'OrderID'. Description CentPub is central publisher which just collecting data from subscribers. So, Order was made on one of the other subscribers different than Sub2, mean user at location3 insert order with details in local database, after a while, CentPub take this order to its database (MyDB), after that CentPub try to sync with some of the other subscribers (i.e. Sub2 == location2) and then for some unknown reason orderDeatils failed to insert in Sub2's orderDeatil table because constraint 'FK_OrdersDetails_Orders' conflict. After that happened, thing goes like in http://support.microsoft.com/kb/307482 (generally: in next session, failed order details are deleted from CentPub, which means deleted from all subscribers after syncs.) The problem is that subscribers have tables with relations, so Cause isn't as describe in MS kb because my tables have relations at all servers. After finished replications I have Orders without details records at all subscribers, so I assume that merge agents sometimes (not always) try to insert details prior to master table during the same sync session. In resolution section of MS kb article they say 'Mark the subscriber foreign key constraints as NOT FOR REPLICATION'. In relations definition I see 'Enforce relationship for replication) options which is enabled in my tables. If I turn that option off, is it possible to happen that my subscribers receive details without master record? (My observation of behavior says that will not happen(that will lead to problems in my app because my datasets enforce relations too). Deleted record I bring back to life with conflict manager in EM, but I'd like it never happen. Is it a bug, side effect or something I do it the wrong way?
I am using MSDE 2000 for my replication. the problem is that althought at time creating publication it inserts GUID into every table but if one table in 2 nodes has same primary key, it inserts only one row ( accorrding to prority ). there are some Conflict Reslover methods that can be used for this purpose. i wanted to ask that is there any other way for me to resolve this conflict. i am asking for a new way because my database schema has been created and a lot of coding behalf of that schema has been done.
I have a problem with creating Merge replication between two instances of MSDE 2000. In the article http://support.microsoft.com/kb/324992/, published by Microsoft, described, that it is possible.
I am creating Merge Publication at MSDE 2000, with Distributor and Publisher configured at same machine, with options "Allow Pull Subscriptions" and "Allow Anonymous Subscriptions". There is no problem with creating of Publication. Even snapshot generation finishes successfully. Also I create a login with SQL Server Authentication and "System Administrators" database role at the publisher in order to connect to it from Subscriber.
The problem occurs, when creating anonymous pull subscription to this publication at another instance of MSDE. During initial synchronization an error occurs:
The process could not connect to Distributor '<publisher_server_name>'. Login failed for user '<login_created_at_publisher>'. Reason: Not associated with a trusted SQL Server connection. The step failed.
Although I didn't use Windows Authentication at all, so Subscriber doesn't need to connect to Distributor using trusted SQL Server connection.
What is a problem and how can I workaround?
Note: The same works correctly, if MS SQL Server used as a Publisher instead of MSDE.
Please, help!
I can provide publication and subscription creation scripts, if required.
Our current environment is: SQL Server 2000 backend; XP/Windows 2000 clients with MSDE loaded; a Microsoft Access 2002 app using DSN to connect to the local MSDE database; Merge replication which is a job within the MSDE engine that we execute in VBA code using the following DMO code:
' Find the Job name to execute
lngJobCount = oServer.JobServer.Jobs.Count
lngJobIndex = 1
blFoundJob = False
While lngJobIndex <= lngJobCount And blFoundJob = False
If InStr(1, strJobName, "LEXData", vbTextCompare) > 0 Then
If InStr(1, strJobName, strServerName, vbTextCompare) Then
blFoundJob = True
End If
End If
lngJobIndex = lngJobIndex + 1
Wend
' Execute the job
If blFoundJib Then
Set oServer1 = New SQLDMO.SQLServer
With oServer1
.LoginSecure = True
.Connect strServerName
End With
Set oJob = oServer1.JobServer.Jobs(strJobName)
End If
We are now planning a move to a SQL Server 2005 backend and Express edition on the clients and the app would remain as Access 2002. Any pointers to documents that will be useful would be appreciated but I have a couple of specific questions as well.
1) As I understand it we can load Express side by side with MSDE so we will probably go down that path and load the data from MSDE into an Express version of the database. Once that's done we will create a DSN pointing to the new database and name it the same as the DSN which is currently used for the MSDE database. If we do this will our Access app simply work as before (ignoring replication)?
2) When we have our app happily running against a DSN that points to an Express database would we expect our existing DMO code (as above) to work, e.g. find a particular job in a list jobs and executes it? I can see that the replication job itself may have to be rejigged for Server 2005.
I have the following scenario. An application using SQL Server 2000 SP3 on the server side and clients using MSDE 2000A (SP3a) on the client machine. Since the client is offline quite often merge replication is used to keep the clients in sync.
Now we try to upgrade to SQL Server 2005 SP1. The publisher and distributor upgrade (on the same box) worked fine and all clients could still synchronize. Fine :-)
Now we try to upgrade the clients to SQL Express SP1. Now the problems start :-(
1) After the upgrade the entry within the Synchronization Manager is gone (we can overcome this by using sp_MSregistersubscription or by manually disable and enable Synchronization manager on the subscription properties)
2) Initial Synchronization (takes a long time but) works fine, if I apply changes to the subscriber or force a reinitialize all following synchronization will fail. With the following messages:
Error messages: The merge process could not clean up the subscription to 'tstvmw23':'TestBase:'TestBase'. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200965) Get help: http://help/MSSQL_REPL-2147200965 New request is not allowed to start because it should come with valid transaction descriptor. (Source: MSSQLServer, Error number: 3989) Get help: http://help/3989
Remark the test system I use is a nearly empty db (only with full schema and a few lookup tables) with 15MB. The same error occures if I create a new database an subscribe to the same publication.
Please help otherwise I'm forced to step back to MSDE.
I'm not sure if it's the setup I did wrong, but I can't seem to get my text datatype in my database to store more than 900 characters. I'm trying to setup a news database for my website, which will populate the information into a datagrid. To test, I manually added a news item in the database through the visual studio 2003 gui. I immediately noticed a problem as the I was getting an error after a long news item saying:
"The value you entered is not consistent with the data type or length of the column, or over grid buffer limit."
I couldn't find anthing to set the buffer limit and the datatype is "text" filled with simple text in the column. As a further test, I simply entered 12334567890123... up to 900 characters and still recevied the error.
I would appreciate someone leading me in the right direction on this one.
I am trying to change the computer name of a machine running MSDE but I get an error when SQL Server starts. With regular SQL when I change the name of a computer I re-run setup and setup fixes this problem. MSDE can only be installed from unattended mode so I can’t rerun setup and fix the problem.
My question is "How do I change the name of a computer running MSDE with out reinstalling MSDE"
We currently have the problem, that all our machines are produced with the same name and afterwards the name is changed. So we have the problem that the checksum key for the MSDE isn't valid anymore. As MSDE can only be installed from unattended mode so I can’t rerun setup and fix the problem. Does anyone know a solution for this problem ?? A program recalculating the cheksum ??
I'm not sure if this is the correct forum for this this question but I'll give it a shot.
The only db development that I have ever done is in MS Access. I have a project at work that is being accomplished in VB and I need a db engine to use as the back end. Visual studio came with a copy of MSDE. Is this tool worth using or should I invest in mySQL? Are there any advantages to using MSDE over mySQL?
I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.
Hi,I have transactional replication set up on on of our MS SQL 2000 (SP4)Std Edition database serverBecause of an unfortunate scenario, I had to restore one of thepublication databases. I scripted the replication module and droppedthe publication first. Then did a full restore.When I try to set up the replication thru the script, it created thepublication with the following error messageServer: Msg 2714, Level 16, State 5, Procedure SYNC_FCR ToGPRPTS_GL00100, Line 1There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in thedatabase.It seems the previous replication has set up these system viewsSYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replicationmodule again to see if it drops the views but it didn't.The replication fails with some wired error & complains about thisviews when I try to run the synch..I even tried running the sp_removedbreplication to drop thereplication module, but the views do not seem to disappear.My question is how do I remove these system views or how do I make thereplication work without using these views or create new views.. Whyis this creating those system views in the first place?I would appreciate if anyone can help me fix this issue. Please feelfree to let me know if any additional information or scripts needed.Thanks in advance..Regards,Aravin Rajendra.
In my production box is running on SQL7.0 with Merge replication and i want add one more table and i want add one more column existing replication table. Any body guide me how to add .This is very urgent Regards Don
DBCC OPENTRAN shows "REPLICATION" on a server that is not configured for replication. The transaction log is almost as large as the database (40GB) with a Simple recovery model. I would like to find out how the log can be truncated in such a situation.
Hello,I'm getting the following error message when I try add a row using aStored Procedure."The identity range managed by replication is full and must be updatedby a replication agent".I read up on the subject and have tried the following solutionsaccording to MSDN without any luck.(http://support.Microsoft.com/kb/304706 )sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/library/aa239401(SQL.80).aspx ) has no effectFor Testing:I've reloaded everything from scratch, created the pulications from byrunning the sql scripts generated,created replication snapshots andstarted the agents.I've checked the current Identity values in the Agent Table:DBCC CHECKIDENT ('Agent', NORESEED)Checking identity information: current identity value '18606', currentcolumn value '18606'.I check the Table to make sure there will be no conflicts with theprimary key:SELECT AgentID FROM Agent ORDER BY AgentID DESC18603 is the largest AgentID in the table.Using the Table Article Properties in the Publications PropertiesDialog, I can see values of:Range Size at Publisher: 100,000Range Size at Subscribers: 100New range @ percentage: 80In my mind this means that the Publisher will assign a new range whenthe Current Indentity value goes over 80,000?The Identity range for this table cannot be exhausted! I'm not surewhat to try next.Please! any insight will be of great help!Regards,Bm
I have a VB.net app that access a SQL Express database. I have transactional repliaction set up on a SQL 2000 database (the publisher) and a pull subscription from the VB.net app. I use RMO in the VB app to connect to the publisher. My problem is I am getting some strange behaviour as follows
- if I run the app and invoke the pull subscription it works fine. If I then close my app and go back in, I can access my data without any problem
- If I run the app and try to access data in my SQL Express database it works fine. I can then close the app, reopen it and run the pull subscription it works fine
however.......
- if I run the app, invoke the pull subscription (which runs fine), and then try to access data in my local SQL Express database without firstly closing and reopening the app, I get a login error
- if I run the app, try to access data in my local SQL Express database (which works fine), and then try to run the pull subscription I get a "the process cannot acces the file as it is being used by another process" error. In this case I need to restart the SQL Express service to be able to run replication again.
I get exactly the same behaviour when I use the Windows Sync tool (with my app open at the same time) instead of my RMO code to replicate the data.
I am using standard ADO.Net 2 code to access my SQL Express data in the app and closing all connections etc
I have recently setup a transactional replication in MS SQL 2000. After setting up the replication the clients TempDB grew by almost 60GB. Now the client is Blaming me for the TempDB GROWTH and saying that its because of the replication being setup i tried to convince them but they are not satisfied yet. Can anybody please tell me does replication cause the tempdb to grow. If yes then how. can u suggest any good link for getting to know the internal working of SQL Server replication????
I know that adding a column using ALTER TABLE to add a column automatically allows SQLSERVER 2005 to replicate the schema changes to the subscribers, however, I would like to add a new column to an existing article that is being used for merge replication, however, I don't want this column to be replicated. Re-initialising the subscriptions is not a option. Help would be appreciated.
I have been researching on the proper steps or sequence to follow to completely remove SQL Server 2012 Transactional Replication. Â I have read articles about using SSMS as well as using replication stored procedures and some procedures use SQLCMD or just regular TSQL executed in SSMS. Â I have also read articles where people said all you really need is connect to the Publisher instance, find the publication you want to remove and choose "Delete" and everything will be taken care of behind the scene. I have three SQL servers that participate in transactional replication. Â SQL-P (publisher),Â
SQL-D (distributor) and SQL-S (subscriber). Â Do I need to connect to the distributor instance and the subscriber instance when removing transactional replication or is it just really connecting to the publisher and click delete on the publication? I want everything gone including any metadata, systems tables, distributions db and any other replication objects created during the initial configuration.
Hello everyone,I am involved in a scenario where there is a huge (SQL Server 2005)production database containing tables that are updated multiple timesper second. End-user reports need to be generated against the data inthis database, and so the powers-that-be came to the conclusion that areporting database is necessary in order to offload report processingfrom production; of course, this means that data will have to bereplicated to the reporting database. However, we do not need all ofthe data in the production database, and perhaps a filtering criteriacan be established where only certain rows are replicated over to thereporting database as they're inserted (and possibly updated/deleted).The current though process is that the programmers designing thequeries/reports will know exactly what data they need from productionand be able to modify the replication criteria as needed. For example,programmer A might write a report where the data he needs can beexpressed in a simple replication criteria for table T where column X= "WOOD" and column Y = "MAHOGANY". Programmer B might come along amonth later and write a report whose relies on the same table T wherecolumn X = "METAL" and column Z in (12, 24, 36). Programmer B willhave to modify Programmer A's replication criteria in such a way as toaccomodate both reports, in this case something like "Copy rows fromtable T where (col X = "WOOD" and col Y = "MAHOGANY") or (col X ="METAL" and col Z in (12, 24, 36))". The example I gave is reallytrivial of course but is sufficient to give you an idea of what thecurrent thought-process is.I assume that this is a requirement that many of you may haveencountered in the past and I am wondering what solutions you wereable to come up with. Personally, I believe that the above method isprone to error (in this case the use of triggers to specifyreplication criteria) and I'd much rather use replication services tocopy tables in their entirety. However, this does not seem to be anoption in my case due to the sheer size of certain tables. Is thereanything out there that performs replication based on complexprogrammer defined criteria? Are triggers a viable alternative? Anyalternative out-of-the-box solutions?Any feedback would be appreciated.Regards!Anthony
I am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.
This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??
Example... Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??
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 looking for some guidance here. I have database which has exceeded the 2 gig limit. I deleted some old tables in an attempt to acquire some space. This worked out. However when I look at the size of my *.mdf it still appears in excess of the 2 gigs . I know this isn't true since I deleted 500 megs of stuff. How do i reclaim the space (get the file size to report correctly) or do I need to even worry about it.
I created an instance which is NETSDK I created a password I don't know why my server choose the name FRANK i was expecting local. I have to use FRANKNETSDK or else i can't get my server to Start.
This is the error: Login failed for user 'FRANKASPNET'.
[SqlException: Login failed for user 'FRANKASPNET'.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +474 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372 System.Data.SqlClient.SqlConnection.Open() +384 sqlconnect.WebForm1.Page_Load(Object sender, EventArgs e) in c:inetpubwwwrootsqlconnectwebform1.aspx.cs:27 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +35 System.Web.UI.Page.ProcessRequestMain() +731
For quite some time nowI have been trying to give user rights to my MSDE , needless to say the fruits of my labor have turned up very little success.
So far i have tried using the osql commands to grant user access to my database. I have run the stored procedures(sp_grantlogin ,sp_grantdbaccess ) but to little avail!
So now i have taken a new approach i installed the Enterprise Manager. I have just a minor problem with this new strategy , i don't know how to use it!!
This is what i have prensently installed on my system: Visual studio.Net ,Microsoft SQL(MSDE) and finally the Enterprise Manager.
My Servername is FRANK. I have created an instance which is NetSDK (FRANKNETSDK) My SAPWD is Kildare I have Mixed Mode
Can anyone please tell me how i can use the Enterprise Manager to grant access to MSDE. I am sure you have all realized from all these insipid post that i have no experience. So please be explicit in all your details.
Having Problem connecting to Database with WebMatrix
When I try to ADD Database in WebMatrix through Sql Connection & enter USERNAME & PASSWORD it fails. Is there a way to create New USERNAME & PASSWORD? Perhaps through the Command Line Prompt?
I have never done anything with databases, so I am a newbie. My goal is to install an ASP.NET forum, but I need MSDE installed. I downloaded MSDE from the Microsoft website. When I run the setup.exe file, I get an error. It says:
A strong SA password is required for security reasons. Please use SAPWD switch to supply same. Refer to readme for more details. Setup will now exit.
I looked at the documentation but could not find anything usefull, what do I have to do to install it. Thanks in advance for responses.
I am newbie in ASP.NET and MSDE arena. I understand that MSDE has no GUI and I need to have MSDE tools to work with it. I have already installed ASP.NET 1.1 and Microsoft.NET SDK 1.1 and MSDE in my Windown Professional 2000 PC and I have run and tested for .aspx and its working perfectly. But I want to use MSDE to create some database driven web applications.
Could you please help me how I can do that? BTW, I installed Microsoft SQL Web Data Administrator and its giving me SQLWebAdminStart.exe Application error and asking for debugging while opening the program. I am stranded what to do now?
