Suhail Kaleem

RemovePreviousVersions not working

0

In visual studio 2010 while creating setup package when you select RemovePreviousVersions = true

even then the old application installed is not removed  in order to fix that

save the below vbs code in project directory


Dim objInstaller
 Dim objDatabase
 Dim objView
 Dim objResult

Dim strPathMsi

If WScript.Arguments.Count <> 1 Then
 WScript.Echo "Usage: cscript fixRemovePreviousVersions.vbs "
 WScript.Quit -1
 End If

strPathMsi = WScript.Arguments(0)

Set objInstaller = CreateObject("WindowsInstaller.Installer")
 Set objDatabase = objInstaller.OpenDatabase(strPathMsi, 1)
 Set objView = objDatabase.OpenView("UPDATE InstallExecuteSequence SET Sequence=1450 WHERE Action='RemoveExistingProducts'")

WScript.Echo "Patching install sequence: UPDATE InstallExecuteSequence SET Sequence=1450 WHERE Action='RemoveExistingProducts'"
 objView.Execute
 objDatabase.Commit

WScript.Quit 0

save the file with name  fixRemovePreviousVersions.vbs

now in vs 2010 set PostBuildEvent  to  cscript “$(ProjectDir)fixRemovePreviousVersions.vbs $(BuiltOuputPath)”

PostBuildEvent  can be found in setup project properties

Now build you msi and it will patch the msi and now the previous installed version will be removed.

Get months list between two dates

0

Following function  will  get a list of months  with days remaining in those months  when start date and end dates are provided


Shared Function GetMonthsList(StartDate As Date, EndDate As Date) As ArrayList

Try

Dim dt As Date = StartDate
Dim edt As Date = EndDate

Dim ar As New ArrayList
Dim str As New StringBuilder

While dt < edt

str.Append(dt.Date.ToString("MMMM yyyy"))
str.Append(",")
Dim dt2 As Date = dt.AddMonths(1)

If dt2 > edt Then
Dim minusdays As Integer = DateDiff(DateInterval.Day, dt, edt)
str.Append(minusdays + 1)
ar.Add(str.ToString)
Exit While
End If

Dim remainingdays As Integer = System.DateTime.DaysInMonth(dt.Year, dt.Month) - dt.Day
str.Append(remainingdays + 1)

ar.Add(str.ToString)
dt = dt.AddDays(remainingdays + 1)
str.Clear()
End While

Return ar

Catch ex As Exception
Throw ex
End Try
End Function

Use can use this functions as


GetMonthsList(Date.Parse("1/4/2012"),Date.Parse("4/30/2012"))

this will output

January 2012,28
February 2012,29
March 2012,31
April 2012,30

Rebuild Indexes of all tables in a single MS SQL database

0
  1. Rebuilds indexes on smallest tables first, allowing the maximum number of indexes to be rebuilt in the shortest amount of time.
  2. Real time progress updates, allowing you to estimate how much time is remaining before completion.
  3. Correctly handles multiple schemas, a common flaw in other scripts.
SET NOCOUNT ON
GO

--Set the fillfactor
DECLARE @FillFactor TINYINT
SELECT @FillFactor=80

DECLARE @StartTime DATETIME
SELECT @StartTime=GETDATE()

if object_id('tempdb..#TablesToRebuildIndex') is not null
begin
drop table #TablesToRebuildIndex
end

DECLARE @NumTables VARCHAR(20)

SELECT
s.[Name] AS SchemaName,
t.[name] AS TableName,
SUM(p.rows) AS RowsInTable
INTO #TablesToRebuildIndex
FROM
sys.schemas s
LEFT JOIN sys.tables t
ON  s.schema_id = t.schema_id
LEFT JOIN sys.partitions p
ON  t.object_id = p.object_id
LEFT JOIN sys.allocation_units a
ON  p.partition_id = a.container_id
WHERE
p.index_id IN ( 0, 1 ) -- 0 heap table , 1 table with clustered index
AND p.rows IS NOT NULL
AND a.type = 1  -- row-data only , not LOB
GROUP BY
s.[Name],
t.[name]
SELECT @NumTables=@@ROWCOUNT

DECLARE RebuildIndex CURSOR FOR
SELECT
ROW_NUMBER() OVER (ORDER BY ttus.RowsInTable),
ttus.SchemaName,
ttus.TableName,
ttus.RowsInTable
FROM
#TablesToRebuildIndex AS ttus
ORDER BY
ttus.RowsInTable
OPEN RebuildIndex

DECLARE @TableNumber VARCHAR(20)
DECLARE @SchemaName NVARCHAR(128)
DECLARE @tableName NVARCHAR(128)
DECLARE @RowsInTable VARCHAR(20)
DECLARE @Statement NVARCHAR(300)
DECLARE @Status NVARCHAR(300)

FETCH NEXT FROM RebuildIndex INTO @TableNumber, @SchemaName, @tablename, @RowsInTable
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @Status='Table '+@TableNumber+' of '+@NumTables+': Rebuilding indexes on '+@SchemaName+'.'+@tablename + ' ('+@RowsInTable+' rows)'
RAISERROR (@Status, 0, 1) WITH NOWAIT  --RAISERROR used to immediately output status

SET @Statement = 'ALTER INDEX ALL ON ['+@SchemaName+'].['+@tablename +'] REBUILD WITH (FILLFACTOR = '+CONVERT(VARCHAR(3), @FillFactor)+' )'
EXEC sp_executesql @Statement

FETCH NEXT FROM RebuildIndex INTO @TableNumber, @SchemaName, @tablename, @RowsInTable
END

CLOSE RebuildIndex
DEALLOCATE RebuildIndex

drop table #TablesToRebuildIndex

Print 'Total Elapsed Time: '+CONVERT(VARCHAR(100), DATEDIFF(minute, @StartTime, GETDATE()))+' minutes'

GO
Go to Top