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?
