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

SQL Server – “Lock Timeouts” – Trigger Timeouts – SSMS – Table Designer

Background

In our initial post we spoke about experiencing an Outage.

Our Clients in the Development side of the house explained they have been experiencing “Lock Timeouts“.

We wanted to see what tools we can use to detect similar occurrences on the DB.  And, we cited SQL Server Profiler as one of those tools.

In this post, we will attempt to simulate time outs due to expired Lock requests and see how well SQL Server Profiler performs.

 

Lab

Outline

  1. Start SQL Server Profiler and set to capture event
  2. Create a bare minimum table
  3. Add data
    • Start Transaction
    • Add a couple of records
    • Temporarily leave Transaction Uncommitted
  4. In another session, return to Table designer & initiate changes
    • Add one or two new columns, attempting to make design changes

Steps

SQL Server Profiler

Here are the events that we said we will capture:

Image

Image – Events Selection

Image – Edit Filter

 

Tabulation

Tabulation – Events Selection
Event Category Event
 Locks  Lock:Cancel
 Stored Procedures  RPC:Completed
 TSQL  SQL:Batch Completed

 

 

Tabulation – Filter
Column Value
 Duration  30000

 

 

Create a Bare Minimum Table

Image

Explanation

In the screen above, we have created a table and added a couple of columns ( personID and personName).

 

Attempt to add data

Script


set nocount on
go
set XACT_ABORT on
go

use [DBLab]
go

declare @transactionComplete bit

set @transactionComplete = 0

begin tran

	insert into [dbo].[person]
	default values;

 

Table Designer

Table Designer – Adding new column – dateAdded

Image

 

Table Designer – Execution Timeout Expired

Image

Textual

'person' table
- Unable to modify table.
Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

 

SQL Server Profiler

Image

Lock:Cancel

Tabulated
Event Category Event Columns Values
 Lock  
 Lock:Cancel
 TextData
 Application  Microsoft SQL Server Management Studio
 NTUserName  dadeniji
 Duration  29999
 StartTime  2017-03-25 16:23:18.190
 EndTime  2017-03-25 16:23:18.190
 Mode  2-SCH-M
 ObjectID 110623437
 ObjectID2  110623437
 OwnerID  1-TRANSACTION
 DatabaseID  7
 DatabaseName  DBLAB
 Hostname  DADENIJI
 IntegerData2  0-LOCK

 

 

Explanation
  • You want to pay attention to ObjectID and Database ID
    • Those two will lead you to Database, Schema, and Object name
  • Mode
    • Will lead you to desired Lock
      • In our case, 2-SCH-M or Schema Modification Lock
  • And, of course Application, Host, and Username, and TextData
    • Determine causation and forensics

 

SQL: Batch Completed

Tabulated
Event Category Event Columns Values
 T-SQL  
SQL: Batch Completed
 TextData  ALTER TABLE dbo.person ADD dateAdded null
 Application  Microsoft SQL Server Management Studio
 NTUserName  dadeniji
 Duration  29999
 StartTime  2017-03-25 16:22:48.190
 EndTime  2017-03-25 16:23:18.190

 

 

Explanation
  • You want to pay attention to ObjectID and Database ID
    • Those two will lead you to Database, Schema, and Object name
  • Mode
    • Will lead you to desired Lock
      • In our case, 2-SCH-M or Schema Modification Lock
  • And, of course Application, Host, and Username, and TextData
    • Determine causation and forensics

 

 

Dedicated

Dedicated to MSFT’s SQL Server Team.

 

 

SQL Server Management Studio ( SSMS ) – Non-Printable Characters

 

Background

Through the use of a Microsoft LightSwitch application that we are working on, I have been eating my dog food.

 

Data Entry

As one who hates the sheer drudgery of data entry, I end up doing quite a bit of copy and paste.

I copy and paste data from the original Excel file into the LightSwitch application.

And, things have been mostly OK; as the data that needs to be copied over is minimal.

But, recently I started noticing little errors cropping up.

I launched SQL Server Profiler and noticed the errors are due to referential integrity.

The error was traced to trying to place data into secondary tables whereas those same data are not in our primary data.

 

TroubleShooting

 

Query Data

Launched Sql Server Management Studio ( SSMS ), and queried for the data and they appear to be in the Primary table.

 

Issue

After a bit of head scratching traced the problem back to white spaces at the end of the primary key fields.

 

Remediation

As always took to the Net to determine fixes and workarounds.

Outline

Here are the workarounds that we found thus far:

  1. Using Transact SQL
    • Identify data that have non-printable characters ( CRLF )
    • Strip out the Non-Printable characters

 

Code

Using Transact SQL

Here the functions that I found on the Net

Sourced

  1. dbo.fn_ShowWhiteSpace
  2. dbo.fn_nonPrintableStringClean

 

Code Snippet

 
use master
go
 

 
if schema_id('npc') is null
begin
 
    exec('create schema [npc] authorization [dbo];');
 
end
go
 
if object_id('[npc].[fn_ShowWhiteSpace]') is null
begin
 
    exec('CREATE FUNCTION [npc].[fn_ShowWhiteSpace]() RETURNS varchar(8000) AS BEGIN return 1/0 END')
 
end
go
 
ALTER FUNCTION [npc].[fn_ShowWhiteSpace]
(
    @str varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
    /*
        Michael Riley - AKA Gunny
        <a href="http://stackoverflow.com/questions/8655909/whats-the-best-way-to-identify-hidden-characters-in-the-result-of-a-query-in-sq">http://stackoverflow.com/questions/8655909/whats-the-best-way-to-identify-hidden-characters-in-the-result-of-a-query-in-sq</a>
 
        Michael Riley - AKA Gunny - Profile
        <a href="http://stackoverflow.com/users/195983/michael-riley-aka-gunny">http://stackoverflow.com/users/195983/michael-riley-aka-gunny</a>
 
    */
 
     DECLARE @ShowWhiteSpace varchar(8000);
     DECLARE @expanded bit
 
     set @expanded = 1
 
     SET @ShowWhiteSpace = @str
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(32), '[?]')
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(13), '[CR]')
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(10), '[LF]')
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(9),  '[TAB]')
 
     if (@expanded = 1)
     begin
 
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(1),  '[SOH]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(2),  '[STX]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(3),  '[ETX]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(4),  '[EOT]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(5),  '[ENQ]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(6),  '[ACK]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(7),  '[BEL]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(8),  '[BS]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(11), '[VT]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(12), '[FF]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(14), '[SO]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(15), '[SI]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(16), '[DLE]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(17), '[DC1]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(18), '[DC2]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(19), '[DC3]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(20), '[DC4]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(21), '[NAK]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(22), '[SYN]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(23), '[ETB]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(24), '[CAN]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(25), '[EM]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(26), '[SUB]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(27), '[ESC]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(28), '[FS]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(29), '[GS]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(30), '[RS]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(31), '[US]')
 
    end --if (@expanded = 1)
 
    RETURN(@ShowWhiteSpace)
 
END
go
 
grant execute on [npc].[fn_ShowWhiteSpace] to public
go

 

 

 

 
use [master]
go
 
set ansi_nulls on
go
set quoted_identifier on
go
 

if schema_id('npc') is null
begin
 
    exec('create schema [npc] authorization [dbo];');
 
end
go
 
if object_id('[npc].[fn_StringClean]') is null
begin
 
    exec('CREATE FUNCTION [npc].[fn_StringClean]() RETURNS varchar(8000) AS BEGIN return 1/0 END')
 
end
go
 
ALTER function [npc].[fn_StringClean] 
(
 @strIn as varchar(8000)
)
returns varchar(8000)
as
begin
 
    /*
        Blog
        How to remove unprintable characters from various fields in table
        <a href="https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1bf3e072-f194-4bff-87ee-07376927a7f8/how-to-remove-unprintable-characters-from-various-fields-in-table?forum=transactsql">https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1bf3e072-f194-4bff-87ee-07376927a7f8/how-to-remove-unprintable-characters-from-various-fields-in-table?forum=transactsql</a>
 
        Profile:
        NaveenCR
        <a href="https://social.msdn.microsoft.com/profile/naveencr/?ws=usercard-mini">https://social.msdn.microsoft.com/profile/naveencr/?ws=usercard-mini</a>
    */
 
    declare @iPtr as int
 
    set @iPtr = patindex('%[^ -~0-9A-Z]%', @strIn COLLATE LATIN1_GENERAL_BIN)
 
    while @iPtr > 0 
    begin
    
      set @strIn = replace(@strIn COLLATE LATIN1_GENERAL_BIN, substring(@strIn, @iPtr, 1), '')
 
      set @iPtr = patindex('%[^ -~0-9A-Z]%', @strIn COLLATE LATIN1_GENERAL_BIN)
 
     end
 
     return ( @strIn )
 
end
go
 
grant execute on [npc].[fn_StringClean] to public
go

 

 

Lab

Code
 

 

    select 
 
              tblLS.[serverName]
 
            , [serverName_Len] 
                = len(tblLS.[serverName])
 
            , [serverNameTrim]
                 = ltrim(rtrim(tblLS.[serverName]))
 
            , [serverName_Trim(Len]
                 = len(ltrim(rtrim(tblLS.[serverName])))
 
            , [ShowWhiteSpace]
                = [master].dbo.[fn_ShowWhiteSpace](tblLS.servername)
 
            , [serverName_NPClean]
                 = [master].[npc].[fn_StringClean]  (tblLS.[serverName])
 
            , [serverName_NPClean_Len] 
                = datalength([master].[npc].[fn_StringClean]  (tblLS.[serverName]))
 
    from   [DBLAB].dbo.[listofServers.2017022] tblLS
 
    where  (
                (
                    (
                         tblLS.servername 
                            != [master].[npc].[fn_StringClean] (tblLS.servername) 
                    )
                )
          )    

 

 

 

Output

dbo_listofservers_20170222_1036pm

 

Explanation
  1. Here are the columns that are being shown
    1. serverName
      • The actual Server Name
      • The length of the Server Name
    2. serverName Trimmed
      • Using ltrim and rtrim we trim the Server Name
      • We get the length of the trimmed column
    3. Show whitespace
      • Show the serverName and display Non-Printable characters
    4. Show “Cleaned” serverName
      • Show cleaned Server Name
      • Show length of cleaned Server Name

 

Microsoft Connect Items

  1. SQl Server Management Studio should show new lines in records. – by Michael Freidgeim
    • Submitted By :- Michael Freidgeim
    • Item ID :-381955
    • Date Submitted :- 2008-Nov-14th 4 AM
    • Link
    • Description:-
      • 1. If there are newLines characters in the record data, SQl Server Management Studio in Grid view shows the record as one string, without indication that newlines are present.
        It causes confusion to the user, invalid interpretation of the data and even data loss.See scenario in my post
        http://geekswithblogs.net/mnf/archive/2008/11/13/sql-server-management-studio-doesnt-show-new-lines-in-records.aspx
        It will be good if newLines will be shown as actual new lines(consistent with SQL Server 2000 Enterprize Manager) or as some special character(e.g \n or |) to inform user.
        Ideally method on new line presentation could be configured in Tools/Options/Query Results/SQL Server/Results to Grid.
      • 2. By the way, separate suggestion: make Results to Text Maximum Length of the output default  to 8192  (rather than 256) to avoid truncations, that are not obvious.
    • Microsoft Feedback
      • Posted by Seshagiri ( Microsoft ) on 2011-May-31st 7:33 AM
        • Hi Michael,
          This is related to the connect item 381955. We evaluated the feedback carefully and are able to reproduce the cases you listed. However because of the following reasons we are unfortunately not able to attend to this request:

          • 1. new lines in grid
            When using the results to grid option, we use the standard Windows grid control to display the results. This grid control treats each cell value as a plain text and hence the new line characters are ignored. The Save as function when executed from the results section just takes the content from the grid as it is and hence the newline is not found in the new file created from the grid. However if you use the results to text option or the results to file the new line characters are retained. SSMS is not really intended to be a reporting tool, so we are unable to spend much time on the result formatting, especially if some workarounds exist like mentioned above.
          • 2. Max characters when using results to text
            The number specified here is used to preallocate memory for the text column data. A larger value for default could mean lot of unnecessary memory being allocated but not used. Hence it is left to the user to increase the value based on their needs.
            I hope the above sounds reasonable. I am currently closing the issue as a wont-fix. However in case there is anything not mentioned previously please let us know and we will surely take a relook.
          • Thank you
            Seshagiri
            PM, SSMS

Sql Server Management Studio ( SSMS ) – Query Results – Max Column Length

Background

Ran a query that gives me the most expensive queries.

As I try to capture the SQL Column I am finding out that the results is chopped off.

 

TroubleShooting

SQL Server Management Studio

Query Results

Access the Options Menu and transversed to Query Results \ SQL Server \ Results to Grid

resultstogrid

And, determine the max Non XML data is 65535 Characters.

 

What is Column Length?

Changed the query to emit the column length…

statementlength

Explanation

  1. Our most expensive query is 147,020 characters
  2. Some other queries are 16,736 and another is 11,133

 

StackOverflow

Googled for a fix and as always found one via Stackoverflow.

Link

Copy Current Cell

stackoverflow_question-11897950

Visualize

stackoverflow_question-11897950_visualize

 

SSMSBoost

Journeyed to SSMS Boost web site here

Available Binaries

Downloaded version Specific

download

Download & Install

Downloaded and Installed SSMS Boost

 

License

Retrieve User/machine Has Token

Retrieved User/Machine Token through running SSMS and accessing menu items SSMSBoost / About License.

ssmsboost-aboutlicense-croppedup

 

Request Community License

Requested Community License by going here.

requestingfreelicense-cropped-up

Here is the Free License Request Form that comes up.

requestlicense-cropped

 

Received License

Received license over email

 

Applied License

 

Usage

Ran the query again.

Grid

copycurrentcell1to1-cropped-up

 

Select Grid Cell

Selected the Grid Cell and right clicked on our selection.

Chose “Copy current cell 1:1” from the dropdown menu.

We were able to capture our cell’s content.

 

Editor

NotePad++

Was able to paste into Notepad++.

Contents

notepadplusplus-summary

Summary

In Notepad++, accessed the menu item View \ Summary.

Here is what our summary window looks like:

notepadplusplus-summary-20170216-0604pm

Explanation:

We have about over 43 thousand characters.

 

Dedicated

Dedicated to SSMSBoost’s directory Andrei Rantsevich.

 

Summary

From the Query’s contents:

(@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),@p__linq__2 bit,@p__linq__3 uniqueidentifier,@p__linq__6 bit,@p__linq__4 uniqueidentifier,@p__linq__5 bit)SELECT
[Project40].[Id] AS [Id],
[Project40].[C1] AS [C1],
[Project40].[Id2] AS [Id1],
[Project40].[Id5] AS [Id2],
[Project40].[Id1] AS [Id3]

we can see that the query is not handwritten, but generated via Microsoft .Net Linq.

Google Drive – Google Sheets – Pasting Tables

 

Background

We all take for granted how seamlessly Microsoft Products work together.

Take for instance, I use Microsoft SQL Server Management Studio to run a query and I will like to prepare a document from the query’s result.

I simply copy the Output Grid’s content, launch Microsoft Word or Excel, and pasted it.

Through the magic of OLE or whatever they call it these days, the data is well received and formatted in the receiving Office App.

 

Google Drive

These days my main sharing tool is WordPress for public consumption and Google Docs for private data.

 

Here is the genesis of our Problem

SQL Server Management Studio ( SSMS )

Grid

Here is output of a query I ran in SSSM

ssms

Explanation

  1. Nice and visually appealing for an Engineer

 

Google Drive – Google Docs

Here is what things look like when pasted into Google Docs..

copiedintogoogledocs-20170120-1031am

 

What to do

  1. Looked for Convert to Table
  2. Insert Text as table
  3. Import Text

 

Nothing helpful.

 

Solution – 01

Google Drive – Google Sheets

Created a new file, rather than Document went with Sheets

copiedintogooglesheets-2017012-1039am

 

Explanation

  1. Making progress
    • Kept the grid or columns paradigm
    • That is things are not jumbled well, with text intertwined together

 

Google Drive – Copy From Google Sheets Into Google Docs

Here we copied the cells from Google Sheets Into our original Google Docs

copiedfromgooglesheetintodocs-20170120-1044am

 

Explanation

  1. Thankfully things are kept neatly arranged in a Columns
  2. Need to get rid of some extra columns and that is doable

 

Google Drive – Google Docs – Table – Delete Extra Columns

Got rid of the extra columns using the menu item Table / Delete Columns.

Steps

  1. Inside the Google Doc
  2. Select the extra columns
  3. And, use menu item Table / Delete Columns

 

googledocs-columnsdeleted-20170120-1053am

Format the Table

One of the great things about these Google Products such as Chrome and Google Drive is that they are extensible and have such a nice and rich 3rd party ecosystem.

 

Adds On

Table Formatter

I already have Table Formatted installed and so let us initiate it by accessing Google Docs menu items “Add-ons” \ “Table Formatter”.

 

Google Docs – Menu – Add-Ons

addson

 

Add-On – Table Formatter – Default Templates

Here are some of the Default Templates available

tableformatter-20170120-1057am

Customize Table with Add-On – Table Formatter

Select the Google Docs’s tale and choose the one of the Formatting Choices.

Here is our colored layout.

googledocs-tableformatted-20170120-1103am

 

Summary

  1. Copy SSMS Grid Data into Clipboard
  2. Create a new Google Sheet ou use existing one
    • Paste copied into Sheet
  3. Create a new Google Doc
    • Select data from Google Sheet
    • Copy into Clipboard
    • And, paste into Google Doc
  4. In Google Docs
    • Using 3rd Party Add Ons such as Table Formatter format Table

Solution – 02

Outline

  1. Copy SSMS Grid output into Clipboard
  2. Use Microsoft Excel
    • Launch Microsoft Excel
    • Copy Grid’s data into Excel
    • If you like the way Excel Formats Table, use Excel’s Table Formatting functionality
      • Using F8, make Sheet’s column into actual table
        • Be sure you have Column Headers and all
  3. Create or use New Google Docs
    • Copy Table’s content from MS Excel
    • Paste into Google Doc

 

In MS SSMS, Copying with Headers

ssms-copywithheaders-20170120-1126am

Explanation

 

In MS Excel, Pasted SSMS Grid

excel-pastedgrid-20170120-115am

 

In MS Excel, Create Table

Here is the panel displayed upon clicking on F8 and thus initiating the “Create Table” options

createtable-20170120-1119am

Explanation

  1. Please pay attention to the “My table has headers” option

 

In MS Excel, Formatted Table

excel-createtabled-20170120-1120am