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

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