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?
Hi
Try running SQL Server Profiler to see what is going on when you run it from
QA and by using DTS? Do you have trigger/s defined on the table?
How big is your load? Does the database set up iwth FULL recovery mode?
"SQL Server Questions" <farkerku@.gmail.com> wrote in message
news:1168149322.774824.251680@.51g2000cwl.googlegro ups.com...
> 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?
>