HOW To Pull Merge Subscriptions In SQL Server 2000 Using Stored Procedures ??? (URGENT)
Dec 15, 2005
I've successfully carried out pull subscription using Wizard in SQL server 2000.
But NOW its the time to deploy the project & I've to do it programatically........
i tried to use the default stored procedures like "sp_addmergepullsubscription ", "sp_addmergepullsubscription_agent ", "sp_reinitmergepullsubscription ", with respective parameters.
Then using replmerge.exe i tried to execute the batch file But could not succeed......
Can anyone guide me from the Basics ??? (i knw the Replication Architecture its heirarchy....but have NO idea of performing it Programatically......!!!!!!!!)
by BASICS, i mean......how should i execute the resp. stored procedures with what parametes & the order i should follow in order to successfully pull a merge subscription in SQL server 2000.
if a Sample code is mentioned as example, would be better for me to understand....
I have my database: "RequestTrack" My table (with its columns): "Request"RequestKey (automatically generated)..and the Primary KeyEntryDate (datetime)Summary (nvarchar)RequestStatusCodeKey (bigint)EntryUserID (nvarchar)EntryUserEmail (nvarchar)I am wanting to create a basic web form where my user interface has 3 text boxes and a Submit button: txtUserID.TexttxtEmailAddress.TexttxtRequestSummary.Text **After I hit the submit button the information will then be inserted into the database. Also the RequestStatusCodeKey will be MANUALLY typed in so that will not require the user to add that. Please please please help ! I've been searching online for days and looking at various websites and still havent found anything. I've found somethings but they went into too much depth with too much information. I am just wanting to stay basic but w/o using SQLDataSource Controls. I would like to be able to store a lot of data. Thanks for your help!!!
I have set up a pull subscription and nothing seems to come through although everything seems set up correctly. Push subscriptions are working just fine. Any thoughts why push subscriptions work and pull subscriptions do not?
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.
HiI'm running a Windows 2000 SP4 Server which has SQL Server 2000 SP4running.My problem is whenever i try to import a database over from one serverto the other the tables/data etc imports but the stored procedures andviews are not imported over and when i try to select these to importthey do not appear in the list. Any ideas on whats up?Many Thanks in advanceT
What is the overhead of using extended stored procedures?I created a table with 500,000 rows.1) I ran a select on two columns and it runs in about 5 seconds.2) I ran a select on one column and called an UDF (it returns aconstant string) and it takes 10 seconds.3) I ran a select on one column and called a UDF that calls an extendedstored procedure that returns a string and it takes 65 seconds.I also tried running test 3 with 4 concurrent clients and each clienttakes about 120 seconds.
Hello.I am trying to use Transact SQL to prepare publication for replication (SQL Server 2000 SP4 and SQL Mobile). I can easily create publication, add filters etc. but there is one thing which I can't find : parameter which tells me subscriber type and allow me to change subscriber type for publication. I don't know where I can change (or even if I can change) this property. If someone made publication for mobile devices and replication with filters,resolvers etc. will it be a problem to share experiences here? Maybe some sample code with script or something what will guide me. Thanks in advance. Maciej Wysocki
We are trying to be proactive and stop a potential performance issue by reducing the number of recompiles in our SQL 2000 database application. This database is replicated. After viewing output from Profiler and PerfMon it seems that over 90% of the recompiles are due to system stored procedures generated by replication merge agents. Can anything be done about this?
This is a solution for a very specific problem, and it's one that you'll hardly ever use, but it's important to know about that one scenario where it can save your neck. Ordinarily, stored procedures are only recompiled if they're no longer in the procedure cache. But if a stored procedure's execution plan is still in the cache, then SQL Server reuses the compiled storedprocedure and its existing execution plan. This is almost always the best course of action. Almost always, but not always.Sometimes, however, reusing an existing plan doesn't offer the most efficient performance. Imagine, for example, that your stored procedure accepts a parameter that determines the natureof a JOIN operation. The results can vary in a big way, so you wouldn't want your procedure to be locked into an execution plan that might be completely inappropriate for that JOIN. In a highlyspecialized case like this, you might want to force SQL Server to recompile the procedure every time the procedure runs. Doing so comes at a performance cost, but this might be offset by thesavings you gain in not executing the procedure with an awful compiled execution plan. Consider carefully whether to use this approach (or whether to re-engineer the over-design of yourapplication to avoid this situation in the first place). Should you need to instruct SQL Server to recompile each time, add the WITH RECOMPILE directive to the procedure, like this: CREATE PROCEDURE ProcName @Param int /* ... other parameters */ WITH RECOMPILE AS /* ... procedure code follows */ If we omit "WITH RECOMPILE", what will be the consequence? Thanks
I have successfully moved my data from a SQL Server 2000 hosting site to a SQL Server 2005 hosting site. I Made a backup of my database using Enterprise Manager (2000) and imported the database tables using SQL Server Management Studio (2005). I do not know how to move the 25 or so stored procedures that I have in SQL Server 2000. I have a very short amount of time to figure this out and am hoping that someone can give me a brief step by step answer on how to get this done. I would appreciate any information you can provide. Thank you!!
We are developing a production/management solution for the photo finishing sector. We need a performance of 1 order priced per second. If we run the procedure once we dont have a dramatical performance loss due to recompilation of the stored procs. If we have about 3 consecutive sessions we find the performance loss to be at a rate of about 200 - 500 %. We can't afford this. On the site of msdn we found some reasons why sql server needs to recompile, but since the structure of our db can't be changed in such a manner that this would resolve the problem we need an alternative. All help is greatly appriciated.
I need to run stored procedures which are currently run on server A(located in another city),by connecting myself to it through my laptop running on SQL server 7.0(only client components installed)on NT workstaion4.0. Can anyone tell all the steps involved to run those stored procedures everyday.Even I need to monitor Server A from my laptop.Please advice...Urgent!!
Hi all!I am in need of writing a few stored procedures.The first one is to create a stored procedure to recover a databasefrom backup and the second one is to create a stored procedure toexecute a transaction log backup (even though I know this can be donethrough a maintainence plan). Any help would be greatly appreciated.I know the commands to recover and backup -- but I dont know how toformulate them into a stored procedureThanks in advance!
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
How can I list all of the Subscriptions in a CE Database that was created with the .Net System.Data.SqlServerCe.SqlCeReplication.AddSubscription method?
After I create the CE database in an sdf file, I can connect to it with SQL Servr Mgmt Studio, then see the list of Subscriptions in the Object TreeView, under "Replication"
But I can't find that Subscription list in any properties or methods of System.Data.SqlServerCe.SqlCeReplication or System.Data.SqlServerCe.SqlCeConnection
Should I be looking in RMO features like Microsoft.SqlServer.Replication??
We want to confirm that a local CE database was last synced from the expected SQL Server database by checking the Publisher property of each subscription in the CE database.
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
I am trying to enable a replication process between a SQL 2005 server and sqlexpress instances and i run into the following problem.
On the publisher a subscription (sp_addmergesubscription) is added for each 'machine' that participates in the replication. Is there a way to get around this?
Because when we distribute the installation we dont know all the machine names. I have made the publication accesible for anynymous access. But when the replication script is installed on the client ,the Error message that the subscription does not exists appears.
The only difference between the instal script for the client is that we do not add a sp_addmergesubscription on the server. Is it possible to let SQL server make an subscription when the client connects for the first time?
To summarize my question: Is there a way to enable the replication with only running a script on the client and not on the server?
If i need to explain it more in detail , just let me know and ill try
- Transactional (5 articles - tables only) Users receive data based on their territory #, therefore they receive their customers sets of data. It happens that customer change from one territory to another but not frequently. When it happens, so far so good, the data is redirected to the new salesrep using the model we configured (Territory table with SUSER_NAME() to filter the data).
Ok, here's my problem. Since a while, I can see in the replication monitor that some users seems to log the same conflict again and again (Merge process). I mean, checking the history for many subscribers, there is always the same number in the "Conflict" colums. As an example: - Merge completed after processing 18 data change(s) (4 insert(s), 14 update(s), 0 delete(s), 31 conflict(s)) - Merge completed after processing 27 data change(s) (10 insert(s), 17 update(s), 0 delete(s), 31 conflict(s)) - Merge completed after processing 20 data change(s) (5 insert(s), 15 update(s), 0 delete(s), 31 conflict(s))
and so on...(Those are only 3 historical entries for a single subscriptions but there are many like that, always with the same count of conflict - vary per user). It appears to me that the same conflicts come over and over.
The thing is that if I decide to reinitialize a subscription, conflicts will disappear, therefore I know that it is not a process on the server that keeps changing the data; anyway, even if it was, changes would be applied on the subscription because the server always win in my setup.
Any idea what should I do with this? Any help would be greatly appreciated. Thanks.
I doubt this is possible, but can someone think of a way to change the email address used for sending report subscriptions based on the report or subscription?
It's a need that I've heard from a number of different clients. Scenario: a company has one reporting services server with reports running from numerous departments. Report subscriptions are sent to internal and external email addresses and there's a business need to use different "from" addresses based on the report (or audience).
If I want to access or modify my local subscription data(not the configuration) how can I do that(from sql server 2005 or from asp.net)? Also can i update directly to the local subscriptions data or do I need another layer which will update from a table for example??
We are running merge replication, SQL Server 2005 Enterprise with SQL Mobile 2005 (Windows Mobile 5) subscribers. Partitions are filtered on HOST_ID.
Ocassionally we experience a situation where a subscriber experiences an unusually long synchronization duration, and upon examining Replication Monitor, it appears that twice the number of rows, or X the number of rows (up to 7 times the number of rows) that should have been inserted are recorded as synchronized for the session: once the normal amount as inserts and once the normal amount as updates. This occurs for all tables in the subscription. This occurs on a first time synchronization to an empty subscriber database where there should be only inserts taking place.
I have examined the snapshot partition folders for these users on the file system and they appear to be identical in size and content as other subscribers. Checking the last partition snapshot job run and other characteristics for the the subscriber in question, everything appears to be the same as other subscribers functioning normally.
The HOST_ID for us is an employee ID used to filter employee specific data. I have seen this happen if the subscriber changes the value for the HOST_ID used in filtering, after the mobile database has already been populated (2 employees attempt to use the same mobile device and database). But, we have seen this happen recently where the HOST_ID was apparantly never changed midstream.
This just started happening recently. The only modification around the same time frame was the implementation of a custom business logic handler/custom conflict resolver that performs like "Latest Wins" but has logic added to update the a last-update datetime column for selected transaction tables at time of synchronization, so that an SSIS job can detect the changed rows for copying incremental database changes to another application database. This all seems to be working perfectly.
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?
Hi! I use sql 2005 express on my computer for testing. The server that hosts my site is a sql 2000 server. Therefore I tuned the one database I use down to sql 8. When I create stored procedures from web developer express, to the local sql 8 database I have in my sql express, it works nice. But when I copy all my files and my stored procedure to the webhost, it doesn't work.
Do you guys have any suggestions? Thanks in advance.
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.
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.
i have a assignment on hands to transfer store procedures of 2005 to 2000. I have tryed by scripting SPs and then runing them in 2000 QA, but it gives me error every time ....... so please help me to resolve this problem.
