Exceute select on another db

[TomBarrand]

Is it possible to execute a SELECT statement on database B from within a stored procedure in database A?

If so syntax would be appreciated.

Thanks

 

[Ologhai]

DECLARE @retstat int
EXECUTE @retstat = SQLSERVER1.pubs.dbo.checkcontract '409-56-4008'
--------------------------------
Hugues Gauthier, DEC
Développeur Web

Intelligence Digital
1751 Richardson
Suite 4230
Montréal, Québec
H3K 1G6

http://www.intelligencedgt.com/

hgauthier@intelligencedgt.com
hugues_gauthier@hotmail.com

-------------------
ICQ: 2151800

 

[osjohnm]

Hi

If the databases are on the same server then it would look something like this:

select * from database2.dbo.table1

-- or like so

select order_no, order_date from orders
where product_code in
(select product_code from database2.dbo.product)

-- you can join table from different databases as well

select p.name, a.address
from database1.dbo.person p, database2.dbo.addressbook a
where p.address_id = a.address_id


just make sure you fully qualify the database name, owner and table name

John

 

[TomBarrand]

Thanks thats great.

The problem I now have is that the database has a - in it and I get an incorrect syntax error.

e.g. Test-A

Any ideas how I get round this?

Thanks

 

[alfredp77]

enclose the database name in square brackets:

select * from [Test-A].dbo.TableName

 

[spacew]

Is it possible to use a variable for the database name in the query? I want to select data from another database but the database name is read from a table which is configurable. Please advice. Thanks.

Test Scripts:

declare @db as varchar(20)
declare @sql as varchar(50)
set @db = 'stbank'
set @sql = 'SELECT * FROM ' + @db + '.dbo.master'
exec @sql

Errors Received:
Server: Msg 911, Level 16, State 1, Line 5
Could not locate entry in sysdatabases for database 'SELECT * FROM stbank'. No entry found with that name. Make sure that the name is entered correctly.

But I can execute the following statement without any problem:
SELECT * FROM stbank.dbo.master

 

[osjohnm]

Hi

You didn't enclose your @sql in brackets when you executed it. Also I removed the spaces around @db which might have caused some problems. All my changes are in bold.

Try the following:

declare @db as varchar(20)
declare @sql as varchar(50)
set @db = 'stbank'
set @sql = 'SELECT * FROM '+@db+'.dbo.master'
exec(@sql)

another way to execute it without the brackets is like so:

execute sp_executesql @sql

Hope this helps

John

 

[spacew]

It works! Many thanks for your help!