Post reply

Synonyms

  • August 1, 2016 at 1:17 pm

    #328530

    I think the answer is 'no', but I'll ask it anyway. I have two databases on different servers referenced through linked server. Is it possible to create a synonym that encompass the linked server name, database name and schema , but not the object? For example:

    CREATE SYNONYM MySynonym FOR

    [MyLinkedServer].[MyRemoteDB].[MyRemoteSchema]

    Then use the synonym as:

    select * from MySynonym.MyRemoteTable

    SQL Server had no problem creating the synonym, but I couldn't get the query to work, and I was wondering if I was doing something wrong with the query, or the problem is more basic, that I can't use the synonym as I wish.

    Thanks

  • August 1, 2016 at 1:40 pm

    #1893052

    CREATE SYNONYM (Transact-SQL)

    Synonyms can be created for the following types of objects:

    Assembly (CLR) Stored Procedure

    Assembly (CLR) Table-valued Function

    Assembly (CLR) Scalar Function

    Assembly Aggregate (CLR) Aggregate Functions

    Replication-filter-procedure

    Extended Stored Procedure

    SQL Scalar Function

    SQL Table-valued Function

    SQL Inline-table-valued Function

    SQL Stored Procedure

    View

    Table (User-defined)

    Note that schema is not one of the listed objects.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • August 1, 2016 at 2:32 pm

    #1893069

    The answer is NO, objects only

    😎

  • August 2, 2016 at 11:58 am

    #1893311

    Also, to save you the trouble of future attempts along similar lines, it is important to understand that SQL queries reference specific objects and create execution plans based on the metadata and statistics for those objects. These plans are usually stored for re-use. This is why you can't just tack on the table name as if it were a string to be manipulated.

    The only way to create a SQL string and execute it is through Dynamic SQL. Be aware that it can expose your database to SQL Injection attacks if done incorrectly.

    https://msdn.microsoft.com/en-us/library/ms188001.aspx

    https://technet.microsoft.com/en-us/library/ms161953(v=sql.105).aspx

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 4 posts - 1 through 3 (of 3 total)

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