How Can I Create Replication
Jul 15, 2005hi,
View 6 Replies
ADVERTISEMENT
Replication Issues After A Database Restore - Unable To Drop Or Create Transactional Replication
Sep 13, 2007Hi,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.
View 2 Replies View RelatedReplication :: Cannot Create Snapshot
Jun 3, 2015We have a database which was replicated with merge replication. we added a few table and a field to another. When I try to create the snapshot I get:
An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.
I have detached and re-attached the db, removed all triggers from the tables, and still I cannot recreate the snapshot the last step before the error as listed in the monitor is generating all procedures for all table articles 49%.
Replication - Can Not Create Publication Database
Jul 23, 2005Hi,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 RelatedCan Not Create New Replication After Changing Server Name
Dec 5, 2005After i changed my computer's name(eg. the original computer name is 'SERVER-1',I has changed it to 'SERVER-2'),I can not create a new replication in SQL Server 2005.The error message :
View 5 Replies View RelatedRunning Create Replication Script
Feb 25, 2006I am about to replace a SQL Server which is replicated to about 25 computers. The replacement PC (and SQL Server) will have the same name. I intend to generate a Create Replication Script before shutting down the old PC and to run it on the new one.
Should I also create and use a Remove Replication script and run it on the old PC before shutting it down? I am thinking that I might need to do this to remove Replication infromation from the subcribers before running the Create script.
In other words, is it OK (or not OK) to run the Create script with the subcribers already in existence? Is there possibly a way to run the Create script on the sever and skip updating of the subcribers?
Thanks,
Dick Campbell
Replication :: Not Create New Constraint Range For Subscriber
Jul 14, 2015I have a merge replication in place. I increased the identity_range to 100000 for a table. I have done this both ways, via the properties of the publication on SSMS, and via TSQL. I have call sp_adjustpublisheridentityrange. Then I recreated the sanpshot. EXEC sp_adjustpublisheridentityrange @table_name = N'Label', @table_owner = N'caseup';but after synchronization, the range defined in the table's constraint has not changed and now all of the identity values are used up. All inserts are failing.What needs to be done to force the publisher to recreate the identity range on a subscriber(s).
View 3 Replies View RelatedUnable To Create Replication Publisher W2k3sql2000sp3a
Nov 27, 2006Hi,
I`m trying to setup our sql2000 sp3a sql server as a Publisher.When i use the wizard to config Publishing and Distribution wizard. I recieve the following error:
Sql Enterprise manager could not configure server as the distributor. Error 14133 Could not execute instdist.sql. Check instdist.out in the install directory.
I`ve checked the install directory and cannot find the file instdist.out file. I assume that it failed before completeing this stage.
Has anyone come across this error before and any ideas on how to resolve would be appreciated.
Thanks in Advance,
Steve
How To Create Merge Replication With Latest Update Always From Subscribers
May 13, 2006Hi ,
I am trying to create Replication Topology (Merge Replication) like below.
Subscriber1 --> Publisher <-- Subscriber2.
I have created both subscribers with Subscription Type as Server with Priority as 75. I am updating the Column A of Row_10 in Subscriber1 on time say 11 am. After i am running the Starting synchronizing agent from Subscriber1. The value propagated to Publisher now publisher contains the latest value in Column A. Uptonow the Subscriber2 is not synchronized with Publisher.
Now in Subscriber2 also Column A of Row_10 is updated say at 11.10 am. Actually now Publisher contains the value from Subscriber1 for that Column and in Subscriber2 we have the same column updated.
Now i am running the Synchronization in Subscriber2, i am getting the result which is not expected. Here Publisher's value is propagated to Subscriber2. But as per real scenario Subscriber2 has the Latest value which is updated on 11.10 AM.
I don't know what am i missing here. Actually merge replication should see the time stamp and it has to decide winner. But here it always considers publisher as a winner and puts the data to Subscriber.
Can anyone help ?
Thanks in advance
Application Locks Up When Trying To Write To Db After A Replication Where I Create The Database.
Jan 3, 2008Hello all,
When I create a new database and replicate to it using BeginMonitoredBackgroundSync :
Code Block
public void BeginMonitoredBackgroundSync(string User)
{
CreateReplicationInstance(User);
repl.BeginSynchronize(
OnSimplifiedSynchronizeComplete,
SqlCeReplication_OnStartTableUpload,
SqlCeReplication_OnStartTableDownload,
SqlCeReplication_OnSynchronization,
repl);
}
private void CreateReplicationInstance(string User)
{
repl = new SqlCeReplication();
string host = repl.HostName;
repl.HostName = User;
string dbFilePath = "";
dbFilePath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) +
"\" + repl.HostName + ".sdf";
string myConnectionString = string.Format("Data Source = {0};PWD = {1}", dbFilePath, sqlSettings.Items["SqlPassword"]);
repl.InternetUrl = dynamicsReplicationSettings.ReplicationSettingsItems["InternetUrl"];
repl.PublisherSecurityMode = SecurityType.DBAuthentication;
repl.Publisher = dynamicsReplicationSettings.ReplicationSettingsItems["Publisher"];
repl.PublisherDatabase = dynamicsReplicationSettings.ReplicationSettingsItems["PublisherDatabase"];
repl.PublisherLogin = dynamicsReplicationSettings.ReplicationSettingsItems["PublisherLogin"];
repl.PublisherPassword = dynamicsReplicationSettings.ReplicationSettingsItems["PublisherPassword"];
repl.Publication = dynamicsReplicationSettings.ReplicationSettingsItems["Publication"];
repl.Subscriber = "RemoteSubscription" + repl.HostName;
repl.SubscriberConnectionString = myConnectionString;
repl.ConnectionRetryTimeout = 120;
repl.LoginTimeout = 120;
repl.CompressionLevel = 6;
if (File.Exists(dbFilePath))
{
FileInfo info = new FileInfo(dbFilePath);
if (info.Length <= 20480)
{
File.Delete(dbFilePath);
repl.AddSubscription(System.Data.SqlServerCe.AddOption.CreateDatabase);
}
}
else
{
repl.AddSubscription(System.Data.SqlServerCe.AddOption.CreateDatabase);
}
primeConnection();
}
After the replication finishes, I dispose the replication object like so:
Code Block
void OnAsyncSynchronizeComplete(IAsyncResult asyncResult)
{
try
{
repl.EndSynchronize(asyncResult);
if (repl != null)
{
repl.Dispose();
repl = null;
}
if (ReplicationComplete != null) ReplicationComplete(this, true);
}
catch(Exception ex)
{
if (ReplicationComplete != null) ReplicationComplete(ex, false);
}
}
Then later, if I try to update, insert or delete to the database, the application will hang. I can read from it, but I cannot write. If I close the application down and open it back up without replicating, I will not get any lockups. It also will not hang up after any replications prior to the create replication. I think I am doing something wrong in the initial replication that is holding on to some connection to the DB causing it to lock up. Has anyone seen anything like this before?
Replication :: Failing To Create Subscription For One Node But Working For Other Nodes
Oct 12, 2015Merge replication fails on one node with error. The merge process could not update the list of subscriptions.
(Source: MSSQL_REPL, Error number: MSSQL_ REPL-2147201010)
Cannot insert duplicate key row in object 'dbo.sysmergepublications' with unique index 'nc1sysmergepublications'.
The duplicate key value is . (Source: MSSQLServer, Error number: 2601)The subscription could not be created.
(Source: MSSQLServer, Error number: 14057) works fine on other subscribers.
Can Not Get Sqlcmd And Unable To Create Peer-to-peer Replication.
Sep 12, 2007Hi, Pros,
I am new to SQL server 2005. We have installed SQL server 2005 cluster. I tried to setup peer-to-peer replication with another standard server. Unfortunately, when I creating publication, I got error message : SQL server cound not configure "MyServer" as a Distributor [New publication Wizard] Additional information: An exception occurred whild executing a Transact_SQL statement or batch.
The Server "Myserveris already defined as a Distributor. To reconfigure the server as a Distributor, you must first uninstall the exisitingDistributor. Use the stored procedure sp_dropdistributor, or use the Disable Publishing and Distribution Wizard. Changed database context to 'master'. [Microsoft SQL Server, Error: 14099]
I tried to use sqlcmd and I got another error message:
C:>sqlcmd
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
Does anyone have any idea how to get the sqlcmd work?
Thank you in advance.
SQL 2005 Error: Replication-Replication Distribution Subsystem: Agent (null) Failed.
Jun 15, 2007I'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.
View 3 Replies View RelatedAdding New Table In Replication And Changing One Column Replication Database
Jan 17, 2002Hi,
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
Aug 22, 2007Hello,
I have this problem on a Production database.
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.
Thank you.
Identity Range Managed By Replication Is Full And Must Be Updated By A Replication Agent. Error Message Makes NO SENSE.
Mar 6, 2007Hello,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
View 1 Replies View RelatedProblems On Create Proc Includes Granting Create Table Or View Perissinin SP
Aug 4, 2004Hi All,
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
Create PROC dbo.GrantPermission
@user1 varchar(50)
as
Grant create table to @user1
go
Grant create view to @user1
go
Grant create Procedure to @user1
Go
Thanks Guys.
Boolean: {[If [table With This Name] Already Exists In [this Sql Database] Then [ Don't Create Another One] Else [create It And Populate It With These Values]}
May 20, 2008the subject pretty much says it all, I want to be able to do the following in in VB.net code):
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
How would I do this?
Create Variable To Store Fetched Name To Use Within BEGIN / END Statements To Create A Login
Mar 3, 2014I created a cursor that moves through a table to retrieve a user's name.When I open this cursor, I create a variable to store the fetched name to use within the BEGIN/END statements to create a login, user, and role.
I'm getting an 'incorrect syntax' error at the variable. For example ..
CREATE LOGIN @NAME WITH PASSWORD 'password'
I've done a bit of research online and found that you cannot use variables to create logins and the like. One person suggested a stored procedure or dynamic SQL, whereas another pointed out that you shouldn't use a stored procedure and dynamic SQL is best.
Replication :: Difference Between Snapshot And Transaction And Merge Replication?
May 26, 2015What is the main difference between snapshot and transactional and merge replication?
View 5 Replies View RelatedSQL Express Replication And Problems Accessing Data After Replication
Jul 28, 2006Hi,
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
Any advice appreciated !
Thanks
Ronan
Dynamic Create Table, Create Index Based Upon A Given Database
Jul 20, 2005Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
View 1 Replies View RelatedCan CREATE DATABASE Or CREATE TABLE Be Wrapped In Transactions?
Jul 20, 2005I have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND
View 2 Replies View RelatedCreate Script To Create/Refresh Identical Database
Mar 26, 2008
I'm new to using SSIS and have been reading and learning slowly how to use it. I'm trying to create an identical copy of our database for reporting. I've used the Import/Export wizard, but have had some issues with foreign keys and with sql_variant columns.
I've tried searching for anything but haven't had any luck as of yet. I guess I don't even know where to start or what to look for.
Any help would be appreciated. Thanks!
Does Replication Affect Tempdb GROWTH Replication
Jun 28, 2007Hi all,
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????
Thanks in advance
Jacx
How Do We Add A New Column To A Merge Replication Article, But Specify It As Not For Replication?
Aug 30, 2007Hi all,
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 am using SQLSERVER 2005 (SP1).
Replication :: Transactional Replication Removal Sequence
Sep 2, 2015I 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.
Grant CREATE VIEW, CREATE PROCEDURE ...
Apr 12, 2006Hi,
I have currently a problem with setting up the permissions for some developers. My configuration looks like this.
DB A is the productive database.
DB B is a kind of "development" database.
Now we have a couple of users call them BOB, DAVID, ...
who are members of the db role db_reader and db_writer for the productive db a but they should be allowed to do nearly everything on db b.
Therefor I added them to the db role db_owner for db b.
For testing purposes I tried to "CREATE" a view TEST as BOB in database B but I received the error message
'Msg 262, Level 14, State 1, Procedure Test, Line 3
CREATE VIEW permission denied in database 'b'.'
I cross checked the permissions on db level and I even granted all available permissions on db level but nevertheless I receive this error message.
What's my mistake?
Of course it worked fine when I give them sysadmin rights but then they have far too much permissions.
Regards,
Stefan
DB Replication Or Table Replication Via Triggers?
Apr 17, 2007Hello 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
View 11 Replies View RelatedMerge Replication Set Off Transactional Replication
Oct 9, 2007I 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??
thanks...
Create INDEX Within CREATE TABLE DDL
Jan 27, 2006Hi Minor and inconsequential but sometimes you just gotta know: Is it possible to define a non-primary key index within a Create Table statement? I can create a constraint and a PK. I can create the table and then add the index. I just wondered if you can do it in one statement. e.g. I have: CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE NONCLUSTERED ON [DEFAULT])which creates a table with a PK and unique constraint. I would like (pseudo SQL):CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE INDEX NONCLUSTERED ON [DEFAULT]) No big deal - just curious :D Once I know I can stop scouring BOL for clues. Tks in advance
View 2 Replies View RelatedPublisher Is Empty Under Replication Monitor - Publisher.! But Replication Is Still Running Fine..
Nov 9, 2006I 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..
View 2 Replies View RelatedRed X Show On Replication Monitor (publications Is Disconnected From Replication Monitor)
Jan 30, 2007I have setup transactional replication everything on one box. later(two or three weeks later), Replication monitor is show red X Under my publishers (publications is disconnected). this is SQL2005.
Everyone known how to fix this problem?
Thanks,
Frank