Philip Hendry's Blog

Calling SQL Server Stored Procedure Synonym fails with parameter error

Sep 2, 2010 • Problem, SQL Server, T-SQL • Less than a minute read

I’ve just tried to call a stored procedure in another database via a synonym but received an error ‘Procedure or function ‘LMEmployeesHasNewStoreTempsInStore’ expect parameter ‘@OrganisationLevelValueID’, which was not supplied.

Here’s how I defined the synonym and called it :

CREATE SYNONYM [dbo].[LM_Employees_HasNewStoreTempsInStore] 
FOR [PHENDRY].[LabourManager_DEV].[dbo].[LM_Employees_HasNewStoreTempsInStore]

exec [dbo].[LM_Employees_HasNewStoreTempsInStore] 1119

As you can see, I’m definitely passing the parameter!!

The problem appears to be how the synonym is defined when the two database exist on the same server. The machine name, here ‘[PHENDRY].’ can be removed completely and everything works fine!

Post by: Philip Hendry