How to select from a table in a different database in SQL Server 2008

Question

I have many database instances say for e.g EWB1,EWB2,EWB3 and so on..if suppose I am in EWB1 and I want to access a particular table's column from other nstances (in this case say EWB2,EWB3 and so on).. I am doing something like this

declare @name varchar(10);
DECLARE cur3 CURSOR FOR select EnterpriseDBInstance from [EWB_DAL].[dbo].[Enterprise] where EnterpriseDBInstance <>  CONVERT(varchar(10),DB_NAME());
OPEN cur3;
Fetch next from cur3 into @name;
WHILE @@FETCH_STATUS = 0
BEGIN   
declare @test varchar(10);
set @test = (select @name);     
select ChapterName from [@test].[dbo].[FormData] -- This line is giving error
                                                 -- unable to find @test....
Fetch next from cur3 into @name;
END
close cur3; 
deallocate cur3;

But I could see EWB2,EWB3... in @test..

What database are you using? Each vendor has a different way of supporting such functionality (if at all). What is "SQL 2008"? Do you mean SQL Server 2008? — Oded
– Oded, Commented Mar 23, 2012 at 11:30

Oded · Accepted Answer · 2012-03-23 11:33:53Z

1

You can't use database names dynamically (i.e using variables), in the same way that you can't use table or column names in such a manner.

The query you have is trying to find a database named @test.

You need to look at using dynamic SQL for such work, or rethink your approach. I suggest reading The Curse and Blessings of Dynamic SQL by Erland Sommarskog.

answered Mar 23, 2012 at 11:33

Oded

501k102 gold badges900 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sajesh Nambiar · Accepted Answer · 2012-05-30 06:03:58Z

0

use qoutename like qoutename(ur DB variable name) this works...

answered May 30, 2012 at 6:03

sajesh Nambiar

6992 gold badges11 silver badges28 bronze badges

Collectives™ on Stack Overflow

How to select from a table in a different database in SQL Server 2008

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related