Connect to Skydrive as a network drive on windows 7

Windows 7 allows you to connect to your skydrive folders as network folders simply and easily, allowing you to copy and paste files without messing with all the web interface shizzle. The additional 25GB is great for passing your files too and from work, as shown here :

First open up your skydrive account and go into the folder that you want to share. In this example I've chosen the folder "New Folder".

As you can see above I've underlined two very important parts of the URL, these identify you account and your folder, which we'll use later. So... next step....

Open explorer and go to "my computer" then select the "Map Network Drive" option from the toolbar, when the setup screen appears the details similar to below but replace it with the number and folder name from the URL you looked at earlier.

As you can see the path is \\docs.live.net@SSL\<userid>\<foldername>, once you've entered this just press connect and enter your live username and password when prompted and then you're all done, your SkyDrive will appear in your drive listings :)

Ohh one more thing to remember, if you want to enter the "My Documents" or "Documents" default folder on skydrive you have to enter ^2Documents e.g. \\docs.live.com@SSL\170faf79965c6946\^2Documents don't ask why.

Anonymous Types

While creating a server/client app, I need to send a blob of information without the hassle of shared classes when using serialise / deserialise. After messing with my own serialiser (which worked and was ugly) I came across Anonymous Types. These little beauties let you create a type on the fly without prior declarations and are great for times when you need to store data for on a single occasion. Behold ....

MyData = New With {
      .ID = 999, 
      .Name = "SGs solarium",
      .Value = 999.99
      .Info = "Test anonymous types"
}

Later on you can then use the above like any other type.

As you can see by the lack of line continuation this is a 2010 only sample (maybe 2008, haven't tested that)

Bite Sized Web Server

A few months back I needed a way of pulling information off of a machine that wasn't capable of supporting IIS. I was going to go down the route of some bespoke client server protocol, but then I thought that since it's only basic information and files that I need to send I'd create my own small web server. After doing some research on the subject and having messed with TCP components in a previous project on windows mobile I had a pretty good foundation to start moving.

First off we need a TCP Listener, this helpful object sits there and tells us when someone is connecting on a specified port, the actual transmitting of data is handled via another object known as the socket but we'll get to that shortly

Public Class WebServer
    Dim mthdServer As Thread
    Dim mwebSession As WebSession
    Dim mtcpListener As TcpListener

    Public Sub New()
        Try
            ' Get the IP address of the machine
            Dim strHostName As String = Dns.GetHostName()
            Dim ipServer As IPAddress = Dns.GetHostEntry(strHostName).AddressList(0)
            ' Specify which Port we'll be using.
            Dim strPort As String = "81"
            ' Open a tcp listener on the port we've specified
            mtcpListener = New TcpListener(ipServer, Int32.Parse(strPort))
            ' Start it up
            mtcpListener.Start()
            ' Some internal info, you could also log this to a file or the event log.
            Console.WriteLine("Web server started at: " & ipServer.ToString() & ":" & strPort)
            ' Start up the worker thread and pass it our listening item
            mwebSession = New WebSession(mtcpListener)
            mthdServer = New Thread(New ThreadStart(AddressOf mwebSession.ProcessThread))
            mthdServer.Start()
        Catch ex As Exception
            ' Bomb
            Console.WriteLine(ex.StackTrace.ToString())
        End Try
    End Sub
    ' Clean up
    Protected Overrides Sub Finalize()
        MyBase.Finalize()
        ' clean up the thread
        mthdServer.Abort() : mthdServer = Nothing
        mwebSession = Nothing
        ' Stop listening on the port
        mtcpListener.Stop() : mtcpListener = Nothing
    End Sub
End Class

You'll notice above that we have a second class known as the webServer this is the core of the service and handles the transmission of the file data and is basically the work horse.

Public Class WebSession
    ' Internal store for the listener and also the socket for data movement
    Private mtcpListener As System.Net.Sockets.TcpListener
    Private msktClient As System.Net.Sockets.Socket

    ' We're passed the listener so store that away.
    Public Sub New(ByVal tcpListener As System.Net.Sockets.TcpListener)
        Me.mtcpListener = tcpListener
    End Sub

    ' Since we're a thread this is a starting point, we're just gonna sit in a loop
    ' get a request for a file and then return it
    Public Sub ProcessThread()
        While (True)
            Try
                ' Wait for a connection from the outside
                msktClient = mtcpListener.AcceptSocket()
                ' Get information on the client using the IPEndPoint
                Dim ipepClientInfo As IPEndPoint = CType(msktClient.RemoteEndPoint, IPEndPoint)
                ' Start a new thread for the request then the loop can go to process additional
                ' requests
                Dim mthdClient As New Thread(New ThreadStart(AddressOf ProcessRequest))
                mthdClient.Start()

                ' Fault? oh dear, let's bomb the current thread, the client will just
                ' get a connection fault and they'll have to try again.
            Catch ex As Exception
                Console.WriteLine(ex.StackTrace.ToString())
                If msktClient.Connected Then
                    msktClient.Close()
                End If
            End Try
        End While
    End Sub

    ' This is where we figure what file the client wants and then throw it back via a stream
    Protected Sub ProcessRequest()
        Dim recvBytes(1024) As Byte
        Dim strHTMLReq As String = Nothing
        Dim intBytes As Int32

        Try
            ' Receive the request from the client, we'll need some buffer space to shove
            ' it into
            intBytes = msktClient.Receive(recvBytes, 0, msktClient.Available, SocketFlags.None)
            ' Transfer the buffer to a readable format
            strHTMLReq = Encoding.ASCII.GetString(recvBytes, 0, intBytes)

            ' Could log this but I'm just using it to display what the request is
            Console.WriteLine("HTTP Request: ")
            Console.WriteLine(strHTMLReq)

            ' Get the path of to where our files are stored and the default page name
            Dim strDefaultPage As String = "index.html"
            Dim strPath As String = Directory.GetCurrentDirectory() & "\WWWRoot\"
            ' Some buffers we'll be using
            Dim strArray() As String
            Dim strRequest As String

            ' Chop up the request so it's eaier to Interpret
            strArray = strHTMLReq.Trim.Split(" ")
            ' Determine the HTTP method, we only use GET, in here we use our
            ' HTMLResponse function which is where the data is streamed
            If strArray(0).Trim().ToUpper.Equals("GET") Then
                strRequest = strArray(1).Trim
                ' Do we have a file name?
                If (strRequest.StartsWith("/")) Then
                    strRequest = strRequest.Substring(1)
                End If
                ' No file Name then default page.
                If (strRequest.EndsWith("/") Or strRequest.Equals("")) Then
                    strRequest = strRequest & strDefaultPage
                End If
                ' Send the data
                strRequest = strPath & strRequest
                HTMLResponse(strRequest)
            Else ' Not GET method, we'll go away passing back the errorpage.
                strRequest = strPath & "Error\" & "400.html"
                HTMLResponse(strRequest)
            End If

            ' Fault? Just kill the connection
        Catch ex As Exception
            Console.WriteLine(ex.StackTrace.ToString())
            If msktClient.Connected Then
                msktClient.Close()
            End If
        End Try
    End Sub

    ' This procedure will send back the file specified in a stream back to the client.
    Private Sub HTMLResponse(ByVal httpRequest As String)
        Try
            ' Open the file we've been given, we'll use the streamreader to pull everything
            ' into a string for easy transport.
            Dim strData As StreamReader = New StreamReader(httpRequest)
            Dim strBuff As String = strData.ReadToEnd()
            strData.Close() : strData = Nothing

            ' Push the string into a byte array
            Dim respByte() As Byte = Encoding.ASCII.GetBytes(strBuff)

            ' Set HTML Header, this is taken from the W3S consortium site, only thing
            ' you need to take note of is the GetContentType function used to pass
            ' back what type of document we're returning.
            Dim htmlHeader As String = _
                "HTTP/1.0 200 OK" & ControlChars.CrLf & _
                "Server: Stews Webserver 1.0" & ControlChars.CrLf & _
                "Content-Length: " & respByte.Length & ControlChars.CrLf & _
                "Content-Type: " & getContentType(httpRequest) & _
                ControlChars.CrLf & ControlChars.CrLf

            ' Again encode the header to bytes.
            Dim headerByte() As Byte = Encoding.ASCII.GetBytes(htmlHeader)

            ' Send HTML Header back to Web Browser via our socket
            msktClient.Send(headerByte, 0, headerByte.Length, SocketFlags.None)
            ' Send Contents back to Web Browser via our socet
            msktClient.Send(respByte, 0, respByte.Length, SocketFlags.None)
            ' Close HTTP Socket connection
            msktClient.Shutdown(SocketShutdown.Both)
            msktClient.Close()

            ' Error? Bomb
        Catch ex As Exception
            Debug.Print(ex.Message)

            If msktClient.Connected Then
                msktClient.Close()
            End If
        End Try
    End Sub

    ' Determines the type of returned content.
    Private Function GetContentType(ByVal httpRequest As String) As String
        If (httpRequest.EndsWith("html")) Then
            Return "text/html"
        ElseIf (httpRequest.EndsWith("htm")) Then
            Return "text/html"
        ElseIf (httpRequest.EndsWith("txt")) Then
            Return "text/plain"
        ElseIf (httpRequest.EndsWith("xml")) Then
            Return "text/xml"
        ElseIf (httpRequest.EndsWith("gif")) Then
            Return "image/gif"
        ElseIf (httpRequest.EndsWith("jpg")) Then
            Return "image/jpeg"
        ElseIf (httpRequest.EndsWith("jpeg")) Then
            Return "image/jpeg"
        ElseIf (httpRequest.EndsWith("pdf")) Then
            Return "application/pdf"
        ElseIf (httpRequest.EndsWith("pdf")) Then
            Return "application/pdf"
        ElseIf (httpRequest.EndsWith("doc")) Then
            Return "application/msword"
        ElseIf (httpRequest.EndsWith("xls")) Then
            Return "application/vnd.ms-excel"
        ElseIf (httpRequest.EndsWith("ppt")) Then
            Return "application/vnd.ms-powerpoint"
        Else
            Return "text/plain"
        End If
    End Function
End Class

And that's about it, as you can see it's all a lot more simpler than it used to be in C++ but that's the power of .NET for you. You can always make changes to the HTMLResponse proc if you want to implement your own type of data response.

And just before I go some additional information, we use the following imports

Imports System.Net
Imports System.Net.Sockets
Imports System.Threading
Imports System.Text
Imports System.IO

and to run the webserver tis easy.

Dim myWebServer As WebServer = New WebServer

Common Table Expressions

Producing Dates – Sometimes a list of dates is required for you to cross join against, I used to do this via a large table filled with dates which used to work well. Since the introduction of SQL Server 2005 however I now use a common table expression (CTE) to create a list of dates, the advantage here is speed (no table needs to be scanned) as well as storage space (no data is stored down). The following code will generate a list of dates between 01/01/2010 and 31/12/2010

WITH myDates AS
(
 SELECT CAST('01/Jan/2010' AS DATETIME) DateValue
 UNION ALL
 SELECT DateValue + 1
 FROM myDates
 WHERE DateValue + 1 <= N'31/Dec/2010'
)
-- Immediately use the above CTE table, the table will no longer exist after -- it’s first use
SELECT DateValue FROM myDates OPTION (MAXRECURSION 0)

Please note the OPTION (MAXRECURSION 0) statement, when using CTEs the default maximum number of recursions is 100, here we set it to 0 to stipulate that the loop must continue until it naturally completes. The key principle of the CTE is its ability to self reference allowing you to perform tasks on the data that is already present in the table without complex join scenarios. Also the ability to use recursion as shown above allows you to programmatically create large tables without the use of stored procedures. The only downside to CTEs appears to be the fact that the table itself only exists for the first statement use outside of the WITH statement. Anywho back to our statement, you’ll probably want this as part of another SQL Statement let’s say a list of dates in September and a list of sales against those dates? First of let’s create a function that will allow us to get the dates we need and using the code above.

CREATE FUNCTION DateList
( 
   @StartDate AS DATETIME, @EndDate as DATETIME
)
RETURNS @tabDates TABLE
(
   DateValue DATETIME
)
AS
BEGIN
   WITH myDates AS
   (
      SELECT @StartDate DateValue
      UNION ALL
         SELECT DateValue + 1
            FROM myDates
            WHERE DateValue + 1 <= @EndDate
   )
   INSERT INTO @tabDates SELECT DateValue FROM myDates OPTION (MAXRECURSION 0)
   RETURN
END

Now with the above function we can link this to anytable and retrieve the results so we’ll get a full list of dates for September and then link the total sales per date

DECLARE @Sales TABLE (ID INTEGER, [Date] DATETIME)
INSERT INTO @Sales VALUES (1, N'02/Sep/2010')
INSERT INTO @Sales VALUES (2, N'12/Sep/2010')
INSERT INTO @Sales VALUES (3, N'19/Sep/2010')
INSERT INTO @Sales VALUES (4, N'02/Sep/2010')
INSERT INTO @Sales VALUES (5, N'04/Sep/2010')
INSERT INTO @Sales VALUES (6, N'01/Sep/2010')
INSERT INTO @Sales VALUES (7, N'02/Sep/2010')



SELECT d.DateValue, COUNT(s.ID) 
FROM DateList(N'01/Sep/2010', N'30/Sep/2010') d
LEFT OUTER JOIN @Sales AS s ON d.DateValue = s.[Date]
GROUP BY d.DateValue

This is just a brief overview of CTEs and i'll create a more in depth example later on.

Replication monitoring

Got fed up of looking at the replication monitor day after day so created this simple stored proc to do it for me.

CREATE PROCEDURE dba_CheckSubscriptions
AS BEGIN
SELECT [Subscriber_server] + '/' + [db_name] AS [Server], [last_sync_date] AS [SyncTime], CASE WHEN [last_sync_date] > DATEADD(minute,-10,GETDATE()) THEN 'True' ELSE 'False' END AS [IsSync]
      FROM dbo.sysmergesubscriptions
      WHERE [last_sync_date] IS NOT NULL
END

This routine will populate a list of subscriptions attached to a merge replication (if you're using transactional or snapshot replication there are two different tables, not too sure which but just look under system tables)

You can then set up a job to email you if the InSync column comes up as false... Of course I don't use SQLMail as it poses security issues so I have instead went for the .NET way of notifying me if there's an issue, below is the code I used in a console app, notice I'm also using the DBConnection and DBRecordset classes that you can look at here.

Sub Main()
        Dim strData As String = ""
        Dim blnContinue As Boolean = True
        Dim strServer As String = ""
        Dim strDatabase As String = ""
        Dim strEmailServer As String = ""
        Dim strEmailAddress As String = ""
        Dim blnFailed As Boolean = False
        Dim myConnection As DBConnection = New DBConnection

        ' Check the arguments given are valid, we need the following
        ' %1 - Name of the database server
        ' %2 - Name of the Database
        ' %3 - SMTP Server to connect to
        ' %4 - Email address to send to
        If (blnContinue) Then
            Dim strCommands As String() = Command.Split(",")
            If (strCommands.Length <> 4) Then
                Console.WriteLine("Parameters Incorrect")
                Console.WriteLine("Usage ReplMon [Database Server],[Server Instance],[Email Server],[Email Address]")
                Console.WriteLine("   e.g. ReplMon 'BACKUP\BackupLogs', 'Logs', 'TAG', 'dba@mymail.co.uk'")
                blnContinue = False
            Else
                ' Get the details into our vars, notice I strip any quotes
                strServer = strCommands(0).Replace("""", "").Replace("'", "")
                strDatabase = strCommands(1).Replace("""", "").Replace("'", "")
                strEmailServer = strCommands(2).Replace("""", "").Replace("'", "")
                strEmailAddress = strCommands(3).Replace("""", "").Replace("'", "")
            End If
        End If
        ' Connect to the database with the details given
        If (blnContinue) Then
            myConnection.DatabaseName = strDatabase
            myConnection.Server = strServer
            blnContinue = myConnection.ConnectDatabase()
            If (Not blnContinue) Then
                Call Console.WriteLine("Unable to connect to the database " + strServer + "\" + strDatabase)
            End If
        End If
        ' Get the data from the stored proc and produce the email
        If (blnContinue) Then
            Dim recSet As DBRecordSet = New DBRecordSet(myConnection)
            recSet.OpenRecordSet("EXEC dba_CheckSubscriptions")
            If (recSet.EOF) Then
                Console.WriteLine("No records retrieved from database, replication maybe faulty")
                blnContinue = False
            Else
                ' Put the information from the database into a text object to send in the email
                strData = "Location            Last Sync Time      In Sync?" + vbCrLf
                Do Until (recSet.EOF)
                    strData += recSet.Fields("Computer").ToString.PadRight(20, " ")
                    strData += recSet.Fields("SyncTime").ToString.PadRight(20, " ")
                    strData += recSet.Fields("InSync").ToString + vbCrLf
                    ' If we have a sync issue, mark the failed flag we'll use this to change the header on the email
                    If (recSet.Fields("InSync") <> "True") Then
                        blnFailed = True
                    End If
                    ' Move to the next record
                    recSet.MoveNext()
                Loop
            End If
            ' CLose up the recordSet
            recSet.Close() : recSet = Nothing
        End If
        ' Close the Database COnnection
        myConnection = Nothing

        ' Send the mail
        If (blnContinue) Then
            Dim mailMsg As Net.Mail.MailMessage = New Net.Mail.MailMessage
            mailMsg.From = New Net.Mail.MailAddress(strEmailAddress)
            mailMsg.To.Add(New Net.Mail.MailAddress(strEmailAddress))
            mailMsg.Subject = IIf(blnFailed, "DATABASE REPLICATION SYNC FAILED", "Database Replication is in sync")
            mailMsg.Body = strData
            mailMsg.IsBodyHtml = False
            ' Set priority if we have a fail
            mailMsg.Priority = IIf(blnFailed, Net.Mail.MailPriority.High, Net.Mail.MailPriority.Normal)
            Dim smtpClient As New Net.Mail.SmtpClient
            smtpClient.Host = strEmailServer
            smtpClient.Send(mailMsg)
            mailMsg = Nothing
        End If
    End Sub

Notice no error logging??? so did I, I know my email and SQL server work so I don't care, you may want to put some in.

With the app compiled I then just fill in the arguments as described and then set it up as a scheduled task to run every four hours. I then get and email informing me of the up to date status of the database replication... Simples.

Connecting to an SQL Server Database

Coming from working with ADO and DAO, I've found it hard adapting to the naff way in which .net handles recordset, to deal with these shortcomings I made these two classes which I use in nearly all my projects.. Enjoy.

DBConnection - Used to connect to an SQL Server, allows you to open recordsets and transactions as well as executing SQLs with a single instruction.

Imports Microsoft.SqlServer

Public Class DBConnection
    Private mstrDatabaseName As String
    Private mstrServer As String
    Private mstrUserName As String
    Private mstrPassword As String
    Private mTransaction As SqlClient.SqlTransaction = Nothing
    Friend SQLDatabase As SqlClient.SqlConnection
    ' A creatable COM class must have a Public Sub New() 
    ' with no parameters, otherwise, the class will not be 
    ' registered in the COM registry and cannot be created 
    ' via CreateObject.
    Public Sub New()
        MyBase.New()

    End Sub
    ' ///////////////////////////////////////////////////////////////////////////////
    ' Databse Name
    Public Property DatabaseName() As String
        Get
            Return mstrDatabaseName
        End Get
        Set(ByVal value As String)
            mstrDatabaseName = value
        End Set
    End Property
    ' ///////////////////////////////////////////////////////////////////////////////
    ' Server
    Public Property Server() As String
        Get
            Return mstrServer
        End Get
        Set(ByVal value As String)
            mstrServer = value
        End Set
    End Property
    ' ///////////////////////////////////////////////////////////////////////////////
    ' UserName
    Public Property Username() As String
        Get
            Return mstrUserName
        End Get
        Set(ByVal value As String)
            mstrUserName = value
        End Set
    End Property
    ' ///////////////////////////////////////////////////////////////////////////////
    ' Password
    Public Property Password() As String
        Get
            Return mstrPassword
        End Get
        Set(ByVal value As String)
            mstrPassword = value
        End Set
    End Property
    ' ///////////////////////////////////////////////////////////////////////////////
    ' Set All 4 properties
    Public Sub SetDatabase(ByVal strServer As String, ByVal strDatabase As String, ByVal strUsername As String, ByVal strPassword As String)
        mstrServer = strServer
        mstrDatabaseName = strDatabase
        mstrPassword = strPassword
        mstrUserName = strUsername
    End Sub
    ' ///////////////////////////////////////////////////////////////////////////////
    ' Connects to the database that we've been given the details for
    Public Function ConnectDatabase() As Boolean
        Dim strConnectionString As String
        Dim dbTest As SqlClient.SqlConnection

        '  Create a connection string.
        strConnectionString = "Data Source=" + mstrServer + "; Initial Catalog=" + mstrDatabaseName + ";"
        If (mstrUserName <> "" And mstrPassword <> "") Then
            strConnectionString = strConnectionString + "User Id=" + mstrUserName + ";"
            strConnectionString = strConnectionString + "Password=" + mstrPassword + ";"
        Else
            strConnectionString = strConnectionString + "Trusted_Connection=Yes;"
        End If
        ' Connect to the database
        dbTest = New SqlClient.SqlConnection(strConnectionString)
        dbTest.Open()
        ' If we've opened then store the connection otherwise, exit
        If (dbTest.State = ConnectionState.Open) Then
            If (Not SQLDatabase Is Nothing) Then
                SQLDatabase.Close()
            End If
            SQLDatabase = Nothing
            SQLDatabase = dbTest
            dbTest = Nothing
            Return True
        Else
            dbTest = Nothing
            Return False
        End If
    End Function
    ' /////////////////////////////////////////////////////////////////////
    ' Begins a tranasction
    Public Sub BeginTransaction()
        If (mTransaction Is Nothing) Then
            mTransaction = SQLDatabase.BeginTransaction()
        Else
            Throw New Exception("Transaction is already open!")
        End If
    End Sub
    Public Sub CommitTransaction()
        If (mTransaction Is Nothing) Then
            Throw New Exception("There is no transaction open!")
        Else
            mTransaction.Commit()
            mTransaction = Nothing
        End If
    End Sub
    Public Sub RollbackTransaction()
        If (mTransaction Is Nothing) Then
            Throw New Exception("There is no transaction open!")
        Else
            mTransaction.Rollback()
            mTransaction = Nothing
        End If
    End Sub
    ' /////////////////////////////////////////////////////////////////////
    ' Open recordSet Opens a recordSet Object and then does stuff with it.
    Public Function OpenRecordSet(ByVal strSQL As String) As DBRecordSet
        Dim retData As DBRecordSet = New DBRecordSet

        retData.Database = Me
        retData.OpenRecordSet(strSQL)
        Return retData
    End Function
    ' /////////////////////////////////////////////////////////////////////
    ' Executes an SQL that Needs Executing
    Public Function Execute(ByVal strSQL As String, Optional ByRef RecordsAffected As Long = 0) As Boolean
        Dim cmdCommand As SqlClient.SqlCommand

        cmdCommand = New SqlClient.SqlCommand(strSQL, SQLDatabase, mTransaction)
        RecordsAffected = cmdCommand.ExecuteNonQuery()
        Execute = True
        ' Clean and Exit
        cmdCommand = Nothing
    End Function
    ' /////////////////////////////////////////////////////////////////////
    ' Clean up and Exit
    Protected Overrides Sub Finalize()
        If (Not mTransaction Is Nothing) Then
            mTransaction.Rollback()
            mTransaction = Nothing
        End If
        SQLDatabase = Nothing
        MyBase.Finalize()
    End Sub
End Class

DBRecordSet - Used to manipulate a recordset handed back by DBConnection, similar to the ADO recordset object it will allow you to move forward and backward through the records as well as giving you a way to determine BOF & EOF.

Public Class DBRecordSet
    Private mdbConn As DBConnection
    Private mdbData As DataTable
    Private mlngCounter As Long
    '///////////////////////////////////////////////////////////////////////////////
    ' Pass the database through
    Default Public ReadOnly Property Fields(ByVal FieldName As String) As Object
        Get
            If (mdbData Is Nothing) Then
                Return Nothing
            Else
                If (mlngCounter >= 0) Then
                    Return mdbData.Rows(mlngCounter).Item(FieldName)
                Else
                    Return Nothing
                End If
            End If
        End Get
    End Property
    Default Public ReadOnly Property Fields(ByVal FieldIndex As Integer) As Object
        Get
            If (mdbData Is Nothing) Then
                Return Nothing
            Else
                If (mlngCounter >= 0) Then
                    Return mdbData.Rows(mlngCounter).Item(FieldIndex)
                Else
                    Return Nothing
                End If
            End If
        End Get
    End Property
    Public Property Database() As DBConnection
        Get
            Return mdbConn
        End Get
        Set(ByVal value As DBConnection)
            mdbConn = Nothing
            mdbConn = value
        End Set
    End Property
    '///////////////////////////////////////////////////////////////////////////////
    Public Sub New()
    End Sub
    Public Sub New(ByRef Connection As DBConnection)
        Me.Database = Connection
    End Sub
    Friend Function OpenDataSetFromSP(ByVal StoredProcName As String) As DataSet
        Dim varReturn As Long
        Return OpenDataSetFromSP(StoredProcName, varReturn, Nothing)
    End Function
    '///////////////////////////////////////////////////////////////////////////////
    ' Open the dataset
    Friend Function OpenDataSetFromSP(ByVal StoredProcName As String, ByRef ReturnValue As Object, ByVal ParamArray Args() As Object) As DataSet
        Dim dbData As SqlClient.SqlDataAdapter
        Dim dbCommand As SqlClient.SqlCommand
        Dim parameter As SqlClient.SqlParameter
        Dim lngCounter As Long
        Dim ds As DataSet

        OpenDataSetFromSP = Nothing
        Try
            dbCommand = New SqlClient.SqlCommand(StoredProcName, Me.Database.SQLDatabase)
            dbCommand.CommandType = CommandType.StoredProcedure
            LoadParams(dbCommand)
            lngCounter = 0
            If (dbCommand.Parameters.Count > 0) Then
                For Each parameter In dbCommand.Parameters
                    If (Not parameter.Direction = ParameterDirection.ReturnValue) Then
                        If (Args.Length >= lngCounter) Then
                            parameter.Value = Args(lngCounter)
                        End If
                        lngCounter = lngCounter + 1
                    End If
                Next
            End If
            ' Execute what we need
            dbData = New SqlClient.SqlDataAdapter(dbCommand)
            ds = New DataSet
            ' Shove indataSet
            dbData.Fill(ds)
            OpenDataSetFromSP = ds
            ' Retrieve the Result?
            For Each parameter In dbCommand.Parameters
                If (parameter.Direction = ParameterDirection.ReturnValue) Then
                    ReturnValue = parameter.Value
                End If
            Next
            ' Exit

        Catch ex As Exception

            'DB.GenerateError("DbRecordSet::OpenDataSetFromSP : " + StoredProcName, ex)
            OpenDataSetFromSP = Nothing
        End Try
        ' Clean up
        dbCommand = Nothing
        dbData = Nothing
    End Function
    ' //////////////////////////////////////////////////////////////////////////////////////////////
    ' Caches and loads cached params for a stored procedure.... saves on the call backs
    Private Sub LoadParams(ByRef dbCommand As SqlClient.SqlCommand)
        Dim strProcName As String = dbCommand.CommandText
        Dim dbparameter As Collection = Nothing
        Dim parameter As SqlClient.SqlParameter = Nothing
        Static dicParams As Collection
        If (dicParams Is Nothing) Then
            dicParams = New Collection
        End If

        ' Find this itme in the list
        If (dicParams.Contains(strProcName)) Then
            dbparameter = dicParams(strProcName)
            If (Not dbparameter Is Nothing) Then
                For Each parameter In dbparameter
                    dbCommand.Parameters.Add(CType(parameter, ICloneable).Clone)
                Next
            End If
        Else
            SqlClient.SqlCommandBuilder.DeriveParameters(dbCommand)
            ' now record the params
            For Each parameter In dbCommand.Parameters
                If (dbparameter Is Nothing) Then
                    dbparameter = New Collection
                End If
                dbparameter.Add(CType(parameter, ICloneable).Clone)
            Next
            dicParams.Add(dbparameter, strProcName)
        End If
    End Sub
    '///////////////////////////////////////////////////////////////////////////////
    ' The Dataset object, just return tis all.
    Friend Function OpenDataSet(ByVal strSQL As String) As DataSet
        Dim datData As DataSet = Nothing
        Dim objCommand As SqlClient.SqlCommand
        Dim objAdapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter()

        Try
            objCommand = New SqlClient.SqlCommand(strSQL, mdbConn.SQLDatabase)
            objAdapter.SelectCommand = objCommand
            datData = New DataSet
            objAdapter.Fill(datData)
            objAdapter.Dispose()
            objCommand.Dispose()
        Catch ex As Exception

        End Try

        objAdapter = Nothing
        objCommand = Nothing
        Return datData
    End Function
    '///////////////////////////////////////////////////////////////////////////////
    ' Opens the recordSByVal strSQL As String) As Boolean
    Friend Function OpenRecordSet(ByVal strSQL As String) As Boolean
        Dim objAdapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter()
        Dim objCommand As SqlClient.SqlCommand

        OpenRecordSet = False
        Try
            mlngCounter = -1
            mdbData = Nothing
            objCommand = New SqlClient.SqlCommand(strSQL, mdbConn.SQLDatabase)
            objAdapter.SelectCommand = objCommand
            mdbData = New DataTable
            objAdapter.Fill(mdbData)
            objAdapter.Dispose()
            objCommand.Dispose()
            If (mdbData.Rows.Count > 0) Then
                mlngCounter = 0
            End If
            OpenRecordSet = True
        Catch ex As Exception
            Throw
        End Try
        objCommand = Nothing
        objAdapter = Nothing
    End Function
    ' ////////////////////////////////////////////////////////////////////////////////////
    ' MoveNext, MovePrevious, MoveFirst and MoveLast, self explanatory
    Public Function MoveLast() As Long
        If (mdbData Is Nothing) Then
            Return 0
        Else
            mlngCounter = mdbData.Rows.Count - 1
            Return mlngCounter
        End If
    End Function
    Public Sub MoveFirst()
        mlngCounter = 0
    End Sub
    Public Function MoveNext() As Long
        If (mdbData Is Nothing) Then
            Return 0
        Else
            mlngCounter = mlngCounter + 1
            If (mlngCounter > mdbData.Rows.Count) Then
                mlngCounter = mdbData.Rows.Count
            End If
            Return mlngCounter
        End If
    End Function
    Public Function MovePrevious() As Long
        mlngCounter = mlngCounter - 1
        If (mlngCounter < 0) Then
            mlngCounter = 0
        End If
        Return mlngCounter
    End Function
    ' ////////////////////////////////////////////////////////////////////////////////////
    ' EOF & BOF, checks to see if we're at the end or begining of the recordSet
    Public Function EOF() As Boolean
        EOF = False
        If (Not mdbData Is Nothing) Then
            If (mlngCounter >= mdbData.Rows.Count Or mdbData.Rows.Count = 0) Then
                ' DO the move last thing incase we are over the top on our recordcount
                EOF = True
            End If
        End If
    End Function
    Public Function BOF() As Boolean
        BOF = False
        If (Not mdbData Is Nothing) Then
            If (mlngCounter <= 0 And mdbData.Rows.Count > 0) Then
                mlngCounter = 0
                BOF = True
            End If
        End If
    End Function
    ' ////////////////////////////////////////////////////////////////////////////////////
    ' SImilar to the .close command in recordSets, just frees up the data.
    Public Sub Close()
        mdbData = Nothing
    End Sub
    ' ////////////////////////////////////////////////////////////////////////////////////
    ' Clean up and Exit
    Protected Overrides Sub Finalize()
        mdbData = Nothing
        mdbConn = Nothing
        MyBase.Finalize()
    End Sub
End Class

That is basically that with these two classes you can go around and do what you want on the SQL server... for example

' Open a connection to the database
        Dim myConnection As DBConnection = New DBConnection
        myConnection.SetDatabase("MYSERVER\Instance", "DatabaseName", "", "")
        ' Execute an SQL Directly
        myConnection.Execute("UPDATE [Article] SET [Data] = [Data] + 1")

        ' Open a recordSet
        Dim myRecordSet As New DBRecordSet(myConnection)

        ' Execute Straight record return SQLs
        myRecordSet.OpenRecordSet("SELECT * FROM [Article]")
        If (Not myRecordSet.EOF) Then
            Dim lngData As Long = myRecordSet.Fields("Data")
        End If

        ' Return Datasets from SQLs
        Dim myData As DataSet = myRecordSet.OpenDataSet("SELECT * FROM [Article]")

        ' Exeute Stored Procedures
        Dim lngReturn As Integer
        Dim myData As DataSet = myRecordSet.OpenDataSetFromSP("dba_CheckData", lngReturn, 1, 2, 3, "Some Parameter", 2341)

' That's that

Using the "Instead Of" triggers

Found a nice little SQL instruction the other day called INSTEAD OF, this gem allows you to override the INSERT, UPDATE or DELETE methods on a view or table and implement your own code.... Lets say for example you have a table of employees and you want to make sure that when the employees are deleted from the database they aren't actually deleted only flagged so they don't show up?  You could move the items into another table using a DELETE trigger or you could write something up in the calling app to do it for you but for supreme ease of use the INSTEAD OF trigger comes up trumps... behold

CREATE TRIGGER myDelete ON dbo.Employees INSTEAD OF DELETE
AS
BEGIN
   -- The table [Deleted] will contain all the records that have been set for deletion although you can't alter
   -- these directly so we join them to the real table
    UPDATE dbo.[Employees] SET dbo.[Employees].[Deleted] = 1
       FROM dbo.[Employees] INNER JOIN [Deleted] ON dbo.[Employees].[ID]=[Deleted].[ID]
END

As usual you are given a table (in this case DELETED) which holds the records of the items that have been marked for deletion. You're not allowed to modify this table directly so you have to inner join back to the original table and do what you need to. As you can see from the example above we are merely setting a flag column called [Deleted] rather than actually deleting the record, something that would not be possible with the DELETE Trigger alone, all hail INSTEAD OF.

Embedding Map Data in Report Builder 3

OK now we have some data (see the 2 previous articles) we can now add a map using this spatial data to a report.

OK First open up report builder 3, you can download it from here

Open it and start a new project and select the "Map Wizard" option.

On the source screen select "SQL Server spatial query"

Next add a datasource with a connection to a table / view or Stored Proc that contains some spatial data.

Select the fields you want to view, these will almost certainly be labels under the points on the map, or in some cases bubble sizes on a map.

On the next screen the spatial data will be plotted, at this point you want to add a bing map overlay so select the "Add a bing layer" option and then watch the map appear along with your plotted data!!!

The next couple of screens are just layout screens for the map, you can then go ahead and add additional information to your now more professional report.

Converting Long,Lat to SQL Server Spatial Data

In my last post I pulled across the Postcode database for the UK, I wanted to display some information using this data on the New Map functionality of Report Builder 3, although unfortunately it requires Spatial data for this to work. Having looked around I came across the information, the GEOGRAPHY datatype in SQL Server looks to allow you to store point information in a single column of SQL Server, and there are a few functions for allowing you to convert longitudinal and latitudinal values into this new data type... So without further ado...

Using the previous table we created we need to expand it to create a new column

ALTER TABLE Postcodes ADD [GeoLocation] GEOGRAPHY

next we need to convert the longitude and latitude into point information

UPDATE Postcodes SET GeoLocation = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20))
+ ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)

As you can see we're using the inbuilt STPointFromText function, this requires a point reference and a SRID reference (something to do with initial map referencing, which for our case if 4326), you can view the function in more detail here.

Additional function you can use are geography::STGeomFromText Same as above by the looks of it, geography::Point again similar but without the text modification or geography::Parse again same as the rest but with a SRID (assumes 4326 anyway by the looks of it)

My next post will probably be about placing this stuff on a map in Report Builder 3

UK Postcodes database

Managed to find a CSV on the Internet with all the details needed for the UK Postcodes (download it here). Decided to import it into a database, you can use the script below I made.... of course you could just use the import that comes with SQL.. but where's the fun in that?

CREATE TABLE Postcodes ([Postcode] VARCHAR(10), [IntDate] DATE, [GREast] VARCHAR(5), [GRNorth] VARCHAR(5), [County] INT, [District] VARCHAR(2), [Ward] VARCHAR(2), [UserType] BIT, [GridStatus] SMALLINT, [Country] SMALLINT, [WardStatus] SMALLINT, [NHSCode] VARCHAR(3), [NHSRegion] VARCHAR(3), [Longitude] DECIMAL(9,6), [Latitude] DECIMAL(9,6), [OSRef] VARCHAR(15), [Updated] DATE)
CREATE TABLE #Postcodes ([Postcode] VARCHAR(10), [IntDate] VARCHAR(10), [GREast] VARCHAR(255), [GRNorth] VARCHAR(255), [County] VARCHAR(255), [District] VARCHAR(255), [Ward] VARCHAR(255), [UserType] VARCHAR(255), [GridStatus] VARCHAR(255), [Country] VARCHAR(255), [WardStatus] VARCHAR(255), [NHSCode] VARCHAR(255), [NHSRegion] VARCHAR(255), [Longitude] varcHar(255), [Latitude] varchar(255), [OSRef] VARCHAR(17), [Updated] VARCHAR(255))
DECLARE @SQL VARCHAR(255)
SET @SQL = 'BULK INSERT #Postcodes FROM "c:\uk-post-codes-2009.csv" WITH (FIELDTERMINATOR= '','', ROWTERMINATOR='''+CHAR(10)+''', FIRSTROW=2)'
EXEC(@SQL)

INSERT INTO Postcodes ([Postcode], [IntDate], [GREast], [GRNorth], County, District, Ward, UserType, GridStatus, Country, WardStatus, NHSCode, NHSRegion, Longitude, Latitude, OSRef, Updated)
SELECT Replace([PostCode],'"',''), LEFT(REPLACE([IntDate],'"',''),6)+'01', REPLACE([GREast],'"',''), REPLACE([GRNorth],'"',''), REPLACE([County],'"',''), REPLACE([District],'"',''), REPLACE([Ward],'"',''), REPLACE([UserType],'"',''), REPLACE([GridStatus],'"',''), REPLACE([Country],'"',''), REPLACE([WardStatus],'"',''), REPLACE([NHSCode],'"',''), REPLACE([NHSRegion],'"',''), [Longitude], [Latitude], REPLACE([OSRef],'"',''), REPLACE([Updated],'"','') FROM #Postcodes
DROP TABLE #Postcodes

The reason I used EXEC on the BULK INSERT Command is because the file has Char(10) as a line terminator, and I couldn't seem to specify it as a ROWTERMINATOR option without stuffing it into a string.

After the script has finished you should have 1,841,117 rows, you'll need to add your own indexes.

Creating Random Names in SQL Server

Came across a problem when I needed a load of random names in an SQL Database (for an upcoming project on using maps in SSRS.. which I'll post up later). Looked around but couldn't find anything on the net so I decided to make some up. I Manged to get a list of forenames and surnames from the internet into a text file and decided to merge them up in SQL. For this to work you'll need to download the boys names, girls names and surnames then run the following SQL.

-- Lets Create some People.. a thousand will do
DECLARE @Counter BIGINT
SET @Counter = 1000

-- Create a table to put this in 
CREATE TABLE [Patients] ([Forename] VARCHAR(50), [SurName] VARCHAR(50), Gender BIT)
-- Sort out the Forenames we'll be using for the data, we make a #Name2 table because I have yet to figure our
-- inserting specific columns using BULK INSERT and without using a format file.
CREATE TABLE #Name (Name VARCHAR(50))
CREATE TABLE #ForeNames (FirstName VARCHAR(50), Gender BIT)
-- Move data in the #Name2 table
BULK INSERT #Name FROM "c:\Data\girlsforenames.txt" WITH (ROWTERMINATOR='\n')
-- Now move it to the forename table and add the gender
INSERT INTO #ForeNames SELECT [Name], 1 FROM #Name
-- Delete the names from temporary table
TRUNCATE TABLE #Name
-- Same for the boys
BULK INSERT #Name FROM "c:\Data\boysforenames.txt" WITH (ROWTERMINATOR='\n')
INSERT INTO #ForeNames SELECT [Name], 0 FROM #Name
-- Now do the surnames
TRUNCATE TABLE #Name
BULK INSERT #Name FROM "c:\Data\surnames.txt" WITH (ROWTERMINATOR='\n')

-- Now create these people, we'll be using the Names from above to create a random set of xxxx amount
SET @Counter = 0 
WHILE (@Counter > 0)
BEGIN
 INSERT INTO [Patients] ([Forename],[Surname],[Gender])
    SELECT TOP 1 #ForeNames.FirstName, (SELECT TOP 1 Name FROM #Name ORDER BY NEWID()), #ForeNames.Gender FROM #ForeNames ORDER BY NEWID()    
 SET @Counter=@Counter-1
END

DROP TABLE #Name
DROP TABLE #ForeNames

SELECT * FROM [Patients]

You can see for reading the files into the table I've used BULK INSERT which you can look up here since I find it a rather cool function.

Checking SQL Server Drive Space

Eak… Drivespace was down to 1MB on a customer’s server today (this was due to them deciding that changing passwords and not informing use or changing services settings was a good thing). Well I dialed in and trimmed log files, cleaned up unused file from the OS and such. In the office I have checks daily to make sure my databases are in tiptop condition but we usually leave the maintenance of the customer’s databases to themselves (we have other 400 of them so expecting a daily check is a little time consuming). This can lead to the odd occasion where the drive fills up or a backup script stop running and I’ve decided to do something about it, I created a web service on the sites with checked the most recent backups (see my backup scripts below) and also checks the disk space, I have then made a consuming website which connects to all the servers (eventually) and relays the space and last backup time, I can then from there take action if needed. Simple but very effective, I’ve posted the code below for how I record the drive space for any of you who which to know.

--Create the Database
CREATE DATABASE DBADatabase
GO
-- Switch to the new database for the rest of our setup
USE DBADatabase
GO
-- Create the table we’ll be using to hold the details on the drive space
CREATE TABLE dbo.DriveSpace (
 [ID] uniqueidentifier NOT NULL ROWGUIDCOL,
 [ServerName] varchar(50) NOT NULL,
 [Date] datetime NOT NULL,
 [Drive] char(1) NOT NULL,
 [SpaceMB] int NOT NULL )  ON [PRIMARY]
GO
-- add some indexes and such
ALTER TABLE dbo.DriveSpace ADD CONSTRAINT DF_DriveSpace_ID DEFAULT (newid()) FOR [ID]
GO

ALTER TABLE dbo.DriveSpace ADD CONSTRAINT PK_DriveSpace PRIMARY KEY CLUSTERED ([ID]) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX IX_DriveSpace ON dbo.DriveSpace ([Date]) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE dbo.DriveSpace SET (LOCK_ESCALATION = TABLE)
GO

That’s all we need to store it.. bug how to fill it? Well there is a function that returns the information of the drives of which the server is located called xp_fixeddrives, try it for yourselves and see the two columns returned (Drive and MB free). Anywho we need to set up a stored procedure that will record the details so that you can later set up a job using the GUI or sp_add_job to execute the stored proc daily.

USE DBADatabase
GO
CREATE PROCEDURE dba_UpdateDriveInfo AS
BEGIN
 -- Temporary Table for our results
 DECLARE @DriveData AS TABLE ([Drive] CHAR(1), [MB Free] INT) 
 -- Get results
 INSERT INTO @DriveData EXEC xp_fixeddrives
 -- Dump them in our table
 INSERT INTO dbo.DriveSpace ([ServerName], [Date], [Drive], [SpaceMB])
  SELECT DB_NAME(), GETDATE(), [DD].[Drive], [DD].[MB Free] FROM @DriveData AS [DD]
END

Again all you need to do is set up a job to run this daily and if you wanted to maybe send a mail when you lower than a specific amount? Good Luck

Moving the TempDB

Got some very sexy Intel SSD drives in today for a new server, moved some databases across but also thought I'd move the temp table with it to increase the performance to the max!  Usually with databases I'd detach and then reattach the database but it's a little different for the tempDB, you need to issue the ALTER DATABASE command and tell it to issue a new location for the database.  Once you've done this though you need to restart the SQL Server instance for the changes to take effect.  The temp database has specific names for it's logical files namely tempdev and templog, so I've listed the command used below for you, enjoy speed freaks.

ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, FILENAME='e:\system\tempdb.mdf')
GO
ALTER DATABASE tempdb MODIFY FILE (NAME=templog, FILENAME='f:\system\templog.ldf')
GO

P.S. Don't forget to restart the instance

Stored procedure parameter caching in VB.NET

One of the annoyances of Stored Procedures is having to pass the parameters and know the parameter datatypes, it's a bit of a pain and one I addressed when I started to use .NET all those years ago. I was cleaning up some code and noticed this little nugget and thought I'd share it with you since most (if not all examples) on the net show parameters being declared and passed through programmatically.

To overcome this annoyance I decided to retrieve the parameter definitions direct from the SQL Server using the DeriveParameters command which will retrieve the SPs parameter definition so you can use these when passing the values through instead of declaring them yourself. This has the distinct advantage of making sure that the most up to date definitions are available to you program and also allows you to tweak the stored proc without having to change the code in your VB app. The only issue is that it causes additional calls to the SQL Server before each stored procedure call!!!  To help get around this the parameter definitions are cached in a collection after the first call so we only need that additional call to be made once for each stored proc. So here you go :

Private Sub LoadParams(ByRef dbCommand As SqlClient.SqlCommand)
   Dim strProcName As String = dbCommand.CommandText
   Dim dbparameter As Collection = Nothing
   Dim parameter As SqlClient.SqlParameter = Nothing
   Static dicParams As Collection

   ' If this is the first time in then initialise the holding collection.
   If (dicParams Is Nothing) Then
      dicParams = New Collection
   End If

   ' Check to see if the stored procedure has been called before, if so then retrieve the cached parameter list
   If (dicParams.Contains(strProcName)) Then
      dbparameter = dicParams(strProcName)
      If (Not dbparameter Is Nothing) Then
         ' Go through the parameters we have stored and add them to the command parameter list.
         For Each parameter In dbparameter
            dbCommand.Parameters.Add(CType(parameter, ICloneable).Clone)
         Next
      End If
   Else
      ' OK this is a never seen before stored procedure, get the current parameter declarations from the SQL Server
      SqlClient.SqlCommandBuilder.DeriveParameters(dbCommand)
      ' Now that we have the list of paramteres we can record them
      For Each parameter In dbCommand.Parameters
         ' Create a new parameter collection, we do this in the loop since some commands come back with no parameters
         If (dbparameter Is Nothing) Then
            dbparameter = New Collection
         End If
         ' Create a copy of the parameter information by cloning it, we have to force the clone method here.
         dbparameter.Add(CType(parameter, ICloneable).Clone)
      Next
      ' Record these paramteres and stored procedure name
      dicParams.Add(dbparameter, strProcName)
   End If
End Sub

Having pasted the code here I can see I have no errorhanding, that's because it's handled in the calling procedure which I'll also post so you can see how to use it, this function opens a stored procedure with the given parameters and then returns a dataset.

'///////////////////////////////////////////////////////////////////////////////

' Open the dataset a stored procedure
Friend Function OpenDataSetFromSP(ByVal StoredProcName As String, ByRef ReturnValue As Object, ByVal ParamArray Args() As Object) As DataSet
   Dim dbData As SqlClient.SqlDataAdapter
   Dim dbCommand As SqlClient.SqlCommand
   Dim parameter As SqlClient.SqlParameter
   Dim lngCounter As Long
   Dim ds As DataSet

   OpenDataSetFromSP = Nothing
   Try
      ' Create the command for this stored procedure
      dbCommand = New SqlClient.SqlCommand(StoredProcName, SQLDatabase)
      dbCommand.CommandType = CommandType.StoredProcedure
      ' Load the parameters definitions for this stored procedure
      LoadParams(dbCommand)
      lngCounter = 0
      If (dbCommand.Parameters.Count > 0) Then
         For Each parameter In dbCommand.Parameters
            ' Ignore the return paramter if the stored procedure has one
            If (Not parameter.Direction = ParameterDirection.ReturnValue) Then
               ' Make sure that we don't push in more params than the stored procedure has
               If (Args.Length >= lngCounter) Then
                  parameter.Value = Args(lngCounter)
               End If
               lngCounter = lngCounter + 1
            End If
         Next
      End If

      ' Execute what we need
      dbData = New SqlClient.SqlDataAdapter(dbCommand)
      ds = New DataSet
      ' Shove the data into a dataSet
      dbData.Fill(ds)
      OpenDataSetFromSP = ds

      ' Retrieve the Return parameter?
      For Each parameter In dbCommand.Parameters
         If (parameter.Direction = ParameterDirection.ReturnValue) Then
            ReturnValue = parameter.Value
         End If
      Next

     ' Exit
   Catch ex As Exception
      DB.GenerateError("OpenDataSetFromSP : " + StoredProcName, ex)
      OpenDataSetFromSP = Nothing
   End Try

   ' Clean up
   dbCommand = Nothing
   dbData = Nothing
End Function

You'll need to make sure you have the SQLConnection object named as SQLDatabase and also you'll need to sort out a different errorhandler, but the classes will hopefully save you a lot of time and heartache.

Forwarding Mail in Exchange Server 2007 / 2010

This should be pretty elementary to anyone who has used exchange and active directory before, but I got asked this morning so I thought I'd put it up.


Adding A Contact To Exchange 2007

1. Add a contact to Active Directory (E.G Tagtronics Support)
2. Open up exchange management console
3. Under "Recipient Configuration" you will see "Mail Contact" click on mail contact and you will see the mail contact wizard.
4. Under "Actions" click on "New Mail Contact"
5. Check the box "Existing Contact" and click on browse, select the contact you wish to add as a exchange forwarding address
6. Click on next and enter a valid SMTP address (E.G. me@myblog.com)
7. Click on next and then click Finnish

Configuring Mail Forwarding Exchange 2007

1. Under "Recipient Configuration" you will see "Mailbox"
2. Double click on the mailbox you would like to configure forwarding for
3. Click on the "Mail Flow Settings" tab and double click on "Delivery Options"
4. Tick Box "Forward To" and "Deliver Messages To Both Forwarding Address And Mailbox"
5. Click Browse and select the contact you added in section 1.
6. Click "OK", "Apply" and "OK" again.

Your email forwarding has now been set

Simple Database Source Control

Got into a pickle the other day because of a stored procedure that was changed but as a result messed a few things up. Had backups so I could retrieve the data , but what if I didn’t??? This got me thinking to a way to store down all the changes I make on database procedures so I could revert should something go horribly wrong. So without further a do….
First off we need a database and a table to hold the list of changes.

CREATE DATABASE SQLSourceSafe
GO

USE SQLSourceSafe
GO

CREATE TABLE dbo.ProcedureChanges
(
    [ID] uniqueidentifier NOT NULL ROWGUIDCOL,
    [Server] VARCHAR(64),
    IP_Address VARCHAR(32),
    [Database] NVARCHAR(255),
    [Schema] NVARCHAR(255),
    [Object] NVARCHAR(255),
    [Date] DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    [Type] NVARCHAR(64),
    ProcName NVARCHAR(255),
    SourceCode NVARCHAR(MAX),
    SourceCodeXML XML,
    UserName    NVARCHAR(255)
)

ALTER TABLE dbo.ProcedureChanges ADD CONSTRAINT ProcedureChanges_ID DEFAULT (newid()) FOR ID

ALTER TABLE dbo.ProcedureChanges ADD CONSTRAINT ProcedureChanges_PK_ID PRIMARY KEY CLUSTERED ( ID )
 WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

So now we have our table we can get a current list of all the procedures in the databases that we want to start auditing. We can easily do this by looking in the sys.procedures table, this holds all the data on the current procedures in the database. So from YOUR database, run the following code.

INSERT INTO SQLSourceSafe.dbo.ProcedureChanges
( [Type],[SourceCode],[Database],[Schema],[Object],[UserName] )
SELECT 'CREATE_PROCEDURE', OBJECT_DEFINITION([object_id]), DB_NAME(), OBJECT_SCHEMA_NAME([object_id]), OBJECT_NAME([object_id]), 'Stewart Millward'
FROM sys.procedures

Notice I use the actual procedure and schema names? I prefer to be able to see the names of the procuedures rather than run around trying to find them. Anywho that’s all the information on the current look of the database now it’s time to record the changes made to the database from now on, for this we chall use a trigger on the events we know change a procedure and then just shove similar code to above in there.

CREATE TRIGGER SourceSafe_ProcedureTrigger
ON DATABASE FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, ALTER_SCHEMA, RENAME
AS
BEGIN
SET NOCOUNT ON
DECLARE @EventData XML = EVENTDATA()
DECLARE @ip VARCHAR(32) = (SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID)

INSERT INTO SQLSourceSafe.dbo.ProcedureChanges
( [Type], [SourceCode], [SourceCodeXML], [Database], [Schema], [Object], [Server], [IP_Address], [ProcName], [UserName] )
SELECT @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'), 
@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
@EventData,
DB_NAME(),
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
HOST_NAME(),
@ip,
PROGRAM_NAME(),
SUSER_SNAME()
END
GO

And that’s that it will start logging the changes in the database for you and you can then always go back should a mistake be made… huzzar!
Just some notes, RENAME is a 2008 event only so you may get an error on a 2005 machine, also if you want to disable the trigger you can use the following code.

DISABLE TRIGGER [SourceSafe_ProcedureTrigger] ON DATABASE

That’s That.

LSASS.exe error and bye bye domain?

Had the following error pop up the other day on a customers domain, it stop the server and clients from logging onto the machine

lsass.exe - System Error : Security Accounts Manager initialization failed because of the following error: Directory Service cannot start. Error Status: 0xc00002e1. Please click OK to shutdown this system and reboot into Directory Services Restore Mode, check the event log for more detailed information.

Directory Services Restore Mode??? I though this must be pretty bad, and I was right.. the Active Directory database was corrupt, I thought OK I'll get them to restore the system state from last nights backup.... They don't do backups, after a few harsh words with them I knew I'd have to do it the hard way.  I have actually done this before a few years back with 2000 Server but this is 2008, luckily the same procedure but it took me a while to remember what i needed to do so here's the general jist for future reference.

On startup press F8 and enter the system via the "Active Directory Restore" option.


When asked for the Username and password enter "Administrator" and the Active Directory Restore Password, this should be listed in the password file and would have been set up when the domain controller was first promoted.

Step 1

• Once the system has booted, go to start -> Run and type "cmd" to go to the command prompt
• In the Command Prompt type "ntdsutil.exe" and press enter to go into the Active Directory Restore program.
• Type "Files" and press enter then type "Info" and press enter this will give you the current location of the NTDS Datastore and should read something like "C:\Windows\NTDS\"
• On the file list given make sure that the following files are present
• NTDS.dit, DSADATA.bak, res2.log res1.log, and edb.log
• Find the file edb.chk under the file path given and rename it to edb.old reboot the machine and see if it boots, if not go to step 2

Step 2

• Boot back into Active Directory Restore mode (as above) and open up another command prompt window (Start->Run->cmd)
• Type the following command to repair any corrupted database files
• ESENTUTL /g \ntds\NTDS.dit /!10240 /8 /v /x /o Exchange the for whatever the NTDS folder was displayed as previously, e.g. ESENTUTL /g C:\windows\ntds\NTDS.dit /!10240 /8 /v /x /o
• If you recieve a "jet_error" or a "database corrupt" error then go back into the NTDSUtil program to repair it as follow
• From the command prompt type
• NTDSUtil
• Type "Files" and press enter
• Type "recover" and press enter
• This will repair and damaged files in the Active Directory, after this type "Quit" and press enter then "Quit" and press enter again.
• Use the ESENTUTL again to attempt to repair the database again e.g.
  ESENTUTL /g \ntds\NTDS.dit /!10240 /8 /v /x /o
• If you still recieve an error goto Step 3 otherwise reboot and try again.

Step 3

• Move all of the .log files from the NTDS folder to a seperate folder (you can delete this later if it works)
• Go to the command prompt and enter the NTDSUtil again
• Type "Files" and press Enter
• Type "integrity" and press Enter
• This will attempt to repair the database without any of the previous transactions stored in the log files (it's probably whatever is in the log file is corrupting the database)
• Type "Quit" and press enter
• Type "Semantic Database Analysis" and press enter
• Type "Go" and press enter, this will give a quick check of the files again
• If errors still occur at this stage then type "Go Fix" and press enter
• Type "Go" and press enter again, this should now have no errors
• Reboot the system and it should now work.

If you ever have to go through this process then you really need to double think your backup procedures.

Connection Details

ohhh look you can retrieve connection details using the sys.dm_exec_connections table linked to the connected users session ID @@SPID :)

SELECT * FROM sys.dm_exec_connections WHERE session_id = @@SPID

Auditing SQL Login Failures

Auditing SQL for login failures is a great way to check for security issues before they become a problems, I myself have written an app that sits in the icon tray and pops up when a failure is detected, you could also set up a job to email you should a failure become apparent. Setting up SQL for auditing is simply and easy so let’s go through some steps.
First make sure that the auditing feature is turned on (it should be by default, but you never know) so open up management studio, right click on the server node and select properties, when the server properties window appears select the “Security” page and then check the setting shown below is enabled.

If you have to change the setting then make sure that you restart the SQL Service since it only seems to pick up on the changes to this section during startup? Anywho, once you’ve made sure the setting is in affect you can start monitoring the SQL Server logs for these failed events, one way is to check in the xp_readerrorlog stored procedure which will give you a list of all the current server logs so run the following line :

EXEC xp_readerrorlog

This will give you a list of results for current log, if you want to go back further you can check additional logs by adding a integer value so as 0 for current, 1 for previous, 2 for… errr.. more previous.. etc. e.g.

EXEC xp_readerrorlog 1

The xp_readerrorlog procedure has a few more parameters though to narrow our search, the second parameter has two options and allows you to view either the errorlog (1 – default) or the agentlog (2) and the third option allows you to search only for specific text, so if we only wanted to see the failed login attempts we could use this

EXEC xp_readerrorlog 0,1,'Login Failed'

And vola, a list of failed logins 

Check Backups on Multiple Servers

I've just finished the backup checking on my SQL Server Admin tool program so you can check the state of the server backups all from one screen as shown below.




I’ll explain what checks I do, when you click the “Check Backup Status” button it will go off and check the msdb tables for the most recent database backup for each table, it gets the time and size and location, the software will then attempt to go and find the backup file and check the size and date stamp against what is stored in the database as a kind of quick verify.

There’s also another button called “Verify Backup with SQL” what this will ask the SQL Server to runs a restore on the backup set with the VERIFYONLY flag set to make sure that the file is valid, all of this is done in a threaded environment so that there is no program hang and multiple servers can be checked at once.

Next task for the program is replication validation.

SQL Backup Logs

While working on the backup checking screen on my SQL Server admin app I had to come up with a few scripts to check on the state of the database backup list. After checking the systems tables I found that the database to use is MSDB which seems to hold information for backups, restore, broker, mail, Agent and maintenance plan metadata, it’s a good job to check out the tables and views in there for youself. After checking out the tables and figuring out the linking it appears that tables I need to concentrate on are backupset, backupmediafamily and backupfile and according to MSDN this is what they do.


• dbo.backupset: provides information concerning the most-granular details of the backup process
• dbo.backupmediafamily: provides metadata for the physical backup files as they relate to backup sets
• dbo.backupfile: this system view provides the most-granular information for the physical backup files
OK So for a full list of database backups you can use this query

SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Database'  
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   msdb.dbo.backupset.backup_size,  
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
ORDER BY  
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.database_name

but this lists all the backups and I’m not really interested so the following SQL will give you the last backup for each database. As usual with these type of SQLs where you have to link on a subquery it’s pretty long.

SELECT  
   A.[Server],
   A.database_name,  
   A.last_db_backup_date,  
   B.backup_start_date,  
   B.expiration_date, 
   B.backup_size,  
   B.logical_device_name,  
   B.physical_device_name,   
   B.backupset_name, 
   B.description 
FROM 
   ( 
   SELECT   
       CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
       msdb.dbo.backupset.database_name,  
       MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date 
   FROM    msdb.dbo.backupmediafamily  
       INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
   WHERE   msdb..backupset.type = 'D' 
   GROUP BY 
       msdb.dbo.backupset.database_name  
   ) AS A 
    
   LEFT JOIN  

   ( 
   SELECT   
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   msdb.dbo.backupset.backup_size,  
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
WHERE  msdb..backupset.type = 'D' 
   ) AS B 
   ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date] 
ORDER BY  
   A.database_name

Of couse my app will be verifying the backups and the files produced so there’s more involved than the above but feel free to use it.

Executing SQLs across multiple servers

I've been working on a project to allow me to manage a few of my servers without having to pay each one attention in management studio. So this is a start, there's a lot more things I want to put in but for the moment it will allow you to execute SQLs across all your servers. Good for me since I have the same databases on loads of databases for the same application. I'll be posting a download link for it soon.

I also plan to implement some backup tracking / verifying, replication verification etc in later versions.

SQL Server Failed Replication and odd behaviour

This is just really a note to the other DBAs out there since I spent half an hour on this issue only to find it was an illogical random problem, but if you can't be bothered reading my story the short hand version is If you are having issues with replication and getting errors concerning snapshot being out of date or not available before you try to go on the wild goose chase, try restarting the SQL Agent service first.

Anywho back to what happened, got a call from a client that the server had stopped replicating between London and Basra, so I got into the server and check the replication monitor, under the subscription it stated You must rerun snapshot because current snapshot files are obsolete. OK I thought and started the snapshot agent, off it went and came back a few seconds later stating A snapshot of 94 articles has been generated. Now this is a 27GB database so I know for a fact that it takes ages for the snapshot to come around, I messed around with folder permissions on the snapshot directory, all seemed fine, I delete the current snapshot and tried the agent again. A snapshot of 94 articles has been generated... LIES!

At this point I went into the Job Manager and ran the merge replication job to see what errors would come up in the history.. The Job failed. The owner of job() ..... does not have server access and that was what made me finally figure what it was. A while back I had the same issue after a power cut, for some reason if there is network failure the Agent has a heart attack and just stops working. Restarting the service fixes the problem.

Adding a machine to a domain

You'll have to excuse me for this (I know tis a simpiltons task) but the specials in support keep asking again... and again... and again!

Log into the machine either with a local user account.
When the desktop appears do the following:
2000 & Windows XP

• Right click on the My Computer and select Properties from the context menu.
• On the Systems Properties screen select the Computer Name Tab and click the Change button
• On the Computer Name Change screen, select the Domian option and then enter a name of a domain e.g. mydomain.local or mycompany.com
• If prompted for a username and password enter one that has privileges to add and remove computers from the domain (usually a domain admin account)
• When the computer is added successfully you will get a message box stating something along the lines of Welcome to the domain
• Press OK
• Back on the Computer Name Change screen press OK
• On the Systems Properties Press OK
• Restart the machine
• When the login screen appears press the Options button and select the newly added domain from the Domain dropdown list
• Log into the machine as a domain user

Vista / Windows 7

• Right click on the My Computer and select Properties from the context menu.
• On the Systems screen select the Change Settings button under Computer name, domain, and workgroup settings
• When the Computer Name screen click the Change... button
• On the Computer Name/Domain Changes screen, select the Domain option and then enter a name of a domain e.g. mydomain.local or mycompany.com
• If prompted for a username and password enter one that has privileges to add and remove computers from the domain (usually a domain admin account)
• When the computer is remove you will get a message box stating something along the lines of Welcome to the domain
• Press OK
• Back on the Computer Name/Domain Changes screen press OK
• On the Systems Properties Press OK
• Close the System screen and restart the machine
• When the login screen appears press enter the name of the added domain and a domain user e.g. mydomain.local\Administrator to log onto that domain

There, now please remember this and stop asking me

BBC iPlayer

Since my parents are both blind using the BBC iPlayer website with their screen reader is an absolute pain, so here is a text only version I created that strips the information from the pages.

http://www.tagtronics.co.uk/tonline/

Export to PST from Exchange 2007

There was a nice time in the past when you could run ExMerge and export users mailboxes to your hearts content although in exchange 2007 onwards they've done away with such GUI goodness and replaced it with some exchange powerscripts... The command we're interested in is Export-Mailbox and requires the following parameters.

Export-Mailbox –Identity <mailboxUser> -PSTFolderPath <pathToSavePST>

Pretty simple right???? WRONG!
You can't run this on a 64bit machine (exchange 2007 is 64bit only for production release), because of the outlook components required it has to be run on a 32bit machine which means installing the Exchange Management Tools software again on a separate machine along with outlook 2003 or 2007 :s

Pretty lame but it does allow you to export mailboxes larger than 2GB which I found a rather annoying constraint of Exmerge.

Defragmenting the field indexes

I came across an odd problem the other day when one of our 32GB enterprise databases started showing some odd behavior and slowing to a halt. After running my usual checks to make sure the indexes where intact and the transaction logs weren't chocka I was left scratching my head. I tried routing in the execution plans to see where the bottleneck was and it was all pointing to an index issue although I couldn't find anything wrong until I checked the fragmentation levels. They basically showed up as 100% on the primary index of the main table. After some research I found the commands needed to defrag the database and created a routine to do the hard work for me on the rest of the fields in the database. So without further ado .....

CREATE PROCEDURE [IndexDefrag] AS
BEGIN
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR(128)
DECLARE @execstr VARCHAR(255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag decimal
DECLARE @maxfrag decimal
DECLARE @IdxName varchar(128)

-- Set the maximum fragmentation level, if the current frag level is below this it will be ignored.
SELECT @maxfrag = 10.0

-- Get a list of all the user tables in the database
DECLARE tables CURSOR FOR SELECT CAST(TABLE_SCHEMA AS VARCHAR(100)) + '.' + CAST(TABLE_NAME AS VARCHAR(100)) AS Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

-- Create a table to store information on the tables index list
CREATE TABLE #fraglist (ObjectName CHAR(255), ObjectId INT, IndexName CHAR(255), IndexId INT, Lvl INT, CountPages INT, CountRows INT, MinRecSize INT, MaxRecSize INT, AvgRecSize INT, ForRecCount INT, Extents INT, ExtentSwitches INT, AvgFreeBytes INT, AvgPageDensity INT, ScanDensity decimal, BestCount INT, ActualCount INT, LogicalFrag decimal, ExtentFrag decimal)

-- Open the cursor, then loop through the list of tables in the recordset
OPEN tables
FETCH NEXT FROM tables INTO @tablename
WHILE @@FETCH_STATUS = 0 
BEGIN
  -- Do the showcontig to get a list of all the indexes in the table
  INSERT INTO #fraglist 
  EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
  FETCH NEXT FROM tables INTO @tablename
END

-- Close and deallocate the cursor.
CLOSE tables
DEALLOCATE tables

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName FROM #fraglist WHERE LogicalFrag >= @maxfrag AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor.
OPEN indexes

-- Loop through the indexes and run the ALTER INDEX command to pull the fragmentation down
FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag, @IdxName 
WHILE @@FETCH_STATUS = 0 
BEGIN
  PRINT 'Now executing ALTER INDEX [' + RTRIM(@IdxName) + '] ON [' + RTRIM(@tablename) + '] REORGANIZE WITH ( LOB_COMPACTION = ON ) - Fragmentation currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%'
  SELECT @execstr = 'ALTER INDEX [' + RTRIM(@IdxName) + '] ON [' + RTRIM(@tablename) + '] REORGANIZE WITH ( LOB_COMPACTION = ON )'
  EXEC (@execstr)
  FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag, @IdxName
END

-- Close and deallocate the cursor.
CLOSE indexes
DEALLOCATE indexes
 
-- Delete the temporary table.
DROP TABLE #fraglist
END

Compress and Backup SQL Database

Down the line you may come across a point when you need to compress an SQL Database by truncating the log files, an annoying task that cannot be completely undertaken from the GUI (the shrink file option does not truncate the log files). After I had several companies complaining of excessively large Log files on some of our larger more heavily utilised databases I came up with the following script that can be run whenever you need to compress the database.

-- Shrink the Log Files as much as we can, this is the same as the GUI option
DBCC SHRINKFILE (N'DatabaseLogFileName',100)
-- We are now going to truncate the log files, there are rear occasions when you may want to keep the transaction logs but I've never had to work in such an environment as of yet. 
ALTER DATABASE [DatabaseName] SET RECOVERY SIMPLE
CHECKPOINT
-- Once the checkpoint command is run the log files they go bye bye so we can now set ourselves back to our original state
ALTER DATABASE [DatabaseName] SET RECOVERY FULL
-- OK Shrink the main database file
DBCC SHRINKDATABASE (DatabaseName)
-- Back it up to a file
BACKUP DATABASE [DatabaseName] TO DISK = N'BackupFileLocation' WITH NOFORMAT, INIT, NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
-- And while we're at it we might as well reindex the database, you can find this code in one of my previous articles
EXEC TableReindexer

Just make sure you replace the DatabaseLogFileName this is usually the name of the Database with _log stuck on the end, but can be located under logical name of the database file properties. DatabaseName and BackupFileLocation are pretty self explanatory.

You can also run this from a batch file using the SQLCMD command should you wish to set up a scheduled task to do such things as I do.

SET SQLSERVER=SERVERINSTANCE
SET DATABASE=DATABASENAME
SET BACKUPLOCATION=BACKUPLOCATION
SET BACKUPFILENAME=BACKUPNAME

DEL %BACKUPLOCATION%%BACKUPFILENAME% /S /Q

REM Reindex all the keys within the database
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "EXEC tag_tablereindexer"

REM -- Truncate the log file attached to the database, this can sometimes get a little large and slow down the system overall
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "DBCC SHRINKFILE (N'%DATABASE%_log',100)"
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "ALTER DATABASE [%DATABASE%] SET RECOVERY SIMPLE"
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "CHECKPOINT"
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "ALTER DATABASE [%DATABASE%] SET RECOVERY FULL"

REM -- Shrink the actual database itself
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "DBCC SHRINKDATABASE (%DATABASE%)"
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "DBCC SHRINKFILE (N'%DATABASE%_log',100)"
REM -- Backup the databse to our backup location
SQLCMD -S%SQLSERVER% -E -d%DATABASE% -Q "BACKUP DATABASE [%DATABASE%] TO DISK = N'%BACKUPLOCATION%%BACKUPFILENAME%' WITH NOFORMAT, INIT, NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

Again SQLSERVER should be set to the name of the server instance e.g. SQLSVR03\DB12 DATABASE is the database name, BACKUPLOCATION is the path to the backup which also requires to be left with a leadin backslash e.g. c:\backups\ BACKUPFILENAME is the name you want for the backup e.g. SQLBackup.bak

Manually reindex the fields within a table

One of the strange an annoying aspects of SQL is the way it sometimes just completely messes up the indexing on the tables. It can on occasion just become confungulated for no apparent reason resulting in extremely slow query execution. To combat such things consider running the stored proc when you have quiet moments in your database, it will run through each table and update the table statistics on that table. Vola!

CREATE PROCEDURE TableReIndexer
AS
BEGIN
-- REINDEX ALL TABLES IN THE DATABASE
DECLARE @SQLExec VARCHAR(255)
DECLARE MyCursor CURSOR FOR 
SELECT 'UPDATE STATISTICS [' + b.name + '] WITH FULLSCAN' AS [Exec Proc]
FROM sysobjects b
where b.type = 'U' AND b.category=0

OPEN MyCursor
FETCH MyCursor INTO @SQLExec
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC (@SQLExec)
FETCH MyCursor INTO @SQLExec
END
CLOSE MyCursor
DEALLOCATE MyCursor
END
GO

Setting up a wii for Backup DVDs

This will only work for 3.3 to 4.1x firmwares, use it at your own risk, also I'll assume you already have the Homebrew channel installed

Download these files

1. Copy the contents of the pack you downloaded above onto the root of your SD card keeping the folder structure.

If you are on System menu 3.3 or below you can skip steps 2 and 3.

2. Launch the Homebrew Channel, and run the Trucha Bug Restorer application

3. Press B for no IOS reload, Choose downgrade IOS15, choose load IOS from sd card, you may be asked this more than once. Follow any prompts to let the process finish, then press a to exit.

4. Run Trucha Bug Restorer, press left on the D-pad until you get to IOS15, and press A. When prompted press 1. Choose IOS36 menu and use the following settings:
Install IOS to slot (249)
Hash check (trucha) YES
ES_Identity YES
Nand Permissions YES

Scroll back up to highlight Install Patched IOS36 once the settings are identical to the above and press A. Choose load IOS from sd card. Follow any prompts and let the process finish.

If at this stage you get an error message stating that IOS36 is higher then v3094 follow the below steps. If you received no error messages, continue to step 5:

Open your SD card on your PC.

on the root there will be a file called IOS36-64-3094.wad.

Right-click and copy this into the wad folder that is also on root.

So now you should have two IOS36 files on your SD card, one on the root and one in the wad folder.

Place SD back in wii and open Trucha Bug Restorer using the Homebrew Channel.

Press left or right till it sits on IOS15. Press A and wait.

Then choose IOS36 Menu.

Set all options to yes.

Set Install to slot.to IOS36 (not IOS249).

Then choose Install hacked IOS36.

Follow the prompts and press A for SD Card whenever it asks.

Exit to Wii Menu when finished.

Open Homebrew Channel and select CIOS rev14 installer.

When Cios Installer opens tell it to use IOS36 and press A.

Press A again to install Cios.

It will then ask if you should do it via WAD or Network, choose WAD Install.

When it is done press HOME to exit.

Now continue from step 5.

**If you were only able to install Bootmii to IOS, repeat step 4 again, but choose to install to slot 36 instead of 249. You may want to install Priiloader next and make a NAND backup of your Wii and familarise yourself with how to access Priiloader before the system menu should you ever need to recover your Wii. When finished installing Priiloader you can continue with step 5.***

5. Open Wadmanager from the homebrew channel, when it asks choose use IOS249. Choose disable for NAND emulation. Press A to open from SD slot. Install IOS60.wad. Press A to install and follow the prompts. Press the home button once you’ve installed to return to the homebrew channel.

If your on System Menu 3.3 or below, you can skip step 6.

6. Open Trucha Bug Restorer again from the homebrew channel. Choose to load IOS249 and Press A, followed by 1 when prompted. Choose to restore IOS15, and select Load IOS from Sd card, follow the prompts and let the process finish.

7. Open Homebrew Channel and run wad manager again. When it opens choose IOS249, Press A to leave NAND emulator disabled, Press A to open from SD slot. Install the following WAD files to your Wii:
cIOS249-v14.wad
cIOS250-v14.wad
cIOS202[38+60]-v4.wad
cIOS222[38]-v4.wad
cIOS223[38+37]-v4.wad
uloader-UMRD wad

You are now set for playing backups on your Wii.

Jailbreaking the iPhone

A new and simple way to jailbreak the iphone has come to light that will allow you to do it all without the usual app installation and hacking. All you have to do is go to http://www.jailbreakme.com on your iPhone. The website downloads a file with a known fault that allows execution of the jailbreaking code, it takes a few minutes then it's all done you can then install free apps.

In the case of the free apps a good program to find them for you is installous, this requires a little more messing to get going but is a really good app and allows you to install all of the apps available for download without you going through the trouble of finding them yourself.

So

1. Open Cydia, tap on "Manage" at the bottom
2. Tap on "Sources"
3. Tap on "Edit" on top right then tap on "Add" on top left
4. Type in http://cydia.hackulo.us Cydia will warn you about copyright information just tap ok (hackuolo.us repository is very crowded and they lack donations so it is down very often. You may try the new brotherhood project repository http://cydia.install0us.com/. That is a ZERO not an O )
5. Wait for repository to be added and updated.
6. From search or hackulo.us repository directory
7. Install Installous
8. Restart your phone
9. Launch installous and have free fun.

This is all good news as I hate Apple and Steve jobs