The ole db access interface "sqlncli" returns the message "unspecified error ".

This month, the company acquired a small company. The database of this small company uses the 32-bit SQL2000, which has been in use for a long time and the system is relatively stable. In line with the principle of cost saving, the company retains the original company's management system, but requires that important data be uploaded to the company's 64-bit sql2005 database every day, this simple job is done by a person like Gao Sheng.

I thought this was a simple task, but when I built the linkserver, the following error was reported, and my nightmare began:

Ole db provider "sqlncli" for linked server "test_gao" Returned message "unspecified error ".
Ole db provider "sqlncli" for linked server "test_gao" Returned message "the stored procedure required to complete this operation cocould not be found on the server. Please contact your system administrator .".
Message 7311, level 16, status 2, 1st rows
Cannot obtain the schema rowset "dbschema_tables_info" for ole db provider "sqlncli" for linked server "test_gao". The Provider supports the interface, but returns a failure code when it is used.

Error 7311:Do I need to upgrade the version installed in the "test_gao" service? After checking whether the server of the other party is the SP3 version, a SP4 patch is downloaded, but the problem persists after installation. Then I used sql2005 installed on my computer to establish a linkserver test with the remote SQL2000. The results were normally connected and the data could be queried. This is a strange question. What kind of spiritual events have occurred? My own computer and two servers are both installed Windows Server 2003 sp2 operating systems. Why can I connect them to sql2005, but not to the server?

It turns out that after the 32-bit SQL2000 is upgraded to SP3 or SP4, You need to manually execute the instcat In the patch package. the SQL script can connect to the 64-bit sql2005, run cmd to enter the command line mode, enter the following statement to complete the installation, then the linkserver can be connected normally.

Input: osql-u Gaosheng-P Sheng-s 127.0.0.1-I c: "sql2ksp4" Install "instcat. SQL

-U: SQL2000 login name. If you have administrative permissions, it is best to use the SA user.
-P: Password
-S: server name or IP address
-I: Specifies the path and name of the script. It must be case sensitive.

Refer to msdn:Http://support.microsoft.com/kb/906954/zh-tw 

 

After the linkserver is created, the next step is to query data and write data. The SQL statement is easy to write, but the problem occurs again after the transaction is started:

Ole db provider "sqlncli" for linked server "test_gao" Returned message "the partner Transaction Manager has disabled its support for remote/network transactions .".
Message 7391, level 16, status 2, 1st rows
The operation cocould not be performed med because ole db provider "sqlncli" for linked server "test_gao" was unable to begin a distributed transaction.

Error 7391:This obviously requires the support of the Distributed Transaction Coordinator because the service is enabled. Open Control Panel-Administrative Tools-service, and enable the Distributed Transaction Coordinator (MSDTC) service on the two servers. Note that you must select the network service user as the MSDTC Service login name. Then, set advanced options, run dcomcnfg.exe, go to component services, right-click my computer, and choose "MSDTC-Security Configuration". Check "Network DTC Access" to allow inbound and outbound access, verification is not required. Other options can be selected as needed, not required.

Ole db provider "sqlncli" for linked server "test_gao" Returned message "no transaction is active .".
Message 7391, level 16, status 2, 1st rows
The operation cocould not be performed med because ole db provider "sqlncli" for linked server "test_gao" was unable to begin a distributed transaction.

Error 7391:Why is it that the distributed transaction cannot be started because there is no active transaction? The advanced configuration has been done and verification is not required. Is this still not feasible? The problem is that the two servers are not in the same domain, so how can we make them trust each other? Then we need to configure the hosts file.

C: "Windows" System32 "drivers" etc "hosts, locate the hidden hosts system file in the system directory, and add the IP address and Host Name of the other party at the end of the file, if the server is a cluster, add the cluster address and virtual IP address, for example:

136.16.25.79 gaosheng_cluster
136.16.25.40 gaosheng_v1
136.16.25.80 gaosheng_db1

The first is the IP address and Host Name of the cluster host, the second is the virtual IP address and host name, and the third is the real address. The first two addresses are added to ensure that the server goes down, the cluster implements failover and linkserver can still be used normally. If it is not a cluster server, you only need the third real address, so that both parties can trust each other and use distributed transactions normally. Note that the hosts file must be configured on both sides, with the IP address and file name of the other party configured.

Ole db provider "sqlncli" for linked server "test_gao" Returned message "cannot start more transactions on this session .".
Message 7395, level 16, status 2, 1st rows
Unable to start a nested transaction for ole db provider "sqlncli" for linked server "test_gao". A nested transaction was required because the xact_abort option was set to off.

Come back! Why is there an error?
Error 7395:Let me know how to do this. Just add set xact_abort on before begin transaction.

Now, the problem is finally solved. So many problems have occurred when I build a simple linkserver. On the one hand, I have poor learning skills, and on the other hand, it is reflected that operations on a single server are usually performed, and many problems cannot be solved at all, theory alone is far from enough. However, the last solution also reflects my ability to solve the problem. If I do not understand it, I can ask people. If I understand it, I can answer people. If no one understands it, I can ask God. (Msdn and csdn)

 

Summary:

In actual work, There will be various problems. After all, things that are always tested and learned on a server are limited. Therefore, you must learn to solve the problem next time, exercise your problem solving skills more. If you do not have multiple servers for environment learning, I recommend Microsoft software Virtual PC, which can simulate many computers on one computer and have their own IP addresses, it is helpful for us to learn about multi-server operations.

Programmers should not only learn code, but also analyze and solve problems. Only qualified programmers can possess these skills. Of course, there is also a lot of communication is necessary, but the premise is that you first think about the problem, then to communicate. I am very eager to communicate with many programmers here. Although the current level is not high, there will always be some achievements in the accumulation of 1.1 points.

 

Write two errors that are most likely to be reported when SQL Server 2005 is used for remote connection for the first time.

Ole db provider "sqlncli" for linked server "test" Returned message "Login timeout expired ".
Ole db provider "sqlncli" for linked server "test" Returned message "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. ".
MSG 65535, level 16, state 1, line 0
SQL Network Interfaces: error locating server/instance specified [xffffffff].

This is the most common mistake for new users to use SQL Server 2005 for the first time because remote connection is disabled by default. Many people who are not familiar with databases do not know how to configure them. Therefore, SQL Server 2005 provides the settings in this "peripheral application configurator, change the remote connection mode to local connection and remote connection. Only TCP/IP is used. Note that after this option is changed, the sqlserver service must be restarted to take effect. In addition, an important service, SQL Server Browser, enables this service client for normal access.

MSG 15281, level 16, state 1, line 1
SQL Server blocked access to statement 'openrowset/OpenDataSource 'of component 'ad hoc distributed queries 'because this component is turned off as part of the security configuration for this server. a system administrator can enable the use of 'ad hoc distributed queries 'by using sp_configure. for more information about enabling 'ad hoc distributed queries ', see "surface area configuration" in SQL Server books online.

This error is also caused by the default configuration of SQL Server 2005. The OpenRowSet/OpenDataSource permission is disabled by default. The solution is also configured in the "peripheral application configurator". Enable OpenRowSet/OpenDataSource in the ad hoc remote query.

Of course, you can also use the following code to execute the following code:

Sp_configure 'show advanced options', 1; -- enable advanced configuration
Go
Reconfigure;
Go
Sp_configure 'ad hoc distributed queries ', 1; -- enable ad hoc query
Go
Reconfigure;
Go
Sp_configure 'show advanced options', 0; -- disable Advanced Configuration
Go
Reconfigure;
Go

If my upgrading level is limited, it will inevitably lead to errors. I hope you will criticize and correct me. Thank you!

Address:Http://blog.csdn.net/hb_gx/archive/2007/10/15/1826436.aspx