I've looked into this and it is a bug in the software. I've notified the developers and they are hopefully going to fix this in a future version of SQL Prompt.
Great, thank you. I'm surprised no one else has noticed or commented about this. Unfortunately this issue is really preventing me from getting my whole team to use this product, which we purchased in advance of it being ready. The sooner, the better.
This will be fixed in the 3.5 final release. We now honour the value of the "Qualify object names" option when inserting JOIN conditions exception where columns in another database are referenced. The following SQL illustrates this:
-- Note that these examples are for illustrative purposes
-- only. They use AdventureWorks 2005 and won't execute because
-- you have to qualify object names if their owner is not
-- your default schema.
USE [master]
GO
-- Working cross-database with qualify object names disabled
SELECT * FROM
[AdventureWorks]..[Product] INNER JOIN [AdventureWorks]..[BillOfMaterials]
ON [AdventureWorks].[Production].[Product].[ProductID] = [AdventureWorks].[Production].[BillOfMaterials].[ComponentID]
GO
USE [AdventureWorks]
GO
-- With qualify object names disabled
SELECT * FROM
[Product] INNER JOIN [BillOfMaterials]
ON [Product].[ProductID] = [BillOfMaterials].[ComponentID]
-- With qualify object names enabled
SELECT * FROM
[Production].[Product] INNER JOIN [Production].[BillOfMaterials]
ON [AdventureWorks].[Production].[Product].[ProductID] = [AdventureWorks].[Production].[BillOfMaterials].[ComponentID]
Comments
Hope that helps.
Thanks,
Bart
Principal Consultant
bartread.com Ltd