---PULL Replication With SQL Server Express, It Is Possible Whether Or Not?---
Nov 14, 2006
Hello!
Question of such plan, to documentation to Microsoft SQL Server 2005 Express Edition it is written, that the given product supports only PUSH replication, however in his{its} structure there is an agent replmerg.exe which description says:
replication merge. Synchronization means, that the agent of merge transfers changes from the subscriber to the publisher, and then transfers changes from the publisher on the subscriber €¦
And so the question if this agent is present at this edition, whether that is possible to adjust manually his{its} call, or through Windows the agent, for realization PULL replication or it is impossible and for what reasons?
SELECT P.Publication ,P.Publication_type ,S.Subscriber_ID ,S.Update_Mode FROM MSPublications P INNER JOIN MSSubscriptions S ON P.Publication_ID = S.Publication_ID
give me publication_type=0. So it is transactional replication but how do we know that is pull or push?
I seem to be missing something. I'm trying to pull a subscription from SQL Server 2000. (To a desktop SQL 2000 install <msde i guess>) The general error I keep running into is When creating the subscription...
"Error 15004: Name cannot be NULL"
I cannot figure out where this problem originates. I'm running the SQL Service account and Executive under a domain account. (same one on both machines)
Does anyone know where I'm missing the Permissions for the subscription?
Please impart any Replication suggestions you have. Don't hold back....
hi, I want to setup transactional replication(PULL) between 2 servers . can anyone guide me with the steps involved while performaing a Pull replication from server1 to server2. Any help appreciated.
Q1: Does Sql Server 2005 Express support Web/Internet to other SQL Server 2005 Express Clients or does it have to Synch across the internet to a fully installed setup SQL Server 2005 with IIS?
Q2: Does SQL Server 2005 Express support Direct Replication between other SQL Server 2005 Express clients?
I have setup a pull subscription on the subscriber for transactional replication. The distribution job continues to run, but doesn't do anything because it says: The initial snapshot for publication 'man4' is not yet available.
How can I tell if the snapshot is running, and can I see the snapshot where it was created?
I have the following situation. I have a single publication on my publisher Server. This publication is created using SQL Server Manager. Snapshot is created completly. Now I want several Pull subscriptions from several machines to work with this publication (One subscription per machine). I'm creating these subscription using RMO. I'm synchronizing data using RMO again. My code workflow is: Syncronize -> success -> do nothing -> fail -> Check if everything with Publisher and Publication is ok -> Generate Snapshot -> Create Subsscription -> Sync again. (I tried to eliminate Generate Snapshot step but couldn't because I receive error that I must rerun Snapshot Generation.)
When I tested my code per single machine it's working. Next test I tried was to run my program on 2 machines simultaneously.
The result is:
---> System.Data.SqlClient.SqlException: Another snapshot agent for the subscription(s) is running or the server is working on a previous request by the same agent.
or:
---> System.Data.SqlClient.SqlException: Unable to acquire the replication merge administrative application lock for database 'XXX'. This could be due an active snapshot running while the schema change (DDL) or the administrative proc change was attempted. Replication merge admin stored procedure 'sp_changemergepublication' failed for publication 'YYY'. This could be due an active snapshot running while the admin proc was called.
The problem is obvious but because I'm new to the replication I'm not sure if I did general mistake in what I want to achieve. Any advices how can I fix my problem will be highly appreciated.
I am working on a replication setup using transactional replication using with pull subscriptions and a separate distributor. The pull subscriptions are located on a SQL cluster using the virtual SQL Server name as the subscriber; when the box fails over, we get an error of missing replication.dlls. Researching further, we found that replication only works on one node of the subscriber.
I created transactional replication on a database and setup pull subscriptions on each subscriber to run at a scheduled time once a day. The scheduled start time on each subscriber can differ. The transaction log on the publishing database will eventually consume all possible disk space. Is it possible (and safe) to shrink or truncate the transaction log file for the publishing database before all the subscribers completed running its daily pull subscription? If not, how can I manage disk space for the transaction log on the publishing database and ensure all transaction are replicated to the subscriber?
Our current application which uses SQL 2000/CE uses a single Merge publication to populate numerous "read only" tables on the mobile device, and create the schema for a "transactions" table, which will be filled by the mobile device during work, then merged at sync.
Now, the master "transactions" table at the publisher has millions of rows, and the mobile device does not need any of them... so currently we have it "filtering" by a always false clause, so that none of the data is pushed to the client.
The problem with this is that we cannot archive any of the records in the transaction table, as doing so (say, removing 200k rows) would result in the mobile devices (of which there are about 15,000 active in the field) to attempt to reconcile each of those 200k rows when it is next synced, which takes way too long.
Now, what we would like to do is upgrade to SQL 2005/CE 2005, if the new features will allow us a PULL ONLY table in some way? I cannot find how to do this via the wizard, but am sure I am missing something. At least I hope so.
I have the report sever running on windows2003 with sql standard server 2005. However, I' m trying to create a report that pull data from a different server with sql express. Could I do this? If so...any links, examples, suggestion I can take a look at.
We have setup transactional replication between 2 databases on SQL Server 2000 SP3a (~70GB), using a concurrent snapshot (to prevent locking out of the live database) to initilaise the data and a pull subscription from the second database.
From analysing the msdistribution_history table in the distribution database on the subscriber it appears that the snapshot is being applied in a continuous loop to the subscriber database. Viewing the comments column in the msdistribution_history table we can see the following sequence of events occuring
Initialising Applied script 'snapshot.pre' Then it applies all the schema files .sch Then it applies all the index files .idx The it bulk copies the data in (bcp) Then it creates the Primary Keys Then it applies all the trigger files .trg Then it applies all the referential integrity files .dri
These all complete successfully but then the process kicks off again immediately after reapplying the snapshot. We are unaware of any settings that may be causing this.
Any help on what maybe causing this would be much appreciated.
I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:
Code Snippet
using (SqlCeEngine engine = new SqlCeEngine(connStr))
the sqlcesa30.dll cannot connect to SQL Server database.
In the sqlcesa30.log then I found following line:
Code Snippet
2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456
The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.
So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?
Hi All I am trying to sync sql server file on two separate computers running SQL Server 2005 Express. I am writing an application (VB.NET) and I am trying to update the other computers when data is changed. Is it possible?
I am running a local version of SQL Server 2005 Express and SQL Server Management Studio Express Edition. I have a web app using the database and I need to migrate the app, the database and database objects to my outsourced host. What is the best way to do this, since the replication and packaging capabilities are limited. Is there a standard, simple way to do this?
I want to replicate data from sql server 2000 to a sql server express 2005 box.
I can dts data across..but I want to be able to set up push replication from the Sql Server 2000 box, but the option to push the data to the Sql Express server in the GUI does not come up only Sql Server 2000 boxes are listed. Initially I will be using Snapshot replication but would like to eventually move on to transactional rep if only I can get it to push the data across.
Does anyone know how to acheive this thanks for any help Sammy.
Wondering if anyone has any experience with SQL Server Express Edition (SSEXP). We're looking at a mobile sales force type model, so a local database on a laptop with no real time network connection. So the users would collect data locally, then connect up to the network every few days to replicate the data to a central server. So questions.. Has anyone tried anything similar? How stable/mature is SSEXP? Any other thoughts, alternatives or gotchas anyone can think of?
I need to synchronize the data between two SQL Server 2005 Express.
The database is really simple, just few tables (but data are inserted very often) without any triggers.
Since Express edition could not behave as a Publisher I was thinking about implementing manually a sort of Replication mechanism... It should be a sort of Transactional Replication with queueud updating.
Does it sound a good idea? Has anyone done this before o there are better solution?
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 need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?
We have a large number of clients attempting to replicate two publications on 2005 Express databases (2 publications subscribed to the one subscriber database) with our 2005 Server (9.00.3042.00 SP2 Standard Edition) and experiencing two significant problems:
1) Users experience the following message:
The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Subscriber for changes not yet sent to the Publisher. You must reinitialize the subscription (without upload).
This problem should not apparently occur with SQL Server 2005 (or 2005 Express) instances with SP2 applied. All clients experiencing this problem have SP2 installed as does our Server and the retention period is 30 days. The subscribers have been replicating well under that.
2) Replications never succeed after appearing to replicate/loop around for hours
This issue is the most critical as we have clients who have been installed and re-installed with new instances of SQL Server 2005 Express, new empty databases (on subscriber before snapshot extraction), and using fresh snapshots (less than an few hours old) which cannot successfully replicate.
Interestingly there is at least 1 instance where several computers are subscribed and successfully replicating the same database as another where replication refuses to succeed.
To test we have taken a republished database from another 2005 Server which is working fine and restored it to the same server as the one holding the database with which we are experiencing problems and subscribed to it. This test worked fine and replication of both publications went through fast and repeatedly without showing any signs of problem.
This indicates that the problem is perhaps data related as it appears localised to that database.
Below are two screenshots which may assist.
Screenshot 1 Shows that on the server side the replication attempts look like they are succeeding despite the fact that the subscriber end does not indicate success. Also the history indicates the the subscription has spent all it's time initialising and not merging any changes.
Screenshot 2 Shows a rogue process which has appears on many of the problem child subscribers. It shows a process running with no end time even though the job indicates failure in the message and even though other replication attempts appear to have succeeded after it. This process stays in the history showing that it is running even when I can find no corresponding process for it.
Can anyone suggest a further course of action/further testing/further information required which may assist?
This is extremely urgent and any assistance would be greatly appreciated!
Hi, In my application i am storing the data in a database(server A). And i have a scenario where in a middle server(server B) is there in which i need to create a table and pull the data from one of the tables from my original database server(server A) into second database server(server B). Where can i get the code to write a job script which can connect from server B to server A and pull the data from the table and delete that data from the table in server A. Can any give me the code or link where i can find the code. Please help. Thanks
I run a query from Excel to pull the data from SQL server and I have created another worksheet to add my columns for different analysis and costs. When someone add another line in SQL against an order or a customer then manually added columns data goes out of sync.Is there anyway when I refresh the RAWDATA or anything changes on SQL table then it should sync my formatted worksheet.
So if someone makes a change to a vendor address, it creates a new row with the new address and a new "RowVersion" number is created. Right now this query will pull both addresses. I need it to pull the row with the highest rowversion number. The datatype for the RowVersion column is "int", and VEND_ID I believe is the unique identifier for the rowversion.
Could someone give me the general syntax to pull xml data from a web server that I have access to? I do know that I can not use an ip address, I have to use the hostname, etc.
I was using something like
FROM OPENROWSET(BULK '\SERVERNAMESHAREPATHDATAFILE.XML', SINGLE_BLOB) as x
instead of using SERVERNAME I am user http:\www.mycompanyname.commycompanynamestoredata.xml
I do not understand why this does not work. I hope I gave enough information for this.
