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

Qualify Object names - cross database reference

tkdennistkdennis Posts: 114
edited June 23, 2010 9:45AM in SQL Refactor Previous Versions
When I have a query that references tables from two databases database, SQL Refactor is not able to find them. If I put a USE <other_database_name> statement at the top (but don't execute it), then it will qualify the names, and tell me it couldn't find the objects from the first database.
SELECT a_col1, b_col2
	FROM db1.dbo.table1 d1
	JOIN db2.dbo.table2 d2
		ON d1.a_col1 = d2.b_col2
becomes
SELECT a_col1, d2.b_col2
	FROM db1.dbo.table1 d1
	JOIN db2.dbo.table2 d2
		ON d1.a_col1 = d2.b_col2
but underlines db1.dbo.table1 as not identifiable

so I add the USE statement
USE db1
	SELECT a_col1, d2.b_col2
	FROM db1.dbo.table1 d1
	JOIN db2.dbo.table2 d2
		ON d1.a_col1 = d2.b_col2
and I now I get this
USE db1
	SELECT d1.a_col1, d2.b_col2
	FROM db1.dbo.table1 d1
	JOIN db2.dbo.table2 d2
		ON d1.a_col1 = d2.b_col2
and it underlines db2.dbo.table2 as not identifiable.

I was in db2 with a USE db2 statement at the top of the script when I first tried it. I got the same result trying to qualify the script in a new window in a 3rd unrelated database without any USE statements - db2 was qualified and db1 was not identified.

Thanks,
Traci
SQL Refactor 1.5.1.61

Comments

  • Options
    Anu DAnu D Posts: 876 Silver 3
    Thanks for your post.

    I am trying to replicate the issue and I will get back to you on this soon!
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: support@red-gate.com
  • Options
    Anu DAnu D Posts: 876 Silver 3
    Many thanks for your post and apologies for delay in replying you.

    I have added your request in our internal tracking system as a feature request whose tracking id is SR-892.

    I will update you as soon as this feature is incorporated.

    Thanks for your feedback and your patience.
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: support@red-gate.com
Sign In or Register to comment.