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

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

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

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.googlegroups.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?
>sql

LINK REMOTE SQL SERVER 2000 Database

I have a local mdb file and remote sql server 2000 database..
i know only my remote table user name and password...
my mdb file and sql database are same...
I tried to link my remote database
I click link table and odbc from my mdb...
i choose machine data source and new ...
i choose sql server and i write my server ip and click next...
i write my database username and password but it failss.
how can i link my remotet sql server 2000 database? what is my fault...
?Hi
This sounds like you are using access? In which case you may have a better
response in an access newsgroup.
If you are using an ODBC data source then you may need to set up a system
DSN in the ODBC configuration applet found in the Administrators menu.
If this is a remote database that you are connecting to, then you will need
to make sure that any firewalls/routers are not blocking access to this
server.
Posting the full error number and message will help.
John
"Savas Ates" <savas@.indexinteractive.com> wrote in message
news:%23UZZ1ehMFHA.4028@.tk2msftngp13.phx.gbl...
>I have a local mdb file and remote sql server 2000 database..
> i know only my remote table user name and password...
> my mdb file and sql database are same...
> I tried to link my remote database
> I click link table and odbc from my mdb...
> i choose machine data source and new ...
> i choose sql server and i write my server ip and click next...
> i write my database username and password but it failss.
> how can i link my remotet sql server 2000 database? what is my fault...
> ?
>

LINK REMOTE SQL SERVER 2000 Database

I have a local mdb file and remote sql server 2000 database..
i know only my remote table user name and password...
my mdb file and sql database are same...
I tried to link my remote database
I click link table and odbc from my mdb...
i choose machine data source and new ...
i choose sql server and i write my server ip and click next...
i write my database username and password but it failss.
how can i link my remotet sql server 2000 database? what is my fault...
?You may need to setup a dsn on your own computer that points to the SQL
Server database. Be aware that if you are not the Administrator for this
database, you may not have the permissions needed to link a table from this
SQL Server to your mdb database. This is for good reason as this could give
you privileges that you are not authorized to have. I would contact the SQL
Server database administrator.
"Savas Ates" <savas@.indexinteractive.com> wrote in message
news:%23Pm15dhMFHA.3988@.tk2msftngp13.phx.gbl...
>I have a local mdb file and remote sql server 2000 database..
> i know only my remote table user name and password...
> my mdb file and sql database are same...
> I tried to link my remote database
> I click link table and odbc from my mdb...
> i choose machine data source and new ...
> i choose sql server and i write my server ip and click next...
> i write my database username and password but it failss.
> how can i link my remotet sql server 2000 database? what is my fault...
> ?
>
>
>sql

LINK REMOTE SQL SERVER 2000 Database

I have a local mdb file and remote sql server 2000 database..
i know only my remote table user name and password...
my mdb file and sql database are same...
I tried to link my remote database
I click link table and odbc from my mdb...
i choose machine data source and new ...
i choose sql server and i write my server ip and click next...
i write my database username and password but it failss.
how can i link my remotet sql server 2000 database? what is my fault...
?You may need to setup a dsn on your own computer that points to the SQL
Server database. Be aware that if you are not the Administrator for this
database, you may not have the permissions needed to link a table from this
SQL Server to your mdb database. This is for good reason as this could give
you privileges that you are not authorized to have. I would contact the SQL
Server database administrator.
"Savas Ates" <savas@.indexinteractive.com> wrote in message
news:%23Pm15dhMFHA.3988@.tk2msftngp13.phx.gbl...
>I have a local mdb file and remote sql server 2000 database..
> i know only my remote table user name and password...
> my mdb file and sql database are same...
> I tried to link my remote database
> I click link table and odbc from my mdb...
> i choose machine data source and new ...
> i choose sql server and i write my server ip and click next...
> i write my database username and password but it failss.
> how can i link my remotet sql server 2000 database? what is my fault...
> ?
>
>
>

LINK REMOTE SQL SERVER 2000 Database

I have a local mdb file and remote sql server 2000 database..
i know only my remote table user name and password...
my mdb file and sql database are same...
I tried to link my remote database
I click link table and odbc from my mdb...
i choose machine data source and new ...
i choose sql server and i write my server ip and click next...
i write my database username and password but it failss.
how can i link my remotet sql server 2000 database? what is my fault...
?
Hi
This sounds like you are using access? In which case you may have a better
response in an access newsgroup.
If you are using an ODBC data source then you may need to set up a system
DSN in the ODBC configuration applet found in the Administrators menu.
If this is a remote database that you are connecting to, then you will need
to make sure that any firewalls/routers are not blocking access to this
server.
Posting the full error number and message will help.
John
"Savas Ates" <savas@.indexinteractive.com> wrote in message
news:%23UZZ1ehMFHA.4028@.tk2msftngp13.phx.gbl...
>I have a local mdb file and remote sql server 2000 database..
> i know only my remote table user name and password...
> my mdb file and sql database are same...
> I tried to link my remote database
> I click link table and odbc from my mdb...
> i choose machine data source and new ...
> i choose sql server and i write my server ip and click next...
> i write my database username and password but it failss.
> how can i link my remotet sql server 2000 database? what is my fault...
> ?
>

LINK REMOTE SQL SERVER 2000 Database

I have a local mdb file and remote sql server 2000 database..
i know only my remote table user name and password...
my mdb file and sql database are same...
I tried to link my remote database
I click link table and odbc from my mdb...
i choose machine data source and new ...
i choose sql server and i write my server ip and click next...
i write my database username and password but it failss.
how can i link my remotet sql server 2000 database? what is my fault...
?
You may need to setup a dsn on your own computer that points to the SQL
Server database. Be aware that if you are not the Administrator for this
database, you may not have the permissions needed to link a table from this
SQL Server to your mdb database. This is for good reason as this could give
you privileges that you are not authorized to have. I would contact the SQL
Server database administrator.
"Savas Ates" <savas@.indexinteractive.com> wrote in message
news:%23Pm15dhMFHA.3988@.tk2msftngp13.phx.gbl...
>I have a local mdb file and remote sql server 2000 database..
> i know only my remote table user name and password...
> my mdb file and sql database are same...
> I tried to link my remote database
> I click link table and odbc from my mdb...
> i choose machine data source and new ...
> i choose sql server and i write my server ip and click next...
> i write my database username and password but it failss.
> how can i link my remotet sql server 2000 database? what is my fault...
> ?
>
>
>

LINK REMOTE SQL SERVER 2000 Database

I have a local mdb file and remote sql server 2000 database..
i know only my remote table user name and password...
my mdb file and sql database are same...
I tried to link my remote database
I click link table and odbc from my mdb...
i choose machine data source and new ...
i choose sql server and i write my server ip and click next...
i write my database username and password but it failss.
how can i link my remotet sql server 2000 database? what is my fault...
?
You may need to setup a dsn on your own computer that points to the SQL
Server database. Be aware that if you are not the Administrator for this
database, you may not have the permissions needed to link a table from this
SQL Server to your mdb database. This is for good reason as this could give
you privileges that you are not authorized to have. I would contact the SQL
Server database administrator.
"Savas Ates" <savas@.indexinteractive.com> wrote in message
news:%23Pm15dhMFHA.3988@.tk2msftngp13.phx.gbl...
>I have a local mdb file and remote sql server 2000 database..
> i know only my remote table user name and password...
> my mdb file and sql database are same...
> I tried to link my remote database
> I click link table and odbc from my mdb...
> i choose machine data source and new ...
> i choose sql server and i write my server ip and click next...
> i write my database username and password but it failss.
> how can i link my remotet sql server 2000 database? what is my fault...
> ?
>
>
>

LINK REMOTE SQL SERVER 2000 Database

I have a local mdb file and remote sql server 2000 database..
i know only my remote table user name and password...
my mdb file and sql database are same...
I tried to link my remote database
I click link table and odbc from my mdb...
i choose machine data source and new ...
i choose sql server and i write my server ip and click next...
i write my database username and password but it failss.
how can i link my remotet sql server 2000 database? what is my fault...
?
You may need to setup a dsn on your own computer that points to the SQL
Server database. Be aware that if you are not the Administrator for this
database, you may not have the permissions needed to link a table from this
SQL Server to your mdb database. This is for good reason as this could give
you privileges that you are not authorized to have. I would contact the SQL
Server database administrator.
"Savas Ates" <savas@.indexinteractive.com> wrote in message
news:%23Pm15dhMFHA.3988@.tk2msftngp13.phx.gbl...
>I have a local mdb file and remote sql server 2000 database..
> i know only my remote table user name and password...
> my mdb file and sql database are same...
> I tried to link my remote database
> I click link table and odbc from my mdb...
> i choose machine data source and new ...
> i choose sql server and i write my server ip and click next...
> i write my database username and password but it failss.
> how can i link my remotet sql server 2000 database? what is my fault...
> ?
>
>
>
sql

Linekd Server and DateTime Problem

,
I runs this querys from QA in one session to my local
server
// tb1 in local server
Select * from TB1
where cdatetime <= '30/01/2003'
runs ok
// tb1 in linked server
Select * from LinkedServer.DB.dbo.TB1
where cdatetime <= '30/01/2003'
fails with
conversion from data char to l DateTime out of Range
// tb1 in linked server
Select * from LinkedServer.DB.dbo.TB1
where cdatetime <= '01/30/2003'
fails with
conversion from data char to l DateTime out of Range
Two servers have the same language and dateformat dmy
If I open one connection in QA to the linked server, the
query
Select * from LinkedServer.DB.dbo.TB1
where cdatetime <= '30/01/2003'
runs ok.
I've tested with many diferents formats mmddyyyy,
yyyymmdd, and so on.
Any Ideas ?amosquera
Try use 'yyyymmdd' Format
"amosquera" <alvarosusana@.vodafone.es> wrote in message
news:078501c36b9d$ddef4970$a501280a@.phx.gbl...
> ,
> I runs this querys from QA in one session to my local
> server
> // tb1 in local server
> Select * from TB1
> where cdatetime <= '30/01/2003'
> runs ok
> // tb1 in linked server
> Select * from LinkedServer.DB.dbo.TB1
> where cdatetime <= '30/01/2003'
> fails with
> conversion from data char to l DateTime out of Range
> // tb1 in linked server
> Select * from LinkedServer.DB.dbo.TB1
> where cdatetime <= '01/30/2003'
> fails with
> conversion from data char to l DateTime out of Range
>
> Two servers have the same language and dateformat dmy
> If I open one connection in QA to the linked server, the
> query
> Select * from LinkedServer.DB.dbo.TB1
> where cdatetime <= '30/01/2003'
> runs ok.
> I've tested with many diferents formats mmddyyyy,
> yyyymmdd, and so on.
> Any Ideas ?
>|||In the original message I've said
I've tested with many diferents formats mmddyyyy,yyyymmdd,
and so on.
and the query fails.
>--Original Message--
>amosquera
>Try use 'yyyymmdd' Format
>
>"amosquera" <alvarosusana@.vodafone.es> wrote in message
>news:078501c36b9d$ddef4970$a501280a@.phx.gbl...
>> ,
>> I runs this querys from QA in one session to my local
>> server
>> // tb1 in local server
>> Select * from TB1
>> where cdatetime <= '30/01/2003'
>> runs ok
>> // tb1 in linked server
>> Select * from LinkedServer.DB.dbo.TB1
>> where cdatetime <= '30/01/2003'
>> fails with
>> conversion from data char to l DateTime out of Range
>> // tb1 in linked server
>> Select * from LinkedServer.DB.dbo.TB1
>> where cdatetime <= '01/30/2003'
>> fails with
>> conversion from data char to l DateTime out of Range
>>
>> Two servers have the same language and dateformat dmy
>> If I open one connection in QA to the linked server, the
>> query
>> Select * from LinkedServer.DB.dbo.TB1
>> where cdatetime <= '30/01/2003'
>> runs ok.
>> I've tested with many diferents formats mmddyyyy,
>> yyyymmdd, and so on.
>> Any Ideas ?
>
>.
>|||Select * from LinkedServer.DB.dbo.TB1
> where cdatetime <= '01/30/2003'
> fails with
> conversion from data char to l DateTime out of Range
>
This one fails because SQL Server cannot recognize 30 as a month .
Have you changed it to '30/01/2003'?
One more thought: Have you checked regimal settings on the linked server?
I mean an year 2003 or 03 .
"amosquera" <alvarosusana@.vodafone.es> wrote in message
news:07dc01c36ba4$e0e6b350$a501280a@.phx.gbl...
> In the original message I've said
> I've tested with many diferents formats mmddyyyy,yyyymmdd,
> and so on.
> and the query fails.
> >--Original Message--
> >amosquera
> >Try use 'yyyymmdd' Format
> >
> >
> >
> >"amosquera" <alvarosusana@.vodafone.es> wrote in message
> >news:078501c36b9d$ddef4970$a501280a@.phx.gbl...
> >> ,
> >>
> >> I runs this querys from QA in one session to my local
> >> server
> >>
> >> // tb1 in local server
> >> Select * from TB1
> >> where cdatetime <= '30/01/2003'
> >>
> >> runs ok
> >>
> >> // tb1 in linked server
> >>
> >> Select * from LinkedServer.DB.dbo.TB1
> >> where cdatetime <= '30/01/2003'
> >>
> >> fails with
> >> conversion from data char to l DateTime out of Range
> >>
> >> // tb1 in linked server
> >>
> >> Select * from LinkedServer.DB.dbo.TB1
> >> where cdatetime <= '01/30/2003'
> >>
> >> fails with
> >> conversion from data char to l DateTime out of Range
> >>
> >>
> >> Two servers have the same language and dateformat dmy
> >>
> >> If I open one connection in QA to the linked server, the
> >> query
> >>
> >> Select * from LinkedServer.DB.dbo.TB1
> >> where cdatetime <= '30/01/2003'
> >>
> >> runs ok.
> >>
> >> I've tested with many diferents formats mmddyyyy,
> >> yyyymmdd, and so on.
> >>
> >> Any Ideas ?
> >>
> >
> >
> >.
> >|||Hi,
Well I think that I've tested all possible formats of
datetime and the query fails.
>--Original Message--
> Select * from LinkedServer.DB.dbo.TB1
>> where cdatetime <= '01/30/2003'
>> fails with
>> conversion from data char to l DateTime out of Range
>This one fails because SQL Server cannot recognize 30 as
a month .
>Have you changed it to '30/01/2003'?
>One more thought: Have you checked regimal settings on
the linked server?
>I mean an year 2003 or 03 .
>
>
>"amosquera" <alvarosusana@.vodafone.es> wrote in message
>news:07dc01c36ba4$e0e6b350$a501280a@.phx.gbl...
>> In the original message I've said
>> I've tested with many diferents formats
mmddyyyy,yyyymmdd,
>> and so on.
>> and the query fails.
>> >--Original Message--
>> >amosquera
>> >Try use 'yyyymmdd' Format
>> >
>> >
>> >
>> >"amosquera" <alvarosusana@.vodafone.es> wrote in message
>> >news:078501c36b9d$ddef4970$a501280a@.phx.gbl...
>> >> ,
>> >>
>> >> I runs this querys from QA in one session to my local
>> >> server
>> >>
>> >> // tb1 in local server
>> >> Select * from TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> runs ok
>> >>
>> >> // tb1 in linked server
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> fails with
>> >> conversion from data char to l DateTime out of Range
>> >>
>> >> // tb1 in linked server
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '01/30/2003'
>> >>
>> >> fails with
>> >> conversion from data char to l DateTime out of Range
>> >>
>> >>
>> >> Two servers have the same language and dateformat dmy
>> >>
>> >> If I open one connection in QA to the linked server,
the
>> >> query
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> runs ok.
>> >>
>> >> I've tested with many diferents formats mmddyyyy,
>> >> yyyymmdd, and so on.
>> >>
>> >> Any Ideas ?
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||The linked server has the same settings options that the
main server.
dd/mm/yyyy
>--Original Message--
>Hi,
>Well I think that I've tested all possible formats of
>datetime and the query fails.
>>--Original Message--
>> Select * from LinkedServer.DB.dbo.TB1
>> where cdatetime <= '01/30/2003'
>> fails with
>> conversion from data char to l DateTime out of Range
>>This one fails because SQL Server cannot recognize 30 as
>a month .
>>Have you changed it to '30/01/2003'?
>>One more thought: Have you checked regimal settings on
>the linked server?
>>I mean an year 2003 or 03 .
>>
>>
>>"amosquera" <alvarosusana@.vodafone.es> wrote in message
>>news:07dc01c36ba4$e0e6b350$a501280a@.phx.gbl...
>> In the original message I've said
>> I've tested with many diferents formats
>mmddyyyy,yyyymmdd,
>> and so on.
>> and the query fails.
>> >--Original Message--
>> >amosquera
>> >Try use 'yyyymmdd' Format
>> >
>> >
>> >
>> >"amosquera" <alvarosusana@.vodafone.es> wrote in
message
>> >news:078501c36b9d$ddef4970$a501280a@.phx.gbl...
>> >> ,
>> >>
>> >> I runs this querys from QA in one session to my
local
>> >> server
>> >>
>> >> // tb1 in local server
>> >> Select * from TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> runs ok
>> >>
>> >> // tb1 in linked server
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> fails with
>> >> conversion from data char to l DateTime out of Range
>> >>
>> >> // tb1 in linked server
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '01/30/2003'
>> >>
>> >> fails with
>> >> conversion from data char to l DateTime out of Range
>> >>
>> >>
>> >> Two servers have the same language and dateformat
dmy
>> >>
>> >> If I open one connection in QA to the linked
server,
>the
>> >> query
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> runs ok.
>> >>
>> >> I've tested with many diferents formats mmddyyyy,
>> >> yyyymmdd, and so on.
>> >>
>> >> Any Ideas ?
>> >>
>> >
>> >
>> >.
>> >
>>
>>.
>.
>|||I've had a new test:
Select * from LinkedServer.DB.dbo.TB1
where convert(char(10),cdatetime,103) <= '01/30/2003'
and it runs ok but the execution plan show that the query
uses a table scan rather than a index seek on cdatetime
column.
Thanks.
>--Original Message--
>The linked server has the same settings options that the
>main server.
>dd/mm/yyyy
>>--Original Message--
>>Hi,
>>Well I think that I've tested all possible formats of
>>datetime and the query fails.
>>--Original Message--
>> Select * from LinkedServer.DB.dbo.TB1
>> where cdatetime <= '01/30/2003'
>> fails with
>> conversion from data char to l DateTime out of Range
>>This one fails because SQL Server cannot recognize 30
as
>>a month .
>>Have you changed it to '30/01/2003'?
>>One more thought: Have you checked regimal settings on
>>the linked server?
>>I mean an year 2003 or 03 .
>>
>>
>>"amosquera" <alvarosusana@.vodafone.es> wrote in message
>>news:07dc01c36ba4$e0e6b350$a501280a@.phx.gbl...
>> In the original message I've said
>> I've tested with many diferents formats
>>mmddyyyy,yyyymmdd,
>> and so on.
>> and the query fails.
>> >--Original Message--
>> >amosquera
>> >Try use 'yyyymmdd' Format
>> >
>> >
>> >
>> >"amosquera" <alvarosusana@.vodafone.es> wrote in
>message
>> >news:078501c36b9d$ddef4970$a501280a@.phx.gbl...
>> >> ,
>> >>
>> >> I runs this querys from QA in one session to my
>local
>> >> server
>> >>
>> >> // tb1 in local server
>> >> Select * from TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> runs ok
>> >>
>> >> // tb1 in linked server
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> fails with
>> >> conversion from data char to l DateTime out of
Range
>> >>
>> >> // tb1 in linked server
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '01/30/2003'
>> >>
>> >> fails with
>> >> conversion from data char to l DateTime out of
Range
>> >>
>> >>
>> >> Two servers have the same language and dateformat
>dmy
>> >>
>> >> If I open one connection in QA to the linked
>server,
>>the
>> >> query
>> >>
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '30/01/2003'
>> >>
>> >> runs ok.
>> >>
>> >> I've tested with many diferents formats mmddyyyy,
>> >> yyyymmdd, and so on.
>> >>
>> >> Any Ideas ?
>> >>
>> >
>> >
>> >.
>> >
>>
>>.
>>.
>.
>|||Hi Uri,
Thanks in advantaje
I know this, only to remark that although the query runs,
the execution plan is not valid for me.
Any more ideas about the original question ?
>--Original Message--
>The CONVERT dictate to optimyzer use a INDEX SCAN
insetad of INDEX SEEK
>
>
>"amosquera" <alvarosusana@.vodafone.es> wrote in message
>news:0a4d01c36bb3$36968ce0$a401280a@.phx.gbl...
>> I've had a new test:
>> Select * from LinkedServer.DB.dbo.TB1
>> where convert(char(10),cdatetime,103) <= '01/30/2003'
>> and it runs ok but the execution plan show that the
query
>> uses a table scan rather than a index seek on cdatetime
>> column.
>> Thanks.
>>
>> >--Original Message--
>> >The linked server has the same settings options that
the
>> >main server.
>> >dd/mm/yyyy
>> >>--Original Message--
>> >>Hi,
>> >>
>> >>Well I think that I've tested all possible formats of
>> >>datetime and the query fails.
>> >>
>> >>--Original Message--
>> >> Select * from LinkedServer.DB.dbo.TB1
>> >> where cdatetime <= '01/30/2003'
>> >>
>> >> fails with
>> >> conversion from data char to l DateTime out of
Range
>> >>
>> >>This one fails because SQL Server cannot recognize 30
>> as
>> >>a month .
>> >>Have you changed it to '30/01/2003'?
>> >>One more thought: Have you checked regimal settings
on
>> >>the linked server?
>> >>I mean an year 2003 or 03 .
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>"amosquera" <alvarosusana@.vodafone.es> wrote in
message
>> >>news:07dc01c36ba4$e0e6b350$a501280a@.phx.gbl...
>> >> In the original message I've said
>> >>
>> >> I've tested with many diferents formats
>> >>mmddyyyy,yyyymmdd,
>> >> and so on.
>> >>
>> >> and the query fails.
>> >>
>> >> >--Original Message--
>> >> >amosquera
>> >> >Try use 'yyyymmdd' Format
>> >> >
>> >> >
>> >> >
>> >> >"amosquera" <alvarosusana@.vodafone.es> wrote in
>> >message
>> >> >news:078501c36b9d$ddef4970$a501280a@.phx.gbl...
>> >> >> ,
>> >> >>
>> >> >> I runs this querys from QA in one session to my
>> >local
>> >> >> server
>> >> >>
>> >> >> // tb1 in local server
>> >> >> Select * from TB1
>> >> >> where cdatetime <= '30/01/2003'
>> >> >>
>> >> >> runs ok
>> >> >>
>> >> >> // tb1 in linked server
>> >> >>
>> >> >> Select * from LinkedServer.DB.dbo.TB1
>> >> >> where cdatetime <= '30/01/2003'
>> >> >>
>> >> >> fails with
>> >> >> conversion from data char to l DateTime out of
>> Range
>> >> >>
>> >> >> // tb1 in linked server
>> >> >>
>> >> >> Select * from LinkedServer.DB.dbo.TB1
>> >> >> where cdatetime <= '01/30/2003'
>> >> >>
>> >> >> fails with
>> >> >> conversion from data char to l DateTime out of
>> Range
>> >> >>
>> >> >>
>> >> >> Two servers have the same language and
dateformat
>> >dmy
>> >> >>
>> >> >> If I open one connection in QA to the linked
>> >server,
>> >>the
>> >> >> query
>> >> >>
>> >> >> Select * from LinkedServer.DB.dbo.TB1
>> >> >> where cdatetime <= '30/01/2003'
>> >> >>
>> >> >> runs ok.
>> >> >>
>> >> >> I've tested with many diferents formats
mmddyyyy,
>> >> >> yyyymmdd, and so on.
>> >> >>
>> >> >> Any Ideas ?
>> >> >>
>> >> >
>> >> >
>> >> >.
>> >> >
>> >>
>> >>
>> >>.
>> >>
>> >>.
>> >>
>> >.
>> >
>
>.
>