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

BlitzIndex output to table failing #1952

Closed
arampendley opened this issue Feb 8, 2019 · 2 comments
Closed

BlitzIndex output to table failing #1952

arampendley opened this issue Feb 8, 2019 · 2 comments

Comments

@arampendley
Copy link
Contributor

Version of the script
SET @Version = '7.2';
SET @VersionDate = '20190128';

What is the current behavior?
Output to table fails.

Starting run. sp_BlitzIndex(TM) v7.2 - January 28, 2019
Create temp tables.
Adding UQ index on #IndexSanity (database_id, object_id, index_id)
Inserting data into #IndexColumns for clustered indexes and heaps
Inserting data into #IndexColumns for nonclustered indexes
Inserting data into #IndexSanity
Checking partition count
Preferring non-2012 syntax with LEFT JOIN to sys.dm_db_index_operational_stats
Inserting data into #IndexPartitionSanity
Inserting data into #MissingIndexes
Inserting data into #ForeignKeys
Gathering Computed Column Info.
Gathering Trace Flag Information
Gathering Temporal Table Info
Updating #IndexSanity.key_column_names
Updating #IndexSanity.partition_key_column_name
Updating #IndexSanity.key_column_names_with_sort_order
Updating #IndexSanity.key_column_names_with_sort_order_no_types (for create tsql)
Updating #IndexSanity.include_column_names
Updating #IndexSanity.include_column_names_no_types (for create tsql)
Updating #IndexSanity.count_key_columns and count_include_columns
Updating index_sanity_id on #IndexPartitionSanity
Inserting data into #IndexSanitySize
Warning: Null value is eliminated by an aggregate or other SET operation.
Determining index usefulness
Updating #IndexSanity.referenced_by_foreign_key
Update index_secret on #IndexSanity for NC indexes.
Update index_secret on #IndexSanity for heaps and non-unique clustered.
Populate #IndexCreateTsql.
Populate #PartitionCompressionInfo.
Update #PartitionCompressionInfo.
Update #IndexSanity for filtered indexes with columns not in the index definition.
@mode=2, here's the details on existing indexes.
Failure analyzing temp tables.
Msg 50000, Level 16, State 3, Procedure dbo.sp_BlitzIndex, Line 4892 [Batch Start Line 2]
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

If the current behavior is a bug, please provide the steps to reproduce.
DECLARE @DatabaseName NVARCHAR(128) = N'MYDATABASE'
DECLARE @mode TINYINT = 2
DECLARE @OutputDatabaseName NVARCHAR(256) = N'master'
DECLARE @OutputSchemaName NVARCHAR(256) = N'dbo'
DECLARE @OutputTableName NVARCHAR(256) = N'BlitzIndex'

EXECUTE [dbo].[sp_BlitzIndex]
@DatabaseName = @DatabaseName
,@mode = @mode
,@OutputDatabaseName = @OutputDatabaseName
,@OutputSchemaName = @OutputSchemaName
,@OutputTableName = @OutputTableName

What is the expected behavior?
Output to table with no errors.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
SQL Server 2017 CU12. Yes, worked in previous version.

@alchemistmatt
Copy link
Contributor

I am also seeing this on Microsoft SQL Server 2014; it was working last month. Unfortunately, the reported line number (4892) isn't helpful.

@alchemistmatt
Copy link
Contributor

Digging into this I found the issue: the [Drop_Tsql] and [Create_Tsql] columns are not in the correct position for the INSERT query at


They are currently between [index_name] and [index_id] but they need to be between [more_info] and [display_order].

I'll look into making a Pull Request.

alchemistmatt added a commit to PNNL-Comp-Mass-Spec/SQL-Server-First-Responder-Kit that referenced this issue Feb 9, 2019
BrentOzar added a commit that referenced this issue Feb 19, 2019
* Updating readme.md for sp_DatabaseRestore

Documenting @ExistingDBAction for https://dba.stackexchange.com/questions/226145/sp-databaserestore-msg-50000.

* 1900 sp_BlitzIndex add histograms

When @TableName is specified and sys.dm_db_stats_histogram is available. Closes #1900.

* #1903 sp_Blitz SQLServerCheckup

Adding filter for that app name. Closes #1903.

* #1905 sp_BlitzIndex remove BOU link

Nothing against BOU, just don't need it in that particular place. Closes #1905.

* #1908 Update copyright dates

Ah, the glamour. Closes #1908.

* Issue #1904 Change RAISERROR 'severity' for that should trigger throw and error.

* Issue #1910 Add SQL Server version check before choosing 'memory grant' as the @BlitzCacheSortOrder.

* Issue #1910 Add temp table creation.

* #1914 sp_BlitzIndex partition error severity

Dropping severity level from 16 to 0 since we're logging it in the result set anyway. Closes #1914.

* Issue 1894 Moved RESTORE HEADERONLY up

* LF line endings

* added some extra checks + corrected some nesting

* rebase and line endings

* Web site commit

Does this fix line endings?

* #1916 sp_Blitz ignore backup on TempDB drive

Closes #1916.

* Auto line endings

Dealing with sp_DatabaseRestore's line endings.

* 1919 sp_Blitz ignoring a few checks for Managed Instances (#1920)

* 1919 sp_Blitz ignoring a few checks for Managed Instances

Working on #1919.

* #1919 sp_Blitz excluding restored databases

When checking backup history. Working on #1919.

* #1919 sp_Blitz ignore some checks on Mgd Instances

Changing RCSI wording on Azure SQL DB. Working on #1919.

* #1921 Sp_BlitzIndex Add Drop and Create Columns to Output (#1923)

Joined output query to #IndexCreateTsql to retrieve the CreateTsql already generated earlier in the script.  Created Drop TSQL based off evaluating what type of index was present.

* #1921 sp_BlitzIndex adding drops (#1924)

Moving drop and create TSQL to the end of mode 2's results. Closes #1921.

* #1925 sp_Blitz AWS RDS detection (#1926)

Don't just rely on EC2 VM name to detect RDS. Also adds a new result noting that checks were skipped. Closes #1925.

* #1927 sp_BlitzIndex skip rdsadmin db (#1928)

GetAllDatabases = 1 fails when it hits rdsadmin because they're referring to the resource db. Closes #1927.

* 2019_01 Release (#1931)

Prep work for the release - changing version numbers, building build scripts.

* #1933 sp_BlitzIndez Azure SQL DB (#1934)

Check for DaysUptime is null. Closes #1933.

* 1936 Deprecate Power BI Dashboard (#1937)

Closes #1936.

* sp_BlitzFirst - Decimals - Fixes #1940 (#1941)

* batch requests & waits/core/sec are now decimals

* also updated final data set for consistency

* 1950 sp blitz lock azure sql db hyperscale (#1951)

* #1950 sp_BlitzLock Azure SQL DB Hyperscale

Fixes #1950.

* #1950 sp_BlitzLock Azure SQL DB Hyperscale

Oops, forgot my semicolon. Fixes #1950.

* #1947 sp_BlitzIndex stats for current db (#1955)

Can only get stats if you're in the database you're analyzing. Closes #1947.

* #1952 sp_BlitzIndex Fix INSERT query column order (#1954)

* remove table name from create index script (#1942)

* Add Version Checking  for all SP (#1949)

* Update sp_BlitzCache.sql

DECLARE @BlitzCacheVersion     VARCHAR(30);
DECLARE @BlitzCacheVersionDate DATETIME2;
EXEC dbo.sp_BlitzCache @VersionCheckMode = 1, @Version = @BlitzCacheVersion OUTPUT, @VersionDate = @BlitzCacheVersionDate OUTPUT ;
SELECT @BlitzCacheVersion as BlitzCacheVersion , @BlitzCacheVersionDate as BlitzCacheVersionDate

* Update sp_BlitzFirst.sql

* Update sp_BlitzIndex.sql

* Update sp_BlitzLock.sql

* Update sp_BlitzQueryStore.sql

* Update sp_BlitzWho.sql

* Update sp_DatabaseRestore.sql

* Update sp_BlitzInMemoryOLTP.sql

* Update sp_foreachdb.sql

* Update sp_ineachdb.sql

* Update sp_BlitzBackups.sql

* Update sp_Blitz.sql

* Update sp_AllNightLog_Setup.sql

* Update sp_AllNightLog.sql

* Update sp_BlitzInMemoryOLTP.sql

* Update sp_BlitzLock.sql

* sp_ineachdb version check

Tweaking #1948 so it works. Closes #1948.

* Update sp_BlitzCache.sql (#1953)

Complete the join between #missing_index_detail and ##bou_BlitzCacheProcs as otherwise on servers running jobs with many steps, each missing indices will cause #missing_index_pretty to grow exponentially, causing sp_BlitzCache to run for hours.

* Fixes #1943 - sp_Blitz check if SSIS/SSRS/SSAS are running (#1957)

* Update sp_Blitz.sql

* Update sp_Blitz.sql

* Update sp_Blitz Checks by Priority.md

* Update sp_Blitz Checks by Priority.md

* Update sp_Blitz Checks by Priority.md

* altered search strings

because *of course* there's white space. shouldn't hurt too badly because there aren't many services running on sql boxes (hopefully)

* #1943 adding URL (#1960)

For new SSAS/IS/RS check. Closes #1943.

* sp_BlitzCache additional plural checks for @sortorder (#1963)

* additional plural checks

added in plural checks, also raised error to 16 when invalid sort order is chosen.

* removed unnecessary checks

* minor typo (#1965)

* #1967 sp_Blitz updating supported versions (#1969)

SQL 2012 pre-SP4 is out of support. Closes #1967.

* #1935 sp_BlitzCache Azure compatibility (#1972)

Azure SQL DB compatibility and removing bou from global temp table names. Closes #1935.

* #1973 sp_BlitzIndex truncation error (#1974)

Fixes #1973.

* #1971 add POOL_LOG_RATE_GOVERNOR as poison (#1975)

Closes #1971.

* #1966 fix sp_Blitz check IDs (#1976)

203 and 224 were swapped in the documentation. Closes #1966.

* 20190219 bumping version numbers

To prep for imminent release...

* 20190219 release

Updating install-all-scripts with new versions.
BrentOzar added a commit that referenced this issue Feb 19, 2019
* Updating readme.md for sp_DatabaseRestore

Documenting @ExistingDBAction for https://dba.stackexchange.com/questions/226145/sp-databaserestore-msg-50000.

* 1900 sp_BlitzIndex add histograms

When @TableName is specified and sys.dm_db_stats_histogram is available. Closes #1900.

* #1903 sp_Blitz SQLServerCheckup

Adding filter for that app name. Closes #1903.

* #1905 sp_BlitzIndex remove BOU link

Nothing against BOU, just don't need it in that particular place. Closes #1905.

* #1908 Update copyright dates

Ah, the glamour. Closes #1908.

* Issue #1904 Change RAISERROR 'severity' for that should trigger throw and error.

* Issue #1910 Add SQL Server version check before choosing 'memory grant' as the @BlitzCacheSortOrder.

* Issue #1910 Add temp table creation.

* #1914 sp_BlitzIndex partition error severity

Dropping severity level from 16 to 0 since we're logging it in the result set anyway. Closes #1914.

* Issue 1894 Moved RESTORE HEADERONLY up

* LF line endings

* added some extra checks + corrected some nesting

* rebase and line endings

* Web site commit

Does this fix line endings?

* #1916 sp_Blitz ignore backup on TempDB drive

Closes #1916.

* Auto line endings

Dealing with sp_DatabaseRestore's line endings.

* 1919 sp_Blitz ignoring a few checks for Managed Instances (#1920)

* 1919 sp_Blitz ignoring a few checks for Managed Instances

Working on #1919.

* #1919 sp_Blitz excluding restored databases

When checking backup history. Working on #1919.

* #1919 sp_Blitz ignore some checks on Mgd Instances

Changing RCSI wording on Azure SQL DB. Working on #1919.

* #1921 Sp_BlitzIndex Add Drop and Create Columns to Output (#1923)

Joined output query to #IndexCreateTsql to retrieve the CreateTsql already generated earlier in the script.  Created Drop TSQL based off evaluating what type of index was present.

* #1921 sp_BlitzIndex adding drops (#1924)

Moving drop and create TSQL to the end of mode 2's results. Closes #1921.

* #1925 sp_Blitz AWS RDS detection (#1926)

Don't just rely on EC2 VM name to detect RDS. Also adds a new result noting that checks were skipped. Closes #1925.

* #1927 sp_BlitzIndex skip rdsadmin db (#1928)

GetAllDatabases = 1 fails when it hits rdsadmin because they're referring to the resource db. Closes #1927.

* 2019_01 Release (#1931)

Prep work for the release - changing version numbers, building build scripts.

* #1933 sp_BlitzIndez Azure SQL DB (#1934)

Check for DaysUptime is null. Closes #1933.

* 1936 Deprecate Power BI Dashboard (#1937)

Closes #1936.

* sp_BlitzFirst - Decimals - Fixes #1940 (#1941)

* batch requests & waits/core/sec are now decimals

* also updated final data set for consistency

* 1950 sp blitz lock azure sql db hyperscale (#1951)

* #1950 sp_BlitzLock Azure SQL DB Hyperscale

Fixes #1950.

* #1950 sp_BlitzLock Azure SQL DB Hyperscale

Oops, forgot my semicolon. Fixes #1950.

* #1947 sp_BlitzIndex stats for current db (#1955)

Can only get stats if you're in the database you're analyzing. Closes #1947.

* #1952 sp_BlitzIndex Fix INSERT query column order (#1954)

* remove table name from create index script (#1942)

* Add Version Checking  for all SP (#1949)

* Update sp_BlitzCache.sql

DECLARE @BlitzCacheVersion     VARCHAR(30);
DECLARE @BlitzCacheVersionDate DATETIME2;
EXEC dbo.sp_BlitzCache @VersionCheckMode = 1, @Version = @BlitzCacheVersion OUTPUT, @VersionDate = @BlitzCacheVersionDate OUTPUT ;
SELECT @BlitzCacheVersion as BlitzCacheVersion , @BlitzCacheVersionDate as BlitzCacheVersionDate

* Update sp_BlitzFirst.sql

* Update sp_BlitzIndex.sql

* Update sp_BlitzLock.sql

* Update sp_BlitzQueryStore.sql

* Update sp_BlitzWho.sql

* Update sp_DatabaseRestore.sql

* Update sp_BlitzInMemoryOLTP.sql

* Update sp_foreachdb.sql

* Update sp_ineachdb.sql

* Update sp_BlitzBackups.sql

* Update sp_Blitz.sql

* Update sp_AllNightLog_Setup.sql

* Update sp_AllNightLog.sql

* Update sp_BlitzInMemoryOLTP.sql

* Update sp_BlitzLock.sql

* sp_ineachdb version check

Tweaking #1948 so it works. Closes #1948.

* Update sp_BlitzCache.sql (#1953)

Complete the join between #missing_index_detail and ##bou_BlitzCacheProcs as otherwise on servers running jobs with many steps, each missing indices will cause #missing_index_pretty to grow exponentially, causing sp_BlitzCache to run for hours.

* Fixes #1943 - sp_Blitz check if SSIS/SSRS/SSAS are running (#1957)

* Update sp_Blitz.sql

* Update sp_Blitz.sql

* Update sp_Blitz Checks by Priority.md

* Update sp_Blitz Checks by Priority.md

* Update sp_Blitz Checks by Priority.md

* altered search strings

because *of course* there's white space. shouldn't hurt too badly because there aren't many services running on sql boxes (hopefully)

* #1943 adding URL (#1960)

For new SSAS/IS/RS check. Closes #1943.

* sp_BlitzCache additional plural checks for @sortorder (#1963)

* additional plural checks

added in plural checks, also raised error to 16 when invalid sort order is chosen.

* removed unnecessary checks

* minor typo (#1965)

* #1967 sp_Blitz updating supported versions (#1969)

SQL 2012 pre-SP4 is out of support. Closes #1967.

* #1935 sp_BlitzCache Azure compatibility (#1972)

Azure SQL DB compatibility and removing bou from global temp table names. Closes #1935.

* #1973 sp_BlitzIndex truncation error (#1974)

Fixes #1973.

* #1971 add POOL_LOG_RATE_GOVERNOR as poison (#1975)

Closes #1971.

* #1966 fix sp_Blitz check IDs (#1976)

203 and 224 were swapped in the documentation. Closes #1966.

* 20190219 bumping version numbers

To prep for imminent release...

* 20190219 release

Updating install-all-scripts with new versions.

* 2019_02 Release

Bumping version numbers & dates.

* Merge conflicts, good times

* Merge conflicts, whee
BrentOzar added a commit that referenced this issue Feb 19, 2019
* Updating readme.md for sp_DatabaseRestore

Documenting @ExistingDBAction for https://dba.stackexchange.com/questions/226145/sp-databaserestore-msg-50000.

* 1900 sp_BlitzIndex add histograms

When @TableName is specified and sys.dm_db_stats_histogram is available. Closes #1900.

* #1903 sp_Blitz SQLServerCheckup

Adding filter for that app name. Closes #1903.

* #1905 sp_BlitzIndex remove BOU link

Nothing against BOU, just don't need it in that particular place. Closes #1905.

* #1908 Update copyright dates

Ah, the glamour. Closes #1908.

* Issue #1904 Change RAISERROR 'severity' for that should trigger throw and error.

* Issue #1910 Add SQL Server version check before choosing 'memory grant' as the @BlitzCacheSortOrder.

* Issue #1910 Add temp table creation.

* #1914 sp_BlitzIndex partition error severity

Dropping severity level from 16 to 0 since we're logging it in the result set anyway. Closes #1914.

* Issue 1894 Moved RESTORE HEADERONLY up

* LF line endings

* added some extra checks + corrected some nesting

* rebase and line endings

* Web site commit

Does this fix line endings?

* #1916 sp_Blitz ignore backup on TempDB drive

Closes #1916.

* Auto line endings

Dealing with sp_DatabaseRestore's line endings.

* 1919 sp_Blitz ignoring a few checks for Managed Instances (#1920)

* 1919 sp_Blitz ignoring a few checks for Managed Instances

Working on #1919.

* #1919 sp_Blitz excluding restored databases

When checking backup history. Working on #1919.

* #1919 sp_Blitz ignore some checks on Mgd Instances

Changing RCSI wording on Azure SQL DB. Working on #1919.

* #1921 Sp_BlitzIndex Add Drop and Create Columns to Output (#1923)

Joined output query to #IndexCreateTsql to retrieve the CreateTsql already generated earlier in the script.  Created Drop TSQL based off evaluating what type of index was present.

* #1921 sp_BlitzIndex adding drops (#1924)

Moving drop and create TSQL to the end of mode 2's results. Closes #1921.

* #1925 sp_Blitz AWS RDS detection (#1926)

Don't just rely on EC2 VM name to detect RDS. Also adds a new result noting that checks were skipped. Closes #1925.

* #1927 sp_BlitzIndex skip rdsadmin db (#1928)

GetAllDatabases = 1 fails when it hits rdsadmin because they're referring to the resource db. Closes #1927.

* 2019_01 Release (#1931)

Prep work for the release - changing version numbers, building build scripts.

* #1933 sp_BlitzIndez Azure SQL DB (#1934)

Check for DaysUptime is null. Closes #1933.

* 1936 Deprecate Power BI Dashboard (#1937)

Closes #1936.

* sp_BlitzFirst - Decimals - Fixes #1940 (#1941)

* batch requests & waits/core/sec are now decimals

* also updated final data set for consistency

* 1950 sp blitz lock azure sql db hyperscale (#1951)

* #1950 sp_BlitzLock Azure SQL DB Hyperscale

Fixes #1950.

* #1950 sp_BlitzLock Azure SQL DB Hyperscale

Oops, forgot my semicolon. Fixes #1950.

* #1947 sp_BlitzIndex stats for current db (#1955)

Can only get stats if you're in the database you're analyzing. Closes #1947.

* #1952 sp_BlitzIndex Fix INSERT query column order (#1954)

* remove table name from create index script (#1942)

* Add Version Checking  for all SP (#1949)

* Update sp_BlitzCache.sql

DECLARE @BlitzCacheVersion     VARCHAR(30);
DECLARE @BlitzCacheVersionDate DATETIME2;
EXEC dbo.sp_BlitzCache @VersionCheckMode = 1, @Version = @BlitzCacheVersion OUTPUT, @VersionDate = @BlitzCacheVersionDate OUTPUT ;
SELECT @BlitzCacheVersion as BlitzCacheVersion , @BlitzCacheVersionDate as BlitzCacheVersionDate

* Update sp_BlitzFirst.sql

* Update sp_BlitzIndex.sql

* Update sp_BlitzLock.sql

* Update sp_BlitzQueryStore.sql

* Update sp_BlitzWho.sql

* Update sp_DatabaseRestore.sql

* Update sp_BlitzInMemoryOLTP.sql

* Update sp_foreachdb.sql

* Update sp_ineachdb.sql

* Update sp_BlitzBackups.sql

* Update sp_Blitz.sql

* Update sp_AllNightLog_Setup.sql

* Update sp_AllNightLog.sql

* Update sp_BlitzInMemoryOLTP.sql

* Update sp_BlitzLock.sql

* sp_ineachdb version check

Tweaking #1948 so it works. Closes #1948.

* Update sp_BlitzCache.sql (#1953)

Complete the join between #missing_index_detail and ##bou_BlitzCacheProcs as otherwise on servers running jobs with many steps, each missing indices will cause #missing_index_pretty to grow exponentially, causing sp_BlitzCache to run for hours.

* Fixes #1943 - sp_Blitz check if SSIS/SSRS/SSAS are running (#1957)

* Update sp_Blitz.sql

* Update sp_Blitz.sql

* Update sp_Blitz Checks by Priority.md

* Update sp_Blitz Checks by Priority.md

* Update sp_Blitz Checks by Priority.md

* altered search strings

because *of course* there's white space. shouldn't hurt too badly because there aren't many services running on sql boxes (hopefully)

* #1943 adding URL (#1960)

For new SSAS/IS/RS check. Closes #1943.

* sp_BlitzCache additional plural checks for @sortorder (#1963)

* additional plural checks

added in plural checks, also raised error to 16 when invalid sort order is chosen.

* removed unnecessary checks

* minor typo (#1965)

* #1967 sp_Blitz updating supported versions (#1969)

SQL 2012 pre-SP4 is out of support. Closes #1967.

* #1935 sp_BlitzCache Azure compatibility (#1972)

Azure SQL DB compatibility and removing bou from global temp table names. Closes #1935.

* #1973 sp_BlitzIndex truncation error (#1974)

Fixes #1973.

* #1971 add POOL_LOG_RATE_GOVERNOR as poison (#1975)

Closes #1971.

* #1966 fix sp_Blitz check IDs (#1976)

203 and 224 were swapped in the documentation. Closes #1966.

* 20190219 bumping version numbers

To prep for imminent release...

* 20190219 release

Updating install-all-scripts with new versions.

* 2019_02 Release

Bumping version numbers & dates.

* Merge conflicts, good times

* Merge conflicts, whee

* 2019_02 release

Updating build scripts.
BrentOzar pushed a commit that referenced this issue Mar 19, 2019
* #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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants