Problem occurs when migrating a SQL Server 2000 database to a SQL Server 2005 server

When I try to migrate a database on a SQL Server 2000 server to a SQL Server 2005 server with the Copy Database Wizard of the SQL Server Management Studio, I'm confronted with the following problem;

Performing operation...

- Add log for package (Success)
- Add task for transferring database objects (Success)
- Create package (Success)
- Start SQL Server Agent Job (Success)
- Execute SQL Server Agent Job (Error)
Messages
* The job failed. Check the event log on the destination server for details. (Copy Database Wizard)

When I take a look at 'Event viewer' on the SQL 2005 server, the following error is displayed;

InnerException-->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)

I already enabled the MSSQLSERVER network configuration protocols (TCP/IP and Named Pipes ).

How do I solve this problem?

Go into the Surface Area Configuration tool and select Configuration for Services and Connections. On your server, under Database Engine/Remote Connections make sure the Local and Remote Connections radio button is selected.|||See more about that in my screencast section on my site.

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Alan,

The Local and Remote Connections radio button is already selected on my Windows SQL 2005 server.

KevinDSE

|||Any firewall enabled on the server ? Are you running on a non default port ? The default one is 1433. Are you running SQL Server Browser service ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens,

The standard Windows firewall on my SQL 2005 server (with Windows Server 2003 Standard Edition) is disabled.
I am also running the default port 1433 for TCP/IP etc.
The SQL Server Browser service is also enabled on this machine.

|||I dont know how familiar you are wih named instances, or if you even have a named instance, but if you want to connect to a named instance, you will have to call it like the following syntax: Servername\InstanceName. Just to quite sure (wheter you are or not using SQL Server named instances, I would try putting a ,1433 after the server / server\InstanceName like Servername\InstaneName,Portnumber to explicitly call the sevrer on this port. make also sure that the services was started probably and bound the TCP port to the service.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I am having the exact same issue and was wondering what the exact solution is.

Thanks, Kyle

|||Dear kyle,

This reply may be late. But this may be helpful for those who happen to get to this topic now ;)

In the Copy wizard, at source server, make sure you connect with sa/password [ dont go with "Windows Auth"] and same with destination server. Make sure firewall at both source and destination are switched off.

Regards,
Vipin Venu