Thursday, March 5, 2015

Powershell progress bar for Exchange mailbox moves

I was at a client site recently to provide backup support for some cross-forest Exchange mailbox migrations, which meant I was there onsite but not actually doing much aside from the occasional weird error when Prepare-MoveRequest.ps1 couldn't find the target AD account and decided to create a new one, which is more than a little annoying. But anyway, I have always wanted to play with Powershell with Windows Forms, and the Exchange Management Console's move request section is sorely lacking in graphical progress. Sure, you can add the "Percent Complete" column but you still have to refresh it manually. Usually I just execute

while((get-moverequest -MoveStatus InProgress) -ne $null) {get-moverequest -MoveStatus InProgress | Get-MoveRequestStatistics; start-sleep -s 5}

But I thought it would be cool to have a GUI progress bar so I did a little Googling and found two articles that combined provided me with what I needed and I used my free time to build a cool script that my client can use for their migrations.

Using the code from the first article created the window and progress bar just fine but it wouldn't update. The second article provided the key to getting it working properly using a Timer. Without further ado, here is the final code to pop up a progress bar for an Exchange mailbox move. Although it does lock up the Powershell window while it's running, and you have to launch it from an Exchange Powershell, you can't just right click and say Open With Powershell. Maybe I'll update this in the future.

[CmdletBinding()] 
param(
[Parameter(Mandatory=$true)][string]$Username
)

Add-Type -assembly System.Windows.Forms

$mb = Get-MoveRequest $Username

$Title = "Mailbox Move Progress: $($mb.DisplayName)"
$height=100
$width=400
$color = "White"

$form1 = New-Object System.Windows.Forms.Form
$form1.Text = $title
$form1.Height = $height
$form1.Width = $width
$form1.BackColor = $color

$form1.FormBorderStyle = [System.Windows.Forms.FormBorderStyle]::FixedSingle 
$form1.StartPosition = [System.Windows.Forms.FormStartPosition]::CenterScreen

$label1 = New-Object system.Windows.Forms.Label
$label1.Text = "not started"
$label1.Left=5
$label1.Top= 10
$label1.Width= $width - 20
$label1.Height=15
$label1.Font= "Verdana"

$form1.controls.add($label1)

$progressBar1 = New-Object System.Windows.Forms.ProgressBar
$progressBar1.Name = 'progressBar1'
$progressBar1.Value = 0
$progressBar1.Style="Continuous"

$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = $width - 40
$System_Drawing_Size.Height = 20
$progressBar1.Size = $System_Drawing_Size

$progressBar1.Left = 5
$progressBar1.Top = 40

$form1.Controls.Add($progressBar1)

$label1.text="Preparing to analyze $($mb.DisplayName)"
$form1.Refresh()

start-sleep -Seconds 1

Function GetPct {
    if((Get-MoveRequest -Identity $Username).Status -eq "InProgress") {
        [int]$pct = (Get-MoveRequestStatistics $Username).PercentComplete
        $progressbar1.Value = $pct
        $label1.text="$($mb.DisplayName) Progress: $pct %"
        #Write-Host $pct
        $form1.Refresh()
    } else {
        $timer.enabled = $false
        Write-Host "Move complete, please close window when ready."
    }
}

$timer = New-Object System.Windows.Forms.Timer 
$timer.Interval = 5000

$timer.add_Tick({
GetPct
})

if((Get-MoveRequest -Identity $Username).Status -eq "InProgress") {
    $timer.Enabled = $true
    $timer.Start()

    $form1.Add_Shown({$form1.Activate()})
    $form1.ShowDialog()
} else {
    Write-Host "Move is $($mb.Status) for $($mb.DisplayName)"
}

Friday, April 25, 2014

Windows Server 2012 "Run as Administrator"

Windows Server 2012 has some nice features but the Windows 8 interface is horrible. The Dashboard gives you easy access to "Administrative Tools" but I find myself running most things like regedit from a Powershell window. Since I'm always remoted into a server via Terminal Services, TeamViewer or whatever else, Windows Key shortcuts are not available without reconfiguration.

The worst part is getting access denied when trying to do anything without right-clicking and selecting "Run as Administrator" even with the UAC slider all the way down! Luckily there is a fix for this.

Set-ItemProperty -Path "HKLM:\Software\Microsoft\Windows\CurrentVersion\Policies\System" -Name "EnableLUA" -Value "0"

This effectively makes everything run as administrator, which is how it should be when I'm LOGGED INTO A SERVER AS ADMINISTRATOR.

Wednesday, February 6, 2013

Secondary DNS vs. Conditional Forwarders

I came across an interesting problem today. I have a client with three sites, all connected by VPN, each with their own Active Directory forests connected via Trust Relationships. All servers deliver mail to each others Hub Transport servers through internal IPs and host names across the VPN, external SMTP delivery is locked down pretty tight.

Everything was working well until yesterday, when users from Site B could not send emails to Site A, the NDR was a 5.5.0 No unauthenticated relaying permitted. The weird thing is that it came from some unknown mail server. IP lookup showed some east coast company, this client doesn't have any east coast locations.

After a little checking, it turns out that IP is their public web server, hosted by a company on the east coast. So why was internal mail being routed to the public web server?

The answer was DNS. Their public DNS has a wildcard record which resolves any unknown host name to the web site, which is a pretty standard configuration. The VPN between Sites A and B must have been down, causing mail-server.internal.site-a.com to resolve to their web site's address via public DNS.

Now I remember when I had setup the mail routing between sites, there was a conditional forwarder already configured on Site B's DNS server for internal.site-a.com. That's fine, I usually add secondary DNS instead but the end result is the same so I just left it alone.

Turns out, conditional forwarders don't work when the VPN is down because the remote DNS server is inaccessible and it falls back on public DNS, which led to this chain of events. The solution was to delete the conditional forwarders and add internal.site-a.com to Site B's DNS as a secondary domain. In the event of another VPN outage, the remote server will still resolve to the correct internal IP and mail will just queue up until the VPN is up again.

So if you ever have the option to setup conditional forwarders or secondary DNS and aren't sure which one to choose, I would go with secondary DNS since it still resolves the correct addresses when site links are down.

Thursday, August 12, 2010

.NET Image Resizing!

I have had a function in my personal library for quite a few years called "ResizeImage" which takes a filename, width, and height as parameters. It creates a new file with the width and height and saves it with the same filename and a number appended to it to indicate the new size. It handles JPG, GIF, and PNG files. It did the job for a while but wasn't perfect.

The problem is that the resizing quality never really looked that great, and to maintain proportions it would calculate which is larger, the width or height, and calculate the difference in the smaller dimension which would result in an image that was no more than the width or height specified in the parameters, but the smaller dimension would be ignored. That's fine and all but I really would rather it use the actual width and height specified by the person calling the function, without contorting the image, adding whitespace to the smaller dimension, the equivalent of using both Image Size and then Canvas Size in Photoshop. So I finally got around to making it better! The System.Drawing.Graphics namespace has a ton of features to get the job done, and it's all using the standard .NET libraries.

Here is the final product:


Function ResizeImage(ByVal sFilename As String, ByVal iMaxW As Integer, ByVal iMaxH As Integer) As String
Dim imgPhoto As System.Drawing.Image = Drawing.Image.FromFile(sFilename)
Dim x, y, x2, y2, x3, y3 As Integer
Dim xD, yD As Double

x = imgPhoto.Width
y = imgPhoto.Height

xD = x / iMaxW
yD = y / iMaxH

If xD > yD Then
x2 = iMaxW
y2 = y * (iMaxW / x)
Else
y2 = iMaxH
x2 = x * (iMaxH / y)
End If

Dim imgOutput As New System.Drawing.Bitmap(x2, y2)
Dim g As System.Drawing.Graphics = System.Drawing.Graphics.FromImage(imgOutput)
g.InterpolationMode = Drawing2D.InterpolationMode.HighQualityBicubic
g.SmoothingMode = Drawing2D.SmoothingMode.HighQuality
g.PixelOffsetMode = Drawing2D.PixelOffsetMode.HighQuality
g.CompositingQuality = Drawing2D.CompositingQuality.HighQuality
g.DrawImage(imgPhoto, 0, 0, x2, y2)

Dim imgExact As New Bitmap(iMaxW, iMaxH)

Dim gE As Graphics = Graphics.FromImage(imgExact)
gE.Clear(Color.FromArgb(-1))
x3 = (iMaxW / 2) - (imgOutput.Width / 2)
y3 = (iMaxH / 2) - (imgOutput.Height / 2)
Dim rDest As New Rectangle(x3, y3, imgOutput.Width, imgOutput.Height)
gE.DrawImage(imgOutput, rDest, 0, 0, imgOutput.Width, imgOutput.Height, Drawing.GraphicsUnit.Pixel)

Dim sExt As String = Path.GetExtension(sFilename).ToLower
Dim sNewFilename As String = Replace(sFilename.ToLower, sExt, "_" & iMaxW & "x" & iMaxH & sExt)
Select Case sExt
Case ".jpg"
Dim info() As ImageCodecInfo = ImageCodecInfo.GetImageEncoders()
Dim enc As EncoderParameters = New EncoderParameters(1)
enc.Param(0) = New EncoderParameter(Encoder.Quality, 100L)
imgExact.Save(sNewFilename, info(1), enc)
Case ".gif"
imgExact.Save(sNewFilename, Drawing.Imaging.ImageFormat.Gif)
Case ".png"
imgExact.Save(sNewFilename, Drawing.Imaging.ImageFormat.Png)
End Select
imgOutput.Dispose()
imgPhoto.Dispose()
imgExact.Dispose()
imgPhoto = Nothing
imgOutput = Nothing
imgExact = Nothing

Return sNewFilename
End Function

Friday, April 30, 2010

More Fun With XML and XSLT!

This was a fun one. A client wanted to receive an email whenever a new user account is created on their web site. Simple enough, but they wanted all of the customer information such as address, email, etc. Now it's not a lot of data, about 10 fields and I could have just concatenated it into a string and sent it as the HTML body. But that's no fun! Instead we will use XSLT with some custom code to format the phone numbers properly. And we will get the data directly from a dataset! And we will do it all in memory without writing any files!

First, a SQL stored procedure to retrieve the customer data:


CREATE PROC usp_user

@Users_ID int

AS

SELECT u.Users_ID, u.Customers_ID, u.FName+' '+u.LName As DisplayName,
u.FName, u.LName, u.Email, u.Active, u.Create_Date, c.Company, c.Address1,
c.Address2, c.City, c.State, c.Zip, c.Phone1, c.Phone2, c.Fax,
c.Create_Date, c.Active, r.Role, u.User_Phone1
FROM Users u
INNER JOIN Customers c ON c.Customers_ID=u.Customers_ID
WHERE u.Users_ID=@Users_ID


Then, a class to handle our data format functions:


Public Class xsldataformatter
Public Function FormatPhone(ByVal strOrigPhoneAs String) As String
If IsDBNull(strOrigPhone) Then
Return ""
Else
Dim strMatch, strNewPhone As String
Dim intDigits As Integer
strMatch = "[^0-9]"

' This regex strips out all non number characters
strNewPhone = System.Text.RegularExpressions.Regex.Replace(strOrigPhone, strMatch, "", System.Text.RegularExpressions.RegexOptions.Singleline)
intDigits = strNewPhone.Length
If intDigits > 10 Then
strNewPhone = System.Text.RegularExpressions.Regex.Replace(strNewPhone, "^(...)(...)(....)(.*)$", "($1) $2-$3 x$4")
Else
strNewPhone = System.Text.RegularExpressions.Regex.Replace(strNewPhone, "^(...)(...)(....)(.*)$", "($1) $2-$3")
End If
Return (strNewPhone)
End If
End Function
End Class


Next, our XSLT file, "newuser.xslt". Notice the line at the top, xmlns:formatdata="urn:xsldataformatter", that will be used later:


<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
xmlns:formatdata="urn:xsldataformatter"
>
<xsl:output method="xml" indent="yes"/>

<xsl:template match="/NewUser/User">
<style>
body,td {font-family: Myriad, Arial, Helvetica, sans-serif; font-size: 10pt;}
td,th {vertical-align: top; }
h1, .h1 {font-family: Avant Garde, Century Gothic, Arial, Helvetica, sans-serif; font-size: 16pt; font-weight:bold; color: black; text-shadow: 3px 3px 3px #bbbbbb;}
h2, .h2 {font-family: Avant Garde, Century Gothic, Arial, Helvetica, sans-serif; font-size: 14pt; font-weight:bold; color: black; text-shadow: 3px 3px 3px #bbbbbb;}
h3, .h3 {font-family: Avant Garde, Century Gothic, Arial, Helvetica, sans-serif; font-size: 12pt; font-weight:bold; color: black; text-shadow: 3px 3px 3px #bbbbbb;}
h4, .h4 {font-family: Avant Garde, Century Gothic, Arial, Helvetica, sans-serif; font-size: 11pt; font-weight:bold; color: black; text-shadow: 3px 3px 3px #bbbbbb;}
TH, .th {background-color: #505050; color: White; font-weight:bold; border: 1px solid black}
A:link {color: #333333;}
A:visited {color: #333333}
A:hover {color: Blue}
</style>
<h1>New User Created</h1>
<table align="center">
<tr>
<th align="right" nowrap="nowrap">Company:</th>
<td nowrap="nowrap">
<xsl:element name="a">
<xsl:attribute name="href">
https://www.mywebsite.com/admin/customers.aspx?c=<xsl:value-of select="Customers_ID"/>
</xsl:attribute>
<xsl:value-of select="Company"/>
</xsl:element>
</td>
</tr>
<tr>
<th align="right" nowrap="nowrap">Name:</th>
<td nowrap="nowrap">
<xsl:element name="a">
<xsl:attribute name="href">
https://www.mywebsite.com/admin/users.aspx?u=<xsl:value-of select="Users_ID"/>
</xsl:attribute>
<xsl:value-of select="DisplayName"/>
</xsl:element>
</td>
</tr>
<tr>
<th align="right" nowrap="nowrap">Email:</th>
<td nowrap="nowrap">
<xsl:element name="a">
<xsl:attribute name="href">
mailto:<xsl:value-of select="Email"/>
</xsl:attribute>
<xsl:value-of select="Email"/>
</xsl:element>
</td>
</tr>
<tr>
<th align="right" nowrap="nowrap">Address:</th>
<td nowrap="nowrap">
<xsl:value-of select="Address1"/>
<xsl:if test="Address2 != ''">, <xsl:value-of select="Address2"/></xsl:if>
<br />
<xsl:value-of select="City"/>, <xsl:value-of select="State"/> <xsl:value-of select="Zip"/>
</td>
</tr>
<tr>
<th align="right" nowrap="nowrap">Phone 1:</th>
<td nowrap="nowrap">
<xsl:value-of select="formatdata:FormatPhone(Phone1)"/>
</td>
</tr>
<tr>
<th align="right" nowrap="nowrap">Phone 2:</th>
<td nowrap="nowrap">
<xsl:value-of select="formatdata:FormatPhone(Phone2)"/>
</td>
</tr>
<tr>
<th align="right" nowrap="nowrap">User Phone:</th>
<td nowrap="nowrap">
<xsl:value-of select="formatdata:FormatPhone(User_Phone1)"/>
</td>
</tr>
<tr>
<th align="right" nowrap="nowrap">Fax:</th>
<td nowrap="nowrap">
<xsl:value-of select="formatdata:FormatPhone(Fax)"/>
</td>
</tr>
</table>
</xsl:template>
</xsl:stylesheet>



And finally, the routine to put it all together:


Sub SendNewUserNotification(ByVal userid As Integer)
Dim dsU As New DataSet("NewUser")
Dim daU As New SqlDataAdapter("usp_user", oConn)
With daU.SelectCommand
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@Users_ID", SqlDbType.Int).Value = userid
End With
daU.Fill(dsU, "User")

Dim x As New XmlDataDocument(dsU)
Dim xt As New XslCompiledTransform
Dim ms As New MemoryStream
Dim sr As New StreamReader(ms)
Dim xw As New XmlTextWriter(ms, System.Text.Encoding.UTF8)
Dim xa As New XsltArgumentList
xa.AddExtensionObject("urn:xsldataformatter", New xsldataformatter)
xt.Load(Server.MapPath("/newuser.xslt"))
xw.Flush()
xt.Transform(x, xa, xw)
ms.Seek(0, SeekOrigin.Begin)
Dim htm As String = sr.ReadToEnd
xw.Close()
ms.Close()
sr.Close()

Dim oMsg As New Net.Mail.MailMessage
oMsg.From = New Net.Mail.MailAddress("support@mywebsite.com")
oMsg.To.Add(New Net.Mail.MailAddress("admin@mywebsite.com"))
oMsg.Subject = "New User Notification"
oMsg.IsBodyHtml = True
oMsg.Body = htm
Dim smtp As New System.Net.Mail.SmtpClient()
Try
smtp.Send(oMsg)
Catch ex As Exception
End Try
End Sub


That's it! Our custom function is loaded into the XslCompiledTransform using the AddExtensionObject method of XsltArgumentList. Also, make sure your SMTP server is setup in web.config in the configuration section:


<system.net>
<mailSettings>
<smtp from="support@mywebsite.com">
<network host="localhost"/>
</smtp>
</mailSettings>
</system.net>

Monday, February 1, 2010

Custom Sitemap/Treeview Navigation

This was a bit of a challenge, the goal was to provide a Treeview control that lets you navigate through product categories that are nested and filter a Gridview based on the selection. The list of categories comes from a SQL table, the categories can change anytime based on the product list so using a standard XML file as a datasource was not an option. The data had to come directly from a SQL query. This exercise uses SQL stored procedures, Datasets, XML, and XSLT.

First, a couple of tables, Categories and Products.

Categories:

Categories_ID Category Parent_ID
1 Fruit NULL
2 Meat NULL
3 Dairy NULL
4 Apples 1
5 Oranges 1
6 Chicken 2
7 Beef 2
8 Milk 3
9 Cheese 3


Products:

Products_ID ProductName Categories_ID
1 Red Apples 4
2 Green Apples 4
3 Bananas 1
4 Oranges 5
5 Chicken Wings 6
6 Breast Meat 6
7 Pork Chops 2
8 Ground Beef 7


First notice that with Parent_ID we can have multiple levels, but in this example we're only going to have Category and Subcategory. Some products are assigned to subcategories such as Green Apples (category: Fruit, subcategory: Apples) but others are just assigned to a top-level category (Pork Chops, Bananas).

Now for the stored procedure to build the navigation:


CREATE PROC [dbo].[mysp_catnav]

@Categories_ID int=NULL

AS

SELECT Categories_ID, Category As CategoryName
FROM Categories Category
WHERE Parent_ID Is NULL
ORDER BY Category

SELECT Categories_ID As Subcategories_ID, Category As SubcategoryName,
Parent_ID As Categories_ID
FROM Categories Subcategory
WHERE Parent_ID=@Categories_ID
OR Parent_ID=(SELECT Parent_ID FROM Categories WHERE Categories_ID=@Categories_ID)
ORDER BY Category


This returns two datasets which is why we'll use a SqlDataAdapter to fill the dataset. But first let's drop some controls on the page, a Treeview and XmlDatasource.


<asp:treeview id="tvCats" runat="server" showlines="True" datasourceid="xmlCats">
<databindings>
<asp:treenodebinding datamember="Categories" valuefield="ID" textfield="Name" navigateurlfield="URL" depth="0">
<asp:treenodebinding datamember="Category" valuefield="ID" textfield="Name" navigateurlfield="URL" depth="1">
<asp:treenodebinding datamember="Subcategory" value="ID" textfield="Name" navigateurlfield="URL" depth="2">
</asp:treenodebinding>
</asp:treenodebinding>

<asp:xmldatasource id="xmlCats" runat="server" transformfile="/nav.xsl" enablecaching="False">
</asp:xmldatasource></asp:treenodebinding></databindings></asp:treeview>


Notice we don't specify the DatasourceID of the XmlDataSource, that's because we're going to generate it on the fly. In the code file, set the .Data attribute during Page.Load:


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
xmlCats.Data = xmlNav()
End Sub


Then the function to fill the dataset and manipulate it to fit a Treeview control:


Private oConn As New SqlConnection(ConnectionStrings("myConnectionString").ConnectionString)

Function xmlNav() As String
Dim daCats As New SqlDataAdapter("mysp_catnav", oConn)
Dim dsCats As New DataSet("Categories")
Dim sqlCats As SqlCommand = daCats.SelectCommand
With sqlCats
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@Categories_ID", SqlDbType.Int).Value = IIf(Request.QueryString("c") = "", DBNull.Value, Request.QueryString("c"))
End With

daCats.Fill(dsCats, "Category")
If dsCats.Tables.Count > 1 Then
dsCats.Tables(1).TableName = "Subcategory"
Dim dtCats As DataTable = dsCats.Tables("Category")
Dim dtSubs As DataTable = dsCats.Tables("Subcategory")
Dim relSC As New DataRelation("Subcategories", dtCats.Columns("Categories_ID"), dtSubs.Columns("Categories_ID"))
relSC.Nested = True
dsCats.Relations.Add(relSC)
End If

Return dsCats.GetXml
End Function


You might want to validate that the QueryString "c" is actually a number. The datarelation is necessary to get the dataset to put the second table in the proper hierarchy in the XML data. .Nested = True is especially important.

And then the XSL to transform the raw dataset XML into what the Treeview expects. The values for URL, ID, and Text have to be attributes, not elements so you can't get a dataset to output it that way without transforms:

nav.xsl:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:formatdata="urn:xsldataformatter">
<xsl:template name="top" match="/Categories">
<xsl:element name="Categories">
<xsl:attribute name="Name">All Products</xsl:attribute>
<xsl:attribute name="ID"></xsl:attribute>
<xsl:attribute name="URL">products.aspx</xsl:attribute>
<xsl:apply-templates select="Category" mode="cat" />
</xsl:element>
</xsl:template>

<xsl:template name="cat" match="Category" mode="cat">
<xsl:element name="Category">
<xsl:attribute name="Name">
<xsl:value-of select="CategoryName"/>
</xsl:attribute>
<xsl:attribute name="ID">
<xsl:value-of select="Categories_ID"/>
</xsl:attribute>
<xsl:attribute name="URL">products.aspx?c=<xsl:value-of select="Categories_ID"/>
</xsl:attribute>
<xsl:apply-templates select="Subcategory" mode="child" />
</xsl:element>
</xsl:template>

<xsl:template name="sub" match="Subcategory" mode="child">
<xsl:element name="Subcategory">
<xsl:attribute name="Name">
<xsl:value-of select="SubcategoryName"/>
</xsl:attribute>
<xsl:attribute name="ID">
<xsl:value-of select="Subcategories_ID"/>
</xsl:attribute>
<xsl:attribute name="URL">products.aspx?c=<xsl:value-of select="Subcategories_ID"/>
</xsl:attribute>
</xsl:element>
</xsl:template>
</xsl:stylesheet>


Finally just add a Gridview control to display the data from the Products table, using the Querystring value "c" as the filter, pass that as a QueryStringParameter for @Categories_ID.


CREATE PROC [dbo].[mysp_products]

@Categories_ID int=NULL

AS

SELECT Products_ID, ProductName
FROM Products
WHERE (Categories_ID=IsNULL(@Categories_ID, Categories_ID) OR Categories_ID IN (SELECT Categories_ID FROM Categories WHERE Parent_ID=@Categories_ID))
ORDER BY ProductName


That's about it! The product list will display all products matching the category and any child categories, so if you select Fruit you will get Bananas, Green Apples, and Red Apples but if you select Apples, you will only get Green Apples and Red Apples.

Wednesday, November 4, 2009

SQL Stuff!

OK kids, time for some stupid SQL tricks. Actually these are mostly things that I don't use that often so it's hard to keep them memorized when I do need them. I have these in a Sharepoint list but you don't have access to that, so here they are.

1. First, converting date formats. Sometimes you need to store the day without the time, and sometimes you need to return a string that does not contain the time.

This converts '1/1/2000 4:20 PM' into '1/1/2000 12:00 AM', which is the actual value of the date without the time:
Convert(datetime, Convert(varchar(15), getdate(), 101))

This converts '1/1/2000 4:20 PM' into '1/1/2000':
Convert(varchar(15), getdate(), 101)

2. Another useful command is when you suddenly discover that the log file has grown out of control because the default recovery model is Full and you're not using Backup Exec with the SQL Agent to backup your database. If your database is not mission-critical and you're OK with restoring from last night's backup in the event of a complete disk failure/database corruption/etc., you can set the recovery model to Simple. However the log file won't shrink until a full SQL-aware backup has been performed. If you don't care and just need to shrink it now, it's a two-step process.

backup log [dbname] with truncate_only
go
dbcc shrinkfile('[logname]', 63)
go


It's important to note that the '[logname]' is not the filename, nor is it the name of the database, it's the Logical Name listed in the Files section of the Database properties. Enclose it in single quotes since it is a string value, not an object. However [dbname] is the object name of the database without quotes just like "use [dbname]". This DBCC command shrinks it to about 64 MB. Just make sure you perform a full SQL backup before running this.

3. Cursors! Cursors should be avoided whenever possible, they are not efficient and produce a load on the server, etc, etc. But sometimes there is no other choice when you need to loop through a recordset. So obviously since we don't use cursors that often, it's hard to remember the exact syntax. Here is a little template to make it easier:

DECLARE @[var1] varchar(8)
DECLARE [cursorname] CURSOR
FOR
SELECT [column1] FROM [table]
OPEN [cursorname]
FETCH NEXT FROM [cursorname] INTO @[var1]
WHILE @@FETCH_STATUS = 0 BEGIN
[query]
FETCH NEXT FROM [cursorname] INTO @[var1]
END
CLOSE [cursorname]
DEALLOCATE [cursorname]


4. Ever have a large database with lots of stored procedures and you're trying to remember where that one awesome piece of code that you wrote last year was used? Well if you can remember some of the code you can search for it!

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%searchtext%'
AND ROUTINE_TYPE='PROCEDURE'


5. Migrated a bunch of databases to a new version of SQL and want to upgrade the databases themselves? Also want to switch them to Simple recovery mode? That's easy. First you can make a list of all databases and those settings:

SELECT name, compatibility_level, is_auto_shrink_on, recovery_model_desc FROM sys.databases
where is_auto_shrink_on=1 or recovery_model=1 or compatibility_level<90

(use compatibility_level<100 if you're on SQL 2008)

Then assuming you've tested them under the new system and everything is good to go, or maybe you're just one of those people that likes to upgrade first and fix issues later...

ALTER DATABASE [dbname] SET RECOVERY SIMPLE, AUTO_SHRINK OFF
EXEC dbo.sp_dbcmptlevel @dbname=N'[dbname]', @new_cmptlevel=90

(again, @new_cmptlevel=100 for SQL 2008)

6. And finally, if you need to know if a temp table exists:

IF OBJECT_ID('tempdb..#temptablename') IS NOT NULL