Querying a linked server

8 Jul 2005 1:18 PM

Raul

I've created a linked server to another SQL Server and can see the master
tables. I can also see the various databases using the following query:

SELECT *
FROM OPENQUERY(linked_server_name,'SELECT * FROM sysdatabases')

My question is how do I get to the data on these databases? I need to be
able to select a particular database listed in sysdatabases and get to the
tables.

Thanks in advance,
Raul

8 Jul 2005 1:24 PM

David Portas

SELECT * FROM linked_server_name.database_name.dbo.table_name

--
David Portas
SQL Server MVP
--

8 Jul 2005 2:10 PM

Raul

This did the trick.

Thanks,
Raul

Show quote

"David Portas" wrote:

> SELECT * FROM linked_server_name.database_name.dbo.table_name
>
> --
> David Portas
> SQL Server MVP
> --
>
>

8 Jul 2005 1:34 PM

JosephPruiett

You need to make sure the account you used to link the server has at least
read permissions to the databases you are wanting to connect to. If the
account does not have permission on the database you will not be able to open
it even though you can see it in sysdatabases.

Hope this helps.

Show quote

"Raul" wrote:

> I've created a linked server to another SQL Server and can see the master
> tables. I can also see the various databases using the following query:
>
> SELECT *
> FROM OPENQUERY(linked_server_name,'SELECT * FROM sysdatabases')
>
> My question is how do I get to the data on these databases? I need to be
> able to select a particular database listed in sysdatabases and get to the
> tables.
>
> Thanks in advance,
> Raul

8 Jul 2005 1:41 PM

Raul

Thanks for the heads-up. In this case my account had the permission but I'll
keep this in mind.

Thanks again,
Raul

Show quote

"JosephPruiett" wrote:

> You need to make sure the account you used to link the server has at least
> read permissions to the databases you are wanting to connect to. If the
> account does not have permission on the database you will not be able to open
> it even though you can see it in sysdatabases.
>
> Hope this helps.
>
> "Raul" wrote:
>
> > I've created a linked server to another SQL Server and can see the master
> > tables. I can also see the various databases using the following query:
> >
> > SELECT *
> > FROM OPENQUERY(linked_server_name,'SELECT * FROM sysdatabases')
> >
> > My question is how do I get to the data on these databases? I need to be
> > able to select a particular database listed in sysdatabases and get to the
> > tables.
> >
> > Thanks in advance,
> > Raul