Substitute Var for part of Fully Qualified Name

Question

Post reply

Substitute Var for part of Fully Qualified Name

dthompson-893123

SSC Veteran

Points: 200
More actions
March 24, 2009 at 7:50 am

#130038

My company hosts databases for many divisions on several SQL servers (SQL Server 2005), many on the same SQL Server.
Each division has a pair of databases, an ERP database and a Reporting database. The ERP database supports a proprietary ERP package and we never, ever change it except at the direction of the ERP software supplier.
All of our customizations are done in the Reporting database.
All of the database pairs are identical except for the database names.
Since there are several divisions hosted on one server and the databases have different names (Div1ERP and Div1Reporting, Div2ERP and Div2Reporting, etc.) all of our views and stored procedures in the Reporting database must have fully qualified names when referencing the ERP database.
Is there a way to create views and stored procedures where part of the fully qualified names can be substituted by a variable?
So instead of select * from Div1ERP.POTable, it could be something analogous to:
DBVar = 'Div1ERP'
Select * from DBVar.POTable
This would allow us to maintain just one view and stored procedure that would be identical across all databases, save tremendous amount of time and eliminate potential errors when updating views and stored procedures.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

Did you try sp_executesql?

USE tempdb

GO

IF (OBJECT_ID('test') IS NOT NULL)

DROP TABLE test

GO

CREATE TABLE test (id INT, txt VARCHAR(100))

GO

INSERT INTO test

SELECT 1, 'hello'

UNION SELECT 2, 'world'

DECLARE @sql NVARCHAR(MAX)

SET @sql = 'SELECT * FROM tempdb..test'

EXECUTE sp_executesql @sql

Greets

Flo

dthompson-893123 SSC Veteran Points: 200 More actions · Answer 2

Hmmm..I see where you are going, but I don't think it's what we need. At the very least it woulid require a collosal re-write and I'm not at all sure how it would work wuth views.

thanks

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

How about a separate synonym for each pair?

http://msdn.microsoft.com/en-us/library/ms187552.aspx

You could define the synonym differently in each db, but iwth the same name.

dthompson-893123 SSC Veteran Points: 200 More actions · Answer 4

I like it. I'm experimenting with it now.

One thing that does not seem obvious to me is this: Do I need to include the SYNONYM in each SQl object (View, Store procedure), or can I define it in the DB schema and have it available to all queries on that DB?

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 5

I think synonyms are always per object.

But if you call

Select * from Div1.POTable

in the Div 1 DB and then from Div 2

select * from Div2.POTable

You can make a synonym that is POTable in each database and call it from the code.

However, if you're calling with SQL like this, you're asking for a lot of code changes. Wouldn't you just define views where needed? That somewhat accomplishes the same thing.

dthompson-893123 SSC Veteran Points: 200 More actions · Answer 6

If I understand you correctly:

We use views extensively and every view in each reporting DB uses fully qualified names. Creating new views or modifying views is a royal pain because all the find and replace action required.

In the best of all possible worlds, in the schema of the database, the synonym would be created and any query done within that database at anytime would be able to reference the synonym.

So

create ReportingDB1 blah blah.

create synonym MyERP for Div1ERP

create table1

balh blah.

Now, whenever I open SQl Management studio and connect to ReportingDB1 I can type "select * from MyERP.POTable".

Any views I created I can simply reference MyERP instead of the fully qualified name. So the Synonym would be a permanent object in the ReportingDB1 database.

I have not tried any of this yet.

Instead I have been playing with views (since it is clear the stored procedures works just fine with synonyms).

It seems to make sense:

USE [ReportingDB1]

GO

CREATE SYNONYM MyERP for iERP81.dbo

go

CREATE VIEW [dbo].[MyView]

As

Select MyERP.SalesOrder.SalesOrderID from MyERP.SalesOrder

But when it runs It claims that MyERP.SalesOrder.SalesOrderID is an invalid Object name. I am not sure at this point if it is because synonyms are not supported in this instance or because I just not doing it right.