Changing Table Props On Replicated Tables
Dec 13, 2001How can I change a field size that is set to numerical 9,2 to 9,3? I need to allow 3 decimal places.
Thank you,
Kameron
ADVERTISEMENT
Changing Indexes In Replicated Tables
Dec 19, 2001I need to drop and recreate indexes in some of my tables that are currently been replicated. I am not sure how this will affect my ongoing replication. Will this cause a problem for me? Please help
Bright
Changing A Primary Key In A Replicated Table
Jul 20, 2006Hi
We have a merge publication - I want to change the primary key for one of the tables (add another column to the primary key)
How do I do it ?
Currently there is no data in the table - which I guess might help..
thanks
Bruce
Changing Column Length Of A Replicated Table
Jul 31, 2006
Can I increase the length of a varchar column of table involved in transactional replication without dropping and recreating publication/subscription?
Any help/short-cuts/undocumented features greatly appreciated.
Regards
Opal
Consolidation - Changing Replicated Data In A Central Subscribing Site
Sep 25, 2006Hi all,
I am new to replication and have a few questions.
1) Are there any "hooks" available to insert processing when a subscriber is about to copy data from a replicating site?
2) Is it possible for a subscriber to change only his local copy of the data - without replicating the changes back to the publisher?
I realise that once the data changes in one place it isn't really replicated anymore, and I realise that my limited knowledge of the subject might well mean I'm not even asking the right questions. Therefore, I shall try to describe as best I can my scenario.
I wish to use many servers for transactional input (to distribute the workload) and use replication to publish the inputted data to a subscribing central site. One of the tables I wish to replicate has an identity column as primary key, but the records should otherwise be unique - i.e. no two records should differ only in the value of the key. Another table, which should also be replicated, uses this id value as a foreign key.
I can use the identity increment and seed to guarantee no key violations will occur when copying data to the central server. However, there is another issue: Several servers can create the same record but with different id values.
I need to "merge" such records by deleting duplicate entries in the table with the identifier as primary key, and update the foreign keys correspondingly. To clarify (I hope!), here's an example of what data I might have on the central site after copying data from two input sites:
TRANSACTION table
amount = 200, metadata_id = 1001 // Replicated from server INPUT_1
amount = -117, metadata_id = 2001 // Replicated from server INPUT_2
METADATA table:
id=1001 Actitiy=Sales, Country=USA
id=2001 Activity=Sales, Country=USA
What I would like is basically for the central site to identify that metadata 2001 is really the same as metadata 1001, update the foreign key in the TRANSACTION record accordingly and not import (or delete, if this "merging" is done in a post-treatment) the duplicate metadata record.
If anyone can offer any advice on how to achieve this I would appreciate your input.
How To Find Out Tables Which Can't Be Replicated
Jun 22, 2004Is there any query to find out all the tables without a Primary key or without a Unique index ?
View 3 Replies View RelatedReplicated Readonly Db And Temporary Tables
Mar 20, 2007I have two replicated databases i.e.
1. Database 1 is a live database where all live applications point
2. Database 2 is a replica of Database 1 and all reporting / BI applications point to this db. This is read-only.
Is it possible for reporting applications, pointing to Database 2, to create temporary tables in the read-only database?
Thanks
A Deadlock When Selecting From A View With Replicated Tables.
Dec 15, 2006I moved this from another forum because it seems more related to replication the longer I look into it!
The following code often dies on a deadlock. I have also seen "Spid x blocked by Spid x", where x is the spid of this connection. The view selects from several tables and some other views as well, and many of the underlying tables are being updated by replication. I have seen cases where the replication Insert proc participated in the deadlock, and the table being inserted into is joined twice in the view, suggesting that somehow this
causes the deadlock or makes a deadlock more likely?
SELECT * INTO dbo.tbl_stg_LoansMarketingFirstBorrower
FROM view_loans_marketing_first_borrower OPTION (MAXDOP 1)
I do not understand how selecting from a view can cause a deadlock with a
proc which does not reference tbl_stg_LoansMarketingFirstBorrower. Any
suggestions on how to diagnose this? I used (1204) to identify the proc and underlying table.
Thanks a bunch.
OLEDB Initialization Props In A Configuration File?
Mar 5, 2008Howdy Folks!
I'd like to put all the properties from the DBPROPSET_SSCE_DBINIT propset into a configuration file (XML probably) such that I can read in and dynamically adjust my SSCE connections. This way stuff like max database size and buffer size can be changed without a recompile.
My idea was to have an XML in the form:
<PropertySet Id = DBPROPSET_SSCE_DBINIT>
<Property Id = DBPROP_SSCE_AUTO_SHRINK_THRESHOLD>
<Options>DBPROPOPTIONS_REQUIRED</Options>
<Type>VT_I4</Type>
<Value>100</Value>
</Property>
<Property Id = DBPROP_SSCE_MAX_DATABASE_SIZE>
<Options>DBPROPOPTIONS_REQUIRED</Options>
<Type>VT_I4</Type>
<Value>1024</Value>
</Property>
.
.
.
</PropertySet>
Now my question is...can y'all think of any better way to convert these strings that I'll retrieve (e.g. "DBPROP_SSCE_MAX_DATABASE_SIZE") to their enum'd equivalents than to do the following:
if(PropertId == L"DBPROP_SSCE_AUTO_SHRINK_THRESHOLD") return DBPROP_SSCE_AUTO_SHRINK_THRESHOLD;
if(PropertId == L"DBPROP_SSCE_MAX_DATABASE_SIZE") return DBPROP_SSCE_MAX_DATABASE_SIZE;
It's seems entirely too unelegant and wasteful...but maybe I'm being too picky. It also has the disadvantage of needing to be updated if more properties are added with subsequent versions.
What do y'all think? Thanks as always!
SQL 2012 :: 2 Replicated Objects (tables) Not Being Created On Subscriber
Sep 10, 2015I'm in the process of migrating over nearly 900 reports to a replicated server.
I have moved over 100 reports, stored procedures and their dependent objects so far.
I have two tables that are not being applied to the subscriber.
Ug! Replicated Tables Missing Default Values And Identities.
Feb 8, 2007Ok, so I must have screwed something up.
I have several databases set up for transactional replication to another instance of SQL Server 2005 for fail over purposes. Today, I restored one of those replicated databases to my development machine and discovered two surprising problems:
1) The Default Values settings in the replicated tables are missing. They are there in the publishing tables, just as they were before I set up replication. However, they are not in the subscribing tables. Now, this is not such a big issue, since I tend to send all default values in insert queries as necessary.
2) The second problem is a more of an issue, since I use auto-numbered Identity columns in my tables (yes, I know that's just plain lazy...). Anyway, in the replicated tables, €śIs Identity€? is indeed set to yes, but despite that fact that there are thousands of records with incrementally unique IDs, SQL server is trying to insert a record starting with 1. This, of course, throws a PK constraint error.
Obviously, if I am use them for failover purposes, these replicated databases need to be identical in every way.
So, what did I do to cause this situation, and how to I fix it?
Thanks a bunch!
md
How To Modify Colum (alter Table) Of Table That Is Replicated?
Jul 20, 2005Hello!I have an MS SQL-server with an database, that runs replication. In thisdatabase there is an table with an columni want to extend; varchar(50)->varchar(60).But I get this error (using design window of Enterprise Manager): Cannotdrop the table 'MytableName' because it is being used for replication.Thanks for helpBjoern
View 1 Replies View RelatedALTER TABLE SWITCH PARTITION On A Replicated Table
Apr 10, 2007A customer wants to implement table partitioning on a replicated table.
They want to hold 13 months of data in the table and roll off the earliest/oldest month to an identical archive table. The table has a date field and partitioning by month makes sense all around.
So SWITCH PARTITION is the obvious solution to this, except for the fact that the table is replicated (transactional w/no subscriber updates).
What are his architectural or practical solutions to using table partitioning and replication?
thx
Replicated Table Is Not Updated
Jun 19, 2008Dear All,
i've made repliaction stup using the information guide below. all the things are done axactly. now i've inserted a row in the publisher. but i'm not getting the record at the subscriber database. please let me know where i'm missing
http://blog.csdn.net/longrujun/archive/2006/06/09/783357.aspx
Arnav
Even you learn 1%, Learn it with 100% confidence.
New Column For Replicated Table
Sep 20, 2007Hope to be my last question.
I used Transacational with update sub method. When adding new column to replicated table. Do I need to generate new snapshot again? Just want to know how can I apply the new schema to subsciber DB without doing all regenerate snapshot, recreate all tables and bulk copy.. Please help
Altering Table Replicated
Feb 2, 2007How can i change my Table Structure that is replicated?
I need to add a new field.
Alter Table In Replicated Database
Apr 26, 2003How can I do an alter table in some table that replicated database ?
I got the error message when I try !
Computed Column In A Replicated Table
Apr 10, 2015I have a strange problem. I have a computed column in a replicated table, the Formula is as follows:
(isnull(hashbytes('SHA2_256',CONVERT([varchar](256),[AccrualReference],(0))),(0))) the column is also Persisted.
This gets around a case sensitivity issue and is used as the Primary Key column, which works well.The problem is that this table is then replicated to another server. On the subscriber the value of this computed field is being returned as 0x00000000 for every row, so this must be the ISNULL function doing its job. But why? The AccrualReference is the true PrimaryKey and is never NULL.
If I remove the computed specification and set the field up as varbinary(64) the value then gets replicated. This then means maintaining a different table schema for in excess of 500 tables.
Rename Replicated Table Columns
Jun 10, 2015I need to rename a replicated table columns.will this affect my current replication (Transactional replication)? Do i have to create a new snapshot and restart the subscriber?
View 1 Replies View RelatedTable Mantenance Within A Replicated Database
Jul 20, 2005When I try to remove a table, it complains because it's part of apublication. I could script the publication and subscriptions suchthat they can be deleted, the table removed, and then added again.But, I not sure if this is the 'best' approach.Anyone faced this challenge before?Regards,gary
View 1 Replies View RelatedDrop Trigger From Replicated Table??
Apr 14, 2007I created a trigger on a replicated table in a publishing database on SQL Server 2000. When I attempt to ALTER TABLE ... DISABLE TRIGGER ..., I get a message that I cannot alter the table since it is part of a publication. Does anyone know if I would be able to issue a DROP TRIGGER or ALTER TRIGGER on a replicated table?
Thanks,
Gerald
Disable/Enable A Trigger On A Replicated Table.
Apr 9, 2002I have transaction replication setup on two SQL7 boxes and a nightly job runs a procedure that need to alter a table and disable a trigger. Since replication has been set up the disable doesn't work. Any way around this.
View 2 Replies View RelatedManually Input Data Into Replicated Table
Jul 17, 2007Is it possible to have a replicated table that you can open from and input data manually?
That is, I've got a table that I am replicating data to and I want to add some manual data to this table, every now and then.
Will this break replication?
www.beyonder422.com
Adding A Not Null Column To Replicated Table
Apr 23, 2007Hi,
I'm merge replicating a SQL Server 2005 database (publisher) to SQL Compact databases (subscribers) on mobile devices. I understood that I could add a "not null" column to a replicated table on the server as long as I specified a default value, but it seems this is not possible. I ran the following script on the server database:
ALTER TABLE Activity ADD ActivityRequiresProject bit not null default(0)
which executed OK. When I went to synchronize the db on the mobile device I got the following error:
Alter table only allows columns to be added which can contain null values. The column cannot be added to the table because it does not allow null values.
The SQL statement failed to execute. If this occurred while using merge replication, this is an internal error. If this occurred while using RDA, then the SQL statement is invalid either on the PULL statement or on the SubmitSQL statement. [ SQL statement = alter table "Activity" add "ActivityRequiresProject" bit not NULL constraint "DF__Activity__Activi__4A47DDAE" default ( ( 0 ) ) ]
Does anyone know if this is a valid error? Is is possible to add a not null column with default, and if not how do I update the schema on a replicated database?
Regards,
Greg
Transactional Replication: Dropping A Replicated Table.
Jan 19, 2007For SQL 2005 transactional replication I have 1400 articles (tables, views, functions, sp) that are all replicated.
Will SQL 2005 allow me to drop a table from from the publication and drop it from the publication database that is referenced by exising views or sps that are replicated or does it somehow check that?
Linda
Replication :: Rename A Column In Replicated Table
Jan 29, 2010I tried to run this(below) the table is replicated(transactional).
EXECUTE
sp_rename N'dbo.Tablename.Columnname, N'New_Columnname', 'COLUMN'But getting this error message:
Msg 15051, Level 11, State 1, Procedure sp_rename, Line 227
Cannot rename the table because it is published for replication.
Problem Adding NOT NULL Column To Replicated Table
Jan 15, 2003I'm new to replication and am trying to determine the best approach to add a column (NOT NULL with no DEFAULT) to a replicated table. The only success I have had is if I do the following:
Delete entire Subscription
Delete entire Publication
Add column to table
Create new Publication
Create new Subscription
Run SnapShot
The problem with this approach is that each step affects the entire database and not just the modified table. I think it is inefficient to redo replication for a simple object change. What am I missing? Is there a way to only replicate the changes made to the one table without having to run a SnapShot for the entire publication?
Keep in mind the column must be defined as NOT NULL and cannot have a Default.
Thanks, Dave
Increase Columns Width Of Merge Replicated Table
Dec 29, 2004We are using merge replication and it is working fine. We are looking for increase in on numeric column 10,5 instead of 7,2. How I can do it?
Any suggession highly appreciated by us.
Thanks
Can A Column Data Type Be Changed On A Replicated Table?
Dec 21, 2006On sqlserver 2000 transactional replication:
How would I best go about changing a published table's column from smallint to int? I could not find anything about it in BOL or MS.com. I do not think EM/Replication Properties allows the change. I suspect I have to run "Alter Table/Column" on the Publisher and each Subscriber the old-fashioned way. Is that true?
Thanks!
SQL Server 2008 :: Adding Column To Existing Replicated Table
Feb 9, 2015I have a scenario where I need to add a blank column to a table that is a publisher. This table contains over 100 million records. What is the best way to add the column? In the past where I had to make an update, it breaks replication because the update would take forever as jobs are continuously updating the table so replication can't catch up.
If I alter a table and add a column, would this column automatically get picked up in replication?
Access2000 Database(table) Replicated To SQL 2000/2003 Server
Nov 4, 2006I'm trying to replicate a table(s) in access 2000 to MS SQL server 2000/2005 programatically on a timed instance and then have these tables merged in SQL to create one table.
Any guidance appreciated.
Thank you,
Transactional Repln,how To Modify The Width Of A Column Of A Table Which Is Replicated
Aug 3, 2006Hi All,
Is there a way by which we can modify the width of a column of a table which is being replicated without touching the ongoing transactional replication? This is for MSSQL2000 Transactional Replication.
I know (and successfully tried) that we can add a column to a table and that gets propaged to the replicate database and indeed the added column gets reflected there. How to add a column? sp_repaddcolumn or Right Click on the Publication-Properties and it shows a button to Add a Column.
This is what I have tried for modifying the width of a column of a table participating in Transactional Replication from varchar(10) to varchar(100)
MH (source) -> MH1 (Replicate)
The column €ścol1€? had width of varchar(10) and this was altered to varchar(100).
insert into MH..test_mh values(4,'abcdeabcdefff')
select * from MH1..test_mh
exec sp_dropsubscription @publication = N'MH', @article = N'test_mh', @subscriber = N'UKPBDRMTST2', @destination_db = N'MH1'
go
exec sp_droparticle @publication = N'MH', @article = N'test_mh'
go
alter table test_mh alter column col2 varchar(100) null OR
MH1..sp_help test_mh
exec sp_addarticle @publication = N'MH', @article = N'test_mh', @source_table = N'test_mh'
go
exec sp_addsubscription @publication = N'MH', @article = N'test_mh', @subscriber = N'UKPBDRMTST2' , @destination_db = N'MH1'
go
Needless to say, help would be apreciated -
~Mihir
Changing Records In Tables
Jul 12, 2006Hi,I was just wondering... Is there any built-in function in MS SQL, which willallow me to do rows' "capitalisation"?Lets say that in database.table.name I've got:"FOO BAR LTD.", which I want to change to "Foo Bar Ltd.""Foo bar LTD.", which I want to be "Foo Bar Ltd.".Is there any way of doing this or do I have to read it from database, changein some script, and then insert it back into the table?Hope it's all clear ;-)Thank you,Martin
View 2 Replies View Related