Cannot Drop The Table Transactional Replication Error 3724

Mar 14, 2006

This thread is no longer active. I moved issue to a new, cleaner thread named: How can I get Transactional Republishing to work?

--------------------------------------------------------------------------------------------------------------------

I've tried for more than a week to figure this one out, but have not found a solution. I'm missing some critical information that perhaps someone can share. I'm desperate and embarassed that this has kept me stuck for so long. Thanks for any assistance.

In my simplist scenario, I'm transactionally replicating a single table from database A to database B and then from database B to database C. All databases are on the same XP server (in this simplist version of the problem). This is SQL 2005 RTM. Publication A to B replicates fine. Publication B to C can't even get the snapshot to work because I get the error:

Command attempted:
drop Table "dbo"."MyReplTable"

(Transaction sequence number: 0x00004174000000E100A300000000, Command ID: 41)

Error messages:
Cannot drop the table 'dbo.MyReplTable' because it is being used for replication. (Source: MSSQLServer, Error number: 3724) Get help: http://help/3724  (fyi - there is no help here)

 

I have a simple example script where I've renamed real names to fake names, that is exactly what I run to simulate the problem. Obviously the script is wrong, but where? I used the wizard to create the whole script (I know that is my first mistake ;-) ). After I start the snapshot jobs, I get the error above.

I presume that the sp_addarticle option @pre_creation_cmd = N'drop' is part of the problem. I don't have row filters. Also, I want to be able to replicate schema modifications when necessary (like add a column or drop a column)

Perhaps the sp_addpublication @repl_freq = N'continuous' option in the AtoB publication prevents the BtoC publication from ever getting a chance to do anything, if it never lets go of the table.

If it helps, here is my script with hopefully helpfully renamed objects. My real world scenario has many articles, but this is a simple version - 2 publications each having 1 article (the same table).

 

/****** Scripting replication configuration for server MyInstance. Script Date: 3/14/2006 11:16:58 AM ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/

/****** Installing the server MyInstance as a Distributor. Script Date: 3/14/2006 11:16:58 AM ******/
use master
exec sp_adddistributor @distributor = N'MyInstance', @password = N''
GO
exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLData', @data_file_size = 4, @log_folder = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLData', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO

use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
 create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
 EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:ThisFolderMY2005DBWorking
epldata', 'user', dbo, 'table', 'UIProperties'
else
 EXEC sp_addextendedproperty N'SnapshotFolder', 'C:ThisFolderMY2005DBWorking
epldata', 'user', dbo, 'table', 'UIProperties'
GO

exec sp_adddistpublisher @publisher = N'MyInstance', @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'C:ThisFolderMY2005DBWorking
epldata', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO

use [DatabaseA]
exec sp_replicationdboption @dbname = N'DatabaseA', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [DatabaseA]
exec sp_addpublication @publication = N'PubAtoB', @description = N'Transactional publication of database ''DatabaseA'' from Publisher ''MyInstance''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO


exec sp_addpublication_snapshot @publication = N'PubAtoB', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1


use [DatabaseA]
exec sp_addarticle @publication = N'PubAtoB', @article = N'MyReplTable', @source_owner = N'dbo', @source_object = N'MyReplTable', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'MyReplTable', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboMyReplTable', @del_cmd = N'CALL sp_MSdel_dboMyReplTable', @upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO

 

 

-----------------BEGIN: Script to be run at Publisher 'MyInstance'-----------------
use [DatabaseA]
exec sp_addsubscription @publication = N'PubAtoB', @subscriber = N'MyInstance', @destination_db = N'DatabaseB', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'PubAtoB', @subscriber = N'MyInstance', @subscriber_db = N'DatabaseB', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20060314, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
-----------------END: Script to be run at Publisher 'MyInstance'-----------------


use [DatabaseB]
exec sp_replicationdboption @dbname = N'DatabaseB', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [DatabaseB]
exec sp_addpublication @publication = N'PubBtoC', @description = N'Transactional publication of database ''DatabaseB'' from Publisher ''MyInstance''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO


exec sp_addpublication_snapshot @publication = N'PubBtoC', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1


use [DatabaseB]
exec sp_addarticle @publication = N'PubBtoC', @article = N'MyReplTable', @source_owner = N'dbo', @source_object = N'MyReplTable', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'MyReplTable', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboMyReplTable', @del_cmd = N'CALL sp_MSdel_dboMyReplTable', @upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO

 

 

-----------------BEGIN: Script to be run at Publisher 'MyInstance'-----------------
use [DatabaseB]
exec sp_addsubscription @publication = N'PubBtoC', @subscriber = N'MyInstance', @destination_db = N'DatabaseC', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'PubBtoC', @subscriber = N'MyInstance', @subscriber_db = N'DatabaseC', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20060314, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
-----------------END: Script to be run at Publisher 'MyInstance'-----------------


 

View 7 Replies


ADVERTISEMENT

Replication Issues After A Database Restore - Unable To Drop Or Create Transactional Replication

Sep 13, 2007

Hi,I have transactional replication set up on on of our MS SQL 2000 (SP4)Std Edition database serverBecause of an unfortunate scenario, I had to restore one of thepublication databases. I scripted the replication module and droppedthe publication first. Then did a full restore.When I try to set up the replication thru the script, it created thepublication with the following error messageServer: Msg 2714, Level 16, State 5, Procedure SYNC_FCR ToGPRPTS_GL00100, Line 1There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in thedatabase.It seems the previous replication has set up these system viewsSYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replicationmodule again to see if it drops the views but it didn't.The replication fails with some wired error & complains about thisviews when I try to run the synch..I even tried running the sp_removedbreplication to drop thereplication module, but the views do not seem to disappear.My question is how do I remove these system views or how do I make thereplication work without using these views or create new views.. Whyis this creating those system views in the first place?I would appreciate if anyone can help me fix this issue. Please feelfree to let me know if any additional information or scripts needed.Thanks in advance..Regards,Aravin Rajendra.

View 2 Replies View Related

SQL 2012 :: Error - Cannot Drop Table Because It Is Being Used For Replication

Sep 2, 2014

The error message is "cannot drop the table because it is being used for replication"

my sql version is in sql server 2012 and Im ussing merge replication.

I want to drop only one table.

View 7 Replies View Related

Transactional Replication - Error 207

Jan 19, 2003

Hi Guys,

I have a small problem with the transactional replication when I run the initial snapshot and distributing some of the stored procedures. For eg., Say if the stored procedure has the following statements:

---------------------------------------
create procedure abc
as
declare @paytype varchar(50)
set @paytype='check'

insert into acct select cusname,cusacctno,@paytype from cuspay
---------------------------------------

I get the following error:
for statement:

set @paytype='check'
--------------------
Invalid column name 'check'.
(Source: ServerSQL1 (Data source); Error number: 207)

I tried changing to double quotes. even tried adding article using query analyzer without using EM

exec sp_addarticle @publication = N'ReplicationSrc', @article = N'abc', @source_owner = N'dbo', @source_object = N'abc', @destination_table = N'abc', @type = N'proc schema only', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x0000000000000001, @status = 16
GO

Still I get the same error. The server has SQL server 2000 SP2 running with remote distributor and pull subscription.

Am I missing anything.


Please help.

Thanks,
Anu

View 2 Replies View Related

Transactional Replication Error

May 5, 2006

I am running sql 2000 sp3.
A pull subscription failed with the following.

errormsg: Cannot drop the table 'table_name' because it is being used for replication.

errordetail: Cannot drop the table 'table_name' because it is being used for replication.
(Source: SQLTEST2 (Data source); Error number: 3724)

This error started after a field was dropped and a new field added to one of the articles in the publication - changes were done through Enterprise Manager.

Why does this error come up. The Default Table Article Properties dialog box>>>Snapshot tab has the option "If a table with the same name as the destination table exists at the Subscriber>>>DROP the existing table and recreate it.

So if this is an option in replication for a Subscriber how come the error message says it cannot drop the table.

Any insight appreciated.

View 2 Replies View Related

Transactional Replication Error

Jun 5, 2006

Hello,
I am a newbie to replication, I recently came up with this error.
Cannot use DROP VIEW with 'dbo.vw_UndeliverableEmail' because
'dbo.vw_UndeliverableEmail' is a table.Use DROP TABLE.
I tried running these two queries to make sure.
SELECT * from syscomments where id = object_id('vw_UndeliverableEmail')
sp_help vw_UndeliverableEmail
I am sure that it is a view.
I am not sure how to proceed since the object referred is indeed a view rather than a table as the Replication Error tries to point out.
The details :
SQL Server 2005 / Transactional Replication/ Pull Subscription Any thoughts/helps/suggestions are most welcome.
Thanks

View 4 Replies View Related

Transactional LOB Replication Error

Feb 8, 2006

We are trying to replicate a varchar(max) column and receive the following error when trying to insert a record on the publisher:



Length of LOB data (75679) to be replicated exceeds configured maximum 65536.



The distribution agent is set up to use the oledb streaming profile so I can't figure out where this setting is.

View 6 Replies View Related

Oracle To SQL Server Transactional Replication Error

Jun 6, 2007

Hi,



I'd appreciate any help i can get here. I'm working with an MS SQL Server 2005 SP2 db on a Windows 2003 OS. I set up replication from an Oracle 10g instance a few months ago, and everything was working until i installed SP2 for SQL Server.



I get the following error:

2007-06-06 16:06:22.488 Status: 4096, code: 20024, text: 'Initializing'.

2007-06-06 16:06:22.488 The agent is running. Use Replication Monitor to view the details of this agent session.

2007-06-06 16:06:23.082 Status: 0, code: 1008, text: 'ORA-01008: not all variables bound
'.

2007-06-06 16:06:23.082 ORA-01008: not all variables bound

2007-06-06 16:06:23.082 Status: 0, code: 22037, text: 'Heterogeneous Logreader encountered an error in call to LoadReplCmds when processing state 'PROCESSSYNCDONE'.'.



I can get snapshot replication to work just fine, but transactional replication throws up the error above.



Anyone with any ideas on how i can proceed?



Thanks in advance!



Regards,

Rama.

View 7 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

Transactional Replication : SQL 2000 To 2005. Add New Table

Mar 2, 2007

I have a publication on 2000 machine, replicating by pull subscription to a 2005 machine. I added a table to the Articles in the publication. Reinitialized the subscription, and also restarted both the snapshot and log reader agent. I was kind of assuming the publication would automatically create the table on the subscriber, but after a very long wait it is still not there. Am I missing anything?

PS. Distination Object setting : "Action if name is in use" is set to drop and create new, not that that should make any difference since name is not in use anyway.

Thanks

View 4 Replies View Related

Updatable Transactional Replication, Queue Reader Error

Jul 3, 2007

I have just started getting the following error from the queue reader but I can't see why.



The Queue Reader Agent has encountered the error ''Row handle is invalid.'' when connecting to ''Database'' on ''ServerName''. Ensure that the publication and subscription are defined properly and that both servers are running.



Does anybody know what it means / how I can fix the problem?



Thanks

View 11 Replies View Related

Transactional Replication With Additional Columns In Destination Table

Jul 15, 2007

In Transactional Replication -while Publisher, Distributer & Subscriber are on same instance- can I have additional columns in destination Table & set "Action if name is in use" for related Article to "Keep existing object unchanged"?

I do this but even Snapshot is not transfered to destination table!!

View 5 Replies View Related

SQL 2000 Transactional Replication-skiperrors Parameter With Error Logging

Mar 15, 2007

I have used skiperrors parameter for 2601,2627 and 20598 errors in my Transaction replication setup. When transactions with above error are encountered, its skipped but I€™m not getting sql command that is skipped in MSrepl_errors table. Since SQL server 2000 is not saving transaction sequence number in the MSrepl_errors we are not getting the command, which have the errors.


Is there any way to track the commands those are skipped in transactional replication.

View 11 Replies View Related

Replication - Cannot Drop Table

Mar 15, 2001

I need some help with Merge Replication. After successfully defining a publisher, distributor and subscriber to perform merge replication, we decided to test Merge Replication to see what we can and can't do.

What we found is that you can't add new fields or change the nullable/null attributes of tables whilst the replication settings are still defined to the databases. ie you get the following error

'tblProducts' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.tblProducts' because it is published for replication.

So we thought, fair enough. We decided to uninstall replication using the wizards provided to see whether we can make schema changes. What we found is that we can make schema changes to the publisher database(on Server1), BUT not to the subscriber (Server2).

Does anyone know why? We are running SQL7 SP2.
Email me on ftowicz@icontact.com.au

View 4 Replies View Related

Can't Drop Table In Replication

Aug 30, 2006



Dear friends

I restore one database in two database servers which is running on SQL server 2000.I replicated these two through snapshot relication.Snapshot agent is creating snapshot.But when I am starting to synchronize it's telling can't drop table because that table is in replication. Backup I taken from a replicated database.so it's having rowguid both the servers.Another thing the table which it is telling not able to drop it's having primary key.please tell me what may be the problem.In replication why it is going to drop table it's only what to transport data na

Filson

View 8 Replies View Related

CAN NOT DROP TABLE Xxx BECAUSE IT IS PUBLISHED FOR REPLICATION

May 5, 2000

Good morning folks,
I'm getting the above error message when trying to drop a table. I'm not to familiar with the setup of this SQL server, so I not sure if the replication is enabled. I have never had to use replication so I don't know to much about it. Does anyone know how I guess to remove this table from the replication list so that it could be dropped?

Thanks much

Dom

View 1 Replies View Related

Cannot Drop A Table On Two Servers After Merge Replication

Oct 3, 2001

Hi,

I tried with success to replicate a table_test_repl from my local server to an other server.

I used only one table for testing.

After that, i can't drop the 2 tables a_test_repl (on my local server and the other : I've got a message 'cannot drop the table because it is published for replication'

What can I do? I need absolutely to drop these tables to keep a clean model
and I can't drop the database and try again.

Thanks to your answers
Axel

View 1 Replies View Related

Replication --&> Error 21122: Cannot Drop Destination Database...

Sep 13, 2004

Good day!

I am getting a bizard error here...

I am trying to drop replication off a database, and funny enough went trough all the steps and I am still getting the error when I try to Disable publishing.

I also did an EXEC sp_removedbreplication 'databasename'

Any ideas?

View 1 Replies View Related

Combining Peer-to-Peer Transactional Replication With Standard Transactional Replication

Dec 1, 2006

Hello,

I'm interested in combining the Peer-to-Peer Transactional Replication and Standard Transactional Replication to provide a scale out solution of SQL Server 2005. The condition is as follows:

We may have 10 SQL Server 2005 (1 Publisher + 9 Subscriber) running transactional replication in the production environment and allow updates in subscribers. To offload the loading of the publisher, we plan to have 2 Publisher (PubNode1 and PubNode2) using Peer-to-Peer Transaction Replication and the rest 8 subscribers will be divided into 2 groups. The subscribers 1-4 (SubNode1, SubNode2, SubNode3, and SubNode4) will be set to be standard transactional replication subscribers of PubNode1, and the rest 4 subscribers (SubNode5, ..., SubNode8) will be set to be standard transactional replication subscribers of PubNode2.

Is it possible to setup above 2 Publisher + 8 Subscriber topology?
Also, could we set the 8 subscribers with updatable subscriptions to achieve each node is updatable?

We do not plan to set all the 10 nodes using Peer-to-Peer Transactional Replication as it is necessary to make sure n*(n-1)/2 (i.e. 45) peer-to-peer connections is reliable. It seems that the maintenance cost is high if the servers are not in a LAN and the topology is very high coupling. So we prefer to divide the 10 nodes into 2 groups and reduce the cost of each node to maintain the connections to all other sites.

That's the scenario.

Any feedback is welcome and appreciated.

Thanks,
Terence

View 4 Replies View Related

Replication :: Drop One Of Larger Table Indices On Subscriber Database

Jul 31, 2015

We have a database we are replicating to about 8 SQL Express subscribers from a SQL 2012 SP2 publisher.  The size of the database grew too large for the 10GB license limit for SQL Express and now replication refuses to replicate any of our deletions on the publisher to reduce the size of the database.  I've come up with a few options below.

1) Drop one of the larger table indices on the subscriber database to get below the size restriction. Permit the replication to replicate the deleted records and then rebuild the index.  (I'm not sure how important an index is to this table.  Is it merely performance related?)

2) "Upsize" SQL Express to SQL Standard on the affected boxes.  Allow the deletes to replicate.  Backup the database, downgrade to SQL Express and restore the database back to SQL a new SQL express instance.  This would involve a lot of work on each box. I'd like to avoid it if possible.

View 2 Replies View Related

Error On DROP TABLE

Jul 17, 2007

Hi,

I was trying to drop a table (or alter it in any way) and the Managment Studio Express gave me an error of timeout. So I say something like DROP TABLE myTable, and it takes a while and gives me the error. Does anybody know how to unlock a table?



Thanks

View 3 Replies View Related

DDL Trigger And Drop Table Causes Error

Jul 24, 2007

I have a DDL Trigger that writes a record into a table when any DDL event happens.

It works great except when I try dropping any object. I get an error at that point and the object is not dropped. The trigger and error are listed below.



Thanks.



J



Trigger:

/****** Object: DdlTrigger [MonitorDBChanges] Script Date: 07/24/2007 11:02:02 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [MonitorDBChanges]

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

set nocount on

declare @EventType varchar(100)

declare @SchemaName varchar(100)

declare @ObjectName varchar(100)

declare @ObjectType varchar(100)

declare @Script varchar(2000)

SELECT

@EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)')

,@SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)')

,@ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')

,@ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)')

,@Script = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

-- Is the default schema used

if @SchemaName = ' ' select @SchemaName = default_schema_name from sys.sysusers u join sys.database_principals p

on u.uid = p.principal_id where u.name = CURRENT_USER

insert into MonitorDBChanges

select @EventType, @SchemaName, @ObjectName, @ObjectType, getdate(), SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN(),@Script



GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ENABLE TRIGGER [MonitorDBChanges] ON DATABASE

*******************************************************************************

Error Msg:

SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. (Microsoft SQL Server, Error: 1934)

View 5 Replies View Related

Replication :: Transactional Replication Removal Sequence

Sep 2, 2015

I have been researching on the proper steps or sequence to follow to completely remove SQL Server 2012 Transactional Replication.  I have read articles about using SSMS as well as using replication stored procedures and some procedures use SQLCMD or just regular TSQL executed in SSMS.  I have also read articles where people said all you really need is connect to the Publisher instance, find the publication you want to remove and choose "Delete" and everything will be taken care of behind the scene. I have three SQL servers that participate in transactional replication.  SQL-P (publisher), 

SQL-D (distributor) and SQL-S (subscriber).  Do I need to connect to the distributor instance and the subscriber instance when removing transactional replication or is it just really connecting to the publisher and click delete on the publication? I want everything gone including any metadata, systems tables, distributions db and any other replication objects created during the initial configuration.

View 6 Replies View Related

Alter Table Drop Column Error

Apr 11, 2001

1. We have user defined type (Sql type = bit ) with rule setting it to 0
2. New column(isVIP) was created using this user defined type
3. sp_unbindrule was used to unbind rule from new column (isVIP)
4. Alter table Client drop Column isVIP !!!! (invalid syntax )

Any ideas ?
Thanks

View 4 Replies View Related

Transact SQL :: Compiler Error Of Unable To Drop Table

May 29, 2015

I have a procedure where an id is passed into the procedure.  It will either be a numeric value or it will be the word "ALL."  All that is taking place is a select statement for the specified user id.  However, if All is passed in then I 1st need to get a list of all possible user id's and produce the results for all of the associated user ids.  This is my syntax, but it keeps producing a compile error of:

Msg 3701, Level 11, State 5, Line 1

Cannot drop the table '#tbl_temp', because it does not exist or you do not have permission.And I placed a comment above the line that is throwing this error.

CREATE procedure [dbo].[TEST]
(
@id varchar(100),
@startDate datetime,
@endDate datetime

[code]...

View 10 Replies View Related

Drop All Indexes In A Table, How To Drop All For User Tables In Database

Oct 9, 2006

Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob

View 2 Replies View Related

Merge Replication Set Off Transactional Replication

Oct 9, 2007

I am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.

This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??

Example...
Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??

thanks...

View 5 Replies View Related

Transactional Replication

Jul 30, 2001

On my distribution server there is a directory called mssql7
epldataunc that houses the bcp, sch and idx files for replications. A directory is created for each publication. Most directories are empty but some contain all the table idx, sch and bcp files for the publication. Some of these files are over 6 month. Does anyone know if these files are neccessary to keep and why do only some publications have these files. I am wondering if they are kept when you push the publicaiton and it errors out originally and you have to restart it? If any one can shed some like on this I would appreciate it.

View 1 Replies View Related

Transactional Replication

Dec 28, 2000

Hi,
I am planning to do a transactional replication from production server to reporting server. In one of my database is having 150 tables in it, only 100 tables are having primary key . Remaining tables are not have primary keys. So i am not able to do transactional replication for those 50 tables. Could any one tell me how i have to approch transactional replication. Because i need to have production data at my reporting server in regular interval.
So pls suggest me .
I am using Sql 7.0 with service pack2.

Thanks!
Kavira

View 1 Replies View Related

Transactional Replication

Feb 13, 2001

I have a problem with transactional replication in SQL Server 7. I have two databases (a publisher-distributor server and a suscriber server) and when I do an UPDATE in the publisher database the Log Agent Reader reads the UPDATE as an DELETE-INSERT and when the suscriber pull the suscription and try to do first the DELETE the transactional replication stops because of the Foreign Keys in the suscriptor database (that are the same as the publisher).
If anybody knows how to solve it, please tell me

Thanks

View 1 Replies View Related

Replication - Transactional - 7.0

Nov 1, 1999

I have two servers networked via workgroups (no domain) and am trying to install Transactional Replication. Everything appears to install correctly, but when I run the initial Snapshot Agent I get the following error -

The process could not create file 'CONCEPCIONC$MSSQL7ReplDatauncCONCEPCION_Erik_Rep lication_Erik_Replication19991029153207Employer.sc h'.
Access is denied.

The SQLAgent is starting with same user account on both machines - admin rights and the same password. I have added this user to the (
epldata) folder permissions as well. I even see a "Employer.sch" file in the directory.

What am I missing?

View 2 Replies View Related

Transactional Replication

Oct 25, 1999

Hello,

I am planning to implement replication between two servers (either snapshot/transactional). Those two servers are physically connected but we don't have domain concept i.e we connected both servers physically and it can be pinked from each other, but not under any domain (not also under different domain). How should I register SQL server& agent services to start with 'Service startup Account'. In this case I cannot start both servers using same domain Account, they how can I relate...

Thanks

View 1 Replies View Related

Help... Transactional Replication

May 22, 2003

Hi guys can anyone help me with this issue. if so thankyou kindly.

1. created transactional publication (for tables, stored procs & views )
2. created empty database at subscriber
3. set up pull subscription at subscriber
4. ran the initial snapshot agent at the publisher
5. checked the schema files & found out it hadn't scripted the primary keys on the tables.

Problem: PRIMARY KEY AINT GOIN ACROSS, COULD ANYONE TELL ME IF IVE MISSED SOMETHING? THANK YOU MUCH APPRECIATED GUYS.

? Could it be that it is not picking up the primary keys on the tables because I am replicating stored procs and views with that publication?

? Should I break my publications into 3; say 1 for each object?

Thank you guys for taking the time to read this.

View 1 Replies View Related







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