Cross Database Access

Question

Cross Database Access

Tim DySard

Grasshopper

Points: 13
More actions
September 15, 2010 at 1:28 pm

#226359

I have 2 databases...
Currently, in a dev environment, DB1 references information in DB2 as follows "select * from [DB2].dbo.table"
DB1 is actually part of an application that needs to be installed on many different clients sql servers. The issue is that DB2 may be something completely different depending on the client.
Is there an easy way to change DB2 to say [DB3] across all sql server objects (in particular views). I'm thinking of using SMO for this, but didn't know if that was my only or best option.
Thanks...

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Assuming you are using stored procs and not dynamic sql in the front end (which then offers a very simple solution), you can change that value in the install script(s).

Or in the install script you can build a series of synonyms which would then point on the correct database. Either way, you need to know that ahead of time before performing the install right in the install GUI.

Tim DySard Grasshopper Points: 13 More actions · Answer 2

Thanks for your response....Synonyms seems promising....

"Assuming you are using stored procs and not dynamic sql in the front end (which then offers a very simple solution), you can change that value in the install script(s).

Or in the install script you can build a series of synonyms which would then point on the correct database. Either way, you need to know that ahead of time before performing the install right in the install GUI."

I tried a test and not sure I am understanding this correctly...

So, I have an install scipt...let's just say there is 1 view in it for now....

Create View vwMAC_ARCUSFIL_SQL

AS

SELECT * FROM [010].dbo.arcusfil_sql WITH (NOLOCK)

[010] is our dev environment db and this script needs to be run on a client site where [010] is going to be [020]. What I want to happen is create a synonym for [010].dbo.arcusfil_sql perhaps call it [020].dbo.arcusfil_sql. Not sure if I am thinking this through correctly....

So, the code (script) will still say [010].dbo.arcusfil_sql...But, when the app references the view, it will really use [020].dbo.arcusfil_sql

CREATE SYNONYM [020].[dbo].[arcusfil_sql]

FOR [010].[dbo].[arcusfil_sql]

I know you can't use a db name in synonym name. Once again, my brain is missing something here so any assistance would be appreciated.

Thanks again.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

You need to use dynamic sql to create the build scripts to install all the objects in the database.

You script would need a single parameter which would be the database name. Then replace the [10]. to [20]. everywhere in the script and you're done.

I would personnally use vb or c# in the application installer. It's easy to just copy everything there, or even read the scripts from a folder and do the replace that way.

In the GUI installer you can setup some sort of screen that lets the user choose a db name, once you have that info you're set to run all the scripts.

Tim DySard Grasshopper Points: 13 More actions · Answer 4

okay, that is what I was thinking as well. Just wasn't sure how synonyms could assist on the client side. Sounds like it could be more help on the dev side of things when testing multiple db's.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

I didn't know about the synonyms in the same DB, I always used them for cross server problems. So assuming your sources are right (and that you may need to support sql 2000), you're better off building the scripts on the fly for the install.

Here's a simple code I use in C#. I work with an ERP that prefixes all the table names with the Company name. So everytime I connect to SQL, I need to set the Cie name in the query (Q._Company is parameterized on the creation of the class) :

public SqlCommand GetMakes()

{

string sQuery;

sQuery = string.Format(@"

--List of makes (Queries.GetMakes)

SELECT Code

, Description

FROM dbo.[{0}$Item Category]

ORDER BY Description"

, Q._Company);

SqlCommand Cmd = new SqlCommand(sQuery);

Cmd.CommandType = CommandType.Text;

return Cmd;

}

//'Constructor

public AllQueries(bool IsCRS, _E_SqlServer SqlServer, string UserName, string Company, ref ViewStateManager VSM, bool bIsFromInternal)

{

Initiate(IsCRS, SqlServer, UserName, Company, ref VSM, bIsFromInternal);

}

private void Initiate(bool IsCRS, _E_SqlServer SqlServer, string UserName, string Company, ref ViewStateManager VSM, bool bIsFromInternal)

{

if (!bIsFromInternal)

{

//'build sub classes...

}

_IsCRS = IsCRS;

_SqlServer = SqlServer;

_UserName = ObfuscateUserInput(UserName);

_Company = ObfuscateUserInput(Company);

_MyCn = GetConnection(false);

_VSM = VSM;

}

All you'd need to do is dump all the .sql script files in a folder somewhere. Then loop trought all the files and replace [10]. with the correct db name before executing the scripts.

Also this solution would work on sql 7 and 2000, if for some odd reason, you need to support them.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Tim DySard (9/16/2010)
okay, that is what I was thinking as well. Just wasn't sure how synonyms could assist on the client side. Sounds like it could be more help on the dev side of things when testing multiple db's.

I've done that kind of stuff as well. What I worry about sql side is my code working on the local server so that a backup / restore works instantly on any machine.

Once that's setup I adapt the client application to "figure out" which environement it's running on. My application is web based so I can use the server name in the url to know what db server to connect to.

I also built a tool on the login screen that appears only on my dev machine to let me choose the work environement.

Nothing fancy but it takes a while to get done right.