OPENQUERY Issue
a.higgins
Posts: 90 Bronze 2
A coworker of mine came to me with a strange SQL Prompt issue this morning. In a very specific set of circumstances, SQL Prompt will remove columns from a SELECT list.
Here's the scenario as I see it:
1. Data is pulled out of OPENQUERY into a temp table, using the SELECT INTO syntax (rather than the CREATE TABLE syntax).
2. A query is written against the temp table and at least one other table, using SELECT *.
3. Formatting the query removes all temp table columns from the SELECT statement.
Here's the repro script:
When I run this query, SomeColumn is included in the output. However, when I expand the wildcard within the SELECT *, SomeColumn is removed from the result set.
Interestingly, if I remove the join to sys.schemas and apply formatting, it doesn't expand the wildcard:
This bug derailed our efforts to get all users to apply consistent code formatting, since it silently removed output columns from the result set. I've been campaigning over the last few months for our team to adopt SQL Prompt, and I'm concerned that this might be a significant roadblock for us.
Here's what I'd like to know:
1. Is there a way for SQL Prompt to correctly identify the columns / data types / whatever from the OPENQUERY syntax?
2. Failing that, can SQL Prompt abort the wildcard expansion when it fails to identify columns from one or more tables (similar to the way it did in the isolated #TempTable select above)?
Here's the scenario as I see it:
1. Data is pulled out of OPENQUERY into a temp table, using the SELECT INTO syntax (rather than the CREATE TABLE syntax).
2. A query is written against the temp table and at least one other table, using SELECT *.
3. Formatting the query removes all temp table columns from the SELECT statement.
Here's the repro script:
SELECT SomeColumn INTO #TempTable FROM OPENQUERY ([MyServerMyInstance], 'SELECT 1 AS SomeColumn'); SELECT * FROM #TempTable t INNER JOIN sys.schemas s ON s.name = 'dbo' DROP TABLE #TempTable
When I run this query, SomeColumn is included in the output. However, when I expand the wildcard within the SELECT *, SomeColumn is removed from the result set.
SELECT SomeColumn INTO #TempTable FROM OPENQUERY ([MyServerMyInstance], 'SELECT 1 AS SomeColumn'); SELECT s.name ,s.schema_id ,s.principal_id FROM #TempTable t INNER JOIN sys.schemas s ON s.name = 'dbo' DROP TABLE #TempTable
Interestingly, if I remove the join to sys.schemas and apply formatting, it doesn't expand the wildcard:
SELECT SomeColumn INTO #TempTable FROM OPENQUERY ([MyServerMyInstance], 'SELECT 1 AS SomeColumn'); SELECT * FROM #TempTable t DROP TABLE #TempTable
This bug derailed our efforts to get all users to apply consistent code formatting, since it silently removed output columns from the result set. I've been campaigning over the last few months for our team to adopt SQL Prompt, and I'm concerned that this might be a significant roadblock for us.
Here's what I'd like to know:
1. Is there a way for SQL Prompt to correctly identify the columns / data types / whatever from the OPENQUERY syntax?
2. Failing that, can SQL Prompt abort the wildcard expansion when it fails to identify columns from one or more tables (similar to the way it did in the isolated #TempTable select above)?
Comments
Sorry you're having problems with the expand wildcard feature.
SQL Prompt doesn't populate its column list with results from OPENQUERY which is why those columns don't get inserted when expanding the wildcard. However SQL Prompt can retrieve columns from tables on linked servers when specified with the 4 part name:
For this to work "SQL Prompt > Options > Suggestions > Connections > Load suggestions for linked servers" must be on.
Currently SQL Prompt doesn't expand wildcards when it thinks there are no columns. Your suggestion to not expand the wildcard if Prompt fails to find columns in any of the tables in a JOIN seems sensible to me.
I hope we can solve this for you soon. Until then it's possible to stop Format SQL expanding wildcards by unchecking SQL Prompt > Options > Format > Actions > Expand wildcards. Expanding wildcards can be done separately from the SQL Prompt menu (keyboard shortcut: Ctrl+B, Ctrl+W) and will be limited to highlighted code if there is any.
Many thanks
Harry
In this private build (http://download.red-gate.com/EAP/SQLPro ... .0.304.exe) wildcards shouldn't expand if SQL Prompt doesn't detect columns in one of the tables in the FROM clause. This will prevent the wildcard expanding in your example. I hope this is a suitable solution for you.
Regarding OPENQUERY - please could you add a request for identifying columns from OPENQUERY to our UserVoice page: https://redgate.uservoice.com/forums/94413-sql-prompt. We use it to help us decide what to work on next.
Let us know if you have any further problems.
Many thanks
Harry
As you suggested, I've created a separate UserVoice request for this feature at https://redgate.uservoice.com/forums/94413-sql-prompt/suggestions/17814271-discover-metadata-for-openquery.