Cannot Edit Jobs

Two days back we have installed SP2 on SQLServer2005 Active/Active Cluster which result in failure.

Only Database Services and Analysis Services where not updated and rest of the services got updated.

After a restart , the behaviour of SQLServer is helarious.

We got into lot of problem so we opened support from Microsoft.

One of the problem i forgot to mentioed to Microsoft was on editing the Job i cannot see anything inside,all the entries where empty closing the job window and opening it again i got all my entries back but on editing i got the below mentioned Error.

Error:

Creating an instance of the COM component with CLSID {E80FE1DB-D1AA-4D6B-BA7E-040D424A925C} from the IClassFactory failed due to the following error:c001f011(Microsoft.SqlServer.ManagedDTS)

If anyone faced the same problem and got any resolution please let me know.

Anyhelp will be appriciated.

Solved the Error.

In one of the artical i read tht after installation of SP2 few of the DLL left unregistered.

Registered the DTS.DLL and it work like charm.

"C:\Program Files\Microsoft SQL Server\90\DTS\Binn>regsvr32 dts.dll"

Hope this help to others.

sql

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 to SQL Server 2005 after Active Directory upgrade

Hi, we recently installed Active Directory here and I can no longer connect to our SQL Server 2005 box. Active Directory was installed on a different server and my development machine was added to the Active Directory. The AD server was also made a domain controller and my development machine was also added to the domain. The SQL Server sits outside the domain, and is NOT included in AD. Now when I attempt to connect via SQL authentication in my c# code, I get the following error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

If I attempt to connect to the SQL Server box using SQL authentication from SQL Server Management Studio, the connection attempt simply times out. However, if I change the protocol used to connect to Named Pipes, I am able to connect to the SQL box.

Can anyone tell me why I cannot connect to the SQL box using SQL authentication and TCP/IP anymore?

I apologize if this post is misplaced.

I'm moving your question to the database engine forum where I hope they can help...

Paul

|||What is your connection string? If it is "<machinename>\<instancename>", then you could try to change this to "<machinename>\<instancename,<portnumber>". Maybe this will help.

Cannot connect to second cluster node w/instance name

I have an active/active SQL2005 cluster. Node1 has a default instance of
SQL 2005 installed (i.e. no named instance). Node2 has a named instance of
SQL 2005 (i.e. node2\sqlb). I can connect remotely to node1 SQL 2005
without problem. When I attempt to connect to node2 remotely I get an error
basically stating "An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused
by the fact that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified)".
When I connect I am using node2\sqlb as the connection name--this should
give me access to the named instance. I can however access node2\sqlb from
node2 directly (locally on the server). Yes, I have made sure named pipes
and tcp/ip are enabled. Made sure no firewalls, etc. The weird thing is
that I was able to make a remote connection for a short time (like two
hours) and then I couldn't anymore.
I am using a Windows Vista Ultimate RTM machine with SQL 2005 management
console installed locally. Any information would be greatly appreciated.
Is the SQL Server Browser Service running to provide the tcp port number for
the named instance?
If not, what port is the named instance listening on? Can you connect to
node2,<port number> ?
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"JPerkins" <joshp@.CUIAB.LOCAL> wrote in message
news:DBDEDBC3-AEF7-48E0-91D7-25977A8AEBB9@.microsoft.com...
>I have an active/active SQL2005 cluster. Node1 has a default instance of
>SQL 2005 installed (i.e. no named instance). Node2 has a named instance of
>SQL 2005 (i.e. node2\sqlb). I can connect remotely to node1 SQL 2005
>without problem. When I attempt to connect to node2 remotely I get an
>error basically stating "An error has occurred while establishing a
>connection to the server. When connecting to SQL Server 2005, this failure
>may be caused by the fact that under the default settings SQL Server does
>not allow remote connections. (provider: SQL Network Interfaces, error:
>26 - Error Locating Server/Instance Specified)".
> When I connect I am using node2\sqlb as the connection name--this should
> give me access to the named instance. I can however access node2\sqlb
> from node2 directly (locally on the server). Yes, I have made sure named
> pipes and tcp/ip are enabled. Made sure no firewalls, etc. The weird
> thing is that I was able to make a remote connection for a short time
> (like two hours) and then I couldn't anymore.
> I am using a Windows Vista Ultimate RTM machine with SQL 2005 management
> console installed locally. Any information would be greatly appreciated.

Cannot connect to second cluster node w/instance name

I have an active/active SQL2005 cluster. Node1 has a default instance of
SQL 2005 installed (i.e. no named instance). Node2 has a named instance of
SQL 2005 (i.e. node2\sqlb). I can connect remotely to node1 SQL 2005
without problem. When I attempt to connect to node2 remotely I get an error
basically stating "An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused
by the fact that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified)".
When I connect I am using node2\sqlb as the connection name--this should
give me access to the named instance. I can however access node2\sqlb from
node2 directly (locally on the server). Yes, I have made sure named pipes
and tcp/ip are enabled. Made sure no firewalls, etc. The weird thing is
that I was able to make a remote connection for a short time (like two
hours) and then I couldn't anymore.
I am using a Windows Vista Ultimate RTM machine with SQL 2005 management
console installed locally. Any information would be greatly appreciated.Is the SQL Server Browser Service running to provide the tcp port number for
the named instance?
If not, what port is the named instance listening on? Can you connect to
node2,<port number> ?
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"JPerkins" <joshp@.CUIAB.LOCAL> wrote in message
news:DBDEDBC3-AEF7-48E0-91D7-25977A8AEBB9@.microsoft.com...
>I have an active/active SQL2005 cluster. Node1 has a default instance of
>SQL 2005 installed (i.e. no named instance). Node2 has a named instance of
>SQL 2005 (i.e. node2\sqlb). I can connect remotely to node1 SQL 2005
>without problem. When I attempt to connect to node2 remotely I get an
>error basically stating "An error has occurred while establishing a
>connection to the server. When connecting to SQL Server 2005, this failure
>may be caused by the fact that under the default settings SQL Server does
>not allow remote connections. (provider: SQL Network Interfaces, error:
>26 - Error Locating Server/Instance Specified)".
> When I connect I am using node2\sqlb as the connection name--this should
> give me access to the named instance. I can however access node2\sqlb
> from node2 directly (locally on the server). Yes, I have made sure named
> pipes and tcp/ip are enabled. Made sure no firewalls, etc. The weird
> thing is that I was able to make a remote connection for a short time
> (like two hours) and then I couldn't anymore.
> I am using a Windows Vista Ultimate RTM machine with SQL 2005 management
> console installed locally. Any information would be greatly appreciated.

Cannot connect to localhost using Windows Security Group as login

I have a test environment set up at home with a small windows server 2003 Active Directory domain. I created a windows security group on the dc and added myself and another user to the group. I then use this group as a login for SQL Server and add the login to the sysadmin server role. This works exactly as I would expect on the Windows Server computer (which is also the Domain Controller) regardless of which computer I use to connect to this Windows Server computer. However, when I set up the exact same login on the local sql server instance of my client laptop running windows xp pro, the I get an error message (Login failed for user MYDOMAIN\dgolds...) when I attempt to connect to the local instance of SQL server, even though I am able to connect to the instance on the domain controller with no problems using this same client laptop under the same user account. The only way I am able to connect locally on the laptop under this account (which does not have local admin privileges on any box, for testing purposes) is to add the user account separately as a login, rather than as part of a group. I'm curious as to why being a member of a security group that is part of the sysadmin server role does not allow me to connect to the local instance of SQL Server on this client laptop. The windows server computer is running SQL2k5 Enterprise, and the client laptop is running SQL2k5 developer. Any insight into this would be appreciated.

Thanks,

Dave

Let's focus on the laptop for a moment, becuase that seems to be where your question is. Is all this true?

1. You have an instance of SQL Server on the laptop

2. You have a user (UserA) in a Windows group (GroupA)

3. The Windows Group (GroupA) has been added as a login to SQL Server as a Windows account

4. When UserA tries to log in to the local instance of SQL Server on the laptop, they can't get in

5. If you add UserA to the local Instance of SQL Server on the laptop, they can get in

Do I have all that right?

|||

All of that is correct.

Thanks,

-Dave

|||

Then let me ask a few more questions:

1. Is the user a domain Windows user?

2. Which service accounts did you use to start SQL Server on the laptop? Are they starting with a user account or the localsystem account?

|||

The user is a domain Windows user. Originally the laptop was using the local system account as its service account, but I thought that might be the problem so I tried using the same service account (a domain user account that I configured specifically as a SQL Server Service Account) that I use on the server, but unfortunately I got the same results.

Thanks,

-Dave

|||

OK - I think we're getting closer. I'm going to assume the laptop is on the domain, since you say you've added that Windows group to SQL Server as a login. Does the account that you are starting SQL Server with on the laptop have the ability to resolve network names?

To help investigate your problem, make sure you check the logs the system creates. You can read more about that here:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=242&rl=1

|||

The laptop is definitely on the domain. As far as the account that I am starting SQL Server with on the laptop having the ability to resolve network names, I'm not sure-how would I find that out?

Thanks,

Dave

|||I'm sure there's an easier way, but I normally just log on as that user, create a local group and then try to add someone from the domain to that local group.|||

Hi,

The SQL Server Service Account that I'm using does have the ability to resolve network names on the laptop.

Thanks,

-Dave

|||

Very odd. At this point, it sounds like you're doing everything properly. It has to be something to do with the security policies on that machine, but without access to it I'm afraid it would take a lot of back-and-forth to figure out where it is. I would suggest checking the logs for the system, using the techniques I outline in this guide:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=242&rl=1

If that doesn't help point out the issue, then you may have to open a support call so that someone can gain access to your machine. sorry that I couldn't be more help here.

|||

Dave, I don't know if this will help, but could you check the error message for the login failure in the ERRORLOG file and post here the two lines that describe the state and message of the error?

Thanks
Laurentiu

|||

Thanks for all the suggestions. It turns out that there was something going on with this group (I'm not sure what). I created another group in the same Active Directory location with the same users as members and it works fine.

Thanks,

-Dave

Cannot connect to localhost using Windows Security Group as login

I have a test environment set up at home with a small windows server 2003 Active Directory domain. I created a windows security group on the dc and added myself and another user to the group. I then use this group as a login for SQL Server and add the login to the sysadmin server role. This works exactly as I would expect on the Windows Server computer (which is also the Domain Controller) regardless of which computer I use to connect to this Windows Server computer. However, when I set up the exact same login on the local sql server instance of my client laptop running windows xp pro, the I get an error message (Login failed for user MYDOMAIN\dgolds...) when I attempt to connect to the local instance of SQL server, even though I am able to connect to the instance on the domain controller with no problems using this same client laptop under the same user account. The only way I am able to connect locally on the laptop under this account (which does not have local admin privileges on any box, for testing purposes) is to add the user account separately as a login, rather than as part of a group. I'm curious as to why being a member of a security group that is part of the sysadmin server role does not allow me to connect to the local instance of SQL Server on this client laptop. The windows server computer is running SQL2k5 Enterprise, and the client laptop is running SQL2k5 developer. Any insight into this would be appreciated.

Thanks,

Dave

Let's focus on the laptop for a moment, becuase that seems to be where your question is. Is all this true?

1. You have an instance of SQL Server on the laptop

2. You have a user (UserA) in a Windows group (GroupA)

3. The Windows Group (GroupA) has been added as a login to SQL Server as a Windows account

4. When UserA tries to log in to the local instance of SQL Server on the laptop, they can't get in

5. If you add UserA to the local Instance of SQL Server on the laptop, they can get in

Do I have all that right?

|||

All of that is correct.

Thanks,

-Dave

|||

Then let me ask a few more questions:

1. Is the user a domain Windows user?

2. Which service accounts did you use to start SQL Server on the laptop? Are they starting with a user account or the localsystem account?

|||

The user is a domain Windows user. Originally the laptop was using the local system account as its service account, but I thought that might be the problem so I tried using the same service account (a domain user account that I configured specifically as a SQL Server Service Account) that I use on the server, but unfortunately I got the same results.

Thanks,

-Dave

|||

OK - I think we're getting closer. I'm going to assume the laptop is on the domain, since you say you've added that Windows group to SQL Server as a login. Does the account that you are starting SQL Server with on the laptop have the ability to resolve network names?

To help investigate your problem, make sure you check the logs the system creates. You can read more about that here:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=242&rl=1

|||

The laptop is definitely on the domain. As far as the account that I am starting SQL Server with on the laptop having the ability to resolve network names, I'm not sure-how would I find that out?

Thanks,

Dave

|||I'm sure there's an easier way, but I normally just log on as that user, create a local group and then try to add someone from the domain to that local group.|||

Hi,

The SQL Server Service Account that I'm using does have the ability to resolve network names on the laptop.

Thanks,

-Dave

|||

Very odd. At this point, it sounds like you're doing everything properly. It has to be something to do with the security policies on that machine, but without access to it I'm afraid it would take a lot of back-and-forth to figure out where it is. I would suggest checking the logs for the system, using the techniques I outline in this guide:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=242&rl=1

If that doesn't help point out the issue, then you may have to open a support call so that someone can gain access to your machine. sorry that I couldn't be more help here.

|||

Dave, I don't know if this will help, but could you check the error message for the login failure in the ERRORLOG file and post here the two lines that describe the state and message of the error?

Thanks
Laurentiu

|||

Thanks for all the suggestions. It turns out that there was something going on with this group (I'm not sure what). I created another group in the same Active Directory location with the same users as members and it works fine.

Thanks,

-Dave

Cannot connect to localhost using Windows Security Group as login

I have a test environment set up at home with a small windows server 2003 Active Directory domain. I created a windows security group on the dc and added myself and another user to the group. I then use this group as a login for SQL Server and add the login to the sysadmin server role. This works exactly as I would expect on the Windows Server computer (which is also the Domain Controller) regardless of which computer I use to connect to this Windows Server computer. However, when I set up the exact same login on the local sql server instance of my client laptop running windows xp pro, the I get an error message (Login failed for user MYDOMAIN\dgolds...) when I attempt to connect to the local instance of SQL server, even though I am able to connect to the instance on the domain controller with no problems using this same client laptop under the same user account. The only way I am able to connect locally on the laptop under this account (which does not have local admin privileges on any box, for testing purposes) is to add the user account separately as a login, rather than as part of a group. I'm curious as to why being a member of a security group that is part of the sysadmin server role does not allow me to connect to the local instance of SQL Server on this client laptop. The windows server computer is running SQL2k5 Enterprise, and the client laptop is running SQL2k5 developer. Any insight into this would be appreciated.

Thanks,

Dave

Let's focus on the laptop for a moment, becuase that seems to be where your question is. Is all this true?

1. You have an instance of SQL Server on the laptop

2. You have a user (UserA) in a Windows group (GroupA)

3. The Windows Group (GroupA) has been added as a login to SQL Server as a Windows account

4. When UserA tries to log in to the local instance of SQL Server on the laptop, they can't get in

5. If you add UserA to the local Instance of SQL Server on the laptop, they can get in

Do I have all that right?

|||

All of that is correct.

Thanks,

-Dave

|||

Then let me ask a few more questions:

1. Is the user a domain Windows user?

2. Which service accounts did you use to start SQL Server on the laptop? Are they starting with a user account or the localsystem account?

|||

The user is a domain Windows user. Originally the laptop was using the local system account as its service account, but I thought that might be the problem so I tried using the same service account (a domain user account that I configured specifically as a SQL Server Service Account) that I use on the server, but unfortunately I got the same results.

Thanks,

-Dave

|||

OK - I think we're getting closer. I'm going to assume the laptop is on the domain, since you say you've added that Windows group to SQL Server as a login. Does the account that you are starting SQL Server with on the laptop have the ability to resolve network names?

To help investigate your problem, make sure you check the logs the system creates. You can read more about that here:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=242&rl=1

|||

The laptop is definitely on the domain. As far as the account that I am starting SQL Server with on the laptop having the ability to resolve network names, I'm not sure-how would I find that out?

Thanks,

Dave

|||I'm sure there's an easier way, but I normally just log on as that user, create a local group and then try to add someone from the domain to that local group.|||

Hi,

The SQL Server Service Account that I'm using does have the ability to resolve network names on the laptop.

Thanks,

-Dave

|||

Very odd. At this point, it sounds like you're doing everything properly. It has to be something to do with the security policies on that machine, but without access to it I'm afraid it would take a lot of back-and-forth to figure out where it is. I would suggest checking the logs for the system, using the techniques I outline in this guide:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=242&rl=1

If that doesn't help point out the issue, then you may have to open a support call so that someone can gain access to your machine. sorry that I couldn't be more help here.

|||

Dave, I don't know if this will help, but could you check the error message for the login failure in the ERRORLOG file and post here the two lines that describe the state and message of the error?

Thanks
Laurentiu

|||

Thanks for all the suggestions. It turns out that there was something going on with this group (I'm not sure what). I created another group in the same Active Directory location with the same users as members and it works fine.

Thanks,

-Dave

Cannot connect to localhost using Windows Security Group as login

I have a test environment set up at home with a small windows server 2003 Active Directory domain. I created a windows security group on the dc and added myself and another user to the group. I then use this group as a login for SQL Server and add the login to the sysadmin server role. This works exactly as I would expect on the Windows Server computer (which is also the Domain Controller) regardless of which computer I use to connect to this Windows Server computer. However, when I set up the exact same login on the local sql server instance of my client laptop running windows xp pro, the I get an error message (Login failed for user MYDOMAIN\dgolds...) when I attempt to connect to the local instance of SQL server, even though I am able to connect to the instance on the domain controller with no problems using this same client laptop under the same user account. The only way I am able to connect locally on the laptop under this account (which does not have local admin privileges on any box, for testing purposes) is to add the user account separately as a login, rather than as part of a group. I'm curious as to why being a member of a security group that is part of the sysadmin server role does not allow me to connect to the local instance of SQL Server on this client laptop. The windows server computer is running SQL2k5 Enterprise, and the client laptop is running SQL2k5 developer. Any insight into this would be appreciated.

Thanks,

Dave

Let's focus on the laptop for a moment, becuase that seems to be where your question is. Is all this true?

1. You have an instance of SQL Server on the laptop

2. You have a user (UserA) in a Windows group (GroupA)

3. The Windows Group (GroupA) has been added as a login to SQL Server as a Windows account

4. When UserA tries to log in to the local instance of SQL Server on the laptop, they can't get in

5. If you add UserA to the local Instance of SQL Server on the laptop, they can get in

Do I have all that right?

|||

All of that is correct.

Thanks,

-Dave

|||

Then let me ask a few more questions:

1. Is the user a domain Windows user?

2. Which service accounts did you use to start SQL Server on the laptop? Are they starting with a user account or the localsystem account?

|||

The user is a domain Windows user. Originally the laptop was using the local system account as its service account, but I thought that might be the problem so I tried using the same service account (a domain user account that I configured specifically as a SQL Server Service Account) that I use on the server, but unfortunately I got the same results.

Thanks,

-Dave

|||

OK - I think we're getting closer. I'm going to assume the laptop is on the domain, since you say you've added that Windows group to SQL Server as a login. Does the account that you are starting SQL Server with on the laptop have the ability to resolve network names?

To help investigate your problem, make sure you check the logs the system creates. You can read more about that here:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=242&rl=1

|||

The laptop is definitely on the domain. As far as the account that I am starting SQL Server with on the laptop having the ability to resolve network names, I'm not sure-how would I find that out?

Thanks,

Dave

|||I'm sure there's an easier way, but I normally just log on as that user, create a local group and then try to add someone from the domain to that local group.|||

Hi,

The SQL Server Service Account that I'm using does have the ability to resolve network names on the laptop.

Thanks,

-Dave

|||

Very odd. At this point, it sounds like you're doing everything properly. It has to be something to do with the security policies on that machine, but without access to it I'm afraid it would take a lot of back-and-forth to figure out where it is. I would suggest checking the logs for the system, using the techniques I outline in this guide:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=242&rl=1

If that doesn't help point out the issue, then you may have to open a support call so that someone can gain access to your machine. sorry that I couldn't be more help here.

|||

Dave, I don't know if this will help, but could you check the error message for the login failure in the ERRORLOG file and post here the two lines that describe the state and message of the error?

Thanks
Laurentiu

|||

Thanks for all the suggestions. It turns out that there was something going on with this group (I'm not sure what). I created another group in the same Active Directory location with the same users as members and it works fine.

Thanks,

-Dave