Problem Occurs When Migrating A SQL Server 2000 Database To A SQL Server 2005 Server

Sep 20, 2006

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?

View 9 Replies

Problem Migrating Application Database From SQL Server 2000 To Express Edition 2005

Jan 21, 2008

Hi there

I am new to SQL Server, but the current project that I am working on has the following requirement:-

1) Migrate the application (a servlet based web application on Apache Tomcat) from Solaris to Wintel
2) Migrate the supporting database from SQL Server 2000 to Sql Server 2005
3) Get IIS to communicate with Tomcat for serving servlet/jsp etc to the client

Though I successfully completed item 1 above, am stuck at item 2. Details are below

Actions taken for item 2

A. Installed MS 2005
B. Created new database in MS 2005 (logged in as user 'sa')
C. Generated SQL scripts (such as create table table_A etc) from existing MS 2000
D. Genearted SQL scripts (such as insert into table_A etc) from existing MS 2000
E. Created new schema in MS 2005
F. Ran scripts C & D in the new schema. All tables are records populated.
G. Obtained new JDBC driver and test run to see if connection is working fine, and it worked. Even ran an sql statment

Code Block[select count(*) from sa.table_A]

and got appropriate response.

H. When I made the application to talk to this new database (which is a copy of Production from step C, D above), it's behaving as though it cannot find the record.
I. When I further debugged, I realised that the web application is excuting queries without mentioning the schema. For eg.

Code Block[Select firstName, lastName from table_A]

Or rather it assumes that the user connecting to database is same as the schema name.

J. To further ascertain my point, I ran the query

Code Block[Select firstName, lastName from sa.table_A]

and it worked!

Now the real problem is that I cannot modify the existing code to append a schema name and this approach is rather not recommended best practise.

I tried to match the user name with the schema name, even made this schema as default to the user. But still not finding any luck.

I request all you experts out there to help me out with this problem.

Regards,

prad.nair

View 3 Replies View Related

Migrating Reporting Services From SQL Server 2000 To SQL Server 2005

Mar 31, 2008

Hi,

I'm migrating SQl Server Reporting Services from 2000 to 2005.The reports are generated normally in 2000 but it seems that it is taking more time in 2005 or sometimes it does not generate the report at all.Could you kindly suggest a solution?

Thanks,
anne