Setting Up Publication For Merge Replication Using Stored Procedures
Aug 2, 2006
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
View 4 Replies
ADVERTISEMENT
Sep 19, 2007
Hi,
I created a .sdf file thru merge replication.
On the step of publication, in the articles object there is an option to select from 1) Tables,views 2) and stored procedures from the databse.
How these two are diferent from each other?
My aim is to use Stored procedures extensively so that I can avoid SQL code in device.
When we use this .sdf file in active sync,we only getting the tables, and no sp's and views.
Is it possible to accept sp's and view from sdf in pocketPC?
I am using SQL server2005 on server , Windows SQL Mobile 2005 in pocket pc 2003 using Compact Framework 2.0
Please advice..
aneesh
View 6 Replies
View Related
Mar 15, 2007
I tried to drop a merge replication publication and it is giving me an invalid object name dbo.ms_mergeagents message. I tried to drop using:
SSMS
sp_dropmergepublication
sp_replicationdboption
I got the last two from Microsoft KB article 324401. The subscription was never created because it gave another error invalid object_name msmerge_subscriptions. I can create new publications but not drop the existing ones.
Please assist...
View 1 Replies
View Related
Jun 7, 2006
I am having a problem with merge replication after recreating a publication. It is a simple two-way replication between two servers allowing applications to update data at both ends, i.e. 1 publisher, 1 subscriber for all tables except some junk ones. The problem started after I did the following:
1) I dropped and recreated the publication to allow for some changes to the database schema. These caused problems so I dropped the subscription and publication and restored the databases at each end from backups taken before we started.
2) When I created the publication again, it wouldn't let me use the same name as it thought the publication already existed. It let me use a different name instead.
3) Most things work OK but one of the tables is not replicating inserts from the subscriber to the publisher. There are no errors and no conflicts, other similar tables replicate OK. Inserts go across the other way OK.
4) I am getting replication conflicts on another table that says 'Unable to synchronize the row because the row was updated by a different process outside of replication'
I believe the problem is to do with the original publication details still being in the restored databases, so am looking to drop the publication & subscription, remove the remnants of the old publication, fix the data and recreate pub & sub. What do I need to do to get rid of the old replication data in the database?
Any help much appreciated.
View 4 Replies
View Related
Jan 3, 2008
Hi,
I am stuck when trying to create a new publication. I fill all the wizard tabs but when finishing I get that message (6 times, one for every table):
Code Block
SQL Server Management Studio could not create article 'applicationModules'. (New Publication Wizard)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
This article cannot use the 'subscriber_upload_options' feature because the publication compatibility level is less than 90. Use sp_changemergepublication to set the publication_compatibility_level of publication 'testMR' to '90RTM'.
Changed database context to 'test'.
Publication 'testMR' has the following property: 'compensate_for_errors'. SQL Server subscribers below version '8.00.0858' will ignore this setting. (Microsoft SQL Server, Error: 21522)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21522&LinkId=20476
The error seemed pretty clear, so I end up doing:
EXEC sp_changemergepublication
@publication = N'testMR',
@property = N'publication_compatibility_level',
@value = N'90RTM'
and running the wizard again.
But I got the same error.
The link on the message take me nowhere.
and by googling for error 21522 I couldn't find any help at all.
Please, could anyone help?
Cheers
View 3 Replies
View Related
Nov 9, 2006
I'm trying to setup merge replication and get the following error from the snapshot agent after I've finished the 'New Publication Wizard'. This doesn't make sense to me as it is an automatic process. Also, it looks like it is complaining about a 'dbo.MSmerge_cont...' view which is a view created automatically for the merge publication.
ERROR MESSAGE:
The identifier that starts with '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC' is too long. Maximum length is 128.
FULL DETAILS:
Message: The identifier that starts with '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC' is too long. Maximum length is 128.
Command Text: IF OBJECT_ID(N'[<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT AUTHORITYSYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_90 as select * from MSmerge_contents where 1 = 2</CommandText></TSQLCommand></EVENT_INSTANCE>]', 'V') IS NOT NULL DROP VIEW [<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT AUTHORITYSYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_90 as select * from MSmerge_contents where 1 = 2</CommandText></TSQLCommand></EVENT_INSTANCE>]
Parameters:
Stack: at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command)
at Microsoft.SqlServer.Replication.AgentCore.AgentExecuteNonQuery(SqlCommand command, Int32 queryTimeout)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate, Int32 queryTimeout)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteAdHocQueryDiscardResults(String strQuery, SqlConnection connection)
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.GenerateSystemTableBcpFiles()
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoPostArticleFilesGenerationProcessing()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 103)
Get help: http://help/103
Server XF4SWCS464, Level 15, State 4, Procedure , Line 1
The identifier that starts with '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC' is too long. Maximum length is 128. (Source: MSSQLServer, Error number: 103)
Get help: http://help/103
View 3 Replies
View Related
Aug 15, 2007
Hi,
I am trying to set up Web Synchronization using Merge Replication. The Distributor and Publisher are on the same machine. Subscriber is a windows mobile application which syncs the data using web synchronization. I have configured IIS on web server for the sync. However there is no domain user on database and web server. Is it possible set up web sync without a domain user? Which user can I then use to give permissions on the snapshot share folder.
Any help is appreciated.
Thanks
View 8 Replies
View Related
Feb 2, 2007
Hi All;
I am trying to set up Merge Replication on a database and want to set the IdentityRange Management to Auto for all my tables which use a Identity column.
In the wizard, on Article Properties Page, I can do this by selecting a Table, and going for its properties, but this is a tedious task as I have ~300 tables to set this property on.
Is there another way or a global location where I can set the property to true and even mention the ranges and the threshold, so that I dont have to pick each table and set it individually.
I am also aware of the fact that I can Generate a Script and modify it and run that, but I was looking for some way to do this in the wizard.
Thanks!
View 1 Replies
View Related
Nov 28, 2007
Greetings
Running SQL Server 2005. The developers on the project can see and edit stored procedures from within the Visual Studio IDE (via Server Explorer) but when they connect through management studio, the stored procedures do not show up at all.
Is there a seperate security setting specifically for management studio?
The user has:
The dbCreator Server Role
Is mapped to the development database as dbo with datareader/datawriter/db owner/public role
Is mapped to master reader/writer/public
Is mapped to model reader/writer public
Is mapped to msdb reader/writer public
Is mapped to tempdb reader/writer publuc
This is probably more security than the user needs, but was grasping at straws to let them edit stored procedures...
View 4 Replies
View Related
Jul 13, 2006
Hello,
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?
Thanks
View 3 Replies
View Related
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....
Thanking all of you in anticipation...........
Mandar
View 1 Replies
View Related
Mar 7, 2001
Hi,
I disabled a merge publication on my server. I then ran sp_removedbreplication to clean the system tables. Thats fine. When I recreate a new publication on my server now, I still get the red x on my publisher node in my replcation monitor from the error I was getting before. Evenm though this publication is error free. Is it saving an srror flag in one of the sytem tabbles
Any ideas on this?
Thanks
View 1 Replies
View Related
Feb 16, 2000
I'm new to Replication and trying to understand some basic concepts. What I really want to do is replicate a View such that it shows as a table in the subscriber database, but it appears as though I cannot do that. First, is that correct? Second, since it appears as though Stored Procedures can be replicated, I was wondering if I could accomplish the same thing via that route. However, I am unclear: does the SP itself replicate? Does replication execute the SP? If the SP simply does a query, are the query results replicated?
I know that I can replicate subsets of tables; limiting rows and columns. This is part of what I wanted the view to do, however I also wanted to be able to include joins.
Thanks for any help anyone can provide this newbie!
View 1 Replies
View Related
Oct 25, 2007
.Net Stored Procedures & Replication
Can you do it? I have some transformation and business logic I need to place in the transactional replication process.
Just curious if its possible.
BTW, I know I should not be doing transformation during transactional replication. At the moment the client wants realtime updates to a central repository thought transactional replication.
Thanks in advance!
Bryan
View 1 Replies
View Related
Apr 22, 2004
I am running merge replication (SQL 2000 with SP2) with an anonymous pull subscription. The application vendor has come out with update that requires adding a table to a database. The vendor has created scripts that will add the table, as well as some stored procedures. If I apply the scripts to both servers and add the table as a new article to the publication, am I going to have to apply a snapshot of the entire database (which is very large)?
Your help is greatly appreciated.
Gary
View 3 Replies
View Related
Jan 10, 2006
Help!!! This is really frustrating me down!
I was trying to follow the MS official tutorial "Creating a mobile application with SQL server mobile" could be found in this link
http://msdn2.microsoft.com/en-us/library/ms171908.aspx
In the step of creating a merge publication, as I noticed, the snapshot agent is started sucessfully. But very soon later (few seconds), it was shut down with an error message. I checked in the error history, the following message I found:
"... The replication agent encountered a failure. The step failed"
I also went to msdb.dbo.sysjobsteps and found "Detected nonlogged agent shutdown".
Anyone could help?
View 7 Replies
View Related
Oct 20, 1998
Has anyone used custom stored procedures with multiple tables? or can you only use them with one.
View 1 Replies
View Related
Oct 22, 2001
Hello all. I have a question, but first the setting.
I have one Publisher pushing out to 4 subscriptions, all are SQL 2000 Servers. The tables all replicate like they are supposed to. My issue is that if I make changes to a stored procedure, I have to physically go to each subscription and make the same changes. I included the stored procedures when I set up the publication.
Now the question, is there an easier way? I want to be able to make the changes on the publisher and the changes to replicate to the other servers.
Thanks!!!!!
Kameron
View 1 Replies
View Related
Nov 22, 2007
I have a system with three replications:
merge replication between SERVER and CLIENT
transactional replication from SERVER to CLIENT
transactional replication from CLIENT to SERVER
SERVER is always distributor.
Everything has been running fine for over a week now, but today something went wrong with the second transactional replication (from CLIENT to SERVER). SERVER is distributor and CLIENT is publisher. The subscription is push.
When I view the synchronization status, the following error message appears:
"Could not find stored procedure 'sp_MSins_dbosessions'."
Indeed, when I look at the stored procedure, it has been renamed to
'sp_MSins_dbosessions_msrepl_ccs'.
I don't know why, when or how this renaming happened, but it happened for every stored procedure that is used for this transactional replication (the other replications are still working fine)
Any help or remarks are highly appreciated.
Roel
View 11 Replies
View Related
May 4, 2006
We have merge replication running with anamous subscribers
We have generested lots of views tables and stored procedures like
sp_ins_C435D35DDEC04FE2517CCD52A9024EC4
ctsv_07BA7383A12B4654B4D3A69B3053B227
aonflict_DH_tblReplicationRegion
How do we get rud of these I am concerned it will fill up the publisher database
Can any one advise
View 1 Replies
View Related
Oct 24, 2014
I'm updating some tables in a subscriber database with a stored procedure. After the tables get updated I'd like to sync them with the other subscriber dbs and the publisher db in that same stored procedure.I can do it manually in SSMS with the View Synchronization method. Are my only alternatives a batch job or C#?
View 1 Replies
View Related
Apr 17, 2007
Hello
I've got the following problem: Our product is delivered with SQL2000 and SQL2005.
Now, there are some schema changes, which I'd like to deploy with T-SQL on the publishers. With SQL2000 I do it with the sp_addmergecolumn etc and on SQL 2005 (if replication compatibility level is 90) with replication of DDL. So far so good.
But how can my T-SQL Script determine, wether the replication of DDL is on? I know there exists sp_helpmergepublication, but how do I get the column replicate_ddl of the result-set?
thanks for your advices.
Aline
View 5 Replies
View Related
Apr 19, 2007
Hi all,
I have a merge publisher with Snapshot agent for that scheduled to run everyday morning, This was runnning till yesterday. But it is not running today and giving the below error.
Can anyone help me out to solve this error? This is occuring in production environment, i really need a solution as fast as possible.
Error messages:
· Message: Failed to read column data
Stack: at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowNativeBcpOutException(CConnection* pNativeConnectionWrapper)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strBaseBcpObjectName, Boolean fUnicodeConversion, String strDataFile, String strLoadOrderingHint, String strWhereClause)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strBaseBcpObjectName, Boolean fUnicodeConversion, String strDataFile, String strLoadOrderingHint)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strDataFile)
at Microsoft.SqlServer.Replication.Snapshot.MergeContentsBcpOutWorkItem.DoWork(MergeSnapshotProvider snapshotProvider, IBcpOutProvider bcpOutProvider)
at Microsoft.SqlServer.Replication.Snapshot.MergeContentsBcpOutThreadProvider.DoWork(WorkItem workItem)
at Microsoft.SqlServer.Replication.WorkerThread.NonExceptionBasedAgentThreadProc()
at Microsoft.SqlServer.Replication.WorkerThread.AgentThreadProc()
at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper() (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
· Message: Shared Memory Provider: No process is on the other end of the pipe.
Stack: (Source: MSSQLServer, Error number: 233)
Get help: http://help/233
· Message: Communication link failure
Stack: (Source: MSSQLServer, Error number: 233)
Get help: http://help/233
· Message: Shared Memory Provider: No process is on the other end of the pipe.
Stack: (Source: MSSQLServer, Error number: 233)
Get help: http://help/233
Message: Communication link failure
Stack: (Source: MSSQLServer, Error number: 233)
Thanks,
Thams
View 16 Replies
View Related
Sep 19, 2006
I am new to replication but I have a question, I have my server which is using a transaction replication process and a client with using SQL Express with replication turned on. I have the identical DB on both nodes yet when I subsrcibe to the server from the client, data is transfer but for some reason, all of my stored procedures are renamed with sp_MSDel_DBOxxxx. How do I keep SQL from renaming these SP's? The server maintains the origial names to the SPs its just happening on my client.
Thanks
View 5 Replies
View Related
Jun 4, 2007
Has anyone had problems with this limitation before or could explain to me what the purpose of this limit is for? It obviously does allow you to publish more than 246 (we have been publishing more than 700 for years). Is this new with SQL 2005? Is my publication going to stop one day and tell me there are too many articles?
Thanks in advance.
Martin
View 15 Replies
View Related
Jun 8, 2004
how to implement the replication with the System Stored Procedures or the Transact-SQL ?
i want to encapsula my replication and make it be reusable and ease to use.So i want to do the replication in a script file.how can i do this?
Thanks.
View 9 Replies
View Related
Aug 15, 2001
I am running MS SQL Server 7 with SP3 installed and am having some problems getting replication to work correctly. I want to replicate all tables from one database to another database on the same server. I can setup replication, add publications and add subscriptions without any problem and all goes well until the replication process starts when the job aborts because it cannot find the sp_MSins..., sp_MSupd... or the sp_MSdel... stored procedures. I have used the New Publication wizard to create a Transactional publication, published all tables and allowed the wizard to use the default stored procedures which it says will be created when the subscribers are initialized but although I have created push subcriptions the stored procedures are never created and hence the replication fails whenever the job runs.
I have SA access to the databases concerned and as far as I am aware the jobs are running as my login - can anybody help me out as this is beginning to drive me nuts.
Thanks
Chris
View 1 Replies
View Related
Jul 20, 2015
Replication-Replication Distribution Subsystem: agent PRODDATA6 failed. Could not find stored procedure 'sp_MSdel_dboActiveTask_msrepl_ccs'.
Replication-Replication Transaction-Log Reader Subsystem: agent PRODDATA6. The process could not execute 'sp_replcmds' on 'PRODDATA6'.
Replication-Replication Distribution Subsystem: agent PRODDATA6. Could not find stored procedure 'sp_MSupd_dboActiveTask'.
Looks like these sp's got deleted?
View 1 Replies
View Related
Aug 9, 2006
Hi Mate !
I was searching to enable replication publication on my SQL 2005 Express, but as far as I look, this feature don't seem to be included.
Am I, correct?
Is there a way to enable this fearture?
Thanks
Zak
View 1 Replies
View Related
May 14, 2008
:mad:
I have one Publisher! (SQL 2005)
Two Subscribers. (SQL 2005)
Each subscriber requires different row filter from the published tables.
So, i need to create Two publications for each subscriber for the same db.
When i setup first publication, and apply filters to published tables.
All goes fine.
As soon i create an other publication with same published tables but DIFFERENT row filter; the log reader agent fails!
The process could not execute 'sp_replcmds' on 'DB1'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
Execution of filter stored procedure 2076702271 failed. See the SQL Server errorlog for more information. (Source: MSSQLServer, Error number: 18764)
Get help: http://help/18764
The Log Reader Agent failed to construct a replicated command from log sequence number (LSN) {0008443a:000003b5:003e}. Back up the publication database and contact Customer Support Services. (Source: MSSQLServer, Error number: 18805)
:confused:
I have tried to re-initialze the subscriptions and re-run snapshots but no luck.
As i remove the row filter from the second publication; log reader starts working fine.
Previously i was using Merge replication and all was going BUT, on very large DML on publisher, we use to receive deadlock errors on application.
I decided to use Tran repl instead because log reader reads the changes from tran-log instead of locking the published tables; NOW I AM STUCK!
Ideas please!!!!
View 5 Replies
View Related
Jul 23, 2005
Hi,I am trying to use Enterprise Manager Wizard to create a newPublication database but I got the following message.Error 2812: Could not find stored procedureMyDB.dbo.sp_MSadd_logreader_agentThere seems that some replication stored procedures.Any help?Thanks in Advance
View 1 Replies
View Related
Jun 12, 2006
Is there an easy way to copy a replication publication definition from one server to another? In our environment we have a development environment, testing environment and soon a production environment. Right now keeping the replication definition between the development and testing environments is a manual process, are there tools that can do a diff on this or a way to keep this in sync?
For example when we change what information gets replicated in the development environment I need a way to propogate that change to the testing environment.
I know I can generate scripts from within SQL Server Management Studio, but it creates scripts very machine specific. So short of going through that and modifying the server names to match the different environments, is there a better way?
View 1 Replies
View Related
