Select command from diferent database -C#

[Guest]

(Using SQL Server and Vs 2003.net - C#)

I have TableA on Database1: //Clients Database
CodCli -- Name -- Age
(...) (...) (...) (several Rows of Information)


I have TableB on Database2: //Contrats of the Clients.(Client can have 2
contrats)
Key -- CodCli -- Date -- Value -- Vend
(...) (...) (...) (...) (...) (several Rows of
Information)

I need a dataset to be printed, that as this:
SQL = "Select * from TableA(Database1) and TableB(Database2)
Where Vend in TableB(Database2) = " &StrVar

In Database1 the primarykey in CodCli.
in Database2 the primarykey is Key
StrVar in a value that the user inputs.

Thanks all.

 

[Guest]

Hi Paul Oliveira,

You can use a linked server query.

select * from openquery (linkedservername,'select column list from
TableA(Database1) where condition') A,TableB(Database2) B where
A.CodCli=B.CodCli and tableB.vend=some value

the tableB should be an sql server table

Hope this helps!.

Regs,
K.suresh

 

[Dave Fancher]

Paul,
In addition to Suresh's suggestion, if both databases are on the same
instance of SQL Server you could also use a cross-database query by
specifying the three part name (database.object_owner.object_name) of the
database object you're selecting from. For example:

SELECT * FROM database1.dbo.TableA a JOIN database2.dbo.TableB b ON a.CodCli
= b.Key

This may not be possible in your environment and both solutions have
advantages and disadvantages which you'll need to weigh against the
requirements of your application. I just offer it as another possible
solution.