SSMS – v2008 – Error – “Index was outside the bounds of the array. (Microsoft.SqlServer.Smo)”

Background

Drew a quick error running Sql Server Management Studio (SSMS).

Error

Text

The error reads :-

Index was outside the bounds of the array. (Microsoft.SqlServer.Smo)

Image

SSSM.v2008.IndexWasOutsideTheBoundsOfTheArray.20190203.0652AM.PNG

Explanation

  1. Error reads
    • Microsoft.SqlServer.Smo
    • Index was outside the bounds of the array

Recreate

Tried connecting to Microsoft SQL Server v2017 instance with Sql Server Management Studio v2008.

 

TroubleShooting

Microsoft.SqlServer.Smo

SysInternals / Process Explorer

Version

Outline
  1. Download SysInternals \ Process Explorer
  2. Launched SysInternal’s Process Explorer
  3. Selected the ssms.exe
  4. View process modules
    • Click on menu “View”\”Show Lower Pane”
  5. Find
    • Click on menu and choose Find
    • Enter smo
    • Reviewed found modules
  6. Launched Windows Explorer
  7. Accessed the found DLL
  8. Noted version number as 10.0.1600.22
Images
Image – Process Explorer

processExplore.20190203.0957AM.PNG

Image – Handle or DLL substring

findModule.smo.PNG

Windows Explorer

ssmo.sysInternals.processExplorer.20190203.0720AM.PNG

Remediation

Microsoft SQL Server 2008 Service Pack 4 Feature Pack

SQL Server SMO is bundled as part of SQL Server Feature Pack.

The latest version is Service Pack 4 and it is available here.

Packages

  1. Shared Management Objects
    • ENU\X86\SharedManagementObjects.msi
  2. Clr Types
    • ENU\X86\SQLSysClrTypes.msi

Outline

  1. Download
    • Download ENU\X86\SharedManagementObjects.msi
    • Download ENU\X86\SQLSysClrTypes.msi
  2. Install
    • Install SQLSysClrTypes.msi
    • Install SharedManagementObjects.msi

Screenshot

Download

SQL System CLR Types

clrtypes.20190203.0732AM.PNG

Management Objects

microsoft.smo.20190203.0728AM.PNG

Installation

Management Objects
Prerequisite – Missing Microsoft SQL Server 2008 System CLR Types

clrtypes.20190203.0730AM.PNG

Welcome

microsoft.smo.welcome.20190203.0733AM.PNG

System CLR Types
Installation – SQL Server 2008 System CLR Types

clrtypes.welcome.20190203.0732AM.PNG

Post Installation Review

Outline

  1. Post Installation
    • Microsoft.SqlServer.Smo.dll
    • Version
      • Moved from 10.0.1600.22 to 10.0.6000.29

Images

WindowsExplorer.Microsoft.SqlServer.smo.dll.20190203.1028AM.PNG

Acknowledged

Acknowledged Giles D Middleton.

GilesDMiddleton.20190203.1041AM

 

References

  1. stack overflow
    • Index was outside the bounds of the Array. (Microsoft.SqlServer.smo)
      Link
  2.  Microsoft
    • Microsoft SQL Server 2008 Service Pack 4 Feature Pack
      Link

 

SSMS – Linked Server – Column Metadata

Background

Had a good meeting this morning while we try to figure out how to better support our Developers.

One of the issues they brought up was an inability to view the datatype of linked Server tables.

Pictorial

Here is the deepest granularity when we connect to our Linked Server.

Image

ssms.columns.20190114.0425PM.PNG

Explanation

  1. We see the following
    • Server
    • Databases
    • Objects
      • Tables
      • Views

View Columns

Script

Outline

Here are avenues we can explore to view column metadata :-

  1. sp_columns_ex
  2. Openquery/sys
    • sys.all_columns
  3. Openquery/informational_schema
    • informational_schema.columns

Choices

exp_columns_ex

Syntax

exec sp_columns_ex
		  @table_server
		, @table_catalog
		, @table_schema
		, @table_name   

Sample

declare @linkedServer   sysname
declare @linkedDatabase sysname
declare @linkedSchema   sysname
declare @linkedTable    sysname

set @linkedServer= 'AWS-JobBuilder'
set @linkedDatabase = 'acs_ap'
set @linkedSchema = 'dbo'
set @linkedTable = 'ap_user'

exec sp_columns_ex
		  @table_server  = @linkedServer
		, @table_catalog = @linkedDatabase
		, @table_schema  = @linkedSchema
		, @table_name    = @linkedTable

Output

sp_tables_ex.2019014.0443pm

openquery/sys.*

Syntax

select top 10 *

from   openquery
        (
              [AWS-JobBuilder]

            , '
                    select
                              [server] = serverproperty(''servername'')
                            , [database] = db_name()
                            , [schema] = tblSS.name
                            , [object] = tblSAO.name
                            , [column] = tblSAC.name
                            , [type]   = tblST.[name]
                            , tblST.max_length
                            , tblST.is_nullable

                    from   sys.schemas tblSS

                    inner join sys.all_objects tblSAO

                            on tblSS.schema_id = tblSAO.schema_id

                    inner join sys.all_columns tblSAC

                        on tblSAO.object_id = tblSAC.object_id

                    inner join sys.types tblST

                        on  tblSAC.system_type_id = tblST.system_type_id
                        and tblSAC.user_type_id = tblST.user_type_id

              '
        )

Output

openquery.sys.all.2019014.0452pm

openquery/information_schema.columns

Syntax
select top 10 *

from   openquery
        (
              [AWS-JobBuilder]

            , '
                select top 100 

                          [server] = serverproperty(''servername'')

                        , [database] = tblSIC.[TABLE_CATALOG]

                        , [schema] = tblSIC.[TABLE_SCHEMA]

                        , [object] = tblSIC.[TABLE_NAME]

                        , [column] = tblSIC.[COLUMN_NAME]

                        , [position] = tblSIC.[ORDINAL_POSITION]

                        , [dataType] = tblSIC.[DATA_TYPE]

                        , [charMaxLength] = tblSIC.[CHARACTER_MAXIMUM_LENGTH]

                        , [charOctetLength] = tblSIC.[CHARACTER_OCTET_LENGTH]

                from   information_schema.columns tblSIC

              '
        )			   	

Output

openquery.openquery.informational_columns.2019014.0515PM.PNG

Summary

Unfortunately, SQL Server Management Studio ( SSMS ) v17.x does not let us view columns on Linked Servers.

To gather column level metadata, one has to write code.

SQL Server – SSMS – Query Plan – Compare

Background

One of my favorite go to tool with SQL Server Query troubleshooting is to simple use SQL Server Management Studio (SSMS) to compare the query plans generated via running the same SQL across different machines or SQL Server Versions.\

Use

To use, please follow these simple steps :-

  1. Invoke SSMS
    • Query 1
      • Enable “Include Actual Execution Plan”
      • Run Query
      • Save resultant plan
    • Query 2
      • Enable “Include Actual Execution Plan”
      • Run Query
      • Save resultant plan
    • Access Query Plan pane
      • Access either Query’s Plan tab
      • Right click in an empty area of the pane
      • The drop-down menu appears
      • From the drop down menu, please choose “Compare Showplan”
      • From the File Open window,  choose the saved query plan from the opposing query

 

Sample

Sample – 01

Showplan Comparison

Pictorial

queryPlan.compare.showPlanComparison.20190109.0535PM.PNG

Explanation

  1. Certificates
    • Top Query :- Index Seek
    • Bottom Query :- Index Scan

 

Showplan Analysis

queryPlan.compare.showPlanAnalysis.20190109.0534PM.PNG

Explanation

  1. Scenarios
    • Difference :- 15316%
    • Estimated top/left :- 3274
    • Estimated bottom/right :- 504816

 

Requirement

As for client, requires at least SQL Server Management Studio v2017.

Works across various SQL Server Database Engine Versions.

Tested here against Server versions :-

  1. v2005
  2. v2017

 

SQL Server On Linux – Post Installation – Configuration – Set Folders

Background

Upon installation, SQL Server on Linux utilizes the same storage folders for data, log, and backups.

Outline

  1. Review Current Settings
  2. Provision Folders
  3. Register Folder Settings on SQL Server
  4. Restart SQL Server
  5. Review new folder settings

Processing

Review Current Settings

SQL Server Management Studio ( SSMS )

Let us use our GUI Tool, SSMS, to determine current default folder settings.

Server Properties

Server Properties – Database Settings
Images

configuration.defaultLocations.ssms.20181127.1135AM

Explanation
  1. Data, Log, Backup
    • /var/opt/mssql/data/

Provision Folders

Outline

  1. Create An Array
  2. Fill Array with list of folders
  3. Iterate Array List
    • Get Folder
    • Determine if folder exists
    • If Folder does not exist, please create it
    • Set Folder Attributes
      • Set Folder Owner to mssql
      • Set Folder Group to mssql

Script


#!/bash/bash

echo "Preparing folders"

declare -a _folderList=(
                          "/app/Microsoft/sqlServer/data"
                          "/app/Microsoft/sqlServer/log"
                          "/app/Microsoft/sqlServer/backup"
                          "/app/Microsoft/sqlServer/dumpCrash"
)

#Get Number of elements
_iNumberofElements=${#_folderList[*]}

echo "Number of elements $_iNumberofElements "

_i=0

while [ $_i -lt $_iNumberofElements ]
do

  #Current Folder
  _folder=${_folderList[_i]}

  _folderExistence=false

   #Does Folder Exist
   if [ -d $_folder ]
   then

     _folderExistence=true

   fi

   #Speak Folder
   echo "$_i ) $_folder "

   #if Folder does not exist, create it
   if [ $_folderExistence = false ]
   then

       echo -e "\tCreating Folder $_folder"

       mkdir --parents $_folder

  fi

  #Change Folder owner
  echo -e "\tChange Folder Owner"
  chown mssql $_folder

  #Change Folder Group
  echo -e "\tChange Folder Group"
  chgrp mssql $_folder

  #Move to next array element
  _i=$((_i+1))

done

Configure SQL Server Default Folders

Outline

  1. Issue folderlocation.default[x]
    • Data
    • Log
    • Backup
    • Dump

Script


/opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /app/Microsoft/sqlServer/data

/opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /app/Microsoft/sqlServer/log

/opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /app/Microsoft/sqlServer/backup

/opt/mssql/bin/mssql-conf set filelocation.defaultdumpdir /app/Microsoft/sqlServer/dumpCrash

Restart SQL Server Service

Outline

Restart SQL Server Service to effect changes

Script


sudo systemctl restart mssql-server.service

Review new settings

SQL Server Management Studio ( SSMS )

Let use again use SSMS to review folder settings.

Server Properties

Server Properties – Database Settings
Images

configuration.defaultLocations.ssms.post.20181127.0245PM

Explanation
  1. We were able to change our targeted folders
    • Data, Log, Backup

 

SQL Server Agent – Configuration on Linux

Background

Post installation of SQL Server on our Linux Box, it is time to review our SQL Server Agent component.

Lineage

  1. SQL Server – Installation on Linux/CentOS
    Link

Check List

Let us go through our checklist.

Outline

Is SQL Server Configured?

SQL Server

Metadata

sys.dm_server_services

SQL

select *
from   sys.dm_server_services 

Output

sys.dm_server_services.pre.20181127.0602AM.PNG

Explanation
  1. sys.dm_server_services
    • Startup Type
      • Automatic
    • Status
      • Stopped
    • Service Account
      • LocalSystem
    • Clustered
      • No

 

Configuration File

/opt/mssql/bin/mssql-conf

List

Let us utilize the SQL Server Configuration Utility ( /opt/mssql/bin/mssql-conf ) to identify configurable elements.

SQL

sudo /opt/mssql/bin/mssql-conf list

Output

mssql-conf.pre.20181127.0607AM.PNG

Explanation
  1. sqlagent.databasemailprofile
    • Database Mail Profile
  2. sqlagent.enabled
    • Is SQL Server Agent Enabled
  3. sqlagent.errorlogfile
    • Error Log File
  4. sqlagent.errorlogginglevel
    • Error Logging Level

 

Configure

/opt/mssql/bin/mssql-conf

Process

Let us utilize the SQL Server Configuration Utility ( /opt/mssql/bin/mssql-conf ) to make configuration changes.

Make Changes

Command

sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true

Output

mssql-conf.set.20181127.0610AM.PNG

Explanation
  1. sqlagent.enabled
    • Enabled SQL Server Agent

 

Effect Changes

To effect the changes, please use systemctl

Command

sudo systemctl restart mssql-server.service

Output

mssql-conf.set.systemctl.20181127.0732AM

Explanation
  1. Restarted SQL Server Service to effect the changes

 

Configuration

SQL Server

Metadata

sys.dm_server_services

SQL

select *
from   sys.dm_server_services

Output

sys.dm_server_services.post.20181127.0735AM

Explanation
  1. sys.dm_server_services
    • Startup Type
      • Automatic
    • Status
      • Running
    • Process ID
      • 4116 ( pid )
        • Obviously yours will be different
    • Last Startup Time
      • 1600-12-31 16:00:00.0000000 -08:00
        • Not Accurate
    • Service Account
      • LocalSystem
    • Clustered
      • No

Usage

SQL Server Management Studio ( SSMS )

Object Explorer

Images
Images – Before

sqlServerExplorer.pre.20181127.0739AM.PNG

Images – After

sqlServerExplorer.post.20181127.0740AM.PNG

 

SSMS – Error Message – “Property Owner is not available for Database”

Background

Trying to access the database property on one of our databases.

Error

Error Message

Syntax

Property Owner is not available for Database ‘[database]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

Sample

Property Owner is not available for Database ‘[AdminDB]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

 

Troubleshooting

Metadata

sp_helpdb

Syntax

use master
go

exec sp_helpdb
go

Output

Explanation

  1. Database
    • AdminDB
      • ~~UNKNOWN~~

sys.databases

Syntax

select 
		  tblSD.[database_id]
		, tblSD.[name]
		, tblSD.[owner_sid]
		, [owner]
			= SUSER_SNAME(tblSD.owner_sid)

from [master].[sys].[databases] tblSD
go

Output

Explanation

  1. Database
    • AdminDB
      • SUSER_SNAME(sys.databases.owner_sid) returns null

 

Remediation

Alter Authorization

Syntax


use [master]
go

ALTER AUTHORIZATION
DATABASE::[database]
TO [sa]
;

Sample


use master
go

BEGIN TRAN

	exec sp_helpdb

	ALTER AUTHORIZATION 
		ON DATABASE::[AdminDB] 
		TO [sa]
		;    

	exec sp_helpdb

ROLLBACK

Output

 

Reproducible

SSMS – v2017

The error does not occur in SSMS v2017.

SSMS – v2014

Noticed it in v2014 when trying to access the database property for an orphaned database.

 

References

  1. Microsoft
    • Docs > SQL > T-SQL > Statements
      • ALTER AUTHORIZATION (Transact-SQL)
        Link
    • Docs > SQL > T-SQL > Functions