SQL Adapter 访问远端数据库

 

今天做了一个接收文件然后用SQL Adapter调用sp insert的例子

CREATE PROCEDURE SP_Demo

@Name varchar(30),

@Mail varchar(30)

as

begin

set nocount on;

insert into Person(Name,Mail) values(@Name,@Age)

select scope_identity() as Id for xml raw

end

GO

这个在本地执行是好的，但是我把SQLAdapter配置里的connection string换成远程数据库时后就出问题了

一开始报错

MSDTC on server “upzoneIBM” is unavailable

检查远程服务器的SQL DTC服务，发现没有启动，但是怎么都是启动失败,

“MS DTC log file not found. After ensuring that all Resource Managers coordinated by MS DTC have no indoubt transactions, please run msdtc -resetlog to create the log file.”

检查一下发现C:\WINDOWS\system32\MsDtc 目录下没有MSDTC.LOG文件,恍然大悟,原来我以前用批处理自动删除了所有的*.log文件

用命令 msdtc –resetlog 回复log文件,好像还有设置一下这个文件的访问权限问题等等,

终于dtc启动成功了

Control PanelàAdministrative ToolsàComponent Services,展开Component ServicesàMy Computer,右键点My Computer,选择MSDTC tab,左下角点Security Configuration按钮,设置DTC的安全权限问题,也不知道到底是哪个，反正都勾上了,我在远程和本地机器上都作了同样的设置，这个很重要，要不然就没有权限访问了

继续执行一下还是有问题

new transaction cannot enlist in the specified transaction coordinator,这个就是我忘记设置本地的MSDTC了，和远程的同样的设置.

还有如果有防火墙的话，要在Firewall的Exception里添加

1. In the Add a Program dialog box, click Browse, and then locate the Msdtc.exe file. By default, the file is stored in the Installation drive:\Windows\System32 folder.

2. In the Add a Port dialog box, type 135 in the Port number text box, and then click to select the TCP option.

That’s all

KB: http://support.microsoft.com/kb/873160

1.Make sure that the Log On As account for the MSDTC service is the Network Service account. To do this, follow these steps:

a. Click Start, and then click Run.

b. In the Run dialog box, type Services.msc, and then click OK.

c. In the Services window, locate the Distributed Transaction Coordinator service under Name in the right pane.

d. Under the Log On As column, see whether the Log On As account is Network Service or Local System.
If the Log On As account is Network Service, go to step 2. If the Log On As account is Local System, continue with these steps.

e. Click Start, and then click Run.

f. In the Run dialog box, type cmd, and then click OK.

g. At the command prompt, type Net stop msdtc to stop the MSDTC service.

h. At the command prompt, type Msdtc –uninstall to remove MSDTC.

i. At the command prompt, type regedit to open Registry Editor.

j. In Registry Editor, locate, and then delete the following subkey:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC

k. Close Registry Editor.

l. At the command prompt, type Msdtc –install to install MSDTC.

m. At the command prompt, type Net start msdtc to start the MSDTC service.
Note The Log On As account for the MSDTC service is set to the Network Service account.

2.To allow the network transaction, you must enable MSDTC. To do this, follow these steps:

a. Click Start, and then click Run.

b. In the Run dialog box, type dcomcnfg.exe, and then click OK.

c. In the Component Services window, expand Component Services, expand Computers, and then expand My Computer.

d. Right-click My Computer, and then click Properties.

e. In the My Computer Properties dialog box, click Security Configuration on the MSDTC tab.

f. In the Security Configuration dialog box, click to select the Network DTC Access check box.

g. To allow the distributed transaction to run on this computer from a remote computer, click to select the Allow Inbound check box.

h. To allow the distributed transaction to run on a remote computer from this computer, click to select the Allow Outbound check box.

i. Under the Transaction Manager Communication group, click to select the No Authentication Required option.

j. In the Security Configuration dialog box, click OK.

k. In the My Computer Properties dialog box, click OK.

3.Configure Windows Firewall to include the MSDTC program and to include port 135 as an exception. To do this, follow these steps:

a. Click Start, and then click Run.

b. In the Run dialog box, type Firewall.cpl, and then click OK.

c. In Control Panel, double-click Windows Firewall.

d. In the Windows Firewall dialog box, click Add Program on the Exceptions tab.

e. In the Add a Program dialog box, click Browse, and then locate the Msdtc.exe file. By default, the file is stored in the Installation drive:\Windows\System32 folder.

f. In the Add a Program dialog box, click OK.

g. In the Windows Firewall dialog box, click to select the msdtc option in the Programs and Services list.

h. Click Add Port on the Exceptions tab.

i. In the Add a Port dialog box, type 135 in the Port number text box, and then click to select the TCP option.

j. In the Add a Port dialog box, type a name for the exception in the Name text box, and then click OK.

k. In the Windows Firewall dialog box, select the name that you used for the exception in step j in the Programs and Services list, and then click OK.