Posted by SQL Server Questions on 01/07/07 05:53

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?

[Back to original message]