simple linked server plumbing

Question

simple linked server plumbing

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

stan SSC Eights! Points: 969 More actions · Answer 1

not sure what you are asking Jeff. I'm going on the word of someone here who said he tried it and got an error that specifically forbids syns on linked servers. I'll try it for myself , to verify what he says and post back here. There is an old saying "no good deed goes unpunished".

stan SSC Eights! Points: 969 More actions · Answer 2

i thought i responded to this already. jeff i dont get what you are saying but a peer claims a synonym for the linked server name itself is forbidden. i'll have to verify and post back here.

frederico_fonseca SSCoach Points: 16148 More actions · Answer 3

you don't create a synonym for the linked sever name - you create a synonym for a object that resides on a linked server.

this means that your code would reference a synonym on current db (for example) pointing to linkedserver.database.schema.remoteobject - and if you change any of the 4 parts of the referenced object you just need to recreate the synonym without chaning any code.

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 4

stan wrote:

i thought i responded to this already. jeff i dont get what you are saying but a peer claims a synonym for the linked server name itself is forbidden. i'll have to verify and post back here.

My apologies that I wasn't clear on this. Synonyms need to point at an object, so you need the 4 part naming for the objects that the linked server can see... just like Frederico said in the post above.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

stan SSC Eights! Points: 969 More actions · Answer 5

stan

SSC Eights!

Points: 969

September 17, 2024 at 12:30 pm

#4459068

thx frederico. we understand.

stan SSC Eights! Points: 969 More actions · Answer 6

thx jeff. i believe what we used to solve this went as follows. i was talking our dba thru this because he has permissions where we wanted it done. i believe i saw it working then when my other peer ran his query, the one that he was hoping he wouldnt need to change in his app.

we created a linked server whose name is db1.
in the "other" (not sql) properties of the linked server we entered a conn string that of course includes a server and catalog, specifically server1's name and actual catalog db1.
my other peer ran the query select * from db1.dbo.view1. which worked and is the query he doesnt want to change in his app. we arent gurus but in our minds by naming the linked server this way, "db1" represents the server1.db1 part of a query against server1.db1.dbo.view1. and its all done without any synonyms anywhere.

This reply was modified 1 month, 3 weeks ago by stan.
This reply was modified 1 month, 3 weeks ago by stan.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

stan wrote:

my other peer ran the query select * from db1.dbo.view1. which worked and is the query he doesnt want to change in his app. we arent gurus but in our minds by naming the linked server this way, "db1" represents the server1.db1 part of a query against server1.db1.dbo.view1. and its all done without any synonyms anywhere.

I seriously doubt this actually worked as you think it worked - since SQL Server has no way of 'translating' the database reference in 3-part names to the 4-part server name.

In other words - SELECT * FROM db1.dbo.View1 - cannot access anything other than a database named db1, a schema named dbo - and an object named View1. I suspect that you still have a database named db1 - and in that database there is a view with the name dbo.View1.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs