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.

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