SQL Server distributed with triggers to insert data

This month the head office acquired a small company, the small company's database with 32-bit Sql2000, has been used for a long time, the system is relatively stable. In line with the principle of cost-saving, the head office retains the original company's management system, but requires important data uploaded to the head office's 64-bit Sql2005 database every day, this simple work is given to me by the small people to complete.

Originally thought this is a very simple errand, but when I built the linkserver but then reported the following mistake, since 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 OP Eration could not being found on the server. Please contact your system administrator. "
Msg 7311, Level 16, State 2, line 1th
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 was used.

error 7311: does the version installed on the server "Test_gao" need to be upgraded? Check the other side of the server or SP3 version, so download a SP4 patch, but the problem remains after installation. Then I used my computer installed on the Sql2005 and remote Sql2000 set up a linkserver test, the result is very normal to connect, and can query out the data. This question is really strange, what happened to the supernatural incident? My own computer and two servers are installed Windows Server 2003 SP2 operating system, why the same Sql2005 my own can be connected, and the server is not connected?

The original is because the 32-bit Sql2000 upgrade to SP3 or SP4 after the need to manually execute the Instcat.sql script within the patch package to connect to the 64-bit Sql2005, execute cmd into the command line mode, enter the following statement, complete the installation, this linkserver able to connect normally.

Input: Osql-u gaosheng-p sheng-s 127.0.0.1-i c:/sql2ksp4/install/instcat.sql

-u:sql2000 the login name, if you have administrative rights, it is best to use the SA user
-P: Password
-S: Server name or IP address
-I: The path and name of the script, note case.

Reference MSDN:HTTP://SUPPORT.MICROSOFT.COM/KB/906954/ZH-TW

Linkserver after the next step is to check data write data, SQL statement is very simple to write, but open the transaction after the problem:

OLE DB Provider "SQLNCLI" for linked server "Test_gao" returned message "The Partner transaction manager have disabled its Support for remote/network transactions. ".
Msg 7391, Level 16, State 2, line 1th
The operation could not being performed because OLE DB provider "SQLNCLI" for linked server "Test_gao" is unable to begin a Distributed transaction.

Error 7391: This is obviously due to the transaction being turned on, which requires the support of the Distributed Transaction Coordinator. Open Control Panel-Administrative Tools-services, turn on the Distributed Transaction Coordinator (MSDTC) service on two servers, and note that the login name of the MSDTC service must be selected by the NETWORK service user. Then set advanced options, run Dcomcnfg.exe, go to Component Services, select My Computer right-click Properties, MSDTC-Security Configuration, tick: Network DTC Access, allow inbound, allow outbound, and do not require authentication. The others can be selected as needed, not a mandatory option.

OLE DB Provider "SQLNCLI" for linked server "Test_gao" returned the message "No transaction is active."
Msg 7391, Level 16, State 2, line 1th
The operation could not being performed because OLE DB provider "SQLNCLI" for linked server "Test_gao" is unable to begin a Distributed transaction.

Error 7391: How is this error, there is no active transaction, unable to start the distributed transaction? Advanced configuration has also been done, and has not been required to verify, is this still not possible? That's the trouble, two servers are not in the same domain, so how to let him trust each other, then you need to configure the hosts file.

C:/windows/system32/drivers/etc/hosts, locate this hidden hosts system file in the system directory, add the IP address and host name to the end of the file, If the server is clustered, add cluster addresses and virtual IP addresses, such as:

136.16.25.79 Gaosheng_cluster
136.16.25.40 GAOSHENG_V1
136.16.25.80 GAOSHENG_DB1

The first is the IP and host name of the cluster host, the second is the virtual IP and hostname, the third is the real address, plus the first two addresses are to ensure that the server down, cluster do a failover linkserver can also be used normally. If not the cluster server only need a third real address on the line, so that the two sides can trust each other, the normal use of distributed transactions. Note that the Hosts file must be configured on both sides, configured with the IP address and file name of the other.

OLE DB Provider "SQLNCLI" for linked server "Test_gao" returned the message "Cannot start more transactions on this session."
Msg 7395, Level 16, State 2, line 1th
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 is set to OFF.

It's coming! Why is there a mistake?
error 7395: OK, just tell me how to do it, add SET xact_abort on before Begin Transaction.

Now finally is not a mistake, query modification also no problem, the problem finally solved. To build a simple linkserver incredibly there are so many problems, on the one hand, I learned not fine, on the other hand, is usually a single server operation, a lot of problems are not met, light theory is far from enough. But finally can solve the problem also reflects my ability to solve problems or some, do not understand to ask people, do understand on the answer, no one knows can also ask God. (MSDN and CSDN)

Summarize:

In the actual work will appear a variety of problems, always on a server to test and learn what is limited, so the next time you have to learn to solve the problem, you have to exercise their ability to solve problems. If you do not have multiple server environment learning, I recommend a Microsoft software Virtual PC, this software can be on a computer simulation of many computers out, have their own IP, for us to learn multi-server operation is very helpful.

Programmers not only learn the code, the most important thing is to analyze the problem-solving ability, with these capabilities can be considered a qualified programmer. Of course, a lot of communication is also necessary, but the premise is that there is a problem of their own brain to think, and then to communicate. I am high here also hope to be able to and more than many of the procedures of the communication technology, although the level is low, but the accumulation of 1.1 points will always be successful.

Write two of the most easily reported errors when using SQL Server 2005 remote Connection for the first time, and make a few words.

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 had occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure is caused by the fact that under the default settings SQL Server does not allow remote connections. ".
MSG 65535, Level A, State 1, line 0
SQL Network interfaces:error locating server/instance Specified [XFFFFFFFF].

This is the most common mistake for beginners to use SQL Server 2005 for the first time, because the default configuration prohibits remote connections. Many people who are not familiar with the database do not know how to configure, so SQL Server 2005 provides this "Surface Application Configurator" setting, the remote connection mode to local and remote connections, using only TCP/IP, note that this option changes after the need to restart SQL Server Service to be effective. In addition, there is a very important service SQL Server Browser, which opens the service client for normal access.

MSG 15281, Level A, 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 the "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 Onl Ine.

This error is also due to the default configuration of SQL Server 2005, which turns off the use of Openrowset/opendatasource permissions by default. The workaround is also configured in the perimeter application configurator to tick the open Openrowset/opendatasource in the Ad hoc remote query.

Of course this can also be done using the following code, execute the following code:

sp_configure ' show advanced options ', 1; --Turn on Advanced configuration
GO
RECONFIGURE;
GO
sp_configure ' Ad Hoc distributed Queries ', 1; --Open Ad hoc query
GO
RECONFIGURE;
GO
sp_configure ' show advanced options ', 0; --Turn off Advanced configuration
GO
RECONFIGURE;
GO additional basic configuration: 1, close both sides of the firewall
2. Start the MSDTC service on both sides
3. Add set XACT_ABORT ON statement before transaction starts
4. Management Tools-Component Services settings allow remote clients, allow remote administration, allow inbound, allow outbound, do not require authentication
5. DTC login account: NT AUTHORITY\NetworkService
6, server and name resolution, I here directly with the IP address should not exist this problemhere are some of your own experiences:First, the above is done according to the original practice OLE DB Provider "SQLNCLI" for linked server "Test_gao" returned the message "No transaction is active." This place can not get over, toss an afternoon, finally with Dtcping.exe to detect problems, fix. The specific problem is RCP denied access, the workaround is as follows:
Here's how:
1. Click Start, click Run, type Gpedit.msc, and then click OK to open Group Policy.
2. After you open Group Policy, select the limit for unauthenticated RPC clients, system-by-remote procedure call, Administrative Templates, computer configuration, and so on.
3. Right-click on the "Restrictions for unauthenticated RPC clients" and select "Properties" in the popup context menu to bring up the Properties dialog box. Select "Enabled" in the Settings property page, "Client restrictions to be applied for RPC Runtime unauthenticated" select "None". Click OK to complete the setup. (This step in Windows Server 2008 is to double-click the limit for unauthenticated RPC clients, and then open the appropriate configuration, other configuration for this step )
two. When configuring the host file, the hostname is determined with ipconfig-all. here is the specific code

CREATE Trigger Oppor_inserton [Opportunitybase]      for Insert-- insert    triggeras-- define variable    declare @id uniqueidentifier, @name varchar ;     -- in the inserted table, the query has    inserted record information = [Opportunityid], @name = name from inserted;    INSERT into commondb.common_opportunity values (@id, @name);    ' Add success! '; go

CREATE trigger Oppor_inserton [opportunity]      for Insert-- insert    triggeras-- define variable    declare @id uniqueidentifier, @name varchar;    -- in the inserted table, the query has inserted    record information = [Opportunityid], @name = name from inserted;    SET xact_abort on      begin distributed Tran    INSERT into remoteserver. CommonDB.dbo.common_opportunity (Id,name) VALUES (@id, @name);    Commit Tran    ' added successfully! '; go

SQL Server distributed with triggers to insert data