Replication :: Rename A Column In Replicated Table

Jan 29, 2010

I 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.

View 4 Replies


ADVERTISEMENT

Rename Replicated Table Columns

Jun 10, 2015

I 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 Related

Transactional Replication: Dropping A Replicated Table.

Jan 19, 2007

For 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

View 1 Replies View Related

New Column For Replicated Table

Sep 20, 2007

Hope 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

View 2 Replies View Related

Computed Column In A Replicated Table

Apr 10, 2015

I 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.

View 2 Replies View Related

Adding A Not Null Column To Replicated Table

Apr 23, 2007

Hi,



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







View 12 Replies View Related

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

View 6 Replies View Related

Problem Adding NOT NULL Column To Replicated Table

Jan 15, 2003

I'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

View 8 Replies View Related

Can A Column Data Type Be Changed On A Replicated Table?

Dec 21, 2006

On 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!

View 3 Replies View Related

SQL Server 2008 :: Adding Column To Existing Replicated Table

Feb 9, 2015

I 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?

View 0 Replies View Related

Transactional Repln,how To Modify The Width Of A Column Of A Table Which Is Replicated

Aug 3, 2006

Hi 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

View 4 Replies View Related

How To Rename A Column Or Table On The Desktop?

Feb 28, 2007

I am using the compact edition on a desktop using VS2005 as well as SQL Server Management Studio. None of those tools allow me to rename a column or rename a table. Can someone point me a tool the runs on the desktop (as opposed to running on a CE device) that allows me to do the renaming?

Thanks

View 4 Replies View Related

Transact SQL :: Alter Table To Rename Column Name With No Space

Oct 27, 2015

I want to alter my all tables to change the name of all columns.
 
I need this because all column names were created with space and I want to remove the space for future work .

For example – In Table Customer there is a column name [Cust_Id ] and I want to change it with [Cust_Id]

View 11 Replies View Related

Adding New Table In Replication And Changing One Column Replication Database

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

Snapshot Replication - New Columns Not Replicated

Oct 10, 2005

I added couple of new columns to the distribution database and those new columns are not being replicated.

View 3 Replies View Related

Replication :: Clustered Index Is Not Replicated

Mar 1, 2010

Using SQL 2005 SP2. I have a publication that contains indexed views, and some other objects that query the indexed view using WITH (NOEXPAND). Currently replication fails because the CLUSTERED INDEX on the view is NOT replicated. I've experimented with various schema options but nothing changes. The view is replicated but not the clustered index on that view. I've seen some discussion on replicating indexed views to a table, but I would like to replicate indexed view schema fully. (Including the clustered index on that view). Is there a way to make this work?

View 4 Replies View Related

Replication Help - Problem Restoring Replicated Database

Nov 27, 2002

I'm a novice with replication and am in need of direction on how to restore replicated databases. Server A replicates 5 databases to server B using transactional replication. Server A also contains the distribution database. I'm perfoming PUSH replication.

The development area asked me to restore Server A with a copy of production data located on server C. After I restored the data from C to A, I noticed that server A was no longer published. Is this normal?

I re-published the 5 databases, but noticed that the Log Reader Agent has now been running for 2 hours. I'm trying to give myself a crash course in replication, but am coming up empty regarding (1) the prefered method for restoring replicated databases and (2) determining why the Log Reader Agent is taking so long.

Any suggestions?

Thanks, Dave

View 8 Replies View Related

Transactional Replication : Deleting Replicated Rows

Dec 28, 2006

I have 1 sql server 2005 database in Server A that is publishing a table to another database in Server B using the transactional method.

It is possible for me to delete all the rows in the database located at the publisher at the same time the subscriber pulls them?

Thanks.

View 1 Replies View Related

Replication :: Data Belonging To Specific Warehouse Replicated To Its Corresponding DB

Jul 10, 2015

We have a master database (SQL 2014 Std) from which data are imported from XML files (send by en ERP system) using SSIS.  There is about 12 other servers (SQL 2014 Express) located in remote warehouses. People will uses PocketPC to scan barcode of products in the warehouses and all operations must be forwarded to the master DB to be exported in a XML file for the ERP system.Now, each warehouses are independant. How can I setup the replication so only data belonging to a specific warehouse is replicated to its corresponding DB? I thought about creating views, one for each warehouses, and setup a replication for each warehouse, so there would be 12 merge replications configured. Is it fine?

View 2 Replies View Related

Replication - Are Schema Changes Replicated If The Sanapshot Agent Runs On A Schedule

Mar 4, 2003

I have Snapshot set up as a schedule task between server A and server B.

If I make changes to the db schema on server A - the changes are not replicated to server B.

Is this normal?

Do I have to restart the Sanapshot agent each time there is a schema change?

Thanks,

Bianca

View 1 Replies View Related

SQL Server Admin 2014 :: Certain Rows Not Replicated Yet Replication Was Not Broken

Oct 24, 2013

We had an issue recently where a (transactional) replicated table was replicating data as expected.

Then about 30 or so rows in the source table were not in the destination table, but other rows created after those 30 rows were replicated.

We have pretty much confirmed that users did not delete those rows.

Unfortunately we had to resolve the issue quickly and so blew away & recreated the subscription so a lot of evidence is probably gone from the crime scene.

We cant figure out what could cause 30 rows not to be replicated, yet leave replication operational.

View 1 Replies View Related

SQL Server 2008 :: Identify Which Indices Are Replicated - Transactional Replication

Feb 20, 2015

Is there a script to find which non-clustered indices are replicated? I know i can do this easily through GUI , having a script will make my life much easier ....

View 0 Replies View Related

Cannot Alter Column (column1) Because It Is Replicated

Jun 9, 2015

I am trying to change the size of the column which is the part of replication but its giving me the error "Cannot alter column 'Column1' because it is 'REPLICATED'.But same i tried to change for another column and its working. 

ALTER TABLE test1 ALTER COLUMN column1l varchar(80) NULL

whats going wrong .

View 3 Replies View Related

How To Rename A Column?

Dec 1, 2004

Hi,

Please let me know which query we will to rename a column in a table. I am using MS-SQL 2000.

View 4 Replies View Related

Rename Column?

Jul 20, 2006

Hi,

Is there a way to rename column like below?

ALTER TABLE table_name RENAME COLUMN from_column TO to_column

the statement above i found it from a web site, did not worked...

View 5 Replies View Related

How To Rename Column Name

Feb 29, 2008

Hai

XYZ (Table name)
a (column name)

Now I have to rename column name a to abc.

alter table xyz
RENAME column a to abc

With this statement, I'm not getting. Its throwing an error

Error : Incorrect syntax near the keyword 'column'.

Can anyone solve this

Thanks in Advance.

Suresh Kumar

View 4 Replies View Related

How To Add New Column In Table : Merge Replication

Sep 27, 2007

Hi,

I have a table used in Merge Replication. If i add a new column in that table it gives me an error for tables in which either this table is a key or this table has key from other tables "Error validating the default for column 'rowguid'." Also for views i'm getting an error
"View 'dbo.vw_TestCaseBasic': indexes and schema binding will be removed."

If i proceed further i get an error and m not able to add the column.
"It is invalid to drop the default constraint on the rowguid column that is used by merge replication.
The schema change failed during execution of an internal replication procedure. For corrective action, see the other error messages that accompany this error message.
The transaction ended in the trigger. The batch has been aborted."

Is there any solution for the same?

Regards,
Ruchir Jain

View 4 Replies View Related

How To Modify Column In A Table With Replication?

Mar 2, 2007

Dear all

I have a problem about replication, so I want to modify some columns in a table, but my database using replicate. How to do it?

Please solution.

Thank.

PC Kaveesin.

View 4 Replies View Related

Replication On Table With Image Column.

Oct 11, 2007

I got a problem when replicating a table with an image field.

First I started replication and it seemed to work. Then after a day or so I noticed that the replication had stopped and it had a few errors like this:

Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x000182EB0000025E001100000000, Command ID: 1)

Error messages:


NULL textptr (text, ntext, or image pointer) passed to UPDATE TEXT function. (Source: MSSQLServer, Error number: 7133)
Get help: http://help/7133

NULL textptr (text, ntext, or image pointer) passed to UPDATE TEXT function. (Source: MSSQLServer, Error number: 7133)
Get help: http://help/7133


I tried adding a filter to prevent rows which are NULL from being replicated with the filter "[content] is not null ", but I got the error message:


Cannot save properties for article "xxxxxxxxx"
Aditional Information

An Exception occured while executing a Transact-SQL statement or batch.
(Microsoft.SQLServer.Rmo)

Replication filter procedures may not contain columns of large object, large value, XML or UDT type.
Cannot generate a filter view or procedure. Verify that the value specified for the @filter_clause parameter of sp_addarticle can be added to the were clause of a select statement to produce a valid query.
Changed Database context to 'xxxxxxxxxx'. (Microsoft SQL server, Error: 341).


Now Im left wondering how to get the replication to work?


View 6 Replies View Related

Rename The Column By Using The Dynamic Value

Feb 5, 2008

Hi All
I want to rename the column name by using the dynamic value as like
declare @name as varchar(12) set @name='ss'
sp_rename 'dbo.employees.id3',@name, 'COLUMN';
go
but it shows the error
Incorrect syntax near 'sp_rename'.
any idea?

View 5 Replies View Related

Rename Primary Key Column?

Jul 22, 2014

will there be any issues if i rename a primary key column name,which is already beging referenced as a foreign key by other tables.

View 1 Replies View Related

Rename Column At Runtime

Oct 20, 2007



I am have this problem, that would not go away, can some please help me.
I want to rename or delete column that is bound to a tableadapter using SQL Express at runtime. is it possibleI have no idea on what to do.

View 4 Replies View Related

How To Rename A Column In SqlCE?

Apr 25, 2007

When I execute EXEC sp_rename 'PATHSPECIMEN.PVVALUE', 'PVPROC', 'COLUMN', I am getting the following error:
Major Error 0x80004005, Minor Error 25621
> EXEC sp_rename 'PATHSPECIMEN.PVVALUE', 'PVPROC', 'COLUMN'
The specified argument value for the procedure is not valid. [ Argument # = 3,Name of procedure(if known) = sp_rename ]

Could some one guide me where I am wrong?
I am executing this query against SqlCE in Sql Server 2005 Management Studio.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved