0

I am using al lot of sql queries and tyred of typing the complete prefixes of

[LINKED_SERVER_ALIAS].[LINKED_SERVER_ON_LINKED_SERVER].[DATABASEPATH].[SCHMEMA].TABLE

No way to change the serverstructure or direct login to linked server on another linked server.

Question: Is there som transact sql command to create a global alias like create alias my_linked_connection for [LINKED_SERVER_ALIAS].[LINKED_SERVER_ON_LINKED_SERVER].[DATABASEPATH].[SCHMEMA].TABLE

that it is possible to use:

select * from my_linked_connection.TABLE

an additional problem is, that these are to many prefixes, so a normal select query is only possible by openquery or declare @cmd ... exec @cmd

Thanks

Combine a part of the prefixes inside the linked server alias sp_addlinked server.

rm8019
  • 3
  • 3
  • What about something simple as creating a view locally on your own database that will only do something like `select * from `? This might have an impact on performance, though I find it hard to believe that it will be much worse than using linked server over another linked server. – Zohar Peled Feb 14 '23 at 08:32
  • Are you sure you can use more than 3 prefixes – siggemannen Feb 14 '23 at 09:01
  • @siggemannen: No, thats the question. (only with openquery and declare cmd). Thats why i am asking for an alias to combine. Zohar Peled: A view is my last option if nothing else will work. Really very bad performance. Linked_server over linked_server -> i know, i am not the admin of these – rm8019 Feb 14 '23 at 09:03
  • You could create some `SYNONYM`s in your database for the tables in the linked server. – Thom A Feb 14 '23 at 09:03
  • Though to reference a table on a different server you would use *four part* naming; you seem to have give five part naming, which isn't "a thing" for objects. – Thom A Feb 14 '23 at 09:11
  • aha, he uses a linked server as a "jumpgate" to another linked server, that's pretty cool (in a not cool sort of way) – siggemannen Feb 14 '23 at 09:16
  • I think there's no great solution, except views, which will make you type less code if you're working with openquery stuff. Perhaps you can create a procedure that handles common logic stuff – siggemannen Feb 14 '23 at 09:54
  • @siggemannen a "jumpgate" , correct. second one is provided by ERP software guys, normally not open to their DB. I am comparing data of 3 ERP Systems and combining hours recording tables of these. --> a central DB as admin connected to 3 linkedServer, but one of them is a linked server itself. – rm8019 Feb 14 '23 at 12:52

1 Answers1

0

Synonym is what you are looking for here

CREATE SYNONYM schema.tablename for linkedservername.remotedatabasename.schema.tablename

This has the advantage (which I expect is what you are looking for) that you can move views, functions and procedures through your development environments whithout having to modify the object code; the only thing that should be different is that the target database for the synonym will be different each time.

Note that Synonym is a MSSQL feature and may not be supported by your ODBC/JDBC drivers so please test fully before deployment.

Aaron Reese
  • 544
  • 6
  • 18
  • this is exactly what i am looking for. But even here, they are to much prefixes. Is a Synonym possible, just to combine LinkedServerName and the next prefix? – rm8019 Feb 14 '23 at 09:58
  • When you reference it in your query, assuming the schema for the synonym is the default (normally dbo) you would just reference it like you would a table or view in your local database. CREATE SYNONYM dbo.OtherTable FOR LinkedServer.OtherDatabase.dbo.OtherTable Then SELECT * FROM OtherTable – Aaron Reese Feb 14 '23 at 10:32
  • rereading.... No, the Synonym has to be 4 part naming convention if the database is remote. You can get away with 3 part naming convention if the database is on the same instance and 2 part naming convention if it is within the same database (e.g. in DEV, the source and target are in the same database but in production they are separated...) The synonym HAS to belong to a schema so will always be 2 parts locally, but if it is in the default schema then you can omit the schema prefix as long as there is no naming clash. – Aaron Reese Feb 14 '23 at 10:35
  • Also note that Synonyns can't be chained, meaning you can't have a Synonym on your server referencing another Synonym on the remote server, so at best you can "compress" 4 parts of the name to one or two. – Zohar Peled Feb 14 '23 at 10:42
  • and if you use a remote view as a synonym you can't get at the dependency tree. – Aaron Reese Feb 14 '23 at 10:49
  • I will accept this answer because i think there is no way removing 5 parts to less – rm8019 Feb 14 '23 at 12:16
  • It does reduce it. you only have to set up the synonym once and then you can use it just like a table or view. – Aaron Reese Feb 14 '23 at 12:33
  • No it wont reduce it because you need to point a table to create a synonym. i cant create a synonym inside the linked server, just in the first. i always have to start with linkedserver1.linkedserver2 ... -> always 1 prefix to much to point a table – rm8019 Feb 14 '23 at 12:38
  • I didn't realise you wanted to go across 2 linked servers. As per another comment, I dont' think SQL allows you to call across 2 linked servers. It sounds like someone higher up the food chain needs to have a discussion around security and linked server configuration. If you are on server C and can only see server B which can see server A, then C cannot query data on A; the data would either need to be persisted onto B or give C a direct linked server connection to A – Aaron Reese Feb 14 '23 at 14:10