Schema Changemultiple Publishers Into Single Subscription Table
Jan 16, 2008
I have scaled out an application so that multiple servers contain identical production databases (various clients use different server databases.)
I have replication configured so that the same tables from these different databases are published into single subscription tables (to provide data warehouse and reporting across all production databases).
The publications are set up to replicate ddl.
Everything works wonderfully except I have just discovered the need to alter the schema of a table that is within the production databases.
When I apply a script to change the ddl in all the publication databases I am getting errors in replication.
I understand that I am NOT supposed to change an uderlying subscription table (this should be done through replication of the schema.)
I suspect that the replication error is caused when the schema changes are replicated from the very first Publication update. What I'm effectively doing is changing the subscription table independently (and prior to) changing the schema of the OTHER (subsequent) publication table schemas.
I experimented and unsubscribed my target table from all but ONE publication, and then my schema change is fine and replication is happy. But when I have multiple publications feeding the subscription I am not able to propagate the schema change across replicating publications without breaking replication.
I am sure that other people out there must have similar situations as mine, where multiple publishers update a single subscription. How are you able to update schema successfully?
Many thanks in advance,
Normajean
P.S. I have posted the script that I am running below....
And here is the error I get in Replication Monitor when I try to run the script:
The index 'Idx_FacilityStayPayer_facStayID_PayerID' is dependent on column 'payerID'. (Source: MSSQLServer, Error number: 5074)
Get help: http://help/5074
The index 'Idx_FacilityStayPayer_payerID' is dependent on column 'payerID'. (Source: MSSQLServer, Error number: 5074)
Get help: http://help/5074
ALTER TABLE ALTER COLUMN payerID failed because one or more objects access this column. (Source: MSSQLServer, Error number: 4922)
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
-- make the facilityStayPayer.payerID column non nullable
-- drop all of the foreignKeys and indexes that reference the column
IF EXISTS (select constraint_name from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
where table_name = 'facilityStayPayer'
and constraint_name = 'FK_FacilityStayPayer_Payer')
BEGIN
ALTER TABLE dbo.FacilityStayPayer
DROP CONSTRAINT FK_FacilityStayPayer_Payer
END
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FacilityStayPayer]') AND name = N'Idx_FacilityStayPayer_payerID')
BEGIN
DROP INDEX [Idx_FacilityStayPayer_payerID] ON [dbo].[FacilityStayPayer] WITH ( ONLINE = OFF )
END
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FacilityStayPayer]') AND name = N'Idx_FacilityStayPayer_facStayID_PayerID')
BEGIN
DROP INDEX [Idx_FacilityStayPayer_facStayID_PayerID] ON [dbo].[FacilityStayPayer] WITH ( ONLINE = OFF )
END
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FacilityStayPayer]') AND name = N'UNQ_FacilityStayPayer')
BEGIN
ALTER TABLE [dbo].[FacilityStayPayer] DROP CONSTRAINT [UNQ_FacilityStayPayer]
END
GO
IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u')
BEGIN
-- alter the column
ALTER TABLE FacilityStayPayer
ALTER COLUMN payerID UNIQUEIDENTIFIER not null
END
GO
IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u')
BEGIN
-- add all of the indexes and foreign keys back
ALTER TABLE dbo.FacilityStayPayer WITH NOCHECK ADD CONSTRAINT
FK_FacilityStayPayer_Payer FOREIGN KEY (payerID)
REFERENCES dbo.Payer(payerID)
ON UPDATE NO ACTION
ON DELETE NO ACTION
END
GO
IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u')
BEGIN
CREATE NONCLUSTERED INDEX [Idx_FacilityStayPayer_payerID] ON [dbo].[FacilityStayPayer]
([payerID] ASC)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
END
GO
IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u')
BEGIN
CREATE UNIQUE NONCLUSTERED INDEX [Idx_FacilityStayPayer_facStayID_PayerID] ON [dbo].[FacilityStayPayer]
([facStayID] ASC,
[payerID] ASC)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
END
GO
IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u')
BEGIN
ALTER TABLE [dbo].[FacilityStayPayer]
ADD CONSTRAINT [UNQ_FacilityStayPayer] UNIQUE NONCLUSTERED
([facStayID] ASC, [payerID] ASC)
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
END
GO
View 1 Replies
ADVERTISEMENT
Oct 15, 2015
Is it possible to replicate data from 3 publishers to a single/central subscriber transactionally? In other words I have Server A, Server B, Server C with databases A,B,C respectively. I need to replicate 2 articles from A,2 from B and 2 from C to a central Server D that hosts database D. D will have only 6 articles. The replication is Transactional Replication.
If it is possible what will be the drawbacks of such implementation? (if one server goes down will the whole replication break?) If not possible then what is the best way of implementing this?
View 3 Replies
View Related
Apr 22, 2015
I want to create multiple partition schema on a single table.
For example - i need to create partition base on region id and Territory Id.
View 2 Replies
View Related
Jun 3, 2008
Hi folks. I'm architecting a data warehouse for the first time and I am wondering what the best logical and physical designs for the datamarts would be. I understand that datamarts are sets of views specific to the needs of a particular user or type of user. I am just wondering whether the best way to deal with datamarts would be to define them as schemas on the database that holds the data warehouse (easy peasy from an implementation standpoint but not great for performance if we get lots of users), or whether I should investigate setting up subscriptions (I have no idea how difficult subscriptions are to implement but I imagine that it would lead to superior performance if we get to the point where we have hundreds of simultaneous users and we want to move the snapshots to additional OLAP servers).
I'm doing a top-down design since our customer wants to be able to do ad-hoc queries on anything that the operational databases might be holding. The initial design will be an offline datawarehouse but it may evolve to to a realtime or near-realtime datawarehouse as requirements are refined. There may be some two way integration on one of the applications
Another question I have has to do with indexing the views. I know that a view must be schemabound and must only reference other objects in the same schema in order to be indexed. How much of a performance boost would I get my using indexed views as opposed to non-indexed views? Is it worth the extra space required to make copies of the tables on all the schemas that will be using them? If I go with subscriptions instead, would that make indexing the views a moot point since I could just use filtered tables instead?
Thanks for any insights you all may offer.
View 3 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
Oct 8, 2007
Has any figured out a work around to having multiple reports in a single email sent out by a data driven sub in SSRS 2005?
View 3 Replies
View Related
Oct 30, 2006
Hello friends,
Is there a way to include multiple render formats within a single subscription? For instance, web format to be included in body of email and exel format as an attachment.
I attempted this with data-driven subscription, but was only able to acheive multiple render formats across multiple communications within a single subscription - not at all what I want.
I would prefer not to have to send out 2 separate communications in order to provide both web format AND excel format reports to my audience.
Thank you!!!
iB
View 6 Replies
View Related
Apr 23, 2008
I think this should be a dead easy one.
SQL Server 200.
Can I query to find all the tables in a single schema? I can't for the life of me find this one out!
I'm currently using a dreaded cursor with an exists test to get the results I want but there is surely an easier way!
Cheers,
G
View 12 Replies
View Related
Feb 22, 2007
In my situation I have multiple transactional publishers replicating to a single subscriber in SQL2005. When making table changes (like adding a column) I want to be able to add the column to one publisher, let it replicate and then add the column to the remaining publishers and have it not replicate (because it would error trying to add a column that already exists on the subscriber). So I thought I could set the first publisher to "Replicate Schema Changes = True" and the remaining to False. Then add the column to each publisher and then insert new rows in each publisher table. It works for the first publisher, but the remaining publishers fail with "stored procedure sp_MSins_dboMYTABLE expects parameter @c3 which was not supplied". The remaining publishers don't seem to recognize the new column to pass the value the subscriber sproc.
I feel I'm missing something basic here. Is there a way to fix this? Or a better way altogether?
Thanks!
View 4 Replies
View Related
Apr 4, 2006
How can i combine my data in single row ? All data are in a single table sorted as employeeno, date
Code:
Employee No Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000
Should Appear
Code:
EmployeeNo Date1 OLDSALARY Date2 NEWSALARY
1 10/30/2006 500 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000 11/01/2006 800
PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT.
THANKS IN ADVANCE
View 3 Replies
View Related
Sep 13, 2005
I’ve got a situation where the columns in a table we’re grabbing from a source database keep changing as we need more information from that database. As new columns are added to the source table, I would like to dynamically look for those new columns and add them to our local database’s schema if new ones exist. We’re dropping and creating our target db table each time right now based on a pre-defined known schema, but what we really want is to drop and recreate it based on a dynamic schema, and then import all of the records from the source table to ours.It looks like a starting point might be EXEC sp_columns_rowset 'tablename' and then creating some kind of dynamic SQL statement based on that. However, I'm hoping someone might have a resource that already handles this that they might be able to steer me towards.Sincerely,
Bryan Ax
View 9 Replies
View Related
Oct 25, 2015
we have a table in our ERP database and we copy data from this table into another "stage" table on a nightly basis. is there a way to dynamically alter the schema of the stage table when the source table's structure is changed? in other words, if a new column is added to the source table, i would like to add the column to the stage table during the nightly refresh.
View 4 Replies
View Related
Jul 28, 2006
Hello,
I'd like to create a temporary table with the same schema as an exiting table. How can I do this without hard coding the column definitions into the temporary table definition?
I'd like to do something like:
CREATE TABLE #tempTable LIKE anotherTable
..instead of...
CREATE TABLE #tempTable (id INT PRIMARY KEY, created DATETIME NULL etc...
I'm sure there must be a simple way to do this!
Many thanks,
Ben S
View 3 Replies
View Related
Dec 20, 2006
I have kind of unique situation. I am running Merge replication. In one of my publications I am only publishing procedures/functions/views. By design, these do not change that often, but when a programmability object changes, it is scripted in a way so that:
1. The article is dropped from the publication
2. the object is then changed
3. The article is added back to the publication
My question is: Is there a table or view that the subscriber or publisher can see that could tell me if reinitialization needs to occur. I am looking at adding an automated script at the subscriber that makes the determiniation and automatically reinitializes the subscription. My alternative is to force the subscriber to reinitialize every time when synchronizing with this publication, even if nothing has changed because the process has to be automated.
Thanks,
Bill
View 8 Replies
View Related
Jul 31, 2007
Hello everyone.
We've been using replication since a while now and I just discovered that some user are not getting all the data they should. Let me explain the way the whole thing is configured for us.
We have a few tables that are replicated using the Merge process with filtering on the data using SUSER_SNAME() function. My users are salesrep using Laptop and they are synchronizing on demand. The merge process is configured so that a row will go to only one subscription --> Each rep has their owns customers and related tables informations.
I noticed that some users do not have locally all data (from their customers) from some tables while they should. I didn't checked in details but it is possible that these users are new reps replacing other staff that left the company. Is it possible that the data they do not receive is marked as to be previous reps owner because the replication is set as "A row from this table will go to only one subscription" ? The new rep uses the same laptop, therefore the exactly same computer name, only the username changes.
Because of this, now I'm having a bunch of conflicts when replicating because we're generating some ID based on existing data when the user enter new records in some tables; since the data is not sitting locally for them, the ID generated is not the proper one and we now have two identical ID (one on the server and one locally) which cause the conflicts to occurs.
Any helps would be appreciated. Thanks.
View 1 Replies
View Related
Jan 15, 2007
Hi
I come from a MySQL background and have been having some trouble finding the MSSQL command that corresponds to MySQL's "describe." I Googled for around 1/2 hour but can't seem to find it.
Any help appreciated
View 3 Replies
View Related
Jan 11, 2004
Hey,
I'm looking at creating a table in a stored procedure
[declare @tempTable table]
however, rather than then declaring the tables schema
[declare @tempTable table (pkField IDENTITY (1,1), SomeOtherField varchar(50), ...)]
I'd like it to simply use the schema of an already existing table (plus an additional column).
Is there a way to do this without having to manually write the table schema?
A simple example is:
I have a table
OriginalTable (idCol, NameCol, InfoCol)
I'd like to create a temp instance of that table called tempTable which would have a final schema of
tempTable (idCol, NameCol, InfoCol, myTempCol)
The reason I'd like to do this using the schema is so that I don't need to update all my procedures in the future when we decide to add some more detail to the originaltable which needs to be selected as well.
Thanks a lot for any help or direction you can provide.
-Ashleigh
View 12 Replies
View Related
Mar 24, 2008
I have a group of TV listing data need to map into database tables.Data looks like following:http://www.oniva.com/upload/1356/crew.jpgI want to create a table for productionCrew of each TV programthe data is like -crew -programID -member-member-member ... etc-programID -member-member-member ... etc-programID -member-member-member ... etc... etcabove are data from productionCrew of all TV program, for eachprogramID we have a list of members.Should I merge all member into a big string?Or should I use 2 tables to store the Crew data?If i use 2 tables, how the fields / column will look like?
View 2 Replies
View Related
Feb 9, 2012
I'm trying to insert data into a table from two tables into a single table along with a hard coded value.
insert into TABLE1
(THING,PERSONORGROUP,ACCESSRIGHTS)
VALUES
((select SYSTEM_ID from TABLE2 where
AUTHOR IN (select SYSTEM_ID from TABLE2 where USER_ID
=('USER1'))),(select SYSTEM_ID from TABLE2 where USER_ID
=('USER2')),255)
I get the following-
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Do I need to use a cursor?
View 5 Replies
View Related
Nov 19, 2015
There are 3 tables Property , PropertyExternalReference , PropertyAssesmentValuation which are common for 60 business rule
SELECT
PE.PropertyExternalReferenceValue [BAReferenceNumber]
, PA.DescriptionCode
[PSDCode]
, PV.ValuationEffectiveDate
[EffectiveDate]
, PV.PropertyListAlterationDate
[ListAlterationDate]
[code]....
Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set tables multiple times
View 11 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
Aug 11, 2007
GO
CREATE TABLE [dbo].[CmnLanguage]( [Id] [char](2) NOT NULL CONSTRAINT PkCmnLanguage_Id PRIMARY KEY, [UniqueName] [varchar](26) NOT NULL, [NativeName] [nvarchar](26) NOT NULL, [DirectionType] [smallint] NOT NULL, [IsVisible] [bit] NOT NULL, [CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(), [ModifiedDateTime] [datetime] NULL)
GO
CREATE TABLE [dbo].[CmnLink]( [Id] [int] IDENTITY(1,1) NOT NULL CONSTRAINT PkCmnLink_Id PRIMARY KEY, [UniqueName] [varchar](52) NOT NULL, [IsVisible] [bit] NOT NULL, [CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(), [ModifiedDateTime] [datetime] NULL)
GO
CREATE TABLE [dbo].[CmnLinkCmnLanguage]( [LinkId] [int] NOT NULL CONSTRAINT FkCmnLinkCmnLanguage_LinkId FOREIGN KEY (LinkId) REFERENCES CmnLink(Id) ON DELETE CASCADE, [LanguageId] [char](2) NOT NULL CONSTRAINT FkCmnLinkCmnLanguage_LanguageId FOREIGN KEY (LanguageId) REFERENCES CmnLanguage(Id) ON UPDATE CASCADE ON DELETE CASCADE, [CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(), [ModifiedDateTime] [datetime] NULL)
View 4 Replies
View Related
Apr 27, 2006
I have a problem, in one of my db's I have a table tblpersons in schema web the rest of the table's in the db are in schema dbo. Now I removed the user web, but I want to change the schema of tblpersons from web.tblpersons to dbo.tblpersons. I created a stored procedure with the line " ALTER SCHEMA dbo TRANSFER web.tblpersons", when executing this I get an error that the table tblpersons couldn't be found.
View 4 Replies
View Related
Sep 27, 2005
Hi all,
i m using sql server2005 CTP...i created a database called TEL and in that database i created a user(in security) as
USE [TEL]
GO
/****** Object: User [COLL_DB] Script Date: 09/27/2005 15:38:51 ******/
GO
CREATE USER [COLL_DB] FOR LOGIN [loginName] WITH DEFAULT_SCHEMA=[COLL_DB]
Now,when i m trying to create a table in the database TEL as
CREATE TABLE [COLL_DB].abc (c numeric)
commit;
it gives me error saying
The specified schema name "COLL_DB" either does not exist or you do not have permission to use it.
Now,can someone tell me...what i have to do to fix this error?????????
thanks...
View 1 Replies
View Related
Jun 12, 2006
Hi Gurus,
I am looking for a script which lists me the differences between two table schemas.
Thanks in advance
Srini
View 3 Replies
View Related
Aug 27, 2012
We currently have all tables in the dbo schema, but for organizational reason we would like to split them up in multiple schemas and I wonder if that can be done without re-creating the tables.
So my question is, is there a way to move a table into a different schema without re-creating it? (For those familiar with Postgres I'm looking for an equivalent to "ALTER TABLE foo SET SCHEMA newschema") sp_rename only allows a "one-part name" for the new name, so apparently that cannot be used.
View 2 Replies
View Related
Dec 9, 2013
I have created a table on SQL Server from SAS. The table gets created fine. However, the table schema has my user ID in it (AD-ENTmyuserid.Table1). How can I change the schema to dbo.(dbo.Table1)? It's fine if I have to make this change in SQL Server Management Studio.
View 4 Replies
View Related
Mar 15, 2014
I am more familiar with writing SQL for Oracle than MS SQL Server. Within Oracle there is a simple command, 'Describe', which actually shows the data types, and whether or not an attribute accepts NULLS. MS SQL Server does appear to support such a command, however I am looking for a way to describe the attributes of tables nonetheless.
View 4 Replies
View Related
Jun 4, 2008
Hi,
I am a bit confused and wish to share this with you for help. We are designing a billing application to bill telephone calls. It currently handles a single rate plan. So what it does is that it looks up the RATES table and matches the called number area code with the RATES.ACCESS_Code field to find the tariff for that area and multiplies that by the number of minutes. Here is the current schema.
CALLS
•ID (pkid)
•Called Number
•Duration
RATES
•Destination Name
•Access_Code (pkid)
•Tariff
Now the problem is that we need to process calls based on RATES per OPERATOR. Each operator is a telephony carrier with similar RATES. However, each call will be prefixed with a number to indicate which operator carried that call. Accordingly, the database should relate that prefix with the proper operator and then looks up the RATES that are related to that operator.
In conclusion we will have a replica of the RATES table for multiple operators. An operator is only supposed to have two fields I guess (name and ID).
So now we need to re-engineer the schema to adapt to this situation.
Eg. 95004433313445 (Will be identified as BT operator)
93004422376234 (Will be identified as AT&T operator)
Can anyone help please?
PS: we are using SQL Express 2005
Thanks
View 4 Replies
View Related
Mar 5, 2014
I created a schema, Admin. I have to transfer a table from the dbo schema to the admin schema. I keep getting an error that I do not have permission or the table does not exist.
Simply looking for confirmation here - is my syntax correct?
ALTER SCHEMA Admin TRANSFER MyShop.Addresses;
(MyShop is the Database, Addresses is the table)
NOTE: When I created the schema, I did not create an inner table. The syntax for that was simply CREATE SCHEMA Admin;
View 8 Replies
View Related
Mar 15, 2014
I am more familiar with writing SQL for Oracle than MS SQL Server. Within Oracle there is a simple command, 'Describe', which actually shows the data types, and whether or not an attribute accepts NULLS.
MS SQL Server does appear to support such a command, however I am looking for a way to describe the attributes of tables nonetheless.
View 6 Replies
View Related
Apr 4, 2014
I have a table.
Column AColumn BColumn C
123
456
I need the schema details of the table like datatype of column A , B and C as well as default values of all the columns and all other details of those 3 columns.
View 2 Replies
View Related
Jul 20, 2005
Quick question:Does SQL do table/schema changes "in place"?I've got a large table (140+ million rows of very widedata) that we want to change the schema on -- basicallyto remove a number of the unused data elements that wedon't use.Anyway, does anyone know if SQL will do an in-placechange, or if it will copy the table to a new table, therebyincreasing my space allocation needs? I'd effectively,temporarily, need space for two tables while the changeis happening if it copies the table first. This is not good asI do not have enough available space at the moment.If you've got pointers to specific MS docs regardingthis issue, please let me have 'em.Thanks in advance.
View 2 Replies
View Related