MSSQLServer connection problem analysis and solution (2)

The database server connection error involves Configuration Analysis and Processing. Please go to MSSQLServer's connection problem analysis and solution (1) and check a connection error, as shown in 1: the figure shows the error message when the local Serv is connected to the remote Serv for DTS package migration. The local Serv is SQL2008, and the remote Serv is SQL2000. I. Obtained

The database Server connection error involves Configuration Analysis and Processing. Please go to MSSQL Server connection problem analysis and solution (1) and check a connection error, as shown in 1: the figure shows the error message when the local Serv is connected to the remote Serv for DTS package migration. The local Serv is SQL2008, and the remote Serv is SQL2000. I. Obtained

For Configuration Analysis and Handling of database server connection errors, see:MSSQL Server connection problem analysis and solution (1)

Let's look at a connection error, as shown in 1:

The figure shows the error message when the local Serv is connected to the remote Serv for DTS package migration. The local Serv is SQL2008, and the remote Serv is SQL2000.

1. Information obtained:

The following message is displayed in the figure:

1. Network Connection error.

For example:Network-related errors occur when you establish a connection with SQL Server.

2. the Instance name is incorrect.

For example:An instance-specific error occurs when you establish a connection with SQL Server.

3. instance connection error.

For example:The server is not found or cannot be accessed.

At the same time, the prompt in the figure shows the suggested solution: Verify that the Instance name is correct and that SQL Server has been configured to allow remote connection.

Ii. Actual Situation: The SP4 patch has been installed in SQL2000, and Basic settings such as firewall are correct. There must be a message that directly or indirectly leads to an error in the information given above. here we can see that instance 2 is named incorrectly (because there is no problem with network connection and access, ).

1. Before the error message is displayed, enter USER214 as the server connection and enter the SA account and password.

2. Because SQL2000Serv has not been connected for a long time, I forgot the Instance name and password. Remember that the remote SQL2000 instance is not the default instance, so when the default instance is started, you can obtain the name of the instance by performing the following query, or connect the mstsc to the remote Serv viewing service.

What if the default instance of the remote Serv instance is not started?

Only the mstsc can be switched to a remote Serv. In CMD, enter net start mssqlserver to start the default instance. For example:

Microsoft Windows [version 5.2.3790]


C:/Documents and Settings/Administrator> net start mssqlserver
The SQL Server (MSSQLSERVER) service is starting...
The SQL Server (MSSQLSERVER) service has been started successfully.

After the SQL Service of the remote Serv is started, run the T-SQL:

--> 1 enable xp_cmdshell

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

-- Enable xp_cmdshell

EXEC sp_configure 'xp _ Your shell', 1

GO

RECONFIGURE

GO

--> 2 query database-related service names in the OS

Declare @ a table (a varchar (200 ))

Insert into @

Exec xp_cmdshell 'SC query state = all'

Select case when a LIKE '% $ %'

THEN 'COMMAND instance' + REPLACE (a, 'service _ NAME :','')

ELSE 'default instance' + REPLACE (a, 'service _ NAME: ', '') END

As service name

From @

Where a like 'service _ NAME: MSSQL % 'andLEN (a) <28

-- Result:

Service name

Command instance MSSQL $ CL05

Command instance MSSQL $ CL2K

Default instance MSSQLSERVER

3. We can see from the returned results that, in fact, the SQL2000 Instance name of the remote Serv is CL2K, so let's look at it again, as shown in 2.

This is still the database connection interface for DTS package migration. The correct server name is 10.0.0.214/cl2k, 1444.

Why is it?

The difference between USER214/cl2k, 1444 and 10.0.0.214/cl2k is that the former is parsed by machine name, and the latter is connected by IP address. Because TCP/IP is enabled for All database connection methods.

10.0.0.214/cl2k, 1444 indicates that the command instance is cl2k, and 1444 indicates the SQL Service port of the cl2k instance, because SQL2008, SQL2005, and SQL2000 are configured on the remote Serv to enable the TCP/IP connection at the same time, the port is modified.

-0-conclusion: this problem is troublesome in the process. The result is very simple because the Instance name is incorrect. In addition to being careful, You must be more careful.

Posted by: select left ('claro', 2) @ 10:35

Lable: SQL