What are the challenges you face when working across database platforms? Take the survey
Options

doesn't work across databases?

justevaluatingjustevaluating Posts: 6
edited July 18, 2007 5:54PM in SQL Prompt Previous Versions
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

Comments

  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    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
    Principal Consultant
    bartread.com Ltd
  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    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
    Bart Read
    Principal Consultant
    bartread.com Ltd
  • Options
    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
  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    OK thanks. Have you tried forcing the popup with CTRL+SPACE after "[Production]."? What does it show?
    Bart Read
    Principal Consultant
    bartread.com Ltd
  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    Forgot to say. You should see owners/users: so e.g. "dbo" should be on the list.
    Bart Read
    Principal Consultant
    bartread.com Ltd
  • Options
    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.
  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    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
    Bart Read
    Principal Consultant
    bartread.com Ltd
  • Options
    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)
  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    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
    Bart Read
    Principal Consultant
    bartread.com Ltd
  • Options
    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.
  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    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
    Bart Read
    Principal Consultant
    bartread.com Ltd
Sign In or Register to comment.