CODEWITHSUNDEEP
sql-server-2008
Sajesh Nambiar
Sajesh Nambiar

Reputation: 1

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

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..

Upvotes: 0

Views: 127

Answers (2)

sajesh Nambiar
sajesh Nambiar

Reputation: 699

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

Upvotes: 0

Oded
Oded

Reputation: 499002

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.

Upvotes: 1

Related Questions