'''' + PARSENAME(base_object_name,2) + ''''įrom sys.synonyms where name = 'syntest' -<<<< The name of your synonym 'exec ' + PARSENAME(base_object_name,4) + '.' + I can use PARSENAME to extract the individual parts of the base_object_name to build the dynamic SQL statement Declare nvarchar(4000) Issuing select * from sys.synonyms where name = 'syntest' reveals a base_object_name of. This synonym points to server SqlCompare, database SCOPERATIONAL_DBCC, schema SCFBDM and table CFG_LINE_BUS. In my example, I created a synonym called syntest as follows: CREATE SYNONYM. This is another example of how you could do this - it may need to be adjusted per your requirements. You can get the list of remote stored procedures using this code: select * I cannot get the list of the stored procedure. Server(Server B) from local server(Server A). I want to know whether I can access the stored procedure of the remote
In accordance synonyms how to#
How to write queryĬommonly to get object_id for this scenario and remote object ?Īs Scott Hodgin mentioned, you can use PARSENAME() to get rid of remote_server's part of your object like this: declare varchar(8000) = (select Scenario : I have server A which has two database( database1 and The word synonym refers to a word that has the same meaning as another word, or a meaning that is very close to that of the other word. Where c.object_id = nvarchar(400) = sysname' Įxecute.
In accordance synonyms code#
If you want to get remote object's object_id, you should do smth like this: exec ('select object_id(''.'')') at Īnd in general, if you want to get the columns of remote object, you should execute your code on the remote server, looking for the columns of remote object in the remote sys.columns in the remote database, for example, this way: declare sysname = '.' OBJECT_ID(_object_name) from your code will be resolved on a local server because OBJECT_ID() operates within local server, but you pass to it the name from remote server, so you'll get NULL here. Your error is that you ask your local server about the object from another, remote server. WHERE _type_id = _type_idĬan you please suggest some other way to retrieve the columns of the synonym or correct the above query? I have tried to retrieve the columns of the synonyms using below query, but I cannot fetch the columns SELECT AS SCHEMA_NAME, select * from dbo.testĭbo is synonym created schema name (local server schema name) and test is synonym name. I can retrieve the columns of the synonym using below query in SQL server. I need to fetch the columns of the synonym. I have created synonyms in my local server for a table which is located on the remote server. I am using two servers local (SQL Server running on my system) and remote (SQL Server running on another system).
0 kommentar(er)
