MSSQL: Find tables that need indexing then generate rebuild/reorganise script

I’ve slightly modified this, but I can’t remember exactly where it came from so I cannot take all the credit.

This script run against the specified database, will identify tables that are too fragmented and generate scripts to that you can copy and past to fix. Try it out!

use PA — DATABASE NAME GOES HERE

SELECT

t.name,

ps.avg_fragmentation_in_percent,

‘ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ‘ +

CASE WHEN ps.avg_fragmentation_in_percent > 40 THEN ‘REBUILD’ ELSE ‘REORGANIZE’ END +

CASE WHEN pc.partition_count > 1 THEN ‘ PARTITION = ‘ + cast(ps.partition_number as nvarchar(max)) ELSE ” END as IndexScript

FROM

sys.indexes AS ix

INNER JOIN sys.tables t ON t.object_id = ix.object_id

INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

INNER JOIN (SELECT object_id, index_id, avg_fragmentation_in_percent, partition_number

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)) ps ON t.object_id = ps.object_id AND ix.index_id = ps.index_id

INNER JOIN (SELECT object_id, index_id, COUNT(DISTINCT partition_number) AS partition_count

FROM sys.partitions

GROUP BY object_id, index_id) pc ON t.object_id = pc.object_id AND ix.index_id = pc.index_id

WHERE

ps.avg_fragmentation_in_percent > 10

AND ix.name IS NOT NULL

ORDER BY avg_fragmentation_in_percent DESC

Ultra-simple PHP Thumbnail Web Gallery

I used this once when I wanted to share a bunch of photos that I took on a group holiday. Years later, this is still my favourite simple option. All you need is a for this to work is a folder called ‘thumbs’ and a folder called ‘images’ (guess what goes where) – just make sure the file names are identical in each. Then simply make your index.php for the root folder out of the below code and you are done!

<?php
if ($handle = opendir('thumbs')) {
      $list = array();
    while (false !== ($file = readdir($handle))) {
            array_push($list, $file);
            sort($list);
    }
     closedir($handle);

    foreach ($list as $filename) {
            if ($filename == '.' || $filename == '..') {
                  //do nothing
            } else {
                  echo '<a href="images/'.$filename.'"><img src="thumbs/'.$filename.'" border=0></a>';
            }
      }
}
?>

Record MS Access field changes to in change log table

I just wrote this little gem which is dynamic enough to withstand all the fields that I am yet to add to my form. It records changes to textboxes and comboboxes. It even takes the displayed content of the comboboxes rather than the bound data so that is end-user friendly.

Steps are:

1. Create the table (mine is in SQL then linked in Access hence the difference in name).

CREATE TABLE [dbo].[tbl_Members_ChangeLog](
[ChangeLog_ID] [int] IDENTITY(1,1) NOT NULL,
[Store_ID] [int] NOT NULL,
[ChangedBy] [varchar](50) NULL,
[DateTime] [smalldatetime] NULL,
[Field] [varchar](50) NULL,
[OldValue] [varchar](500) NULL,
[NewValue] [varchar](500) NULL,
CONSTRAINT [PK_Members_ChangeLog] PRIMARY KEY CLUSTERED
(
[ChangeLog_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tbl_Members_ChangeLog] ADD CONSTRAINT [DF_Members_ChangeLog_DateTime] DEFAULT (getdate()) FOR [DateTime]
GO

2. Added the fOSUserName() function to your VBA module to get the windows user from here: http://access.mvps.org/access/api/api0008.htm

3. Add this code to catch the changes. You can call it from your form’s BeforeUpdate Sub:

Sub UpdateChangeLog()
Dim sSql As String
Dim rs As Recordset
Dim OldVal As String, NewVal As String

Debug.Print “—-”
‘cycle through text and combo boxes
For Each ctl In Me.Controls

If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
If (ctl.Value <> ctl.OldValue) Or (Nz(ctl.OldValue, “”) = “” And ctl.Value <> “”) Then
If ctl.ControlType = acTextBox Then
OldVal = Nz(ctl.OldValue, “”)
NewVal = Nz(ctl.Value, “”)
ElseIf ctl.ControlType = acComboBox Then

‘get text for combobox lookup old value
If Nz(ctl.OldValue, “”) = “” Then
OldVal = “”
Else
Set rs = CurrentDb.OpenRecordset(ctl.RowSource, dbOpenSnapshot)
rs.MoveFirst
rs.FindFirst rs.Fields(0).Name & ” = ” & ctl.OldValue
OldVal = rs.Fields(1)
rs.Close
End If

‘get text for combobox lookup new value
Set rs = CurrentDb.OpenRecordset(ctl.RowSource, dbOpenSnapshot)
rs.MoveFirst
rs.FindFirst rs.Fields(0).Name & ” = ” & ctl.Value
NewVal = rs.Fields(1)
rs.Close

End If

Debug.Print ctl.Name
sSql = “INSERT INTO dbo_tbl_Members_ChangeLog (Store_ID, ChangedBy, Field, OldValue, NewValue) ” & _
“VALUES (” & Me.Store_ID & “, ‘” & fOSUserName() & “‘, ‘” & ctl.ControlSource & “‘, ‘” & OldVal & “‘, ‘” & NewVal & “‘);”

DoCmd.RunSQL sSql

End If
End If

Next ctl

Set rs = Nothing
End Sub

SSRS Query for Report Executions

I whipped up this query to put in a report to give me a list of the executions of our SQL Server Reporting Services reports and their run counts with the user and additional details being available on a drill down. It’s a simple query and very easy to adapt.

SELECT C.[Path], C.CreationDate, CU.UserName as CreatedBy, C.ModifiedDate, MU.Username as ModifiedBy,  E.Username as RunBy, E.TimeStart as DateTimeRun, DATEDIFF("ss", E.TimeStart, E.TimeEnd) as ExecutionTime, E.Parameters as RunParameters

FROM [Catalog] C 
 LEFT JOIN Users CU ON C.CreatedById = CU.UserID
 LEFT JOIN Users MU ON C.ModifiedById = MU.UserID
 LEFT JOIN ExecutionLog E ON C.ItemID = E.ReportID
WHERE C.[Type] = 2 -- reports only
ORDER BY C.[Path], RunBy, E.TimeStart

Get values from an SQL Stored Procedure into Excel (VBA macro)

I’m using the below function in an Excel macro to get the results of a simple stored procedure into an Excel spreadsheet. The function needs the connection string defined within it, but this can easily setup to be dynamic in the function parameters if needed. Also I’m only using one parameter, but I have no doubt that you create a multi-value function parameter to take several stored proc parameters.

In your macro, simply call the function and it will populate cells with your stored proc results beginning at the current active cell.

Function insertStoredProcValues(spName As String, strParameter As String)

    Dim Conn As ADODB.Connection
    Dim ADODBCmd As ADODB.Command
    Dim rs As ADODB.Recordset
    Dim i As Integer
    Dim sConnect As String

    '--DEFINE CONNECTION STRING HERE---------------------------------------------------------
    sConnect = "driver={sql server}; server=MYSERVERNAME; Database=MYDATABASENAME; UID=MYUSERNAME; PWD=MYPASSWORD;"
    '----------------------------------------------------------------------------------------

    'Establish connection
    Set Conn = New ADODB.Connection
    Conn.ConnectionString = sConnect
    Conn.Open

    'Open recordset
    Set ADODBCmd = New ADODB.Command
    ADODBCmd.ActiveConnection = Conn
    ADODBCmd.CommandText = spName
    ADODBCmd.CommandType = adCmdStoredProc
    ADODBCmd.Parameters.Refresh
    ADODBCmd.Parameters(1).Value = strParameter
    Set rs = ADODBCmd.Execute()

    'Loop through recordset and place values
    rs.MoveFirst
    Do While rs.EOF = False
        For i = 0 To rs.Fields.Count - 1
            ActiveCell.Value = rs.Fields(i).Value   'insert value into cell
            ActiveCell.Offset(0, 1).Activate        'move to next cell for next value
        Next i
        ActiveCell.Offset(1, -i).Activate           'move to next row for next record
    rs.MoveNext
    Loop

    'Clean up
    rs.Close
    Set rs = Nothing
End Function
 

SSIS: Output SQL to XML file

I wanted to create an XML file from an SQL table using SSIS. I found the following blog, but the method I used is in the user comments from “J. Morgan Smith”. I also had to do what “sam” and “BB88″ said.

http://consultingblogs.emc.com/jamiethomson/archive/2006/07/11/4209.aspx

 

SQL Reporting Services Date Parameters

Again, I’m posting these here mainly to assist in my own forgetfulness.

Start of Month: =DateAdd("d", -datepart("d", Today()) +1, Today())

Start of Last Month: =DateAdd("m", -1, DateAdd("d", -datepart("d", Today()) +1, Today()))

Last Day of Last Month: =DateAdd("d", -datepart("d", Today()) , Today())

Having trouble bridging your modem and your Astaro Security Gateway?

I know I was. I just couldn’t get the thing to connect when I change my ASG’s external interface from ‘Cable Modem (DHCP)’ to PPPoE.

The answer? Instead of changing the interface delete it first and just re-create it – worked a charm for me!

Also worth noting that deleting the interface took my NAT settings with it – I just reverted to the backup, took some screen shots and then recreated them.

MS Access – Link tables with VBA

Just posting this here because I can never remember it myself. I can also never find it online either so it might be simple and handy to others too…

Sub LinkTables()
    Dim tbl As New TableDef
    Dim ConnectString As String

    ConnectString = "ODBC;DRIVER=SQL Server;SERVER=myServerName;DATABASE=myDatabaseName;uid=myUserID;pwd=myPa$$w0rd"

    Set tbl = New TableDef
    On Error Resume Next

    tbl.Name = "destinationTableName"
    tbl.SourceTableName = "sourceTableName"
    tbl.Connect = ConnectString
    CurrentDb.TableDefs.Append tbl
End Sub

Google mail as a backup email server/mail queue using Small Business Server 2008

This is an expansion of an idea I saw here http://weblogs.asp.net/guybarrette/archive/2008/05/25/using-gmail-as-a-backup-mail-server.aspx

The situation:

I have a small business with one server running SBS 2008 and I want email redundancy so that I don’t miss any email while my server is down for maintenance, power failure, etc. I also don’t want the hassle of periodically checking my off-site email address to see if anything has end up there, hence, I want a mail queue.

The solution:

• Sign up for Google Apps email account (www.google.com/a).
• Replicate your user accounts on the Google mail. You may as well use the one administrator password for all accounts because users will never need to log in themselves.
• Assign your secondary MX records to point to the Google addresses specified in the settings they give.

At this point, Google will receive your mail if your server is unreachable.

• On the SBS Console, click the Netwtorking tab, then the Connectivity tab
• Setup the SBS POP 3 Connector using the information available in the Google settings for each account
• Setup the POP/Forwarding in each Google account and you’re done!

So now, if your server is unreachable, Google will get your mail. When your server is back up, it will query the Google inboxes at the intervals you specified and forward your mail to where it needs to go!