Remote Servers/databases and distributed queries

Last Post 11 Feb 2005 08:54 AM by Janetb99. 7 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Janetb99
New Member
New Member

--
10 Feb 2005 06:59 AM
I have a remote server that I'm able to successfully register on a local sql 2000 server through EntMgr, and make successful selects on both remote and local server databases via odbc.

Here's the question - Can I have a local server with some database/tables, a remote server with some database2/tables, and make a single select statement using table relationships from each? Without having to use DTS to make imports/exports into the local server's database?

It seems that the connection object is specific to a server, but is there a way to handle the reference to the remote server's database through a database in the local server so that the connection object thinks everything's local and can remain one-database-server-specific?

Sorry if I've communicated this poorly. I'd appreciate any responses.
Janetb99
New Member
New Member

--
10 Feb 2005 08:27 AM
Guru,
I don't think I get it, I think, I think.

I currently have a registered, linked server to remote database A in my local server through Enterprise Mgr. and write queries through Query Analyzer and odbc sql executes against the remote server database A. I also have a local database B. What I'm asking is that if there is a way to do something like the following, or similar?:

select a.firstname, b.address from dbname.dbo.databaseA a left outer join dbname.dbo.databaseB b on a.ssn=b.ssn

How would it work if the authentication for the two different registered databases isn't the same?

jb
Janetb99
New Member
New Member

--
10 Feb 2005 10:04 AM
Eureka! Now I think I understand your concept. Thanks, bol here I come.... (I did read that but actually associated linked with registered.)
Janetb99
New Member
New Member

--
11 Feb 2005 04:47 AM
Okee, dokee. I have established the remote server in client utility. I can register the remote server in EM, look at tables. I can run select statements against the remote server in QA when I log in and specify that server. I can reference an odbc data connection with web pages.

Next, I created a linked server under security, using sql (since it's an sql 2000 db, mine is an sql2000 db) with the same name used above in the client utility and the em. I created a local login with the same name and password as the login used successfully above (Paragraph 1). On the security tab, I've used sa as the local login and entered the remote user and password, using the default 'be made without using a security context'. When I try to view the tables (which I can do through registered) or run
through QA SELECT * FROM OPENQUERY(CASCE, 'SELECT person_pk, lastname FROM dbName.dbo.tblName (NOLOCK)'),

I get: sql error 17: sql server does not exist or access denied

If I edit security, wipe out the top settings and specify the security context below using the login information, same thing. Tried messing around with different security contexts, no help or ole db error.

Any help? In addition, any tutorials out there a little more user friendly than the bol? I think I want to use the linked server option and stored procs.

jb



Janetb99
New Member
New Member

--
11 Feb 2005 08:38 AM
Did you logon local server with sa? (yes in both EM and QA)

Does remote id used have proper permission on target table? (Yes? - as demonstrated by being able to successfully select via QA when connecting directly to server in QA or through odbc vs. linked server.)
Janetb99
New Member
New Member

--
11 Feb 2005 08:54 AM
sp3
Janetb99
New Member
New Member

--
11 Feb 2005 09:19 AM
guru,
done - was first thing I did
Janetb99
New Member
New Member

--
11 Feb 2005 10:10 AM
Guru,
Don't ask me what changed, but the baby's purring now.

You've been so gracious without payment, can I impose once more? Since I only have select permissions on remote server tables and views and cannot created/execute stored procs on remote server, do you know of the pros and cons of using local stored procs to take advantage of joins, parameters, etc.

Again, thanks for hanging in there....

jb



Acceptable Use Policy
---