ODBC Data Source to a linked SQL Server

I'd like to set up an ODBC Data Source to a table in a linked SQL
Server, via
my local SQL Server.
When I use the ODBC Data Sources program to set up the ODBC DSN, it
only
shows databases in the local SQL Server, not in the linked one. If I
try typing it in: [NAMEOFLINKEDSERVER].databasename - it tells me that
it is an invalid table.
How can I create an ODBC Data Source which uses a database in the
linked server as its default database?
I've got a similar question. In my case I linked an Oracle database to my
SQL Server 2005 Standard database. I also want to see the tables in the
linked server. I hope someone can answer this for both of us...
Randall Arnold
<listrecv@.gmail.com> wrote in message
news:1137940173.907969.215550@.o13g2000cwo.googlegr oups.com...
> I'd like to set up an ODBC Data Source to a table in a linked SQL
> Server, via
> my local SQL Server.
> When I use the ODBC Data Sources program to set up the ODBC DSN, it
> only
> shows databases in the local SQL Server, not in the linked one. If I
> try typing it in: [NAMEOFLINKEDSERVER].databasename - it tells me that
> it is an invalid table.
> How can I create an ODBC Data Source which uses a database in the
> linked server as its default database?
>

ODBC Data Source to a linked SQL Server

I'd like to set up an ODBC Data Source to a table in a linked SQL
Server, via
my local SQL Server.
When I use the ODBC Data Sources program to set up the ODBC DSN, it
only
shows databases in the local SQL Server, not in the linked one. If I
try typing it in: [NAMEOFLINKEDSERVER].databasename - it tells me that
it is an invalid table.
How can I create an ODBC Data Source which uses a database in the
linked server as its default database?I've got a similar question. In my case I linked an Oracle database to my
SQL Server 2005 Standard database. I also want to see the tables in the
linked server. I hope someone can answer this for both of us...
Randall Arnold
<listrecv@.gmail.com> wrote in message
news:1137940173.907969.215550@.o13g2000cwo.googlegroups.com...
> I'd like to set up an ODBC Data Source to a table in a linked SQL
> Server, via
> my local SQL Server.
> When I use the ODBC Data Sources program to set up the ODBC DSN, it
> only
> shows databases in the local SQL Server, not in the linked one. If I
> try typing it in: [NAMEOFLINKEDSERVER].databasename - it tells me that
> it is an invalid table.
> How can I create an ODBC Data Source which uses a database in the
> linked server as its default database?
>

ODBC Connection to a linked database

I'd like to set up an ODBC DSN to a table in a linked SQL Server, via
my local SQL Server.

I'm having a few problems:
1. When I use Enterprise Manager to link the remote SQL Server, it
doesn't allow me to select the database in the remote server. It only
shows one database.

2. When I use the Data Sources program to set up the ODBC DSN, it only
shows databases in the local SQL Server, not in the linked one. If I
try typing it in: [NAMEOFLINKEDSERVER].databasename - it tells me that
it is an invalid table.Thats quite normal, but you are able to use the four part notation,
which will switch to another database if specified.

E.g. ODBC Connection points automatically to Northwind ( as the default
database of the user) you can use

SELECT <columnlist> FROM Linkdservername.Databasename.Objectname

which switches to the other database to select.

HTH, Jens Suessmeyer.|||Jens,

Thank you.

I'd rather not need to change the table names in the MDB. They just
use the default database of the ODBC Data Source. How can I set the
ODBC default database to be a database in a linked server?|||if you are using a DSN, just go in the ODBC Administrator (of Windows)
and change the database to the ones needed.

HTH, jens Suessmeyer.|||Jens,

Thanks - but the ODBC Adminsitrator only shows *local* databases, not
databases on the linked server. If I try to just type it in, it tells
me that it's not a valid db, and refuses to let me do so!!!|||How did you register the linked server ? Is it a DSN bind linked server
or was it specified within a conneciton string ? ODBC Administrator
doesnt show only the local databases, it shows the servers/databases
that were specified within the DSN. Look at the server ODBC Admin to
see if connection to your linked server is specified (some people look
on their local DSN rather than looking on the remote DSNs onthe SQL
Server which uses the linked server)

HTH, Jens Suessmeyer.

ODBC connection

I am trying to connect to SQL Server using a 3rd party report generating software (dbextra). Everything is located on my local box. I am trying to establish connection through ODBC. Is this installed during setup or is this a function I have to manually set up and configure?

ODBC connections are defined in the "Data Sources (ODBC)" option in the system's "Administrative Tools". You have to set these up manually. Once done, you point the software to the "name" of the ODBC data source you've created.

-Ryan / Kardax

|||

You can connect using ODBC right out of the box through two different paths:

MDAC is already on your Windows computers, you write a normal ODBC connection string for SQL. This typcially uses TCP/IP, so you will need to enable the TCP/IP protocol in SQL Server (it's off by default) using SQL Configuration Manager.

ODBC

Hey

I have developed a asp.net 2 system on my local machine using a local sql2000 server.
Now i'm trying to deploy it to a windows 2003 server, using a sql2000 server on another windows 2003 server. I thought the only thing i had to do was to create a ODBC connection to the sql server, and change the connectionstring. But no :)

What kind of changes do i need to do. Do i need to change the dataprovider.

I get this error:

An error has occurred while establishing a connection to the server. When connecting to sql Server 2005, this failure may be caused by the fact that under the default settings sql Server does not allow remote connections. (Provider: Named Pipes Provider, error: 40 - Could not open a connection to sql server)

But i don't use a sql 2005 server.

Pleace help :)

When I got this error the reason was SQL Server service in SQL Server 2000 was off but I had SQL Server 2005 installed also so check to see if your SQL Server service is on. Hope this helps.|||

I can connect to the database through enterprise mananger, and if i create an ODBC connection. So i'm quite sure the service is running.

But i'm not sure how to connect to a shared sql server on another server. Do i need to use ODBC? Do i need to change some providers? How would the connectionstring look like.

Thanks

|||

No you don't need ODBC to connect to SQL Server because the ODBC standard is very old. Try the link below for connection strings. Hope this helps.

http://www.connectionstrings.com

|||

That's what i thougt to. But i can only get my application to work when using my local sql server. If i try to use a sql server on another server i get this error.

But is this correct:

Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"

Where Aron1 is servername(not sql server name)??
Pubs is the database.

Is this enough information?

Thanks for your help.

|||

Aron1 is the SQL Server instance name and all the information you need is in that site but if you need to know more about connection and SQL Server ANSI SQL permissions try the link below for a Microsoft provided tutorial. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/paght000010.asp

|||

An error has occurred while establishing a connection to the server. When connecting to sql Server 2005, this failure may be caused by the fact that under the default settings sql Server does not allow remote connections. (Provider: Named Pipes Provider, error: 40 - Could not open a connection to sql server)

Change to the TCP/IP Provider and see if your problem goes away.

|||

Found my problem. A firewall on the server was blocking this kind of traffic.

obsolete subscriptions still exist

Hi everyone,
I disabled replication on my local sql server. I would assume publication and subscription folder will be empty after I refresh Enterprise Manager. however that's not the case.
Publication folder is empty after I disabled replication, but subscription folder is not empty, what is going on?
I kept refresh subscription folder under 'replication', I can't get rid of those subscriptions? how can I get rid of them?
thanks
Kevin
Please try running sp_removedbreplication in the relevant databases.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)