Can I coax a synonym to execute in the context of the database?

Question

Can I coax a synonym to execute in the context of the database?

Scott In Sydney

Hall of Fame

Points: 3331
More actions
July 9, 2019 at 2:04 am

#3660981
Hi,
I have this TVF in DB1:
```
ALTER FUNCTION [dbo].[fn_replica_valid_date]
(
    @rldx_audit_key INT
)
RETURNS TABLE
    RETURN (
        SELECT CASE
                WHEN (rldx_audit_key = 26) THEN DATEADD(DAY,3,CAST(extract_date AS DATE))
                ELSE DATEADD(DAY,2,CAST(extract_date AS DATE))
            END AS replica_valid_date
          FROM dbo.RLDX_AUDIT_TRAIL
         WHERE rldx_audit_key = @rldx_audit_key
    )
```
The identical code needs to run in DB2 (and DB3 and DB4 and ...).
Synonyms to the rescue. So I created a synonym in DB2 pointing to the TVF in DB1.
Both databases contain the table dbo.RLDX_AUDIT_TRAIL, with different data.
However, when I execute the code via the synonym in DB2, I retrieve the data from DB1. For example, I provide an rldx_audit_key parameter that is in the DB1 table, but not in the DB2 table and so should return NULL.
Is this just how synonyms work? I assume yes (bummer), but please confirm.
If so, I guess I need to cut-and-paste code across the databases, and all the maintenance headaches that introduces.
Thanks...

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

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

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 1

This isn't well documented for anything other than tables, which is why you may be confused. The Synonym doesn't change context. This provides an easy way to reference something in another database. When you call the synonym, it's as if you executed this:

exec db1.[dbo].[fn_replica_valid_date]

This will execute on the context of db1. If you create a proc like this:

CREATE OR ALTER PROCEDURE GetDBFiles
AS
BEGIN
SELECT * FROM sys.database_files AS df
END

If you create a synonym in another database and execute this, you will get the files from db1.

If you need this in every database, then you need the proc in every database. While you see this as a cut/paste maintenance issue, I see this as a software development issue. You ought to have a deployment process that can update changes to this proc in every database with one push. A DevOps view of this is fix the deployment process.

You could do this as simply as a PoSh script or CMS to deploy the update to all databases. You could also set this up to better deploy all sorts of changes to code across lots of databases.

Scott In Sydney Hall of Fame Points: 3331 More actions · Answer 2

Hi Steve,

Apologies for the late reply. Thanks for that.

While I'm really enjoying working with SQL Server, unfortunately few of my colleagues are proficient in SQL Server. Or, if they are, they're in completely different teams and unknown to me. So there are no shoulders of giants on which I can stand. Which is yet another reason this forum is so useful to me - I get to stand on virtual shoulders, so to speak.

I'll Google (or search these forums) for SQL Server DevOps.

Perhaps another approach would be a SQL Server Database project? I've played with them a bit, although SSMS is still my goto tool. IIRC Database projects could be used to keep objects in sync between databases. I really must wrap my head around how I can integrate them into my work with SQL Server. Although I suspect if I observed 10 DBA's, I might see 10 different ways of working (???).

I'm also fairly experienced with PoSh so that too would be an option for me. We don't use a CMS.

Thanks again for your reply...