SQL Script to display non-printable ASCII values in a varchar string

I had an issue trying to match a field with hidden characters.  I needed to see what non-printable
chars were present.

This script helped me out.

Declare @word nvarchar(max)
Select @word = 'Hello This is the test'

SELECT
@word = REPLACE(REPLACE(cdoLog.Remark,CHAR(13),''),CHAR(10),'')
FROM dbo.ContractDeliveryOrderRemarksLog cdoLog
WHERE
cdoLog.RemarkTypeID IN (2,4)
AND
cdolog.Remark LIKE '%Expired%'
AND
cdoLog.ContractID IN (5095,5083)
AND
ID=94252
--ID=93825
ORDER BY cdoLog.ContractID;



;with cte (Number)as
(Select 1
union all
select Number +1 From cte  where number <len(@word)
)
select Number,Letter,ASCII(Letter) from Cte Cross apply (Select SUBSTRING(@word,number,1 ) ) as J(Letter)

Extracting BLOB data to files

Trying to get a file out of a BLOB field and came up with this script to do it.

--Execute this once before running the following script.
--sp_configure 'show advanced options', 1;
--GO
--RECONFIGURE;

--GO
--sp_configure 'Ole Automation Procedures', 1;
--GO

--RECONFIGURE;
--GO

declare @init int

declare @file varbinary(max) = CONVERT(varbinary(max),(select FILE_CONTENTS_TX from DCOPS_January.DCOPS_OWNER.TESA_DB_DOCUMENTATION WHERE reference_ID = 44545
AND DOCUMENTATION_ID=413228)
)



DECLARE @FileName AS VARCHAR(50)


select @FileName=SOURCE_NAME_TX from DCOPS_January.DCOPS_OWNER.TESA_DB_DOCUMENTATION WHERE reference_ID = 44545
AND DOCUMENTATION_ID=413228


declare @filepath nvarchar(4000) = N'c:\temp\' + @FileName


SELECT @filepath


EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created
EXEC sp_OASetProperty @init, 'Type', 1;
EXEC sp_OAMethod @init, 'Open'; -- Calling a method
EXEC sp_OAMethod @init, 'Write', NULL, @file; -- Calling a method
EXEC sp_OAMethod @init, 'SaveToFile', NULL, @filepath, 2; -- Calling a method
EXEC sp_OAMethod @init, 'Close'; -- Calling a method
EXEC sp_OADestroy @init; -- Closed the resources


Source:




https://stackoverflow.com/questions/10325338/fastest-way-to-export-blobs-from-table-into-individual-files

Powershell in SSMS

I had a restored database of about 300GB and wanted to monitor its progress.  The Stats argument on the RESTORE command will give you some limited feedback but wanted something more.  This Powershell will do a running update every 10 seconds.

import-module sqlps

for(;;)

{

cls

invoke-sqlcmd -serverinstance apcgs0nhqu398mv\privweb -query "select SUBSTRING(text, charindex('[', text) + 1, charindex(']', text) - charindex('[', text) - 1) AS [DB], command, percent_complete from sys.dm_exec_requests cross apply sys.fn_get_sql(sql_handle) where command like 'restore%'"

start-sleep -seconds 10

}

SQL restores

Had to do some SQL Admin work and wanted to put this into my tip list.
The UI in SMSS will generate the script for you but I wanted it for my own
understanding.


RESTORE DATABASE [DCOPS_January]


FROM DISK = N'G:\SQL_Restores_PRIVWEB\DCOPS_OwnerJanuary18.bak' WITH FILE = 1,


MOVE N'DCOPS_Owner' TO N'D:\SQL_Data_PRIVWEB\DCOPS_January.mdf',


MOVE N'DCOPS_Owner_log' TO N'E:\SQL_Logs_PRIVWEB\DCOPS_January_log.ldf',


NOUNLOAD, REPLACE, STATS = 10



GO


This command lets you move the original file names which were backed up to your own name of choice.