SQLServer Distributed Query (2)

3. troubleshooting and more query methods when we access remote data in actual programming, various exceptions may occur due to different operating environments, as follows, I will propose a common exception solution and more query methods for remote data operations. 3.1 remote connection cannot be established. In fact, this problem is extremely common in Distributed queries. this question is caused.

3. troubleshooting and more query methods when we access remote data in actual programming, various exceptions may occur due to different operating environments, as follows, I will propose a common exception solution and more query methods for remote data operations. 3.1 remote connection cannot be established. In fact, this problem is extremely common in Distributed queries. this question is caused.

<3> troubleshooting and more query methods

When we access remote data in actual programming, various exceptions may occur due to different operating environments, as follows, I will propose a common exception solution and more query methods for remote data operations.

<3.1> A remote connection cannot be established.

In fact, this problem is extremely common in Distributed queries, and there are too many factors that cause this problem. At the moment, we cannot determine where the exception actually occurs. We can only set it one by one through troubleshooting:

For example, when we perform a query after establishing an association:

The prompt is: the remote connection times out. The cause may be that the remote server actively denies access!

Make sure that your service is running and runs automatically at startup in SQL Server Configuation Manager.

Check whether the SQl2005 peripheral configuration DataBaseEngine allows remote connection again:

After setting, you also need to set the SQL Server Analysis Services Analysis Service to support remote data query:

If the firewall is enabled on the remote Server, the current SQL Server instance may be blocked. Therefore, when the Server enables the firewall, you must create exceptions for SQL DAtaBase to prevent client requests from being intercepted.

<3.2> the process is occupied by other users.

When we create an action in a remote distributed query or create a new database. sometimes the system prompts "this database cannot be operated and other processes are occupied. as a result, we cannot access the database. or execute the creation operation we want to do.

In this case, we can use the SA permission to query the processes occupied by the Master database and Kill the Process. Query:

1: -- [the sysprocesses table stores information about running on Microsoft®SQL Server™Process information.
2: -- these processes can be client processes or system processes. Sysprocesses is only stored in the master database]
4: use Master
5: go
7: SELECT * FROM sysprocesses, sysdatabases WHERE sysprocesses. dbid = sysdatabases. dbid AND sysdatabases. Name = 'mermerdb'
9: select * from sysprocesses
11: select * from sysdatabases
13: -- kill the process occupied
14: kill5

When the process is cleared, it is possible that the ACCESS database is occupied by the system process. In this case, Sa cannot be used to kill the system. The following message is displayed:

"Only use Process can be Kill" in SQl2005, Only user processes can be Kill.

<3.3> more query operations

In practice, we usually need to have more requirements on data read/write. for example, you can remotely connect to multiple servers to read data or submit local data to the server. distributed transactions are used for batch operations to improve efficiency and performance. the following describes how to perform data operations in Distributed queries:

Import remote data locally:

1: -- import data
2: selecttop (3) * into TestDB. dbo. CopyDb from [192.168.10.76]. wl. dbo. Users

During import, the system automatically creates a CopyDB table locally using the Into method to completely copy the data structure of the Users table on the remote server. however, note that the CopyDB does not contain the primary key and index constraints of the original table. although it can be built quickly, the primary key and index settings will be lost.

Remote import of local data:

-- Import a local table to a remote table [openWset mode]
Insertopenrowset ('sqloledb', 'SQL Server name'; 'username'; 'Password', database name. dbo. Table Name) select * from local table
-- Import a local table to a remote table [open Query mode]
Insertopenquery (ITSV, 'select * FROM database. dbo. Table name ')

Update local table data:

1: -- import a local table to a remote table [opendataSource mode]
2: insertopendatasource ('sqlodb', 'Data Source = ip/ServerName; User ID = login name; Password = password'). database. dbo. Table Name
3:
4: -- update local table [openowset mode]
5: update B set B. column A =. column A fromopenrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. dbo. table name)
6: as a innerjoin local table B on a. column1 = B. column1

Of course, there are more ways to operate distributed queries. You can try it.

<4> slogan

As shown above, I recently processed various aspects of distributed queries in my project. from system architecture to division, you can query detailed operation details. these are basic applications. of course, there are also many references. and verify the cause of the problem. the limited length of writing is a little hasty. it is inevitable that there will be some mistakes and I hope you can correct them.