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.

Open Oracle dmp file and export to SQL DDL file

Problem:

Working a project to migrate a system off of Oracle and onto SQL Server.
The current host responded to my request for DDL with a dmp file!  The dmp
file is a binary format which is used by Oracle toolset to import and export database
content and structures.  The point being that without Oracle installed on a system
you cannot read this file.  Minor point we are not an Oracle shop and so do not
have the in-house expertise.  Say hello to Prof Google.

Solution:

1.) Download Oracle OracleXE112_Win64.zip.  Available on the Oracle site once you register.
2.) Install this onto your box
3.) Most likely will install at c:\oraclexe\app\oracle
4.) Get your dmp file (YourDBA.dmp)
5.) Place it into following location: c:\oraclexe\app\oracle\admin\xe\dpdump
Note: You can place this dmp file in another folder but you will be required to configure
the database settings.(https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_overview.htm#SUTIL819)
6.) Start up a command prompt and navigate to directory: c:\oraclexe\app\oracle\product\11.2.0\server\bin
7.) Issue following command
impdp 'system/root AS SYSDBA' show=Y file=YourDBA.dmp


End result was the creation of the YourDBA.sql which gives us the file we wanted in the first place!

Reference:

1. https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_overview.htm#SUTIL819
2. https://docs.oracle.com/database/122/SUTIL/instant-client-for-SQL-loader-export-import.htm#SUTIL-GUID-FF1B6F75-09F5-4911-9317-9776FAD15965
3. https://stackoverflow.com/questions/16961295/how-to-get-an-oracle-sql-text-from-a-dmp-file

ADFS 2.0 Token based Authentication Token Expiry

Token based Authentication Token Expiry

Tokens issued by AD FS 2.0 expire after a default time of 60 minutes. This requires users to be re-authenticated (for internal access) or to sign in again (for IFD access). The token lifetime is set separately for each relying party trust (internal and external).
To check the life time, complete the following steps on the AD FS 2.0 server:

1. Check the names for the relying party trusts in the AD FS 2.0 Management Console and use the appropriate names in the following steps.
2. Start Windows Powershell (use Run as Administrator).
3. In Powershell, type Add-PSSnapin Microsoft.Adfs.Powershell.
4. To view information for the internal relying party trust, type Get-ADFSRelyingPartyTrust –Name “CRM Internal Claims Relying Party”. Replace CRM Internal Claims Relying Party with the appropriate name for your ADFS configuration.
5. Review the value for TokenLifetime. The value is measured in minutes.
6. To change the value, type Set-ADFSRelyingPartyTrust –Targetname “CRM Claims Relying Party” –TokenLifetime newvalue where newvalue is the value in minutes.
7. Repeat this process for the external (IFD) relying party trust.

Source:

1. http://www.fkbase.info/node/163
2. https://technet.microsoft.com/en-us/library/gg188586(v=crm.6).aspx
3. https://samlman.wordpress.com/2015/03/01/setting-the-login-token-expiration-correctly-for-sharepoint-2010-saml-claims-users/
4. http://stackoverflow.com/questions/23692326/adfs-2-0-microsoft-identityserver-web-invalidrequestexception-msis7042

Working with MVC5 and bootstrap DateTimePicker (eonasdan)

Issue:

Install-Package Bootstrap.v3.Datetimepicker.CSS



bundles.Add(new ScriptBundle("~/bundles/bootstrap").Include(
                      "~/Scripts/bootstrap.js",
                      "~/Scripts/bootstrap-datetimepicker.min.js",
                      "~/Scripts/respond.js"));

bundles.Add(new StyleBundle("~/Content/css").Include(
                      "~/Content/bootstrap.css",
                      "~/Content/bootstrap-datetimepicker.min.css",
                      "~/Content/site.css"));


<script type="text/javascript">
    $('#DateTest').datetimepicker(
        {
   //v3 turn off the time selector
            format: 'DD-MMM-YYYY'
            //pickDate: true                 //en/disables the date picker
           // pickTime: false,                 //en/disables the time picker
            //useMinutes: false,              //en/disables the minutes picker
           // useSeconds: false,              //en/disables the seconds picker
          //  startDate: moment().subtract('days', 1)// a minimum date

        });
</script>

Source:

https://blog.jigsawpieces.me/2014/07/23/lbd-adding-datetimepicker-control-to-mvc-project/
http://eonasdan.github.io/bootstrap-datetimepicker/Options/