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

SQL Server – Dynamic SQL Queries – Duplicate Plans ?

Background

In the last couple of weeks we have taken a sure aim at some of our dynamic queries.

This morning came in and started reviewing our cached plans and noticed that we might have duplicates.

 

Cached Plans

Image

Here is a slimmed down version of our cached Plans

Explanation

I was wondering why it seems I have duplicate entries filtering on timePeriod, stateLimit, and Phone.

The first entry has 2040 hits and the other 134 matches.

 

Comparison

SQL Server Management Studio ( SSMS )

I tried to compare the plans using SQL Server Management Studio v2017, but nothing jumped at me.

 

Dynamic Management Views

sys.dm_exec_plan_attributes

I also looked at the normal causes such as:

  1. userid
    • attribute user_id
  2. Set Options
    • Attribute set_options
      • Quoted Identifier
      • Ansi Nulls

 

Text File Comparison

I then saved the query plans into text files and used an online text comparison tool.

DiffNow

DiffNow is here.

From the few differences between the files, here is why my eyes finally rested.

Image

 

Explanation

  1. Saw that @stateLimit only appears in the second plan

 

In Code

Output

Using XQuery was able to confirm that different queries is indeed attributable to slight variation in filtering columns.

Parameter List

Sample 1

 

Sample 2

 

Take away

Thankfully considering text file comparison got me closer to identifying the causation of the “duplicate” plans.

Sql Server Agent – Step – “SQL Server Integration Services Package” – Error – “Errors were detected in the command line arguments…”

Background

Trying to edit the parameters on a SQL Server Agent Job, but ran into a hard wall.

 

Error Message

Error

Image

Textual

TITLE: Microsoft SQL Server Management Studio

Errors were detected in the command line arguments, please make sure all arguments are set correctly. (SqlManagerUI)

 

Advanced Information

Upon clicking the “Advanced Information” we will click the message pasted below.

Image

Textual

===================================

Errors were detected in the command line arguments, please make sure all arguments are set correctly. (SqlManagerUI)

——————————
Program Location:

at Microsoft.SqlServer.Management.SqlManagerUI.DTSJobSubSystemDefinition.Microsoft.SqlServer.Management.SqlManagerUI.IJobStepPropertiesControl.Save(JobStepData data, Boolean isSwitching)
at Microsoft.SqlServer.Management.SqlManagerUI.JobStepProperties.OnGatherUiInformation(RunType runType)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.OnGatherUiInformation(RunType runType)

Scenario

This problem is reproducible this way.

  1. Start Sql Server Management Studio ( SSMS )
  2. Connect to SQL Server Instance
  3. Access SQL Server Agent \ Jobs \ [Job Name]
  4. Edit a Job Step whose type is “SQL Server Integration Services Package
  5. There are two tabs, Package and Configuration
  6. Access the “Configuration” Tab
  7. Within the “Configuration” Tab there are three Tabs.  The Tabs are “Parameters”, “Connection Managers”, and Advanced
  8. Select the “ExcelConnectionManager_ConnectionString” parameter
    • Click on “ellipse” button
    • The “Edit Literal Value for Execution” window appears
    • Access the “value” textbox and make cosmetic changes
    • Click the OK button
  9. The error, “Errors were detected in the command line arguments, please make sure all arguments are set correctly. (SqlManagerUI)“, aforementioned appears

 

Quick Points

  1. Other Parameters can be modified
    • This error only occurs when we tried amending the Excel File Connection String
    • We were able to modify other parameters; such as SQL Server and SMTP Server
  2. Connection Manager
    • All Connections ( Excel, SQL Server, and SMTP) can be successfully modified via the Connection Manager Tab

 

Honorable Mention

  1. Cody Konior
    • Agent job errors when running an SSIS package
      Published On :- 2015-August-2nd
      Link

 

SQL Server Versions

Here are the versions that exhibit this issue.

SQL Server Engine

Tabulated

Version Version Literal Version#
SQL Server v2014
Microsoft SQL Server 2014 (SP2-CU4) (KB4010394) – 12.0.5540.0 (X64) 12.0.5540.0

 

SQL Server Client

SQL Server Management Studio ( SSMS )

Tabulated

Version Version Literal Version#
SQL Server v2017
v 17.2 14.0.17177.0
SQL Server v2014
SQL Server 2014 SP2 CU10 12.0.5571.0

 

Image

SQL Server v2017

SQL Server v2014

SSMS – “An error occurred while executing batch. Error message is: Exception of type ‘System.OutOfMemoryException’ was thrown”

Background

Eventful day this morning, had to take a walk around the office block.

You know it is a good day when you wake @ 4: 30 AM, pulled in the smartphone and heard what smart bloggers have to say, make the 5:30 AM Bus, made the train and walked the lake just to feel good about work.

Error

An error occurred while executing batch. Error message is: Exception of type ‘System.OutOfMemoryException’ was thrown

Remediation

It is not the Server, but your client, Sql Server Management Studio ( SSMS), in this case.

No need to reboot, but closed the Chrome Tabs, and when that did not help, exited SSMS.

 

Culprit

May be too much “Query Execution Plan” digest.

Looking for a lot of things through the Graphics pane; graphics is always more taxing on system.

Heart

Heart and I spoke about the next few days and how everything is sane and good.

 

Listening

Have a nice bit of work in, and it is only 2 PM.

Daryl Worley
Second Wind
Link

Don’t need a second wind, but can relate….

 

SQL Server – SSIS – Copying Packages Stored in the msdb database

Background

As part of a project that we are working on, we need to move SSIS packages between environments.

Storage Options

SSIS packages can be stored in few places:

  1. File System
  2. MSDB
  3. Integration Services Catalog

 

Storage – MSDB

Here is how to move them if they are stored in the MSDB Database.

 

SQL Server Management Studio

Because of DCOM, SSIS Management is very dependant on the SSIS Engine version.

And, so save yourself the headache and make sure that you are using Management Studio ( SSMS ) that matches your version.

 

Outline

  1. Launch SSMS
  2. Source
    • Connect To Source and export SSIS Package
    • Exported package should be save into File System
  3. Destination
    • Connect to Destination and import SSIS package
    • Import file saved earlier
  4. Validation

Steps

Launch SSMS

Once again launch SSMS that matches the version  of your  Database Engine

 

Connect To Source & Export Package

Steps

  1. Connect to “Integration Services” on your Source Server
  2. In the Explorer Pane on the left side of the window, navigate the Stored Packages node
    • Review the list of packages
    • Select the package
    • Right click on your selection and access the drop down menu
    • From the options availed through down menu, choose to “Export Package
    • In the “Export Package” window
      • Package Location
        • Accept the default choice of “File System”
      • Package Path
        • Please click on the button (  ) to the right of package path
        • A File Dialog appears
        • Navigate the dialog and settle on a target folder
        • Click the Save button to confirm the targeted folder
      • Review your choices
      • Press the OK button to export the package

 

Screen Shot

Connect to Server

Stored Packages

Export Package

 

Save package to path

 

Export Package

 

 

Import package to Destination

Steps

  1. Connect to “Integration Services” on your Destination Server
  2. In the Explorer Pane on the left side of the window, navigate the Stored Packages node
    • Access the msdb node
    • Review the list of packages
    • Delete or rename existing packages that share the name of the package that you will be importing
    • Right click the MSDB Node or the parent folder if you have chosen to use folders
    • From the drop-down menu, choose the “Import Package” menu item
    • In the “Import Package” window
      • Package Location
        • Accept the default choice of “File System”
      • Package Path
        • Please click on the button (  ) to the right of package path
        • A File Dialog appears
        • Navigate the dialog and access the target folder chosen earlier
        • Click the Save button to confirm the targeted folder
      • Review your choices
      • Press the OK button to import the package

 

Screen Shot

Connect to Server

Import Package – Initial

Load package

 

Import Package – Configured

Validation

Olap Helper

Olap Helper has many small SQL Scripts out there.

Olap’s blogs here.

We are going to use one of them to list our SSIS Packages

Coverage

  • Script Center > Repository > Databases > List all SSIS packages stored in msdb database
    List all SSIS packages stored in msdb database
    Link

Code


/*

	List all SSIS packages stored in msdb database. 
	sysssispackages (Transact-SQL)
	https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/sysssispackages-transact-sql
	
	List all SSIS packages stored in msdb database
	Script Center > Repository > Databases > List all SSIS packages stored in msdb database
	https://gallery.technet.microsoft.com/scriptcenter/List-all-SSIS-packages-3b247394
	

*/
SELECT 

		[package]
			= PCK.[name]

	  , [description]
			= PCK.[description]
 
	  , [folderName]
		= FLD.foldername 

	  , [packageType]
		= CASE PCK.packagetype 
			WHEN 0 THEN 'Default client' 
			WHEN 1 THEN 'SQL Server Import and Export Wizard' 
			WHEN 2 THEN 'DTS Designer' 
			WHEN 3 THEN 'SQL Server Replication' 
			WHEN 5 THEN 'SSIS Designer' 
			WHEN 6 THEN 'Maintenance Plan Designer or Wizard' 
			ELSE 'Unknown' 
		END
		 
	  , PCK.ownersid

	  ,[owner]
		= tblSSP.[name]

	  , [isEncrypted]
		= PCK.isencrypted 

	  , [createDate]
		= PCK.createdate 

	  , [version]
			= CONVERT(varchar(10), vermajor) 
				+ '.' + CONVERT(varchar(10), verminor) 
				+ '.' + CONVERT(varchar(10), verbuild) --AS version 

	  , versionComment 
		= PCK.vercomments

	  , [packageSize]
		= DATALENGTH
			(
				PCK.packagedata
			) 

FROM msdb.dbo.sysssispackages AS PCK 

INNER JOIN msdb.dbo.sysssispackagefolders AS FLD 
	ON PCK.folderid = FLD.folderid 


LEFT OUTER JOIN sys.server_principals tblSSP

	ON PCK.ownersid = [tblSSP].[sid]

ORDER BY 
		PCK.[name]


Ouput

SQLServer – Identity Column – Change Existing Column

Background

We have an existing table what was meant to be have its primary key defined as an Identity Column, but due to an oversight it was not

Existing Table

SQL

sp_help

If we issue, sp_help against the table, here is what it looks like…

SSMS – Table Designer

Here is what we see if we use SSMS Table Designer…

 

Remediation

Outline

  1. Backup Table
  2. Identity Column
    • Add new identity column
  3. Populate identity Column with data from original column
    • Set Identity_insert On
    • Delete data from table
    • Import Data into table
      • New Column = Original Column
    • Set Identity_insert Off
  4. Drop table’s primary key
  5. Drop Original Column
  6. Rename Column
    • Identity Column to Original Column
  7. Re-create primary key
  8. Drop temporary table

 

SQL



set nocount on
set XACT_ABORT on
set noexec off
go


declare @schema	sysname
declare @table sysname
declare @column sysname
declare @tableFull sysname
declare @columnFull sysname

declare @columnNewID sysname
declare @columnNewFull sysname

declare @isIdentity bit
declare @ableToUpdateIdentityColumn bit

declare @commit bit

declare @CHAR_TAB varchar(10)

set @CHAR_TAB = char(9)

set @commit = 0
--set @commit = 1

set @schema = 'dbo'
set @table = 'tGeneratedArticulationDetailExcludedIdentifier'
set @column = 'tArticulationDetailExcludedIdentifierSK'
set @tableFull = quoteName(@schema)  + '.' + quoteName(@table)
set @columnFull = @tableFull + '.' + quoteName(@column)

set @columnNewID = '_identityCol'
set @columnNewFull = @tableFull + '.' + quoteName(@columnNewID)

select @isIdentity = max
						(
							cast(is_identity as tinyint)
						)
from   sys.columns tblSC
where  tblSC.object_id = object_id(@table)

print '@isIdentity ' + cast( @isIdentity as varchar(10))

if (@isIdentity = 1)
begin

	set noexec on

end

if (
		(@isIdentity = 0)
		and not exists
		(
			select  1
			from   sys.columns tblSC
			where  tblSC.object_id = object_id(@table)
		)

	)
begin tran

	if object_id('tempdb..#tGeneratedArticulationDetailExcludedIdentifier') is not null
	begin

		print 'drop table #tGeneratedArticulationDetailExcludedIdentifier ...'

		drop table #tGeneratedArticulationDetailExcludedIdentifier

		print 'dropped table #tGeneratedArticulationDetailExcludedIdentifier'

	end

	print 'Backing up table ...' 

	select *
	into   #tGeneratedArticulationDetailExcludedIdentifier
	from   [dbo].[tGeneratedArticulationDetailExcludedIdentifier] tbl

	print 'Backed up table ' 

	--exec sp_help '[dbo].[tGeneratedArticulationDetailExcludedIdentifier]'

	print @CHAR_TAB + 'Add new column _identityCol ...' 

	alter table [dbo].[tGeneratedArticulationDetailExcludedIdentifier]
		add [_identityCol] int not null identity(1,1)

	print @CHAR_TAB + 'Set identity column ' + @tableFull + ' on ... '

	set identity_insert [dbo].[tGeneratedArticulationDetailExcludedIdentifier] on

	/*
		Cannot update identity column '_identityCol'.
	*/					
	if (@ableToUpdateIdentityColumn = 1)
	begin

		if exists
		(
			select *
			from   [dbo].[tGeneratedArticulationDetailExcludedIdentifier]
		)
		exec('
				update  tbl
				set     [_identityCol] = [tArticulationDetailExcludedIdentifierSK]
				from    [dbo].[tGeneratedArticulationDetailExcludedIdentifier] tbl
			')
	
	end
	else
	begin

		delete
		from   [dbo].[tGeneratedArticulationDetailExcludedIdentifier]

		exec
		('
			insert into [dbo].[tGeneratedArticulationDetailExcludedIdentifier]
			(
					 [tArticulationDetailExcludedIdentifierSK]
				   , [tGeneratedArticulationDetailSK]
				   , [CourseIdentifierParentID]

				   , [_identityCol]
			)
			select
					 [tArticulationDetailExcludedIdentifierSK]
				   , [tGeneratedArticulationDetailSK]
				   , [CourseIdentifierParentID]

				   , [tArticulationDetailExcludedIdentifierSK]

			from #tGeneratedArticulationDetailExcludedIdentifier
		')

	end

	print @CHAR_TAB + 'Set identity column ' + @tableFull + ' off'

	set identity_insert [dbo].[tGeneratedArticulationDetailExcludedIdentifier] off

	alter table [dbo].[tGeneratedArticulationDetailExcludedIdentifier]
		drop constraint [PK__tGenerat__33CFD59414D00BD0]

	alter table [dbo].[tGeneratedArticulationDetailExcludedIdentifier]
		drop column [tArticulationDetailExcludedIdentifierSK]


	/*
		print '@columnFull : ' + @columnNewFull
		print '@column : ' + @column
	*/
	EXEC sp_rename
		   @objname = @columnNewFull
		 , @newname = @column
		 , @objtype = 'COLUMN'


	alter table [dbo].[tGeneratedArticulationDetailExcludedIdentifier]
		add constraint [PK_tGeneratedArticulationDetailExcludedIdentifier]
			primary key
			(
				[tArticulationDetailExcludedIdentifierSK]
			)


	if object_id('tempdb..#tGeneratedArticulationDetailExcludedIdentifier') is not null
	begin

		print 'dropping table #tGeneratedArticulationDetailExcludedIdentifier ...'

		drop table #tGeneratedArticulationDetailExcludedIdentifier

		print 'dropped table #tGeneratedArticulationDetailExcludedIdentifier'

	end


print ''

while (@@trancount > 0)
begin

	if (@commit = 1)
	begin

		print 'commit tran'
		commit tran;

	end
	else
	begin

		print 'rollback tran'
		rollback tran

	end

end

go


set noexec off
go

SQL Server – SSMS – Options – “Override connection string time-out value for table designer updates”

 

Background

This is the third in a series of posts on timeouts

SQL Server Management Studio ( SSMS)

Designers

For those that use the Designer built into SSMS to make structural table changes, the Designer Option panel is a good place to go and make sure that the default settings are what you want them to be.

Screen Shot

Here is what that screen looks:

Initial

Post Changes

Image

Explanation
  1. Here we changed the “Transaction Time-out after:” from the default of 30 seconds to 90 seconds

Misnomer

I think outside of the Database World and specifically SQL Server, it is OK to use the phrase & words, but I think for a tool that targets SQL Server and only that table, a bit more care should be taken.

And, you likely ask why.

Grouping :- Override connection string time-out value for table designer updates
Phrase :- Transaction time-out after

Here are the difficulties that I have with the Group Label “Override connection string time-out value

  1. We all know that the Connection String does not expose command execution nor lock timeout
    • Command Execution can be set in the Connection or Command Object, but not in the actual connection string
    • Because of this understanding, coders have to look into the code and set the timeouts
    • They are not able to do so declaratively in the Web.config and thus not suffer the pain of recompile
  2. There is more than enough misappropriation that Connection Open timeouts and Command Execution timeouts, are one the same

Microsoft – Connect

  1. Item #  :- 3130697
    • Link :- Link
      Opened By: Daniel Adeniji
      Date Opened:- 2017-March-25
      Type :- Suggestion
      Status :- Active