doesn't work across databases?

justevaluating

SQL Server 2000

If I'm in query analyzer and the selected db is db1, I can't qualify the table as [db2].tablename and get the intellisense?

Attempting to write a query that does the following...

select a.field1, b.field5
from [db1].table1 a LEFT JOIN [db2].table1 b ON a.id = b.id

Am I missing something?
Thanks

Bart Read

Does the popup appear at all? If not you may need to restart the SQL Prompt tray app and then Query Analyzer. There is a bug in the installer that can cause QA support to be disabled until the tray app is manually restarted even though the installer will already have restarted it.

Is this your problem. If not please let me know.


Thanks,
Bart

Bart Read

The other thing my colleague Tanya pointed out is that your SQL is invalid. If you're working cross-database you need to qualify using three part naming.

So, your example should read:

select a.field1, b.field5
from [db1].dbo.table1 a LEFT JOIN [db2].dbo.table1 b ON a.id = b.id

(assuming your tables belong to dbo)

or you could do the following:

select a.field1, b.field5
from [db1]..table1 a LEFT JOIN [db2]..table1 b ON a.id = b.id

Hope that helps.


Thanks,
Bart

justevaluating

The popup appears, but it is empty.
(restarted SQL Prompt)
if I launch Query Analyzer I'm by default in 'master'.

If I then attempt to select from my db called "production", I can't.

select *
from [production].
- no intellisense

Bart Read

OK thanks. Have you tried forcing the popup with CTRL+SPACE after "[Production]."? What does it show?

Bart Read

Forgot to say. You should see owners/users: so e.g. "dbo" should be on the list.

justevaluating

Bart Read wrote:

OK thanks. Have you tried forcing the popup with CTRL+SPACE after "[Production]."? What does it show?

@identity". Nothing from the Production db.

Also tried "[Production].dbo." (CTRL+SPACE). Same thing.

This product is *exactly* what our development group needs, but we do need to run queries across dbs. We actually have 3 separate dbs (on the same server) that we need to hit on a regular basis, so the ability to get intellisense working would be great.

Thanks for your help. I hope I am just overlooking something here.

Bart Read

OK, something to check now. In your editor please click on the SQL Prompt > Options menu item. Then go to the "Listed Candidates" tab and then make sure you're on the "Cross-Database Support" screen. Then make sure "Enable cross-database and linked server support" is enabled.
Thanks,
Bart

justevaluating

Bart Read wrote:

OK, something to check now. In your editor please click on the SQL Prompt > Options menu item. Then go to the "Listed Candidates" tab and then make sure you're on the "Cross-Database Support" screen. Then make sure "Enable cross-database and linked server support" is enabled.
Thanks,
Bart

Ah! I don't have a "Cross-Database Support" screen or "Enable cross-database and linked server support" checkbox. Could this be a version issue or a 14-day trial thing? I'm currently running the 14-day trial (v. 3.1.0.40)

Bart Read

Well, it's simple to fix: you're using version 3.1 which I'm guessing you downloaded from the main website. You need to download the 3.5 release candidate. The FTP link is in this post:

http://www.red-gate.com/messageboard/vi ... php?t=5263

Hopefully that should sort you out as the installer will do an upgrade, but if you're using Query Analyzer you should manually restart the tray app after the installer has run since there appears to be a bug wherein the QA integration doesn't work until this has happened even though the installer automatically restarts the tray app.

Hope that helps.


Thanks,
Bart

justevaluating

Thanks Bart!
If this works as great as the rest of it does, I'll be purchasing between 5 - 10 licenses. How does this work with versions? If we purchase licenses now, are we purchasing licenses for 3.1? Will we have to pay to upgrade to 3.5 when it is finally released?

Thanks.

Bart Read

No problem! I'm just glad we (hopefully) got it sorted out, I was really baffled there for a while.

As far as licensing goes what you'll be purchasing are licenses for 3.x versions, which means that all 3.x upgrades will be free. However to upgrade to 4.x you'd have to pay unless you also purchased support and upgrades, which I think works on an annual subscription basis but you'd be best off talking to sales about that. Bottom line is you can purchase 3.1 licenses and you don't have to pay to upgrade to 3.5, and you can activate the 3.5 RC with the 3.1 license as soon as you've purchased them.

If you're buying multiple licenses we offer discounts on 5 and 10 user bundles. I'm not sure of the exact discount levels but if you contact sales@red-gate.com they'd be able to answer any questions much more accurately than I can.

If you've got any more technical problems then I'm on my home turf again and please do come back to me here.

Finally, thanks for the positive comments! They're much appreciated by the team as we've all worked really hard on this over the past few months.


Cheers,
Bart