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

Expanding SELECT * across databases - incorrect prefix added

moffmoff Posts: 34
edited July 31, 2007 7:11AM in SQL Prompt Previous Versions
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

Comments

  • Options
    just to add to this, if I fully qualify the first table with database and owner, the problem still persists
  • Options
    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.
    Jason Crease
    Red Gate Software
  • Options
    Thanks for the reply.

    I've installed 3.5.0.333 and can't reproduce the problem now.
Sign In or Register to comment.