Replication :: Same Table From Multiple Databases Publishing To One Subscription Database
Aug 13, 2015
We have our replication environment with a separate SQL 2014 publisher, distributor and subscriber. We have a table named DPSP on 14 databases which is to be published via transactional replication to 1 subscription database.
The source tables are identical in name, schema and clustered index. We have created 14 tables in the subscription database with a suffix to the name (see below):
Each table in the subscription database has its own uniquely named primary key and clustered index:
Below are the settings for one of the published article:
The same process is repeated for each DPSP table created in 14 separate publications. Snapshots are generated and subscriptions created successfully. After some time, the log reader starts to show the following errors in replication monitor:
2015-08-12 21:48:24.156 7 transaction(s) with 11 command(s) were delivered.
2015-08-12 21:49:04.233 5 transaction(s) with 7 command(s) were delivered.
2015-08-12 21:50:04.355 No replicated transactions are available.
2015-08-12 21:50:09.378 13 transaction(s) with 17 command(s) were delivered.
2015-08-12 21:51:12.168 No replicated transactions are available.
2015-08-12 21:52:12.306 No replicated transactions are available.
2015-08-12 21:52:32.337
[Code] ....
View 3 Replies
ADVERTISEMENT
Feb 2, 2007
Hi,
We are Using Transactional Replication with Updatable Subscription in SQL Server 2005 SP1.
Subscription Type : Pull Subscription
Mode : Continuous Running Mode
Conflict Resolution Policy : Publisher Wins.
I have a table "Sample" (which is part of replication) and it has got 3 triggers. All the triggers are set NOT FOR REPLICATION.
The first trigger Updates a column of the "Sample" table in which i inserted a record.
The second trigger inserts record in to another database table and also updates a column of the table "Sample".
The third trigger does not affect any tables, it is written for some manipulations with variables.
In this scenario when I insert a record in the Sample table of the subscription database, that record is visible in the table. But during replication, it shows conflict in the Conflict Viewer and removes the record from the Sample table of the subscription database. The record is not replicated to the publisher and the other Subscriber also.
But when I comment any one update in either the first or second trigger, the insert works fine without any conflict.
Is there any issue with firing two triggers in replication which is updating the same table? I also suspect the Order of Commands moving to the Publisher from the MSReplication_Queue table, becoz the conflict viewer shows the subscriber as the Conflict loser.
Is there any issue with msrepl_tran_version, Since the conflict is decided based on this id??
It would be helpful if u could reply soon.
Thanks and Regards,
Swapna.B.
View 12 Replies
View Related
Sep 2, 2015
We need to replicate multiple databases (publications) to one central subscriber. The schema of those articles are identical in all publications and also the primary keys in publications do not have any overlap.
Is this possible?If yes is there any specific thing that I should consider for it's implementation? Should each publication has it's own dedicated distributor or all of them can share one distributor?
View 2 Replies
View Related
Jul 24, 2015
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',
@profile_name =
'SQL SMTP',
[code]....
View 3 Replies
View Related
Sep 13, 2007
Hi,
I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.
I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.
I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!
Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):
SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLOACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a
I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC
Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)
Thanks
View 8 Replies
View Related
Jan 12, 1999
When I try to 'Install Publishing' from the 'Replication Configuration' drop down on the 'Server' Menu within Server Manager I receive the following error:
"Replication cannot be installed as @@SERVERNAME is NULL"
Please Help.
James
View 1 Replies
View Related
Nov 6, 2015
As per attachment, i have been created replications but in local subscription it is not populated any thing at the same time, Subscription database has been created but tables is not populated as per publication table.
View 2 Replies
View Related
May 2, 2007
i want to use Replication in sql server 2000 at specified condtion ,mean if any condition is satisfied then the data is publish to subscriber database?
View 6 Replies
View Related
Jun 20, 2006
I need to design a system which represents multiple "projects" in SQLServer. Each project has the same data model, but is independent of allothers. My inclination is to use one database to store all projects.Looking at the numbers involved, however, I wonder if I would getbetter performance by storing each project in its own database.Suppose I have 50 projects, each with two users and 10,000 rows; itseems to me I'd rather have 50 x 2 users working in a table with 10,000rows than 1 x 100 users working in a table with 500,000 rows.On the other hand, the single database approach seems more elegant froma design perspective. I wouldn't be creating multiple copies of anidentical data model, and I wouldn't be creating new databases as abusiness procedure, every time a new project is required.Here are my questions:1. For the scenario described above, am I correct to assume I will getbetter performance by using multiple databases, or does SQL Server havesome clever way of achieving the same performance in a single database?2. Is the multiple database approach common? If anyone has tried it,please tell me about how it works in practice.-TC
View 9 Replies
View Related
Apr 20, 2006
Hi champs!
Is it possible to make a database View over two different database installations on different machines?
Many thanks
kurlan
View 4 Replies
View Related
Oct 30, 2014
I just finished installing SQL Server 2012 with the SQL Server Replication feature component checked. On configuring transaction replication, i notice the distribution database wasn't there. I ran the below query
EXEC sp_get_distributor And the value for distribution_db_installed is 0 as shown below
installed distribution server distribution db installed is distribution publisher has remote distribution publisher
0NULL000
View 1 Replies
View Related
May 16, 2008
Hi Guys
I have 4 databases in the same space.My users use all of them and use the same username and password to log into these 4 databases.In each of these databases,i have put a control table to allow me to keep track of all users that have to reset their passwords.
The control table consists of the username and flag fields.When the flag is ON(1) the user is forced to reset thier password and if the flag is OFF(0) they are not.
When a user logs into any one of these databases and they have to reset thier password,how do i make sure that all the other tables in other databases are also updated to make sure that the user is not forced to reset their password again when they log into those other databases later since they are using the same username and password for all databases.
I am planning to use a stored procedure which i will put into all the four databases and when a user logs in and has to reset their password,that sproc is called and automatically updates all the other 3 tables in other 3 databases.
Some SQL examples will be very appreciated.
Thanks.
View 5 Replies
View Related
Aug 13, 2007
Hi
I am learning Sql server 2005 replication.
My requirement is.
1.) Subscribe a Master database
2.) I have to add a timestamp column in every table of subscriber database ( Note: the column will not be in publication database)
I have two sql server
server 1 : I made Merger publication
server 2 : I made subscription of the same
But when i tried to create a column in the Subscriber database table its giving the following error
Validation warnings
warnings were encountered during the pre-save validation process, and might
result in a failute during save. Do you want to continue attempt to save?
''Table1' table
- Error validating the default for column 'rowguid'.
I clicked "Yes"
Post -Save Notication
Errors were encountered during the save process. Some database objects were not saved.
'Table1' table
- Unable to modify table.
The DDL statement cannot be performed at the Subscriber or Republisher.
The DDL operation failed inside merge DDL replication manipulation.
The transaction ended in the trigger. The batch has been aborted.
clicked ok
User canceled out of save diaglog
Can i add a new column to subscriber database table if so please guide me?
View 5 Replies
View Related
Jan 13, 2015
I have multiple databases in the server and all my databases have tables: stdVersions, stdChangeLog. The stdVersions table have field called DatabaseVersion which stored the version of the database. The stdChangeLog table have a field called ChangedOn which stored the date of any change made in the database.
I need to write a query/stored procedure/function that will return all the database names, version and the date changed on. The results should look something like this:
DatabaseName DatabaseVersion DateChangedOn
OK5_AAGLASS 5.10.1.2 2015/01/12
OK5_SHOPRITE 5.9.1.6 2015/01/10
OK5_SALDANHA 5.10.1.2 2014/12/23
The results should be ordered by DateChangedOn.
View 4 Replies
View Related
Jan 17, 2002
Hi,
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
View 1 Replies
View Related
Sep 26, 2007
28638
SSCE_M_DUPLICATETABLE
Existing subscription already contains table included in the new subscription.
What are the possible causes of this merge replication error?
Could it be caused by a SQL Server Compact Edition User trying to sync their .sdf file after their subscription has already expired on the SQL Server?
Would you expect to see a different message if a SQL Server Compact Edition user tried to sync a subscriber database (.sdf file) with merge replication if it's been longer than the subscription retention period since their last sync?
View 6 Replies
View Related
Apr 18, 2007
I am using ASP.NET 2.0 C#, SQL Server 2000Connection string is: "Data Source=USMAN;Initial Catalog=CMS;Integrated Security=True"
And by searching forums and goodle, I have also set sql server 2000's secutiry as mixed modeInfact When I compile and run my projcet from visual studio it is fine, every thing works ok
Then I publish my site to a folder on desktop, when publishing completed I right clicked the folder and in web sharing option I added alias = CMS
Now when I open: http://localhost/CMS/Home.aspx, then it works fine infact all the pages that dont have SQL Server 2000 connectivity work fine, but as soon as I open some page that has connectivity with the database, it gives me error like:
Login failed for user'USMAN/ASPNET'
I wonder why this error only comes after publishing, there is nothing probemaltic when running it from Visual Studio, plz tell me what to do
View 1 Replies
View Related
May 27, 2008
We are trying to script out database schema and data with the Database Publishing Wizard and are having problems with output script trying to create views and stored procedures before the tables they depend on.
I've seen discussion of this on their codeplex site and back in April 2007 they said that the CTP they just released fixed the problem - but we are still seeing it. Does anyone know a solution for this (beyond the obvious copy and pasting into the right order)?
What we are trying to do is move the SQL and some basic reference data into source control and a CI process.
View 2 Replies
View Related
Feb 11, 2007
Hi All,
I have developed a database application using c# and SQL Express 2005.
I have coded a connection string that works fine on the development machine but upon publishing to another machine I am getting the following error message:
Cannot open database "name of database" requested by the login. The login failed. Login Failed for user "machine nameuser name".
The remote machine has SQL server express installed.
Now on the development machine the machine name and user names are different to that on the remote machine. I am using Windows Auth. The application successfully runs on the remote machine. It even updates the same database (as mentioned in the issue above) on a different form which all connection strings etc were created automatically by c#. I'm pretty new to all this and don't know where to go from here.
Any help would be greatly appritiated.
Thanks
Jon.
View 3 Replies
View Related
Feb 11, 2007
Hi All,
I have developed a database application using c# and SQL Express 2005.
I have coded a connection string that works fine on the development machine but upon publishing to another machine I am getting the following error message:
Cannot open database "name of database" requested by the login. The login failed. Login Failed for user "machine nameuser name".
The remote machine has SQL server express installed.
Now on the development machine the machine name and user names are different to that on the remote machine. I am using Windows Auth. The application successfully runs on the remote machine. It even updates the same database (as mentioned in the issue above) on a different form which all connection strings etc were created automatically by c#. I'm pretty new to all this and don't know where to go from here.
Any help would be greatly appritiated.
Thanks
Jon.
View 6 Replies
View Related
Oct 24, 2007
I am using sql 2005 replication in my application. But how can i log the transactions that are happening in the server and client databases please help me...
View 1 Replies
View Related
Nov 28, 2007
I have a central server (CS1) with database CDB and a branch server (BR1)
Inside the branch server, there are two database DB1 and DB2.
CDB contains data for DB1 and DB2
I made a subscription in the brancserver named BR1:DB1(this will replicate the data from CDB to DB1)
I also made a subscription for DB2 named BR1:DB2(this will replicate the data from CDB to DB2)
Unfortunately, upon starting the synchronization for BR1:DB1, the data replicated into DB1 also contains data for DB2. same happens with the BR1:DB2..
How do I filter the data that will be replicate per database..Example:only data for DB1 coming from CDB should be replicated into DB1 if that specific subscription(BR1:DB1) is synchronized.
Thank you
View 1 Replies
View Related
Oct 20, 2006
Now here is something I have been asking the SQL Server Team for a long long while : http://www.microsoft.com/downloads/details.aspx?familyid=16D8C1E9-A6A3-43A2-9BA7-916C6EB5023B&mg_id=10046&displaylang=en . I hope this will be of use to many of us
View 1 Replies
View Related
Oct 28, 2005
I had a merge subscription expire and now need to synchronize two disparate databases. How do I do so without overwriting the subscriber database with the publication snapshot? Thanks for any help you can give.
View 3 Replies
View Related
Dec 10, 2007
Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
View 3 Replies
View Related
Oct 17, 2006
The SQL Server group at Microsoft has just launched the SQL Server Hosting Toolkit with the release of the Database Publishing Wizard Community Technology Preview 1.The objective of the SQL Server Hosting Toolkit is to enable a great experience around hosted SQL Server. The Database Publishing Wizard works toward this mission by making it easy to upload a database from a development box up to a shared hoster. In its first incarnation, the Database Publishing Wizard is a command line tool that generates a T-SQL script designed to be executed in the script execution windows provided by most hosters in their database management consoles. In the coming months we'll be adding a GUI and building seamless integration between the tool and an upload service we'll provide to hosters for deployment.Details on the Database Publishing Wizard as well as the download can be found at http://www.codeplex.com/Wiki/View.aspx?ProjectName=sqlhost&title=Database%20Publishing%20Wizard.We plan to be releasing advances in the Toolkit frequently so check out this first CTP and give us your feedback!Thanks,Dan WinnSenior Program Manager, Microsoft SQL Server
View 15 Replies
View Related
Sep 18, 2015
I'm exploring using VS2013, TFS 2013 and Release Management for DB projects.In VS 2013 the deployment configuration is stored in the publishing profile, which is not necessarily being used by deployment tools. (Release Management for example, while it's supporting the profiles, it does not require them) So, the question is how to control what will happen in the target database when the publishing profile file is not being used?
Is there any way to store the deployment configuration with project so it will be applied by default?For example, if I want to control the following settings: Block incremental deployment if data loss may occur...Drop objects in the target DB that are not in the DB project, e.t.c.
View 6 Replies
View Related
May 23, 2001
Hi,
I am doing a repl.in SQL7/NT.
At time of 'Push Subscription wizard' - I used the option "Yes,Initialize the schema and data......" whereas I already had data and schema at the subscriber.
Now my question is what will happen now and how long will this process take(for approx5GB of DAta).
OR should I stop the process - it's already an hour when I started the process.
Any suggestion appreciated.
View 1 Replies
View Related
Jul 17, 2007
Hi,
I needed to recreate/publish a DDL and data file(of a database) that was produced using Database Publishing Wizard. The size of the file is 4G. I receive the following error when I try to open it up in the new query window of sql server 2005 (sp2)for execution.
"The operation could not be completed. Not enough storage is available to complete this operation."
I know my server has 829G of free storage space available. i receive no errors when I open up a smaller file also created by database publishing wizard.
What possibly is causing this?
Thanks in advance
View 3 Replies
View Related
Jul 23, 2003
I have a snapshot & a transcation publication on Server A, and a pull subscription for each on Server B.
The distribution agent on ServerA has the error:
"The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated."
Any idea what caused this ..... Everything was fine for a week or so.
Does anyone know of any good books or publications on Replication ? I've got some DBA books that cover the basics, but I'd like something that helps explain the oddball situations.
SQL 2K
View 2 Replies
View Related
May 19, 2004
I am trying to pull subscription for a database but I always get the error message ...
Error 15004: Name cannot be null
What exactly have I done wrong
Plaese help
Thanks
Nneka Echebe
View 1 Replies
View Related
Nov 15, 2006
Hi!
I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.
I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.
I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)
Any pointer most welcome!
best regards and thanks
Thibaut
View 1 Replies
View Related
Dec 18, 2002
Trying to set up Merge Replication using FTP with PULL subscriptions on sql 2000
Are there any issues that anyone is aware of that may affect this working.
View 2 Replies
View Related
