Multi-database procedures... 1 Database rehomed

[lameid]

I had two databases in SQL Express.

One has been relocated to a SQL Server.

I still need to use the Second Database for some purposes but several procedures use the first database.

Clearly I need a linked server but I am wondering if there is a path of least resistance I should follow to minimize fixing procedures (I'm guessing a dozen procedures or so). In short this will probably be the most complicated SQL Server change I have made to date and I am pressed for time to do proper research. Advice is greatly apprceciated here.

Ideally I would just add this to the transferred SQL server database as a new schema but the size is causing a more than tolerable bureaucratic pause.... If it had not been for the Express 10 GB size limit, I likely would have done it this way in the first place.

 

[simian336]

Wondering why you wouldn't just move them both to sql server?

Simi

 

[lameid]

Wondering why you wouldn't just move them both to sql server?

not a server admin, ousourced service, bureaucracy, lack of a sufficiently large clue bat, timetable

 

[lameid]

So I've seen Synonyms that apply to schema scoped obects so thats a no go and Server aliases....

Since my syntax for the first databse is....

Datbase.schema.object

I am sunk for those methods... Actually I am even if I had used a server alias because the database name was changed on me. I hate it when I'm not the admin.

 

[lameid]

Any one out there with a brilliant solution?

 

[mrdenny]

Select * from syscomments where text like '%databasename%'

That will find the objects in the database that reference the now missing database.

This is the risk with cross database calls.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Blog