Link Server doesn't work except through query analyzer: MSSQL freezing / timing out

Environment:
Server1 (Local)
OS Windows 2000 Server
SQL Server 2000

Server2 (Remote)
OS Windows 2003 Server
SQL Server 2000
(Both with most recent service packs)

Using Enterprise Manager, we have set up the Link Server (LINK_A) in
the Local Server 1 to connect to Server 2.

The SQL we need to run is the following:

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.

Our problem:

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"

Dim Sql, obj_Conn
Set obj_Conn = CreateObject("ADODB.Connection")
obj_Conn.Open XXXX
obj_Conn.BeginTrans

str_Sql = "INSERT INTO table1("
str_Sql = str_Sql & "column1"
str_Sql = str_Sql & ", column2"
str_Sql = str_Sql & ")"
str_Sql = str_Sql & " SELECT Ａ.column1"
str_Sql = str_Sql & ", A.column2"
str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"
str_Sql = str_Sql & " WHERE A.column1 0"
str_Sql = str_Sql & ";"
obj_Conn.Execute str_Sql
------------------
When we make a Stored Procedure and run the following SQL, it freezes.

INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 xxxx

We've also tried the following with the same results;

INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM [LINK_A].[catalog_name].[dbo].[table2] AS A
WHERE A.column1 xxxx

The same thing happens when we try to run the "SELECT" by itself.

SELECT TOP 1 @.test=A.column1
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 xxxx
ORDER BY A.column1

What is going wrong here, and how do we need to change this so that it
runs without timing out or freezing?SQL Server Questions (farkerku@.gmail.com) writes:

Quote:

Originally Posted by

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?

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks!

We've got to this point and are getting a "New transaction cannot
enlist in specified transaction coordinator" error.

Erland Sommarskog wrote:

Quote:

Originally Posted by

SQL Server Questions (farkerku@.gmail.com) writes:

Quote:

Originally Posted by

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?
>

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx