[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Parameterising DB Names

obelix

3/26/2007 8:02:00 PM

I've got cross database joins in s-procs and triggers. For ease of deployment
I would want to refer to db names through a variable but I have tried this
without success:
a rough e.g
declare @db_name varchar (20)
set @db_name = 'my_database'

select * from @db_name.tbl

This is for MS SQL 2000

--
obelix

"Whether you think you can or you think you cant you are right" .... Anon

Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programmin...
2 Answers

David Portas

3/26/2007 8:26:00 PM

0

On 26 Mar, 21:02, "obelix" <u24035@uwe> wrote:
> I've got cross database joins in s-procs and triggers. For ease of deployment
> I would want to refer to db names through a variable but I have tried this
> without success:
> a rough e.g
> declare @db_name varchar (20)
> set @db_name = 'my_database'
>
> select * from @db_name.tbl
>
> This is for MS SQL 2000
>
> --
> obelix
>
> "Whether you think you can or you think you cant you are right" .... Anon
>
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-progra......

When you need to reference a table in another database, create a view
for it or a synonym (2005 only). That way you can parameterise the
view creation at install time. It's definitely not a good idea to do
the parameterisation for this at run time (dynamic SQL).

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
--

Hari

3/27/2007 5:07:00 AM

0

Hello,

You may need to use Dynamic SQL using EXEC or SP_EXECUTESQL in SQl 2000.
Take a look into the sample from below site:-

http://sqlserver2000.databases.aspfaq.com/how-do-i-get-the-result-of-dynamic-sql-into-a-var...

Thanks
Hari

"obelix" <u24035@uwe> wrote in message news:6fc4b4bd4892e@uwe...
> I've got cross database joins in s-procs and triggers. For ease of
> deployment
> I would want to refer to db names through a variable but I have tried this
> without success:
> a rough e.g
> declare @db_name varchar (20)
> set @db_name = 'my_database'
>
> select * from @db_name.tbl
>
> This is for MS SQL 2000
>
> --
> obelix
>
> "Whether you think you can or you think you cant you are right" .... Anon
>
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programmin...
>