selecting a database from a database

Question

Post reply

selecting a database from a database

erics44

SSCertifiable

Points: 6311
More actions
May 18, 2010 at 1:30 am

#235187

Hi
I have a database that is like an interface to an application.
In that database there are a number of views that retrieve information from tables from a transactional database.
I want to have it these views can either point to the liove transactional database or a test one.
how can I change the views so that if there is an entry in a table (for example) in the interface database then the database that they point to will change.
almost like @database.dbo.tblTable
sorry if i havent explained myself well.
Thanks in advance

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

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

You have to use Dynamic-SQL for these purposes!

Something like

-- Declare and initialize the database name

DECLARE @dBName VARCHAR(20)

SET @dBName = 'MASTER'

-- Declare and initialize the query

DECLARE @QUERY VARCHAR(128)

SET @QUERY = 'SELECT * FROM '+@dBName+'dbo.MyView'

-- check if the concatenated string is syntactically OK

PRINT @QUERY

-- Exec the query

EXEC (@QUERY)

Hope this helps you and gets you started!

Im'Pras 😎

erics44 SSCertifiable Points: 6311 More actions · Answer 2

Hi

Thanks for the reply

I did think about dynamic sql but this would need to be in stored procedure wouldnt it?

Or could i put this in a view or a table-valued function?

thanks again

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

Personally I don’t like mixing test and production environment. I would prefer to have 2 different and separated environments for test and production. I think that using the same database as sort of a gateway to both environments will lead to problems in the future.

Having said that, you can use a user defined function that according to a parameter it will know which database to query. Bellow there is an example for such function. Take into account that each time that you’ll make a modification in your test environment, the function might stop working in you production environment also (which is the main reason that those 2 environments should be completely separated)

create function AdiTest (@DB int)

returns table

as return (select * from tempdb.sys.objects where @DB = 1

union all select * from adventureworks.sys.objects where @DB <> 1)

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

erics44 SSCertifiable Points: 6311 More actions · Answer 4

thanks

that is the answer i was looking for

the wording in my initial question wasnt 100% accurate, i just asked it like that to trya and make it easy to understand

believe it or not we have about 4 test environment of the same system so the function will choose between these.

we also have 2 live environments and I have one "interface" database for each. So changes in a test environment will only affect other test environments.

Thanks again

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 5

I would like to present a different solution for this kind of problem

Requirements:

1. Set of "ALTER VIEW" scripts for all views exising in each database placed in different folders

2. Create a "batch file" for each database scripts, similar to this

sqlcmd -i C:\test\AdventureWorks\VEmployee.sql

2. I assume there will be a table, which stores database Name to connect in Interface database, some thing similar to this

CREATE TABLE dbNameTable(dbName sysname)

3. Create a Trigger on above table similar to this

ALTER TRIGGER tr_dbname ON dbo.dbnametable
AFTER UPDATE
AS
BEGIN
DECLARE @dbNameSYSNAME,
@cmdStringVARCHAR(8000)
SELECT @dbName=dbName FROMdbNameTable
IF @dbName IS NOT NULL
BEGIN
SELECT @cmdString='c:\db'+@dbName+'.bat'
EXECUTE xp_cmdshell @cmdString
END
END

4. Whenever there is a need to change the VIEW's point to different database simply insert a record into table dbNameTable

Hope this helps

erics44 SSCertifiable Points: 6311 More actions · Answer 6

erics44

SSCertifiable

Points: 6311

May 18, 2010 at 5:41 am

#1168724

i like it

its definately interesting

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

I would recommend looking at synonyms. You define the synonyms for that system and create the views to use the synonyms. That way, when you move the code between environments - it does not have to be changed.

Example:

CREATE SYNONYM dbo.MyViewTable FOR db1.dbo.MyTable;

GO

CREATE VIEW dbo.MyView AS

SELECT ... FROM dbo.MyViewTable;

GO

In the other environment, you would create the following synonym:

CREATE SYNONYM dbo.MyViewTable FOR db2.dbo.MyOtherTable;

GO

The same view - in the other environment would access a different table or database.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs