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

Limited Remote Login db access

I have connected my desktop and laptop computer at home, on my desktop I have a series of db's under my default named instance <computername>/sqlexpress and I have created access logins using windows auth. When I connect to the desktop from the laptop using tcp:<computername>\sqlexpress\userid I can only see the system db's...I am trying to view adventure works, which I can see on the desktop fine using the same login...any ideas?

Thanks,

Rob

Do you have a domain controller and home network?

If not, then you must create use SQL logins -you cannot use Windows authentication without an Active Directory (domain controller).

|||

I don't even know what those things mean.

How would I go about doing this with SQL logins?

Rob

|||

I suggest you follow the steps under "To configure AdventureWorks to work with Web server controls" on the following page from the Books Online.

http://msdn2.microsoft.com/en-us/library/ms310325(vs.80).aspx

Hopefully this helps,

John

|||

So I read over the link provided...

I have completed the first part and added it to the server. I can access the database on my desktop machine, when I login from the laptop however (using the same windows login) I can't access to database...do I need to also add something to make it accessible remotely? I don't really understand what "configure AdventureWorks to work with Web server controls
" means..

Rob

|||

These resources might help.

Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

Configuration -Connect to SQL Express from "downlevel clients"
http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

|||

I have done this...as I stated before I can connect fine its just that I can't see anything other than system db's...I imagine I'm missing something stupid, like perhaps I didn't configure the login correctly?

Rob

|||

The login needs to be provided permission to access the databases.

Add the login to the appropriate database role.

|||

Ok first...I am running XP home on both the desktop and laptop...and the logins are windows auth.

if I want full access from the remote connection, where do I change the role of the login? is it in the login menu or the databases menu?

Rob

|||

Look back at the first response given to your problem.

Windows authentication will not work in your situation -you do not have a domain controller.

You will need to establish SQL Logins in each server, and assign permissions accordingly.

Look in Books Online about creating SQL Logins.

Limited Remote Login access

Hello all,

I'm very new to this so excuse my naiveity...I have connected my desktop and laptop computer at home, on my desktop I have a series of db's under my default named instance <computername>/sqlexpress and I have created access logins using windows auth. When I connect to the desktop from the laptop using tcp:<computername>\sqlexpress\userid I can only see the systemdb's...I am trying to view adventure works, just to see how this remote connection stuff works...any ideas?

Thanks,

Rob

See my response to your post in the 'Data Access' forum.

Limitations sql server 2000 Personal edition

SQL Server Personal edition
Enterprise Manager
on Win 2000
I have the above installed on my own computer
I have connected to a remote database using it's IP adress
I can see all the tables - eg NorthWind
With the appropriate login (Server Authentification) should I be able to
create a new database, Create new tables, see permissions and alter them ?
[I have ALSO TRIED www.aspenterprisemanager.com (we-based tool) same
situation can see but I cannaot do anything else!]
BUT have been told my login will allow this!!!!!
[I have also tried connecting through Query Analyser - no creates allowed]
Jim Bunton
Ok - sorted thsi no replies required thanks
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message
news:XK37f.173379$RW.126302@.fe2.news.blueyonder.co .uk...
> SQL Server Personal edition
> Enterprise Manager
> on Win 2000
> I have the above installed on my own computer
> I have connected to a remote database using it's IP adress
> I can see all the tables - eg NorthWind
> With the appropriate login (Server Authentification) should I be able to
> create a new database, Create new tables, see permissions and alter them
?
> [I have ALSO TRIED www.aspenterprisemanager.com (we-based tool) same
> situation can see but I cannaot do anything else!]
> BUT have been told my login will allow this!!!!!
> [I have also tried connecting through Query Analyser - no creates allowed]
> --
>
> Jim Bunton
>

Limitations sql server 2000 Personal edition

SQL Server Personal edition
Enterprise Manager
on Win 2000
I have the above installed on my own computer
I have connected to a remote database using it's IP adress
I can see all the tables - eg NorthWind
With the appropriate login (Server Authentification) should I be able to
create a new database, Create new tables, see permissions and alter them ?
[I have ALSO TRIED www.aspenterprisemanager.com (we-based tool) same
situation can see but I cannaot do anything else!]
BUT have been told my login will allow this!!!!!
[I have also tried connecting through Query Analyser - no creates allowe
d]
--
Jim BuntonOk - sorted thsi no replies required thanks
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message
news:XK37f.173379$RW.126302@.fe2.news.blueyonder.co.uk...
> SQL Server Personal edition
> Enterprise Manager
> on Win 2000
> I have the above installed on my own computer
> I have connected to a remote database using it's IP adress
> I can see all the tables - eg NorthWind
> With the appropriate login (Server Authentification) should I be able to
> create a new database, Create new tables, see permissions and alter them
?
> [I have ALSO TRIED www.aspenterprisemanager.com (we-based tool) same
> situation can see but I cannaot do anything else!]
> BUT have been told my login will allow this!!!!!
> [I have also tried connecting through Query Analyser - no creates allo
wed]
> --
>
> Jim Bunton
>

Limitations Server 2000 Personal Edition

SQL Server Personal edition
Enterprise Manager
on Win 2000
I have the above installed on my own computer
I have connected to a remote database using it's IP adress
I can see all the tables - eg NorthWind
With the appropriate login (Server Authentification) should I be able to
create a new database, Create new tables, see permissions and alter them ?
[I have ALSO TRIED www.aspenterprisemanager.com (we-based tool) same
situation can see but I cannaot do anything else!]
BUT have been told my login will allow this!!!!!
[I have also tried connecting through Query Analyser - no creates allowed]
Jim Bunton
It looks like you have read permissions only
http://sqlservercode.blogspot.com/
"Jim Bunton" wrote:

> SQL Server Personal edition
> Enterprise Manager
> on Win 2000
> I have the above installed on my own computer
> I have connected to a remote database using it's IP adress
> I can see all the tables - eg NorthWind
> With the appropriate login (Server Authentification) should I be able to
> create a new database, Create new tables, see permissions and alter them ?
> [I have ALSO TRIED www.aspenterprisemanager.com (we-based tool) same
> situation can see but I cannaot do anything else!]
> BUT have been told my login will allow this!!!!!
> [I have also tried connecting through Query Analyser - no creates allowed]
> --
>
> Jim Bunton
>
>

Limit remote connections

Hi,
We use SQL server 2000 on windows 2003. We usually use remote desktop to
connect to the SQL server. For security reasons, only 2 concurrent remote
desktop connections are allowed. I just noticed I'm able to use 'osql'
command to open as many connections to the SQL server as I want. Should I be
concerned? How other people deal with remote connections?
Thanks in advance,
Bing
Hello, Bing
In my opinion, the limit of 2 concurrent remote desktop connections on
Windows 2003 is for licensing reasons, not for security reasons.
Having an unlimited number of connections to SQL Server is no cause of
concern for security reasons, as long as each connection is properly
authentificated and comes from an authorised user and/or computer.
Razvan
|||On Jun 2, 1:33 pm, Razvan Socol <rso...@.gmail.com> wrote:
> Hello, Bing
> In my opinion, the limit of 2 concurrent remote desktop connections on
> Windows 2003 is for licensing reasons, not for security reasons.
> Having an unlimited number of connections to SQL Server is no cause of
> concern for security reasons, as long as each connection is properly
> authentificated and comes from an authorised user and/or computer.
> Razvan
However, you can limit the number of connections on an instance of SQL
Server, by using the "user connections" option. By default, SQL Server
allows a maximum of 32,767 user connections, but the actual number of
user connections allowed also depends on the limits of your
applications and hardware. Using the "user connections" option, you
can limit the number of connections to any desired number (less than
32,767), but this is done for performance reasons, not for security
reasons.
Razvan

Limit remote connections

Hi,
We use SQL server 2000 on windows 2003. We usually use remote desktop to
connect to the SQL server. For security reasons, only 2 concurrent remote
desktop connections are allowed. I just noticed I'm able to use 'osql'
command to open as many connections to the SQL server as I want. Should I b
e
concerned? How other people deal with remote connections?
Thanks in advance,
BingHello, Bing
In my opinion, the limit of 2 concurrent remote desktop connections on
Windows 2003 is for licensing reasons, not for security reasons.
Having an unlimited number of connections to SQL Server is no cause of
concern for security reasons, as long as each connection is properly
authentificated and comes from an authorised user and/or computer.
Razvan|||On Jun 2, 1:33 pm, Razvan Socol <rso...@.gmail.com> wrote:
> Hello, Bing
> In my opinion, the limit of 2 concurrent remote desktop connections on
> Windows 2003 is for licensing reasons, not for security reasons.
> Having an unlimited number of connections to SQL Server is no cause of
> concern for security reasons, as long as each connection is properly
> authentificated and comes from an authorised user and/or computer.
> Razvan
However, you can limit the number of connections on an instance of SQL
Server, by using the "user connections" option. By default, SQL Server
allows a maximum of 32,767 user connections, but the actual number of
user connections allowed also depends on the limits of your
applications and hardware. Using the "user connections" option, you
can limit the number of connections to any desired number (less than
32,767), but this is done for performance reasons, not for security
reasons.
Razvan

Limit remote connections

Hi,
We use SQL server 2000 on windows 2003. We usually use remote desktop to
connect to the SQL server. For security reasons, only 2 concurrent remote
desktop connections are allowed. I just noticed I'm able to use 'osql'
command to open as many connections to the SQL server as I want. Should I be
concerned? How other people deal with remote connections?
Thanks in advance,
BingHello, Bing
In my opinion, the limit of 2 concurrent remote desktop connections on
Windows 2003 is for licensing reasons, not for security reasons.
Having an unlimited number of connections to SQL Server is no cause of
concern for security reasons, as long as each connection is properly
authentificated and comes from an authorised user and/or computer.
Razvan|||On Jun 2, 1:33 pm, Razvan Socol <rso...@.gmail.com> wrote:
> Hello, Bing
> In my opinion, the limit of 2 concurrent remote desktop connections on
> Windows 2003 is for licensing reasons, not for security reasons.
> Having an unlimited number of connections to SQL Server is no cause of
> concern for security reasons, as long as each connection is properly
> authentificated and comes from an authorised user and/or computer.
> Razvan
However, you can limit the number of connections on an instance of SQL
Server, by using the "user connections" option. By default, SQL Server
allows a maximum of 32,767 user connections, but the actual number of
user connections allowed also depends on the limits of your
applications and hardware. Using the "user connections" option, you
can limit the number of connections to any desired number (less than
32,767), but this is done for performance reasons, not for security
reasons.
Razvan