Power BI – Rolling Calendar (MCode)

If you open up a “Blank Query” from the “Get data” tab and in the function bar just typed it would give the following literal:

=#date(2018,1,1) 

This would just give you one 1 date as “1 January 2018”

To generate a rolling date press the fx button and then type the formula below:

=List.Dates(Source, Number.From(DateTime.LocalNow()) - Number.From(Source), #duration (1,0,0,0))

This is what the above formula is doing:

• Calculate the current day.
• Compare that with the literal that we created in the beginning and that will be our starting date.
• Based on the difference between those two dates list out all the individual values where the duration is 1 day.

This becomes a list of daily granularity between the given literal date and today.

Every time this query is refreshed that would give us a up-to-date list.

Go to the “Transform” tab and click on “To Table” in the Convert section. From the prompt choose:

• Select or enter delimiter: “None”
• How to handle extra columns: “Show as errors”
• Then click “OK”

Format column as “Date” and then rename the column of your choice.

Read Full Post »

T-SQL: Search objects (Table, Column, Stored Proc, Funciton, Triggers) (optionally in Linked Server)

If we do not want to search in within LINKED SERVER then please remove the text “[LinkedServer].[DatabaseName].” from all the below snippets

--- Columns and Tables
-- ====================
SELECT t.name as TableName, c.name as ColumnName
FROM [LinkedServer].[DatabaseName].sys.columns c
    INNER JOIN [LinkedServer].[DatabaseName].sys.tables t
	   ON c.object_id = t.object_id
WHERE c.name like '%ColumnName%'
    -- t.name like '%TableName%'

--- Stored Procedures, Funcitons and Triggers
-- ============================================
SELECT DISTINCT
       o.name AS Object_Name,
       o.type_desc
FROM [LinkedServer].[DatabaseName].sys.sql_modules m
    INNER JOIN [LinkedServer].[DatabaseName].sys.objects o
         ON m.object_id = o.object_id
WHERE m.definition Like '%MySearchString%';

--- VIEWS
-- ============
SELECT *
FROM [LinkedServer].[DatabaseName].INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION like '%TableName%'

 

Read Full Post »

Resolving “GatherAllFilesToPublish” error after upgrading Visual Studio ASP.Net project

I have a ANP.NET VB Project built with VS2010.
It is 2018 so I have upgraded to SQL Server 2016 and VS2017.
After opening the 2010 project file I was told I did not have the SQL Express installed that is required.
It had a link to the prompt and I have used it to download SQL Express 2014 (didn’t look for 2016 and went with the suggestion). It looked fine and I can see the project with all the files within it and are able to open it.

The real problem started when I wanted to deploy the project and it errored saying: “The target GatherAllFilesToPublish does not exist”. After pulling my hairs for hours and tyring out different unsuccesful suggestions google had to offer I came up with my own solution.

1. I created a new project with VS2016.
2. Then using WinMerege compared the .vbproj file.
3. I found that the below was missing and I copied and pasted it to where it was missing and then it was all working fine:

10.0
$(MSBuildExtensionsPath32)\Microsoft\VisualStudio\v$(VisualStudioVersion)

Read Full Post »

VB ASP.Net – Impersonation & Accessing link with username and password. Reading TIFF with multiple images

I needed a way to access a shared location that prompted for user name and password adn downloaded/diaplyed a TIFF image.
I had the credentials with me but to give the users a seemless experience I had to embbed that username and password.

I tired the impersonation from the link below but it generated “An error occurred while attempting to impersonate.” No matter what I tried it did not work. It couold that there was a policy that didn’t allow impersonation.

https://www.experts-exchange.com/questions/29062577/I-need-to-perform-user-impersonation-using-VB-net-to-a-local-user-account-on-a-remote-computer.html

Then from vairous different research I figures a way of just. Using the below code I could pass the username and password to access the location.

    Private Declare Auto Function LogonUser Lib "advapi32.dll" (ByVal un As String, ByVal domain As String, ByVal pw As String, ByVal LogonType As Integer, ByVal LogonProvider As Integer, ByRef Token As IntPtr) As Boolean
    Public Declare Auto Function CloseHandle Lib "kernel32.dll" (ByVal handle As IntPtr) As Boolean

  Dim tokenHandle As New IntPtr(0)
        Try

            If LogonUser("username", "domain", "password", 2, 0, tokenHandle) Then
                Dim newId As New WindowsIdentity(tokenHandle)
                Using impersonatedUser As WindowsImpersonationContext = newId.Impersonate()

			'perform impersonated commands

                End Using
                CloseHandle(tokenHandle)
            Else
                'logon failed

            End If
        Catch ex As Exception
            'exception

        End Try

Now I needed to display the image in an asp.net Image control. I soon found that the control cannot read TIFF directly so I used the below technique:

https://www.mindstick.com/Articles/478/dynamically-loading-image-in-image-control-in-asp-dot-net

But then I realised there was more restriction because copying the file over using File.Copy was locking the file until I closed the window. I realised I could may be stream the image in the browser since the files can be accessed from the IIS server directly.
I used the below technique from the user “afetchko”:

https://forums.asp.net/t/507202.aspx?can+I+load+image+from+URL

Once that was completed I discovered that a Tiff could contain more than one page. Then I found and implemented the below:

https://www.ryadel.com/en/multipage-tiff-files-asp-net-c-sharp-gdi-alternative/

Fianlly I stiched the pages into one image usign the solution below from the user “Pascalsz”

https://stackoverflow.com/questions/7206510/combine-two-images-into-one-new-image?lq=1

Below is a helpful tip on how to get the library:

http://www.dreamincode.net/forums/topic/131847-trying-to-get-using-systemwindowsmediaimaging%3B-to-work/

Read Full Post »

(WIP) SSIS – Publish in Command LIne using DTExec with Parameters

-- command line to execute a package 

-- Package model deployment was sued ins SSIS 2008 and earlier.
-- In SSIS 2012 to 2014 you could no deploy individual packages from IDE os instead you had to use DTExec.
-- Ther are multiple ways packages can e deployed:
-- 1. Server
-- 2. File System 

--1. Pakcege in a Server
---------------------------
dtexec /sq MyPackageName /ser MyServerName /va

 -- /va stands for validate. This si used when we do not want to EXECUTE a package but rather jsut validate it

start /min -- windows command to start an application minimised

dtexec -- if the executable cannot be found then include the whole path (whereever SSIS/dtexec is installed) as below:
"c:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTEXEC.exe"

/file the attribute to indicate the dtsx file will follow:
"C:\SSIS\TestPackage.dtsx"

/SET --use this only if variables that need to be initiased (this is also known as parameter passing)
"\Package.Variables[User::Variable name].Properties[Value]"

;%1 -- %1 is first the argument that will be passed from cmd. To hard code this do the following:
    ;"hard coded value".

for second or nth parameter follow the same principle as the first one above but increase the number by 1:

/SET "\Package.Variables[User::Second variable].Properties[Value]",%2
.
.
.
/SET "\Package.Variables[User::nth variable].Properties[Value]",%n

Read Full Post »

File Recovery from NAS HDD

I had a HDD taken out of my NAS. Although the HDD was fine but NAS was loosing its connectivity capabilities and eventually not not working one day.

I took the HDD out and found through using different software from Windows that it had Ext4 and RAW file format.

I have tried many software but I was not being able to see my folders and files but rather some other folders and files that I did not recognise (these were the files and folders used by the NAS).

After much looking further I stumbled upon http://www.reclaime.com/.

I tried the trial software and within a few clicks I could see all my files and folders. I was amazed to able to browse the folders and files that belonged to me. I was also really amazed at how easy it was to use the software.

I went ahead and bought the software. Although it was pricey (£190.93) it exactly did what it said on the tin.

 

Read Full Post »

TSQL – Split Function

-- Please note that this is not my code and I have found it somewhere on the net

CREATE FUNCTION [dbo].[udf_Split] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(data NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1
       
        INSERT INTO @output (data)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)
        
    END 
    RETURN 
END

Read Full Post »