join two databases in a query

[krause73]

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?

 

[Art]

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

 

[pocceygirl]

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.

 

[krause73]

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.