SQL Server Bug – Synonyns

The below bug was driving me crazy a few weeks ago until I figured it out via the power of Google.

In short, when your target is on the same server, don’t include the server name.

USE [master]
GO

--Create a database to test with
CREATE DATABASE Test_A
GO

--Create a second database to test with
CREATE DATABASE Test_B
GO

--Start in the first database
USE Test_A
GO

--Create a Procedure to play with
CREATE PROCEDURE TestMe
(
@INT INT
)
AS
SELECT @INT
GO

--Quick test to ensure that everything works as intended
EXEC TestMe @INT = 5
GO

--Switch databases to show the problem we're having
USE Test_B
GO

--Run the procedure using the four part name. Note that with the server name set appropriately everything works...
EXEC DB1.Test_A.dbo.TestMe @INT = 5
GO

--Create a synonym that won't work
CREATE SYNONYM dbo.Fail_SYNONYM FOR DB1.Test_A.dbo.TestMe
GO

--See? Crazy.
EXEC dbo.Fail_SYNONYM @INT = 5
GO

--Create a synonym that will work!
CREATE SYNONYM dbo.Win_SYNONYM FOR Test_A.dbo.TestMe
GO

--See?
EXEC dbo.Win_SYNONYM @INT = 5
GO

/*
USE [Master]
GO

DROP DATABASE Test_A
GO

DROP Database Test_B
GO
*/
This entry was posted in SQL Server Development and tagged , . Bookmark the permalink.

One Response to SQL Server Bug – Synonyns

  1. PocjekJoshua says:

    Did not know that. Any Idea what happens if you create a linked server back to the same server. I suspect it would work but you would loose all the conveniences of a of cross database connections.

    In other words, for development, it may be a good idea to use the synonym with a linked server back to the same server to ensure while testing you don’t accidental do something that won’t work in production like calling a UDF, or trying to pass XML or a NVARChAR(MAX).

    Just a thought.

Leave a Reply

Your email address will not be published. Required fields are marked *