quering off of 2 databases

[bouwob]

I need to run a query that accesses 2 different databases. This normaly would be a simple query with full name values but the databases happen to be on 2 different boxes.

my first though was grab the data off of 1 then ship it to the other via code but I end up getting stack overload errors (not enough memory to run the query)

so something like

select * from db1.table where db1.table.colum in (select db2.table.colum from db2.table)

this query would work fine but if I replace "select db2.table.colum from db2.table" with actual data (which could be 100 characters or 100,000 characters) the query fails do to mamory issues. Does anybody have any suggestions of how to go about doing this. Will making the shorter query still create the timeout issues since the data is still being shipped from 1 place to another?

tia

 

[SQLSister]

why not use linked servers and then use the fourpart name to specify rather than moving the records.

look up sp_addlinkedserver in BOl for how to do this. once the linked server is set up then on servera you can select records from a table in a database on serverb this way

select field1, field2 from serverb.mydatabase.dbo.mytable

"NOTHING is more important in a database than integrity." ESquared

 

[harebrain]

Or you can use Open Query.