I know nothing about synonyms before so this is a good point to learn.

My environment is that I am deploying a stored proc at multiple locations (different customer sites). I can't rely on the server name, (and to a lesser degree the database name) being the same, or I might want to connect to a test vs. production server, which may or may not have the same server or database names.

In the past I'd just create an ODBC connection to the alternate server/database ... but I'd like to try and have the communication between the two databases be handled by the stored procedure in this case without a separate ODBC connection for the second database, and I don't want to have to distribute customized versions of the stored procedure if possible.

If I understand how synonyms might work in my situation it sounds like I still might need a combination of dynamic sql and synonyms.....

First, I would code the stored proc using the synonym ... such as myRemoteServer and then have a procedure in my actual client code that would create the synonym 'on the fly' from a table entry in my main database ... in other words .. something like:

I could draw the values for 'sever2' and 'database2' from a table in my main database, then assemble a text string that contained these values ... as in :

server2 = "someserver"

database2 = "somedatabase"

sql = "CREATE SYNONYM MyRemodeServer FOR " + server2 + "." + database2

xyzconnection.execute SQL <--to create the synonym....

and finally execute that SQL statement to create the synonym in my database ... and then call my stored proc which references that synonym ...

in my stored proc I'd simply refer to MyRemoteServer whereever I would have had to refer to server2.database2 ....

Thanks for the suggestion regarding synonyms.