Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

sp_BlitzIndex - Duplicate columns in Insert statement when writing to an existing table #1988

Closed
alchemistmatt opened this Issue Feb 27, 2019 · 4 comments

Comments

Projects
None yet
2 participants
@alchemistmatt
Copy link
Contributor

alchemistmatt commented Feb 27, 2019

Version of the script
Version 7.3
VersionDate 20190219

What is the current behavior?
Running with @outputToTables=1 leads to error

Msg 50000, Level 15, State 1, Procedure sp_BlitzIndex, Line 4901 [Batch Start Line 8]
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

I have found the issue: Drop_Tsql and Create_Tsql are included twice in the Insert list. See:

If the current behavior is a bug, please provide the steps to reproduce.

EXEC RunBlitzChecks 
    @overview = 0,
    @runBlitz = 1,
    @runBlitzCache = 1,
    @runBlitzIndex = 1,
    @blitzIndexDatabaseList= 'dba',
    @outputToTables=1, 
    @outputDatabaseName='dba'

What is the expected behavior?
No Error.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
Tested with Microsoft SQL Server 2014 (SP2); worked 2 months ago. Tried to fix last month; I suspect a merge mangled things.

@BrentOzar

This comment has been minimized.

Copy link
Member

BrentOzar commented Feb 28, 2019

Makes sense! Not sure what RunBlitzChecks is though. :-D

@alchemistmatt

This comment has been minimized.

Copy link
Contributor Author

alchemistmatt commented Feb 28, 2019

Oh, right, that's my wrapper procedure that calls several first responder stored procedures.

@alchemistmatt

This comment has been minimized.

Copy link
Contributor Author

alchemistmatt commented Feb 28, 2019

Corrected steps to reproduce


EXEC sp_BlitzIndex @GetAllDatabases = 1, @Mode=2,
                    @OutputDatabaseName = 'dba', 
                    @OutputSchemaName = 'dbo',
                    @OutputTableName = 'T_BlitzIndex_Results',
                    @BringThePain = 0

alchemistmatt added a commit to PNNL-Comp-Mass-Spec/SQL-Server-First-Responder-Kit that referenced this issue Feb 28, 2019

BrentOzarULTD#1988 sp_BlitzIndex Update the column order of the INSER…
…T statement and SELECT items to match the CREATE TABLE statement

Move columns Drop_Tsql and Create_Tsql to after index_name for consistency with the CREATE TABLE statement

BrentOzar added a commit that referenced this issue Mar 19, 2019

#1988 sp_BlitzIndex INSERT column order bug (#1989)
* #1952 sp_BlitzIndex Fix INSERT query column order

* #1988 sp_BlitzIndex Update the column order of the INSERT statement and SELECT items to match the CREATE TABLE statement

Move columns Drop_Tsql and Create_Tsql to after index_name for consistency with the CREATE TABLE statement
@BrentOzar

This comment has been minimized.

Copy link
Member

BrentOzar commented Mar 19, 2019

Good job! Reviewed & tested, and merged in for the March release. I'll credit you in the release notes. Thanks, and have a good week!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.