How can we help you today? How can we help you today?

Expanding SELECT * across databases - incorrect prefix added

V3.5.0.323

With a query joining across two databases and only one of the tables qualified, the other taking the current database, when the SELECT * of the table in the current database is expanded it results in incorrect syntax:

This: 
SELECT 	wpam.*
FROM	WebPageAutoMerchandise AS wpam
		INNER JOIN 
		store.dbo.Product AS p
		ON wpam.LineSku = p.sku
with the cursor to the right of the * and tab pressed, expands to: 
SELECT 	dbo.WPAM.WebPageAutoMerchandiseID,
		   dbo.WPAM.WebPageFeaturedProductID,
		   dbo.WPAM.HostPageType,
		   dbo.WPAM.HostPageID,
		   dbo.WPAM.Rank,
		   dbo.WPAM.LineSkuType,
		   dbo.WPAM.LineSku
FROM	WebPageAutoMerchandise AS wpam
		INNER JOIN 
		store.dbo.Product AS p
		ON wpam.LineSku = p.sku
which is invalid - it's taking the table alias as the schema and the schema as the DB
Msg 107, Level 16, State 3, Line 1
The column prefix 'dbo.WPAM' does not match with a table name or alias name used in the query.

I tried recaching, but got the same result.

Unfortunately I can't reproduce this in a dummy environment - this works fine: 
USE DummyDB
go
CREATE TABLE TableA (col1 INT, col2 INT)

USE DummyDB2
go
CREATE TABLE TableB (col3 INT, col2 INT)

USE DummyDB
go

INSERT INTO TableA VALUES (1,2)
INSERT INTO TableA VALUES (1,3)

INSERT INTO DummyDB2.dbo.TableB VALUES (3,2)
INSERT INTO DummyDB2.dbo.TableB VALUES (5,3)[/color]


SELECT 	ta.*
FROM	TableA AS ta
		INNER JOIN 
		DummyDB2.dbo.TableB AS tb
		ON ta.col2 = tb.col2
moff
0

Comments

3 comments

Sort by
  • moff
    just to add to this, if I fully qualify the first table with database and owner, the problem still persists
    moff
    0
  • JasonC
    Hi there. I've tried but I can't repoduce this bug.

    The version you have is 3.5.0.323, the RC. We have just released the final version 3.5.0.333. It has a few fixes in cross DB queries. Try downloading it and seeing if this fixes the problem.

    Thanks for the bug report.
    JasonC
    0
  • moff
    Thanks for the reply.

    I've installed 3.5.0.333 and can't reproduce the problem now.
    moff
    0

Add comment

Please sign in to leave a comment.