Newbie question: query over two data sources

[N Ramsay]

I need to create some SQL queries that join over two data sources.

I can easily create connection strings to these data sources, but am
unclear how to create my SQL.

I'm a real noob to this, and up until now have been using the
drag&drop tools in Vis Web Dev Express.

If I look at the asp code for queries on a single db, I understand
exactly what's going on, but can't seem to work out how to using
mutiple data sources.

Many Thanks in advance.

 

[Cowboy \(Gregory A. Beamer\)]

This is a real "it depends" answer.

One option is to use linked servers and a single query on the server that
has the link to the other server. This works well if you have low latency
and this is not a critical part of your day to day operations.

Another option is to move both databases, via repliction generally, to
another server and use joins across databases. This is a better option if
the need for the joins is reporting, where you can churn for awhile. It
might also be wise to blend schemas on the replicated databases to create a
single database, but this is not always possible. In the same vein, you
might replicate just the tables in question to your main database, althoguh
this is not generally the best pattern.

If you are going to get hammered if you link and a third database is not an
option, you might be better to run separate queries and merge the data in
some form of data construct, like a DataSet. This can perform a bit better
if you have to update info in both databases, but you will have to write the
transactional bits yourself and watch both transactions for failure, so you
can fail the other transaction. If you do not, you will end up with the
databases out of sync. Hopefully, your updates are pretty much in sync.

Which of these should you choose? It really depends on the application and
what you are doing with the two databases.

 

[N Ramsay]

This is a real "it depends" answer.

Thanks Cowboy

The two data sources are 2 different SQL databases that exist in the
same DB schema. They also have a common SQL username and password. The
connection strings to them that I have are identical apart from the
name of the connection string itself.

I thought I would be able to access them both via the same connection
string, but it seems the connection string points to a particular db
rather than the schema as a whole. I don't seem to be able to use
"dbname.tablename.fieldname" as a reference, which is frustrating.

I think a DataSet is probably my best bet.

Cheers,

nts: work out how to use a DataSet...

 

[Cowboy \(Gregory A. Beamer\)]

Thanks Cowboy

The two data sources are 2 different SQL databases that exist in the
same DB schema. They also have a common SQL username and password. The
connection strings to them that I have are identical apart from the
name of the connection string itself.

I thought I would be able to access them both via the same connection
string, but it seems the connection string points to a particular db
rather than the schema as a whole. I don't seem to be able to use
"dbname.tablename.fieldname" as a reference, which is frustrating.

You are limited in query syntax, but this can be overcome with stored
procedures, or at least, potentially overcome. It depends on the "custody
chain" (ownership chain actually). As long as the same SQL user has access
to all objects, you should be able to do something like this:

CREATE PROCEDURE TwoDatabaseProcedure
(
@UserID int
)
AS

SELECT * FROM Users u
JOIN Database2.dbrders o
on u.UserID = o.UserID

NOTE: I am not suggesting using a wildcard, but I did not want to type out a
bunch of fake columns, as it is a waste of my time.

Work in Enterprise manager to create the queries. Log in as the user for the
website (SQL or Windows auth) and make sure you can get your answer.

One ofther benefit with the sproc direction is the ability to secure all
tables from direct user access by granting rights to sprocs only. But, that
is a story for another day.

 

[Jeff Dillon]

The fundamental question is "why" do you have two identical databases

Jeff