Syntax to read/pull data in one server from another server?

16 Feb 2006 7:58 PM

Rich

Hello,

I have a production server and a development server (which I just installed
last night), called serv1 and serv1\dev, respectively. I am trying to read
data from a table in a DB on serv1 but from serv1\dev. I have linked serv1
to serv1\dev from Enterprise Manager and am impersonating my login to serv1
(administrator). Here is what I am trying and the error message that follows:

select fld1 from serv1.DB1_test..tbl1

Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'serv1' does not contain table '"DB1_test"."tbl1"'. The
table either does not exist or the current user does not have permissions on
that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain
the table: ProviderName='serv1', TableName='"DB1_test"."tbl1"'].

So, am I using the correct syntax for reading data from one server to
another? Do I need to do anything with my security context?

Thanks,
Rich

16 Feb 2006 8:52 PM

Rich

Apparently, I could not use my login from the dropdown list in the security
tab of the linked server properties menu. So I the security context to

For a login not defined in the list above, connections will:

* Be made using this security context:
Remot login: sa
With password: ******

Now I can see all the tables in the linked server.

Show quote

"Rich" wrote:

> Hello,
>
> I have a production server and a development server (which I just installed
> last night), called serv1 and serv1\dev, respectively. I am trying to read
> data from a table in a DB on serv1 but from serv1\dev. I have linked serv1
> to serv1\dev from Enterprise Manager and am impersonating my login to serv1
> (administrator). Here is what I am trying and the error message that follows:
>
> select fld1 from serv1.DB1_test..tbl1
>
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'serv1' does not contain table '"DB1_test"."tbl1"'. The
> table either does not exist or the current user does not have permissions on
> that table.
> OLE DB error trace [Non-interface error: OLE DB provider does not contain
> the table: ProviderName='serv1', TableName='"DB1_test"."tbl1"'].
>
> So, am I using the correct syntax for reading data from one server to
> another? Do I need to do anything with my security context?
>
> Thanks,
> Rich
>