Posted by Erland Sommarskog on 01/07/07 10:18

SQL Server Questions (farkerku@gmail.com) writes:
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx;
>
> When we run this from the Query Analyzer, it completes with no problems
> in a few seconds.

And if you slap BEGIN/COMMIT TRANSACTION around it?

> When we add the DTS Package as the ActiveX Script (VB Script) to the
> Local Package, it times out at "obj_Conn.Execute str_Sql"

I believe this is the problem is the increased security with DTC in Windows
2003. We had a problem with this a while back. This was what the support
professional from MS told us:

Let starts with a few troubleshooting steps, first take security MSDTC
configurations at the minimum level. Component Services -> My Computer
-> Right click proprieties ->MSDTC -> Select use local coordinator ->
Security Configuration -> Enable Network DTC access, Enable remote
administration, Enable Allow Remote clients, Enable allow inbound and
outbound, Select no authentication required, Enable XA transactions and
verify that NT AUTHORITY\NetworkService is set in DTC Logon account.

Restart DTC service and SQL Server and test if issue persists.

If problem persist please follow this KB article:
http://support.microsoft.com/kb/306843 - How to troubleshoot
MS DTC firewall issues.

I think the step to restart SQL Server is unnecessary.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Link Server doesn't work except through query analyzer: MSSQL freezing / timing out
• Prev in forum: Re: Idea: new database object that defines a FROM clause
• Thread view: Re: Link Server doesn't work except through query analyzer: MSSQL freezing / timing out

[Reply to this message]