Internet Sellout

I just moved from one Rackspace cloud server to another. The main purpose was to get IIS 8 so I could do TLS/SNI. I got a skimpy partition for the system and the preinstalled SQL Server Web Edition databases were taking up precious space so I moved them with help from this article:

http://technet.microsoft.com/en-us/library/ms345408.aspx

SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO


USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'K:\Data\tempdb\tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'K:\Data\tempdb\templog.ldf');
GO

SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'reportserver');
GO


USE master;
GO
ALTER DATABASE ReportServer 
MODIFY FILE (NAME = ReportServer, FILENAME = 'K:\Data\reporting\ReportServer.mdf');
GO
ALTER DATABASE ReportServer 
MODIFY FILE (NAME = ReportServer_log, FILENAME = 'K:\Data\reporting\ReportServer_log.ldf');
GO

SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'reportserverTempDB');
GO


USE master;
GO
ALTER DATABASE ReportServerTempDB 
MODIFY FILE (NAME = ReportServerTempDB, FILENAME = 'K:\Data\reporting\ReportServerTempDB.mdf');
GO
ALTER DATABASE ReportServerTempDB 
MODIFY FILE (NAME = ReportServerTempDB_log, FILENAME = 'K:\Data\reporting\ReportServerTempDB_log.ldf');
GO

Don't forget to delete the old tempdb after restart SQL Server and move the reportserver databases when the service is stopped after the commands and before turning SQL Server back on.

CREATE FUNCTION [dbo].[ufn_TallyHo] (@quantity int)
RETURNS TABLE AS
RETURN(
with cte(tally)as(select 1 union all select tally +  1 from cte where tally < @quantity)SELECT tally FROM cte
)

I found a table at work called Tally that was a list of numbers. I thought it was sad until I needed something like it, so I created the above function. But this one is better, no recursion:

ALTER FUNCTION [dbo].[ufn_TallyHo2] (@quantity bigint)
RETURNS TABLE AS
RETURN(
WITH CTE1(N) AS (SELECT N FROM (VALUES(1),(1))a(N)),
CTE2(N) AS (SELECT 1 FROM CTE1 x, CTE1 y),
CTE3(N) AS (SELECT 1 FROM CTE2 x, CTE2 y),
CTE4(N) AS (SELECT 1 FROM CTE3 x, CTE3 y),
CTE5(N) AS (SELECT 1 FROM CTE4 x, CTE4 y),
CTE6(N) AS (SELECT 1 FROM CTE5 x, CTE5 y)
SELECT TOP (@quantity) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum FROM CTE6 x, CTE6 y
)

Const ValueSheetName As String = "MyParamSheet"
Const ServerName As String = "ServerName"
Public Sub GetData()

	ThisWorkbook.Names("Results").RefersToRange.Value = "Getting..."
	Dim oWorksheet As Worksheet
	On Error Resume Next
	Set oWorksheet = ThisWorkbook.Worksheets(ValueSheetName)
	On Error GoTo 0
	If Not oWorksheet Is Nothing Then
		If Not Len(ThisWorkbook.Names("ListName").RefersToRange.Value) = 0 Then
			ThisWorkbook.Names("Results").RefersToRange.Value = GetItems(ThisWorkbook.Names("ListName").RefersToRange.Value, ThisWorkbook.Names("DateName").RefersToRange.Value, ThisWorkbook.Names("ValueName").RefersToRange.Value)
			Exit Sub
		End If
	End If
	MsgBox ("Need " & ValueSheetName & " sheet with first column list of values!")
End Sub
Public Function ConCatEndBlank(Delimiter As Variant, ParamArray CellRanges() As Variant) As String

	Dim Cell As Range, Area As Variant
	ConCatEndBlank = ""
	For Each Area In CellRanges
		If TypeName(Area) = "Range" Then
			For Each Cell In Area
				If Len(Cell.Value) > 0 Then
					ConCatEndBlank = ConCatEndBlank & Delimiter & Cell.Value
				Else
					Exit For
				End If
			Next
		Else
			ConCatEndBlank = ConCatEndBlank & Delimiter & Area
		End If
	Next
	ConCatEndBlank = Mid(ConCatEndBlank, Len(Delimiter) + 1)
End Function
Public Function GetItems(sList As String, dDate As Date, sValue As String) As Integer
	GetItems = 0
	If sList = "" Then
		GetItems = -1
	Else
		On Error GoTo Error1
		If sList = "All" Then sList = ""
		Dim oConn As New ADODB.Connection
		oConn.ConnectionString = "Provider=SQLOLEDB;Data Source=" & ServerName & ";Initial Catalog=DataBaseName;"
		Dim oCmd As New ADODB.Command
		oCmd.CommandType = adCmdStoredProc
		oCmd.NamedParameters = True
		oCmd.CommandText = "mystoredprocedure"
		oCmd.Parameters.Append oCmd.CreateParameter("@list", adVarChar, adParamInput, 2147483647, sList)
		oCmd.Parameters.Append oCmd.CreateParameter("@date", adDate, adParamInput, -1, dDate)
		oCmd.Parameters.Append oCmd.CreateParameter("@value", adVarChar, adParamInput, 8, sValue)
		oConn.Open
		Set oCmd.ActiveConnection = oConn
		Dim oRS As New ADODB.Recordset
		oRS.Open oCmd
		Dim oTarget As ListObject
		Dim oWorksheet As Worksheet
		On Error Resume Next
		Set oWorksheet = ThisWorkbook.Worksheets("QueryResults")
		On Error GoTo Error1
		If oWorksheet Is Nothing Then
			Set oWorksheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))oWorksheet.Name = "QueryResults"
		End If
		On Error Resume Next
		Set oTarget = oWorksheet.ListObjects("QueryData")
		On Error GoTo Error1
		If oTarget Is Nothing Then
			Set oTarget = oWorksheet.ListObjects.Add(xlSrcExternal, oRS, True, xlNo, oWorksheet.Range("A1"))
			oTarget.Name = "QueryData"
		Else
			Set oTarget.QueryTable.Recordset = oRS
		End If
		If Not oTarget Is Nothing Then
			Call oTarget.QueryTable.Refresh(False)
			GetParts = oTarget.ListRows.Count
		End If
		oRS.Close
		oConn.Close
		Set oRS = Nothing
		Set oConn = Nothing
		Set oCmd = Nothing
	End If
	Exit Function
Error1:
	MsgBox (Err.Description)
	On Error Resume Next
	oRS.Close
	oConn.Close
	Set oRS = Nothing
	Set oConn = Nothing
	Set oCmd = Nothing
End Function

Brogrammer, I probably am one.

I see a few C# Versions of this on the web. Here is a VB version.

Private Shared Function ProcessKillByOwnerSID(sProcessName As String, sSID As String) As Integer
        Dim oProcesses() As Process = System.Diagnostics.Process.GetProcessesByName(sProcessName)
        Dim i As Integer = 0
        For Each oProcess As Process In oProcesses
            If GetProcessInfoByPID(oProcess.Id, Nothing, Nothing) = sSID Then
                oProcess.Kill()
                i = i + 1
            End If
        Next
        Return i
End Function
Private Shared Function GetProcessInfoByPID(PID As Integer, sOutUser As String, sOutDomain As String) As String
        Dim sOwnerSID As String = String.Empty
        Try
            Dim sQuery As System.Management.ObjectQuery = New System.Management.ObjectQuery("Select * from Win32_Process Where ProcessID = '" & PID.ToString() & "'")
            Dim oSearcher As System.Management.ManagementObjectSearcher = New System.Management.ManagementObjectSearcher(sQuery)
            If oSearcher.Get().Count = 0 Then Return sOwnerSID
            For Each oReturn As System.Management.ManagementObject In oSearcher.Get()
                Dim sOwner(2) As String
                oReturn.InvokeMethod("GetOwner", CType(sOwner, Object()))
                sOutUser = sOwner(0)
                If sOutUser Is Nothing Then sOutUser = String.Empty
                sOutDomain = sOwner(1)
                If sOutDomain Is Nothing Then sOutDomain = String.Empty
                Dim sSID(1) As String
                oReturn.InvokeMethod("GetOwnerSid", CType(sSID, Object()))
                sOwnerSID = sSID(0)
                Return sOwnerSID
            Next
        Catch
            Return sOwnerSID
        End Try
        Return sOwnerSID
End Function

Strings and Noodles

The backup operation that started at '‎2013‎-‎03‎-‎04T04:34:27.113260900Z' has failed with following error code '0xC03A0005' (The version does not support this version of the file format.). Please review the event details for a solution, and then rerun the backup operation once the issue is resolved.

Create an Excel workbook with DSN-less Data Connection that works with parameterized stored procedures that take values from cells.

Alameda County Line

Use the above myspace link to listen to a lovely tune by Red Meat. Below is some fantastic advice I got from here.

^M is DOS line break charater which shows up in unix files when uploaded from a windows file system in ascii format.
To remove this, open your file in vi editor and type

:%s/(ctrl-v)(ctrl-m)//g

and press Enter key.
Important!! – press (Ctrl-v) (Ctrl-m) combination to enter ^M character, dont use “^” and M

Reading List

Confessions - Aliester Crowley
Decline of the West - Oswald Spengler
Secret Doctrine - H. P. Blavatsky
White Goddess - Sir Robert Graves
Justine - Marquis De Sade
Don Quixote - Cervantes
Stand on Zanzabar - John Brunner
Mein Kampf - Adolph Hitler
Gold Warriors - Peggy Seagrave
Sensory Inhibition - Georg Von Bekesy
Brothers Karamazov - Fyodor Dostoyevsky
Sentimental Education - Gustave Flaubert
The Demons - Heimito von Doderer
Anthony Adverse - William Hervey Allen

 

A proper security setting on IIS content folders