CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ron Harlev
Ron Harlev

Reputation: 16673

Using synonyms in stored procedures

I have a synonym for a table in another DB defined using

create synonym TableA for otherDb.dbo.TableA

I have a locally defined stored procedure

CREATE PROCEDURE dbo.spGetTableA
AS 
BEGIN
SELECT * FROM TableA
END

Now when I call the SP

EXEC spGetTableA

I get the following error
Invalid object name 'TableA'

While calling the SQL directly SELECT * FROM TableA
works perfectly.

Any idea what I'm missing for this to work?

Upvotes: 2

Views: 6028

Answers (1)

anon
anon

Reputation:

You are probably calling the stored procedure from a user whose default schema is not dbo. Therefore you should always reference the schema both when you create the synonym and when you reference the table in a query.

DROP SYNONYM TableA;
GO
CREATE SYNONYM dbo.TableA FOR OtherDB.dbo.TableA;
GO
ALTER PROCEDURE dbo.spGetTableA
AS
BEGIN
  SELECT * FROM dbo.TableA;
END
GO
EXEC dbo.spGetTableA;

I wish I could bold all of those dbo. references within the code. They are important and should ALWAYS be there.

Please read:

Upvotes: 3

Related Questions