Tuesday, July 25, 2006

Clearing memory used by a process

Firstly declare this somewhere in the code

#Region "Stuff for Closing the Processes"
'# to close all the resources
Private Declare Auto Function SetProcessWorkingSetSize Lib "kernel32.dll" (ByVal procHandle As IntPtr, ByVal min As Int32, ByVal max As Int32) As Boolean

Public Sub SetProcessWorkingSetSize()
Try
Dim Mem As Process
Mem = Process.GetCurrentProcess()
SetProcessWorkingSetSize(Mem.Handle, -1, -1)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
#End Region


Then call it like this from where you need to in the code

If (Environment.OSVersion.Platform = PlatformID.Win32NT) Then
SetProcessWorkingSetSize(Process.GetCurrentProcess().Handle, -1, -1)
End If

Wednesday, July 19, 2006

Creating KEY row in SQL to compare two tables

-- Insert the Details into tblClientProducts

SELECT A.CLIENT_ID, A.CREDITPRODUCTID, cast(A.TERM as nvarchar),
cast(A.OFFERMONTHS as nvarchar),
cast(A.HOLIDAYMONTHS as nvarchar),
cast(A.PPCODE as nvarchar),
cast(A.APR as nvarchar), cast(A.COMMISSION as nvarchar)
FROM viewstgclientproducts A WHERE
A.CLIENTCODE + '*' + A.CREDITPRODUCTCODE + cast(A.TERM as varchar) + '*' + cast(A.OFFERMONTHS as varchar) + '*' + cast(A.HOLIDAYMONTHS as varchar)
NOT IN
(select c.client_code + '*' + d.creditcode + cast(b.term as varchar) + '*' + cast(b.specialoffer as varchar) + '*' + cast(b.paymentholiday as varchar) from tblclientproducts b
INNER join tblipf_clients con b.clientid = c.client_id
INNER join tblipfcreditcodes d on b.creditproductid = d.creditproductid)

SQL NOT IN statement syntax

-- Insert statement with a NOT IN Select statement

Insert Into tblONE (CreditCode, CreditDescription) (SELECT DISTINCT A.CREDITPRODUCTCODE, A.CREDITPRODUCTNAME FROM tblTWO A WHERE A.CREDITPRODUCTCODE NOT IN (SELECT B.CREDITCODE FROM tblONE B))

Thursday, July 13, 2006

How to cancel O2 contract in UK postal address

Hi all,

well I have decided to end my contract with O2 and obviously it is difficult. I have had to call them several times, but finally the bottom line is if you want to cancel your contract you need to give them 30 day notice in writing stating the phone number and your name and you need to write to this address:

O2 UK Ltd
PO Box 242
Houghton Regis
DUNSTABLE
LU5 5WA

Monday, July 10, 2006

DTS Package run from stored procedure sp

To run DTS from stored procedureyou need to use the following code:

EXEC master..xp_cmdshell 'DTSRun /S "(local)" /U "sa" /P "password" /N "IPF Client Package Upload" /G "{8A50D656-0BA7-4687-AA24-71DA4FD35039}" /W "0"'

To get the DTSRun command line generated use the dtsrunui utility

for more info on dtsrunui utility :

http://www.sqldts.com/default.aspx?301