Archive for the ‘Uncategorized’ Category

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
-- ============================================
       o.name AS Object_Name,
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%';

-- ============


Read Full Post »

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:


Read Full Post »

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.


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)

            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
                'logon failed

            End If
        Catch ex As 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:


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”:


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


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


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


Read Full Post »

-- 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:
/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 »

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 »

-- 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) 
    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)

Read Full Post »

--Please note section 3 nneds to be run each time an Insert, Update or Delete is performed. 
--If section 3 is not run after each DML then only the last DML can be garaunteed.

/*  Section 1: Run to reset. This recreates the tables and makes it ready from scratch

if object_id('tempdb..##org') is not null drop table ##org

if object_id('tempdb..##hist') is not null drop table ##hist

create table ##org
	id int identity 
	, name varchar (50)
	, age int 

create table ##hist
	sur int identity 
	, id int 
	, name varchar (50)
	, age int 
	, isCurrent bit 
	, EffectiveFrom datetime
	, EffectiveTo datetime

insert into ##org values 
	, ('dick','29')
	, ('harry','40')

insert into ##hist
select * , 1, GETDATE(), '9999-1-1'
from ##org

select * from ##org 
select * from ##hist


/*		Section 2: Perform an opertion. Perform an Insert, Update or Delete 

-- insert 
insert into ##org values ('ron','19')

-- update 
update ##org set name = 'jerry' where id =1

-- delete 
delete from ##org where id = 3

select * from ##org  
select * from ##hist


--Section 3: Run this each time an above DML is performed and the changes will be saved in the history table (##hist)

insert into ##hist (id, name, age, isCurrent, effectiveFrom, effectiveTo) 
select id, name, age, 0 as  isCurrent, effectiveFrom, effectiveTo
	merge ##hist as target 
		using ##org as source 
			on		source.id = target.id 

	when MATCHED  
				and isCurrent = 1
				and exists -- UPDATE - when record exists but some other fields changed (the bleow also handles nulls)
						(select source.id, source.name, source. age
						select target.id, target.name, target. age)

	then  -- UPDATE - when record exists but some other fields changed
		UPDATE set
			Target.name = Source.name
			, Target.age = Source.age
			, Target.isCurrent = 1 
			, Target.EffectiveFrom = getdate()
			, Target.EffectiveTo = '9999-1-1' 

	when NOT MATCHED BY TARGET -- INSERT -- new records exits in the source 
		INSERT --(id, name, age, isCurrent, EffectiveFrom, EffectiveTo) 
		values (source.id, source.name, source.age, 1, getdate(), '9999-1-1')
	when NOT MATCHED BY SOURCE -- DELETE -- if a record is not found then UPDATE EffectiveTo date as getdate() adn set isCurrent to 0
		UPDATE set
			Target.isCurrent = 0
			, Target.EffectiveTo = getdate()
	-- Getting the previous row before the update is comminted 
	output $ACTION ActioinOut
			,  deleted.id, deleted.name, deleted.age, inserted.isCurrent as IsCurrent, deleted.effectiveFrom, getdate() as effectiveTo
	) RowBeforeMerge
where RowBeforeMerge.ActioinOut = 'Update'
	and RowBeforeMerge.isCurrent = 1 -- this helps not to write the the row that is deleted twice
									-- when DELETED inserted.isCurrent is equal to 0

select * from ##org  
select * from ##hist

Read Full Post »

Older Posts »