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?
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?
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
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.
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?
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.
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?
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?
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).
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?
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]
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
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?
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.
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?
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.
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 ....
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
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."
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:
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?
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?
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.
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.