Synonym for a function (cross DB, not Cross Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Synonym for a function (cross DB, not Cross Server

Author

Topic

obiron
Starting Member

23 Posts

Posted - 2009-01-30 : 08:37:46

Hi guys, first post here...
Background:
We are in the process of moving our dev and live environments onto a new 2005 box and moving business logic from the 3rd party datastore to a new DW.

we are making extensive use of synonyms for tables (it enables you to write code that will work on the live and dev environment because you simply change where the synonymns point to in the LIVE environment)

the documentation says that you can also use synonyms for functions.

Datastore is A
I am developing in B

When I call the function [A].[dbo].[function] 'param1','param2' from B it works fine.

When I call the function [synonym_of_function] 'param1', 'param2' from B I get an error message
Procedure or function '[synonym_of_function]' expects parameter '@Param1', which was not supplied.

any ideas?

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-30 : 08:43:56

Parameters to functions need to be enclosed in paranthesies, so I doubt your statement " [A].[dbo].[function] 'param1','param2' " will work fine.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-30 : 08:44:41

Try [synonym_of_function]( 'param1', 'param2' )

E 12°55'05.63"
N 56°04'39.26"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-01-30 : 08:53:22

Ok, is it just me or does this use of synonyms sound really silly...? No offence but why not use two databases instead and just change the connection...?

- Lumbago

obiron
Starting Member

23 Posts

Posted - 2009-01-30 : 09:32:47

quote:
is it just me or does this use of synonyms sound really silly

4 databases: Datasource Dev, Datasource Live, reporting Dev, Reporting Live.

We are using VSS for source code control and VS2005 for development (rather than coding directly in MSSMS)

By synonymising (is that a word?) the remote tables in the stored procedures, the same usp will work in both the dev and live environments.

USE Reporting_Dev
GO

IF  EXISTS (SELECT * FROM sys.synonyms WHERE name = N'syn_AR')
DROP SYNONYM [dbo].[syn_AR]
GO

IF NOT EXISTS (SELECT * FROM sys.synonyms WHERE name = N'syn_AR')
CREATE SYNONYM [dbo].[syn_AR] FOR [SW-SQL].[Datasource_Dev].[dbo].[AR]
GO

USE Reporting_LIVE
IF  EXISTS (SELECT * FROM sys.synonyms WHERE name = N'syn_AR')
DROP SYNONYM [dbo].[syn_AR]
GO
IF NOT EXISTS (SELECT * FROM sys.synonyms WHERE name = N'syn_AR')
CREATE SYNONYM [dbo].[syn_AR] FOR [SW-SQL].[Datasource_LIVE].[dbo].[AR]
GO

then in your stored procedure you always use syn_AR and you know your code will work. It is also a lot cleaner to read than [database].[schema].[table]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-01-30 : 10:14:20

I have never seen this approach before but I guess it makes sense if you got a lot of cross-database stuff going on and don't have individual servers for dev/test/prod. To be honest I have never used synonyms before so I learned something new too!

- Lumbago

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources