join two databases in a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

krause73 - 16 May 2007 19:47 GMT

I am trying to run a query that accesses two different databases on
the same SQL server. Is this possible in InfoPath? The query is so
simple in Query Analyzer, but I'm not sure how to get it to work
within the confines of InfoPath.

Select t1.Field1, t2.Field1

>From db1..Table1 As t1

Left Join db2..Table2 As t2 On t2.tableID = t1.tableID

Is this possible? If so, please explain how?

Reply to this Message

Art - 17 May 2007 16:48 GMT

You need to either create a new table with the joins or create two data
connections and set the rules on the first to pull data from the second based
on the match

> I am trying to run a query that accesses two different databases on
> the same SQL server. Is this possible in InfoPath? The query is so
[quoted text clipped - 6 lines]
>
> Is this possible? If so, please explain how?

Reply to this Message

pocceygirl - 17 May 2007 17:01 GMT

Hi Art,

I'm having a similar issue. Could you explain more about creating 2 data
connections? I have my main connection and a secondary data connection. But
with the secondary data connection I'm only allowed dataFields, I would like
to use queryFields.

Thanks.

> You need to either create a new table with the joins or create two data
> connections and set the rules on the first to pull data from the second based
[quoted text clipped - 10 lines]
> >
> > Is this possible? If so, please explain how?

Reply to this Message

krause73 - 17 May 2007 19:16 GMT

Thanks for the reply Art. My response echoes pocceygirl.

My main connection was to db1.Table1. IP gave me data fields and query
fields. The secondary connection was to db2.Table1. IP only gave me
data fields.

Since I now had both connections established, I went back to the main
connection and added my Join to the SQL statement. When I save it, I
get a warning that IP can't be represented in a tree view anymore. And
then another warning telling me that the query might not be safe.
After I finish the whole process, I lose my query fields for the main
connection.

What do you mean by "create a new table with the joins"?

Thanks.

Reply to this Message