Cannot drop the table transactional replication error 3724

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 Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @.data_file_size = 4, @.log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @.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:\ThisFolder\MY2005DBWorking\repldata', 'user', dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', 'C:\ThisFolder\MY2005DBWorking\repldata', 'user', dbo, 'table', 'UIProperties'
GO

exec sp_adddistpublisher @.publisher = N'MyInstance', @.distribution_db = N'distribution', @.security_mode = 1, @.working_directory = N'C:\ThisFolder\MY2005DBWorking\repldata', @.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'--

May I ask why you need a republisher scenario, and not just have C subscriber to A?|||

The reason I ask this is because transactional replication is not designed for a republishing scenario. It can be done, but it involves a lot of hacks and so forth. Merge replication is typically used, and designed, for republishing scenario.

Maybe I should ask what your business needs are, and then we can discuss a solution.

|||

This is the nature of the system I've inherited. I'll represent the A,B,C level as servers because in reality, they often are a server with a single database.

The process thinking is that multiple Level B servers on other servers, spread the processing burden of moving the data from A to C. The C servers generally are physically clustered in the same general geographic area as the B server from which they source their data. Realistically, the A server might be feeding data to multiple B servers which in turn feed multiple C servers.

I hope that clarifies it for you. You can see that my little test scenario is much simplified from the physical reality. Our existing SQL 2000 replication makes use of both transactional and merge replication in this environment. I've only had light experience with transactional replication and not with either merge or republishing.

|||

Hi,

From your description, I assume you saw the error message from distribution agent, when replicating from B to C. Because you set @.pre_creation_cmd = N'drop', distribution agent needs to drop the table at C because it can replicate other transactions. But for some reason, this table is marked published.

So my question is: have you use DatabaseC as publisher? If it is the case, this may be the reason.

Peng

|||

Peng,

For the moment, I'm back to working on my actual script (vs the sample one). I can tell you that I do not have DatabaseC as a publisher in my actual script. However, I will need to make it a publisher because there is one more replication layer that I did not mention. Practically every table will be transactionally replicated to another server (DatabaseD) to support another process.

This is the way we are actually doing things in production now, in SQL 2000, without error.

Paul

|||I've just discovered that SQL 2000 replication was probably interfering with my SQL 2005 replication, so I removed SQL 2000 from my workstation (except for Virtual PC) and will try to work on this again tomorrow to see if anything has changed.

Cannot drop the table transactional replication error 3724

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 Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @.data_file_size = 4, @.log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @.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:\ThisFolder\MY2005DBWorking\repldata', 'user', dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', 'C:\ThisFolder\MY2005DBWorking\repldata', 'user', dbo, 'table', 'UIProperties'
GO

exec sp_adddistpublisher @.publisher = N'MyInstance', @.distribution_db = N'distribution', @.security_mode = 1, @.working_directory = N'C:\ThisFolder\MY2005DBWorking\repldata', @.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'--

May I ask why you need a republisher scenario, and not just have C subscriber to A?|||

The reason I ask this is because transactional replication is not designed for a republishing scenario. It can be done, but it involves a lot of hacks and so forth. Merge replication is typically used, and designed, for republishing scenario.

Maybe I should ask what your business needs are, and then we can discuss a solution.

|||

This is the nature of the system I've inherited. I'll represent the A,B,C level as servers because in reality, they often are a server with a single database.

The process thinking is that multiple Level B servers on other servers, spread the processing burden of moving the data from A to C. The C servers generally are physically clustered in the same general geographic area as the B server from which they source their data. Realistically, the A server might be feeding data to multiple B servers which in turn feed multiple C servers.

I hope that clarifies it for you. You can see that my little test scenario is much simplified from the physical reality. Our existing SQL 2000 replication makes use of both transactional and merge replication in this environment. I've only had light experience with transactional replication and not with either merge or republishing.

|||

Hi,

From your description, I assume you saw the error message from distribution agent, when replicating from B to C. Because you set @.pre_creation_cmd = N'drop', distribution agent needs to drop the table at C because it can replicate other transactions. But for some reason, this table is marked published.

So my question is: have you use DatabaseC as publisher? If it is the case, this may be the reason.

Peng

|||

Peng,

For the moment, I'm back to working on my actual script (vs the sample one). I can tell you that I do not have DatabaseC as a publisher in my actual script. However, I will need to make it a publisher because there is one more replication layer that I did not mention. Practically every table will be transactionally replicated to another server (DatabaseD) to support another process.

This is the way we are actually doing things in production now, in SQL 2000, without error.

Paul

|||I've just discovered that SQL 2000 replication was probably interfering with my SQL 2005 replication, so I removed SQL 2000 from my workstation (except for Virtual PC) and will try to work on this again tomorrow to see if anything has changed.

Cannot connect using sql authentication in query analyzer

I just moved a database from one sql server to another. Both use Windows
2000, sp4, build 2195. Both use SQL Server 7, but the old server uses
service pack 3 (7.00.961). the new server uses service pack 4 (7.00.1094).
I connect to the database in the old server without a problem. I connect to
the new server fine with NT authentication, or if I log in as sa with SQL
authentication, but if I log in as a different user with SQL authentication
on the new server, I get this message:
Unable to connect to server Enterprise3:
ODBC: Msg 0, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
Process 11 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOATION. SQL
Server is terminating this process.
Ooops - Just remembered one other piece that may be impartant: We were
swapping the two servers, so had to rename both. With SQL Server 7, that
means we had to do a mini-reinstall of the "corrupt" SQL Server. We've tried
to reapply the service packs and the hotfixes, but it will not allow it.
(Current installation is more recent.) This is not a problem with the SP3
server, but only with the sp4+ server.
"UWKC Admin" wrote:

> I just moved a database from one sql server to another. Both use Windows
> 2000, sp4, build 2195. Both use SQL Server 7, but the old server uses
> service pack 3 (7.00.961). the new server uses service pack 4 (7.00.1094).
> I connect to the database in the old server without a problem. I connect to
> the new server fine with NT authentication, or if I log in as sa with SQL
> authentication, but if I log in as a different user with SQL authentication
> on the new server, I get this message:
> Unable to connect to server Enterprise3:
> ODBC: Msg 0, Level 16, State 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
> Process 11 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOATION. SQL
> Server is terminating this process.
|||Hi
What do you call "mini-reinstall"? If you have problems, always do a full
install, anything else is not supported by Microsoft.
Regards
Mike
"UWKC Admin" wrote:
[vbcol=seagreen]
> Ooops - Just remembered one other piece that may be impartant: We were
> swapping the two servers, so had to rename both. With SQL Server 7, that
> means we had to do a mini-reinstall of the "corrupt" SQL Server. We've tried
> to reapply the service packs and the hotfixes, but it will not allow it.
> (Current installation is more recent.) This is not a problem with the SP3
> server, but only with the sp4+ server.
> "UWKC Admin" wrote:
|||Mike - Thanks for the question. When I say mini-reinstall, I mean the
reinstall that SQL Server demands we do after renaming a SQL Server 7.0
installation. It tells us something to the effect that that the
installstallation is bad and may have been tampered with, then tells us to
reinstall SQL Server. We do that, using the original SQL Server CD. I call
it a mini-install because it only takes a couple of minutes to complete.
"UWKC Admin" wrote:

> I just moved a database from one sql server to another. Both use Windows
> 2000, sp4, build 2195. Both use SQL Server 7, but the old server uses
> service pack 3 (7.00.961). the new server uses service pack 4 (7.00.1094).
> I connect to the database in the old server without a problem. I connect to
> the new server fine with NT authentication, or if I log in as sa with SQL
> authentication, but if I log in as a different user with SQL authentication
> on the new server, I get this message:
> Unable to connect to server Enterprise3:
> ODBC: Msg 0, Level 16, State 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
> Process 11 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOATION. SQL
> Server is terminating this process.
|||Did you run sp_dropserver and sp_addserver after you
changed the server name and updated the registry with the
SQL Server CD (the process you refer to as a mini-reinstall
just updates the server name entries in the registry)?
e.g.
sp_dropserver 'YourOldServerName'
go
sp_addserver 'YourNewServerName', local
This part of the process just updates the server name in the
sysservers table. You should run in on both servers that you
renamed.
I'm not sure this will make any difference as I don't
remember anyone getting access violations if they miss any
of the steps in renaming a server...but worth a try.
-Sue
On Thu, 9 Dec 2004 11:33:06 -0800, "UWKC Admin"
<UWKCAdmin@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Mike - Thanks for the question. When I say mini-reinstall, I mean the
>reinstall that SQL Server demands we do after renaming a SQL Server 7.0
>installation. It tells us something to the effect that that the
>installstallation is bad and may have been tampered with, then tells us to
>reinstall SQL Server. We do that, using the original SQL Server CD. I call
>it a mini-install because it only takes a couple of minutes to complete.
>"UWKC Admin" wrote:

Cannot connect to Named Instance of MSDE through Enterprise Mananger...

Hi- I am hoping someone can help me or shed some light on an issue I am having.

I have recently moved to a dedicated server with MSDE installed on it. The server was installed as the default instance and I changed the password to the SA account. I then copied my entire SA database over to the new MSDE database and everything was working good so it seemed. I believe at some point during copying of the data, or after installalling an application the SA account begain to fail and the password was changed. I have no idea how this happened, and better yet when I tried to change it logged in as the administrator with OSQL it told me I did not have sysadmin rights (I tried multiple Windows NT Auth logins and still got this error trying to change the password).

So... I reinstalled MSDE and created a named instane. I then attached the mdf/ldf files to my named instance and am able to connect ok from a .NET application directly to my new MSDE Named Instance database. Sounds like everything is great right? Not really, I can't figure out how to connect through enterprise manager and its driving me CRAZY!

1. I CAN connect via any .NET app and a connection string

2. I CAN connect via the server with OSQL

3. I CAN connect via the web based enterprise manager (which is very limited and a freeware ASP app).

4. I CANNOT connect via enterprise manager. I have verified from the (SVRNETCN.EXE utility) that the port is open and have tried connecting to MY_IPADDRESS\NAMEDINSTANCE\ but no luck.

One additional side note, when I wasn't using a named instance I had no problems connecting through enterprise manager to my default MSDE instance.

Any help would be grealy appriciated, this is driving me nuts!

-Chad

Ok, I got this figured out. For anyone else that has this issue here is what solved my problem.

Within the client utility create an alias named YourDomainOrIPAddress\NamedInstance and make sure to open up the TCP port with the SVRNETCN.EXE on the server. Also, enabled TCP/IP on the client utility and make sure its the same port.

Additionally within Enterprise Manager I kept trying YourDomainOrIPAdress\NamedInstance:Port# instead what you need to do is try YourDomainOrIPAddress,Port# (Common instead of semi colon).

-Chad