Internet Sellout

addselftosqlsysadmin.cmd (5.25 kb)

Crap, why can't I connect to SQL Server, I am a local Admin what's the problem?

I am starting a post on my struggles convincing spamassassin's Bayes classification system to learn ham. When I su into the user account I can run this command:

sa-learn --dump magic

and it tells me how many ham and spam it thinks it has seen. when I train spam it goes up, but not when I train ham.

I was trying to diagnose some issues with ASP.NET Web Forms post backs so I turned off IPv6 even though none of my websites were bound to IPv6 addresses. I unchecked it on the adapter property page. I had no logical reason for doing this. Then Windows Update stopped working. This was from a Rackspace Cloud Server. I cannot imagine what kind of tunneling is taking place in various parts of the internet with IPv6 getting rolled out (finally).

Oh and by the way, I had trouble uploading this picture with BlogEngine.NET and I saw this article:

http://blogengine.codeplex.com/workitem/12382

I did what the user said (in settings unchecked optimize which minifies) and it solved the problem, however I am on IIS 8 not IIS Express.

Videos of the Blow Hole:

http://www.youtube.com/results?search_query=sunny%20cove%20blow%20hole&sm=3

What is my preferred IP anyway?

Not this:

Set-NetIPAddress -IPAddress xxx.xxx.xxx.xxx -SkipAsSource 1

You can do that in Power Shell on all the IP addresses you don't want to be chosen as your preferred IP address for your windows 2012 machine.

This will tell you what is set:

Netsh int ipv4 show ipaddresses level=verbose

I have read that once you start managing this with Power Shell you will lose information if you try and edit with the GUI.

This is not going well...

The page size of asp.net had increased when comparing to 2008 R2 IIS 7.5 ASP.NET 4.0

C:\Windows\System32\Inetsrv\Appcmd.exe  set config -section:httpCompression   -[name='gzip'].staticCompressionLevel:9  -[name='gzip'].dynamicCompressionLevel:4

 All of a sudden they got smaller...

This modified the applicationHost.config file.

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

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