FetchMyTip

Thursday, April 5, 2018

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)

Thursday, March 29, 2018

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.

Monday, August 28, 2017

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:

Monday, August 21, 2017

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:

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.
Start Windows Powershell (use Run as Administrator).
In Powershell, type Add-PSSnapin Microsoft.Adfs.Powershell.
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.
Review the value for TokenLifetime. The value is measured in minutes.
To change the value, type Set-ADFSRelyingPartyTrust –Targetname “CRM Claims Relying Party” –TokenLifetime newvalue where newvalue is the value in minutes.
Repeat this process for the external (IFD) relying party trust.

Source:

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/

date type IE-11 limitation

Working in MVC project and using different browser's, I ran into this issue with the date picker supported natively in Chrome but not in IE.

@Stephen Muecke pointed the reason of the problem:

Refer comparision here. The type="date" attribute is not supported in either IE-11 or FireFox-38 so you're out of luck for the time being.

@section Scripts {
    /* ... */
    <script type="text/javascript">
        /* ... */
        if (!Modernizr.inputtypes.date) {
            $('.datepicker').datepicker();
        }
    });

</script>

Source
https://stackoverflow.com/questions/31099367/datetime-picker-doesnt-appear-in-internet-explorer-and-firefox-with-bootstrap

C# date comparison MVC Razor

Issue:
I had a problem with a conditional test which was failing. I was trying to compare two dates but the condition was not firing in my view.

claimPayments.Payment_Date.Value.ToString("dd-MMM-yyyy") == temp.ToString("dd-MMM-yyyy")

changed it to

claimPayments.Payment_Date.Value.Ticks.Equals(temp.Ticks)

This fixed the problem. Using the ticks function gets around the issue of trying to match date formats.

Thursday, June 8, 2017

MVC Caching issue

Issue:

I had an issue with a MVC view rendering a modal page. The problem was that the AJAX call was not refreshing the view when it was re-rendered on the page. The change to the database is occurring but the view refresh was not showing the latest information.

var data = { "claimNumber": activeClaimID };
$.ajax
(
{
url: '@Url.Action("IndexPartial", "CaseFileViewModels")',
async: false,
type: 'GET',
data: data,
success: function (result) {

hideWaitDialog();

//Clear out the entire modal block
$('#modal-container').empty();
$('#modal-container').append(result);
ret = true;
},
error: function (xhr, status, err) {
alert(xhr.responseText);
alert(status);
},
}
);

My problem was that I did not assign the attribute Output Cache to the ActionResult.
Once I disabled the default hander on ActionResult then my view began to refresh.

[OutputCache(Location = System.Web.UI.OutputCacheLocation.None)]
public ActionResult IndexPartial()
{....}

Source:

https://stackoverflow.com/questions/20917398/view-not-refreshing-after-ajax-call

Tuesday, June 6, 2017

Jquery attr method

Issue

I had a checkbox with imbedded attributes. I needed to get these values out of based on a click event
The key to getting the specified values was the use of the attr method.

<input name="SavePaySchedule" id="SavePaySchedule1" type="checkbox" value="true" selectedscheduleitem="1f3dc523-c466-4dc5-bb52-09848a79c9fd" schedulecountry="44207e0b-d4ca-4bfd-b1b3-2f28a2c6d5ef">

<script type="text/javascript">
$('#SavePaySchedule').click(function (o) {
var $input = $(this);
var scheduleCountry = $input.attr('scheduleCountry');
var selectedScheduleItem = $input.attr('selectedScheduleItem');
var activeState = $input.prop('checked');

});
</script>

Source

http://api.jquery.com/attr/

Linq to SQL distinct

Issue:

I needed to get a distinct value from a query like

select distinct o.ProgramID,t.Program
from table1 o
inner join table2 t on o.ProgramID=t.ProgramID
order by t.Program

var result = (from o in table1
              join t in table2 on o.ProgramID equals t.ProgramID
              orderby t.Program
              select new { o.ProgramID, t.Program }).Distinct();

https://stackoverflow.com/questions/4318909/linq-to-sql-distinct-and-orderby

Wednesday, April 19, 2017

MVC razor decimal formatting

Issue:

Trying to get a decimal property of an EF generated model to display more than default 2 decimal place accuracy.

Use N6 as the numeric format string.

myDecimal.ToString("N6");

string.Format("{0:N6}", myDecimal);

Source:

http://stackoverflow.com/questions/8412882/c-sharp-show-a-decimal-to-6-decimal-places

The entity cannot be constructed in a LINQ to Entities query

Issue:

I had a problem casting an anonymous type to a type of a ModelView class that I had defined.
The query worked but failed on my conversion to the type PayScheduleCurrencyModelView.

Incorrect:

var currencyDetail = (from hn in db.Host_Nation
join cur in db.Currencies on hn.Currency_ID equals cur.Currency_ID
where hn.Host_Nation_ID.Equals(country)
select new PayScheduleCurrencyModelView
{
Country_ID = hn.Host_Nation_ID,
Country = hn.Host_Nation_Name,
Currency_Code = cur.Code,
Currency_Description = cur.Description,
Exchange_Rate = cur.Rate
}).AsEnumerable().Select(x => new PayScheduleCurrencyModelView
{
Country_ID = x.Country_ID,
Country = x.Country,
Currency_Code = x.Currency_Code,
Currency_Description = x.Currency_Description,
Exchange_Rate = x.Exchange_Rate
}).FirstOrDefault();

Correct:

var currencyDetail = (from hn in db.Host_Nation
join cur in db.Currencies on hn.Currency_ID equals cur.Currency_ID
where hn.Host_Nation_ID.Equals(country)
select new
{
Country_ID = hn.Host_Nation_ID,
Country = hn.Host_Nation_Name,
Currency_Code = cur.Code,
Currency_Description = cur.Description,
Exchange_Rate = cur.Rate
}).AsEnumerable().Select(x => new PayScheduleCurrencyModelView
{
Country_ID = x.Country_ID,
Country = x.Country,
Currency_Code = x.Currency_Code,
Currency_Description = x.Currency_Description,
Exchange_Rate = x.Exchange_Rate
}).FirstOrDefault();

The fix was to drop the implied cast in the linq to sql query. This cast was causing the EF error.

Source:

http://stackoverflow.com/questions/12916080/the-entity-or-complex-type-cannot-be-constructed-in-a-linq-to-entities-query