DTS error not configured for data access

I just had to rebuild master and msdb in Sql 2000. Now any DTS package accessing that machine fails with the message 'Server __ is not configured for DATA ACCESS.' The message seems to be coming from OLE. Everything else, including applications, scheduled jobs, etc. seems to work fine.

How can I restore data access?You may need to allow other SQL servers to connect. In enterprise manager, right-click on the server and select properties. On the Connections tab, check the box in the Remote Servers frame "Allow other SQL Servers to connect remotely to this SQL Server using RPC".|||No good. Properties showed remote connection OK. I unchecked, rechecked and rebooted to make sure. same problem.|||Can you create a connection to the server in a new DTS package?

It may be that you have to recreate the Server Connections in the existing package(s).|||No. Fails for both new and existing packages. The rebuild was apparently caused by converting databases from another machine from Sql 6.5. After the conversion we saw some odd things showing in msdb and lost alerts, operators etc. I also saw some table structure changes. Even though we didn't include msdb it looked like stuff from 6.5's was showing up. This looks to be a repercussion related to that.

Waht I can't seem to find anywhere is any discussion of the error. DTS's error window says the source is OLE but gives no eror number|||Just to add to the fun, I ignored the error message and built the queries without the point & click junk. Package ran fine. So it looks like all I've lost the visual references to that server.sql

DSNLess connection with Failover to a SQL Server 2005 cluster

Hi,

We have configured two SQL Server 2005 in a cluster (without Witness).

We have severals ASP (using ADO) that are connecting to SQL Server 2005 with SQL Native Client in a DSNLess connection String :

driver={SQL Native Client}; server=MAIN\MAIN; Failover Partner=BCKUP\BCKUP; uid=log;pwd=pwd;database=MyBase

When using this connection string, the connection to MAIN is alrigth, but when we simulate a unavailability situation on "MyBase" or on the entire MAIN instance, the new connection are joining the MAIN and not the BCKUP SQL Server.

We tryed several Connection Strings including Failover_Partner, Initliag Catalog instead of database... but no one succed in joining the BCKUP SQL Server.

I precise that the DNS on the PC that connect to SQL server is configured and both MAIN and BCKUP are resolved in command line or in explorer.

Is there an other driver that must be used, or is there an other parameter to set in the connection string ?

Regards.

When you configure Mirroring without a witness, failover is not automatic. You must manually failover the database - you do this via alter database or using the button provided in the database mirroring properties.

Matt Hollingsworth

Sr. Program Manager

Microsoft SQL Server

DSNLess connection with Failover on SQL Server 2005 Cluster ?

Hi,

We have configured two SQL Server 2005 in a cluster (without Witness).

We have severals ASP (using ADO) that are connecting to SQL Server 2005 with SQL Native Client in a DSNLess connection String :

driver={SQL Native Client}; server=MAIN\MAIN; Failover Partner=BCKUP\BCKUP; uid=log;pwd=pwd;database=MyBase

When using this connection string, the connection to MAIN is alrigth, but when we simulate a unavailability situation on "MyBase" or on the entire MAIN instance, the new connection are joining the MAIN and not the BCKUP SQL Server.

We tryed several Connection Strings including Failover_Partner, Initliag Catalog instead of database... but no one succed in joining the BCKUP SQL Server.

I precise that the DNS on the PC that connect to SQL server is configured and both MAIN and BCKUP are resolved in command line or in explorer.

Is there an other driver that must be used, or is there an other parameter to set in the connection string ?

Regards.

Failover Partner is used in the case of database mirroring, not for the case of cluster. Check here: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Cluster is transparent to client, i.e., client should be able to connect to the server without knowing the server is a cluster or not. In cluster case, use "server=your_virtual_server_name" in the connection string. Please check my blog at http://blogs.msdn.com/sql_protocols/archive/2005/12/05/500013.aspx and let us know if you have further problem.