OPENQUERY Issue

a.higgins

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:

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)?

Harry Frankish

Hi a.higgins

Sorry you're having problems with the expand wildcard feature.

a.higgins wrote:

Is there a way for SQL Prompt to correctly identify the columns / data types / whatever from the OPENQUERY syntax?

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:

SELECT SomeColumn
INTO #TempTable
FROM LinkedServer.Database.Schema.Table

For this to work "SQL Prompt > Options > Suggestions > Connections > Load suggestions for linked servers" must be on.

a.higgins wrote:

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)?

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

Harry Frankish

Hi a.higgins

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

a.higgins

That works quite well, thank you!

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.