Wits end with Cross Connection Queries

Toad Data Point
1

Hi, I am at my wits end. I created and worked on a Cross Connection Query yesterday. The Query Joins tables across Three SQL Servers, and Two different Excel Workbooks. All Connections where working yesterday and the query was returning results. I saved and closed for the the day.

This morning I went back to my query to complete it. Now Toad Data Point acts as though it is not a Cross Connection Query, and returns errors that it cannot connect to my data sources. Any attempt to create a new Cross Connection Query are thwarted the system will not allow me to create one, edit one, or run one.

This is extremely frustrating. I have a report project due today and need to get this completed.
What am I missing here?

Thanks,
Dan

2

Hi Dan,
which version is it? Can we see the error, did you take a screenshot?

3

Unable to connect to any of the specified Data Service hosts version 5.2

4

image
image992×252 17.8 KB

5

This is only because this was the last connection I started.

6

Could not find server 'CARE' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

7

We fixed an error when QB did not recognize a cross query connection for 5.3. Is your build version 5.2.1? Could you try a newer version?

8

I am. I have been trying to download the 5.3 version. Whenever i attempt to I cannot find the actual program. I only see .pdf file for install and version info. The page is very confusing to me and I cannot seem to find the simple .exe file for the new version.

BTW... I fixes this be renaming files to 'old' and rebuilding, It is once again working.

9

There is a green button Add to downloads here.. After you sign in with your Quest account you can download it.

10

Just because you can do a cross connection query, does not always mean you should. Personally I have found the performance to be terrible, so bad that while the cross connection query was running I could write a new query that pulls the data into a temp table from one server on to the main server, index it and then join that tempt table to the main query and get results while the cross connection query was still running. I'm not saying you should never use a cross connection query, just that you might be better off structuring your query in a way that maximizes performance. Iin general if you are cross connecting to get dimension data try loading the dimension into a temp table. If you are cross connecting fact tables, you might not have any choice when the table are too large.

1 Like