tag:blogger.com,1999:blog-29640807969119898732023-11-15T08:41:03.344-08:00Tim's RamblingsTim Andrewshttp://www.blogger.com/profile/10620609226621599080noreply@blogger.comBlogger11125tag:blogger.com,1999:blog-2964080796911989873.post-351441891920234312015-03-05T22:09:00.000-08:002015-03-05T22:09:17.400-08:00Powershell progress bar for Exchange mailbox movesI 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<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">while((get-moverequest -MoveStatus InProgress) -ne $null) {get-moverequest -MoveStatus InProgress | Get-MoveRequestStatistics; start-sleep -s 5}</span><br />
<div>
<br /></div>
<div>
But I thought it would be cool to have a GUI progress bar so I did a little Googling and found <a href="http://mcpmag.com/articles/2014/02/18/progress-bar-to-a-graphical-status-box.aspx">two</a> <a href="https://social.technet.microsoft.com/Forums/windowsserver/en-US/97017441-6a0a-46be-8986-192eadf1f130/running-progressbar-on-powershellgui-systemwindowsforms-when-form-loaded?forum=winserverpowershell">articles</a> 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.</div>
<div>
<br /></div>
<div>
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.</div>
<div>
<br /></div>
<div>
<div>
<span style="font-family: Courier New, Courier, monospace;">[CmdletBinding()] </span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">param(</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[Parameter(Mandatory=$true)][string]$Username</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><span class="Apple-tab-span" style="white-space: pre;"> </span>)</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">Add-Type -assembly System.Windows.Forms</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$mb = Get-MoveRequest $Username</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$Title = "Mailbox Move Progress: $($mb.DisplayName)"</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$height=100</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$width=400</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$color = "White"</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$form1 = New-Object System.Windows.Forms.Form</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$form1.Text = $title</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$form1.Height = $height</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$form1.Width = $width</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$form1.BackColor = $color</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$form1.FormBorderStyle = [System.Windows.Forms.FormBorderStyle]::FixedSingle </span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$form1.StartPosition = [System.Windows.Forms.FormStartPosition]::CenterScreen</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$label1 = New-Object system.Windows.Forms.Label</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$label1.Text = "not started"</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$label1.Left=5</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$label1.Top= 10</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$label1.Width= $width - 20</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$label1.Height=15</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$label1.Font= "Verdana"</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$form1.controls.add($label1)</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$progressBar1 = New-Object System.Windows.Forms.ProgressBar</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$progressBar1.Name = 'progressBar1'</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$progressBar1.Value = 0</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$progressBar1.Style="Continuous"</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$System_Drawing_Size = New-Object System.Drawing.Size</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$System_Drawing_Size.Width = $width - 40</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$System_Drawing_Size.Height = 20</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$progressBar1.Size = $System_Drawing_Size</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$progressBar1.Left = 5</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$progressBar1.Top = 40</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$form1.Controls.Add($progressBar1)</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$label1.text="Preparing to analyze $($mb.DisplayName)"</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$form1.Refresh()</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">start-sleep -Seconds 1</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">Function GetPct {</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> if((Get-MoveRequest -Identity $Username).Status -eq "InProgress") {</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> [int]$pct = (Get-MoveRequestStatistics $Username).PercentComplete</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> $progressbar1.Value = $pct</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> $label1.text="$($mb.DisplayName) Progress: $pct %"</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> #Write-Host $pct</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> $form1.Refresh()</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> } else {</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> $timer.enabled = $false</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> Write-Host "Move complete, please close window when ready."</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> }</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">}</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$timer = New-Object System.Windows.Forms.Timer </span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$timer.Interval = 5000</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">$timer.add_Tick({</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">GetPct</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">})</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">if((Get-MoveRequest -Identity $Username).Status -eq "InProgress") {</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> $timer.Enabled = $true</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> $timer.Start()</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> $form1.Add_Shown({$form1.Activate()})</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> $form1.ShowDialog()</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">} else {</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> Write-Host "Move is $($mb.Status) for $($mb.DisplayName)"</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">}</span></div>
</div>
<div>
<br /></div>
Anonymoushttp://www.blogger.com/profile/03653211219764110367noreply@blogger.com0tag:blogger.com,1999:blog-2964080796911989873.post-92071816698928483062014-04-25T10:40:00.000-07:002014-04-25T10:40:04.129-07:00Windows 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.<br />
<br />
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. <br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">Set-ItemProperty -Path "HKLM:\Software\Microsoft\Windows\CurrentVersion\Policies\System" -Name "EnableLUA" -Value "0"</span><br />
<br />
This effectively makes everything run as administrator, which is how it should be when I'm LOGGED INTO A SERVER AS ADMINISTRATOR. Anonymoushttp://www.blogger.com/profile/03653211219764110367noreply@blogger.com0tag:blogger.com,1999:blog-2964080796911989873.post-66782431312615583022013-02-06T14:56:00.002-08:002013-02-06T14:59:42.556-08:00Secondary DNS vs. Conditional ForwardersI 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.<br />
<br />
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.<br />
<br />
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?<br />
<br />
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.<br />
<br />
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.<br />
<br />
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.<br />
<br />
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.Anonymoushttp://www.blogger.com/profile/03653211219764110367noreply@blogger.com0tag:blogger.com,1999:blog-2964080796911989873.post-33390995996083734482010-08-12T14:06:00.001-07:002010-09-22T16:41:45.690-07:00.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.<br /><br />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.<br /><br />Here is the final product:<br /><br /><code><br />Function ResizeImage(ByVal sFilename As String, ByVal iMaxW As Integer, ByVal iMaxH As Integer) As String<br /> Dim imgPhoto As System.Drawing.Image = Drawing.Image.FromFile(sFilename)<br /> Dim x, y, x2, y2, x3, y3 As Integer<br /> Dim xD, yD As Double<br /><br /> x = imgPhoto.Width<br /> y = imgPhoto.Height<br /><br /> xD = x / iMaxW<br /> yD = y / iMaxH<br /><br /> If xD > yD Then<br /> x2 = iMaxW<br /> y2 = y * (iMaxW / x)<br /> Else<br /> y2 = iMaxH<br /> x2 = x * (iMaxH / y)<br /> End If<br /><br /> Dim imgOutput As New System.Drawing.Bitmap(x2, y2)<br /> Dim g As System.Drawing.Graphics = System.Drawing.Graphics.FromImage(imgOutput)<br /> g.InterpolationMode = Drawing2D.InterpolationMode.HighQualityBicubic<br /> g.SmoothingMode = Drawing2D.SmoothingMode.HighQuality<br /> g.PixelOffsetMode = Drawing2D.PixelOffsetMode.HighQuality<br /> g.CompositingQuality = Drawing2D.CompositingQuality.HighQuality<br /> g.DrawImage(imgPhoto, 0, 0, x2, y2)<br /><br /> Dim imgExact As New Bitmap(iMaxW, iMaxH)<br /><br /> Dim gE As Graphics = Graphics.FromImage(imgExact)<br /> gE.Clear(Color.FromArgb(-1))<br /> x3 = (iMaxW / 2) - (imgOutput.Width / 2)<br /> y3 = (iMaxH / 2) - (imgOutput.Height / 2)<br /> Dim rDest As New Rectangle(x3, y3, imgOutput.Width, imgOutput.Height)<br /> gE.DrawImage(imgOutput, rDest, 0, 0, imgOutput.Width, imgOutput.Height, Drawing.GraphicsUnit.Pixel)<br /><br /> Dim sExt As String = Path.GetExtension(sFilename).ToLower<br /> Dim sNewFilename As String = Replace(sFilename.ToLower, sExt, "_" & iMaxW & "x" & iMaxH & sExt)<br /> Select Case sExt<br /> Case ".jpg"<br /> Dim info() As ImageCodecInfo = ImageCodecInfo.GetImageEncoders()<br /> Dim enc As EncoderParameters = New EncoderParameters(1)<br /> enc.Param(0) = New EncoderParameter(Encoder.Quality, 100L)<br /> imgExact.Save(sNewFilename, info(1), enc)<br /> Case ".gif"<br /> imgExact.Save(sNewFilename, Drawing.Imaging.ImageFormat.Gif)<br /> Case ".png"<br /> imgExact.Save(sNewFilename, Drawing.Imaging.ImageFormat.Png)<br /> End Select<br /> imgOutput.Dispose()<br /> imgPhoto.Dispose()<br /> imgExact.Dispose()<br /> imgPhoto = Nothing<br /> imgOutput = Nothing<br /> imgExact = Nothing<br /><br /> Return sNewFilename<br /> End Function<br /><br /></code>Tim Andrewshttp://www.blogger.com/profile/10620609226621599080noreply@blogger.com0tag:blogger.com,1999:blog-2964080796911989873.post-56034822942646605792010-04-30T13:33:00.000-07:002010-04-30T14:30:48.060-07:00More 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!<br /><br />First, a SQL stored procedure to retrieve the customer data:<br /><br /><code><br />CREATE PROC usp_user<br /><br />@Users_ID int<br /><br />AS<br /><br />SELECT u.Users_ID, u.Customers_ID, u.FName+' '+u.LName As DisplayName,<br />u.FName, u.LName, u.Email, u.Active, u.Create_Date, c.Company, c.Address1, <br />c.Address2, c.City, c.State, c.Zip, c.Phone1, c.Phone2, c.Fax,<br />c.Create_Date, c.Active, r.Role, u.User_Phone1<br />FROM Users u<br />INNER JOIN Customers c ON c.Customers_ID=u.Customers_ID<br />WHERE u.Users_ID=@Users_ID<br /></code><br /><br />Then, a class to handle our data format functions:<br /><br /><code><br />Public Class xsldataformatter<br /> Public Function FormatPhone(ByVal strOrigPhoneAs String) As String<br /> If IsDBNull(strOrigPhone) Then<br /> Return ""<br /> Else<br /> Dim strMatch, strNewPhone As String<br /> Dim intDigits As Integer<br /> strMatch = "[^0-9]"<br /><br /> ' This regex strips out all non number characters<br /> strNewPhone = System.Text.RegularExpressions.Regex.Replace(strOrigPhone, strMatch, "", System.Text.RegularExpressions.RegexOptions.Singleline)<br /> intDigits = strNewPhone.Length<br /> If intDigits > 10 Then<br /> strNewPhone = System.Text.RegularExpressions.Regex.Replace(strNewPhone, "^(...)(...)(....)(.*)$", "($1) $2-$3 x$4")<br /> Else<br /> strNewPhone = System.Text.RegularExpressions.Regex.Replace(strNewPhone, "^(...)(...)(....)(.*)$", "($1) $2-$3")<br /> End If<br /> Return (strNewPhone)<br /> End If<br /> End Function<br />End Class<br /></code><br /><br />Next, our XSLT file, "newuser.xslt". Notice the line at the top, xmlns:formatdata="urn:xsldataformatter", that will be used later:<br /><code><br /><br /><?xml version="1.0" encoding="utf-8"?><br /><xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"<br /> xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"<br /> xmlns:formatdata="urn:xsldataformatter"<br />><br /> <xsl:output method="xml" indent="yes"/><br /><br /> <xsl:template match="/NewUser/User"><br /> <style><br /> body,td {font-family: Myriad, Arial, Helvetica, sans-serif; font-size: 10pt;}<br /> td,th {vertical-align: top; }<br /> 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;}<br /> 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;}<br /> 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;}<br /> 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;}<br /> TH, .th {background-color: #505050; color: White; font-weight:bold; border: 1px solid black}<br /> A:link {color: #333333;}<br /> A:visited {color: #333333}<br /> A:hover {color: Blue}<br /> </style><br /> <h1>New User Created</h1><br /> <table align="center"><br /> <tr><br /> <th align="right" nowrap="nowrap">Company:</th><br /> <td nowrap="nowrap"><br /> <xsl:element name="a"><br /> <xsl:attribute name="href"><br /> https://www.mywebsite.com/admin/customers.aspx?c=<xsl:value-of select="Customers_ID"/><br /> </xsl:attribute><br /> <xsl:value-of select="Company"/><br /> </xsl:element><br /> </td><br /> </tr><br /> <tr><br /> <th align="right" nowrap="nowrap">Name:</th><br /> <td nowrap="nowrap"><br /> <xsl:element name="a"><br /> <xsl:attribute name="href"><br /> https://www.mywebsite.com/admin/users.aspx?u=<xsl:value-of select="Users_ID"/><br /> </xsl:attribute><br /> <xsl:value-of select="DisplayName"/><br /> </xsl:element><br /> </td><br /> </tr><br /> <tr><br /> <th align="right" nowrap="nowrap">Email:</th><br /> <td nowrap="nowrap"><br /> <xsl:element name="a"><br /> <xsl:attribute name="href"><br /> mailto:<xsl:value-of select="Email"/><br /> </xsl:attribute><br /> <xsl:value-of select="Email"/><br /> </xsl:element><br /> </td><br /> </tr><br /> <tr><br /> <th align="right" nowrap="nowrap">Address:</th><br /> <td nowrap="nowrap"><br /> <xsl:value-of select="Address1"/><br /> <xsl:if test="Address2 != ''">, <xsl:value-of select="Address2"/></xsl:if><br /> <br /><br /> <xsl:value-of select="City"/>, <xsl:value-of select="State"/> <xsl:value-of select="Zip"/><br /> </td><br /> </tr><br /> <tr><br /> <th align="right" nowrap="nowrap">Phone 1:</th><br /> <td nowrap="nowrap"><br /> <xsl:value-of select="formatdata:FormatPhone(Phone1)"/><br /> </td><br /> </tr><br /> <tr><br /> <th align="right" nowrap="nowrap">Phone 2:</th><br /> <td nowrap="nowrap"><br /> <xsl:value-of select="formatdata:FormatPhone(Phone2)"/><br /> </td><br /> </tr><br /> <tr><br /> <th align="right" nowrap="nowrap">User Phone:</th><br /> <td nowrap="nowrap"><br /> <xsl:value-of select="formatdata:FormatPhone(User_Phone1)"/><br /> </td><br /> </tr><br /> <tr><br /> <th align="right" nowrap="nowrap">Fax:</th><br /> <td nowrap="nowrap"><br /> <xsl:value-of select="formatdata:FormatPhone(Fax)"/><br /> </td><br /> </tr><br /> </table><br /> </xsl:template><br /></xsl:stylesheet><br /></code><br /><br /><br />And finally, the routine to put it all together:<br /><br /><code><br />Sub SendNewUserNotification(ByVal userid As Integer)<br /> Dim dsU As New DataSet("NewUser")<br /> Dim daU As New SqlDataAdapter("usp_user", oConn)<br /> With daU.SelectCommand<br /> .CommandType = CommandType.StoredProcedure<br /> .Parameters.Add("@Users_ID", SqlDbType.Int).Value = userid <br /> End With<br /> daU.Fill(dsU, "User")<br /><br /> Dim x As New XmlDataDocument(dsU)<br /> Dim xt As New XslCompiledTransform<br /> Dim ms As New MemoryStream<br /> Dim sr As New StreamReader(ms)<br /> Dim xw As New XmlTextWriter(ms, System.Text.Encoding.UTF8)<br /> Dim xa As New XsltArgumentList<br /> xa.AddExtensionObject("urn:xsldataformatter", New xsldataformatter)<br /> xt.Load(Server.MapPath("/newuser.xslt"))<br /> xw.Flush()<br /> xt.Transform(x, xa, xw)<br /> ms.Seek(0, SeekOrigin.Begin)<br /> Dim htm As String = sr.ReadToEnd<br /> xw.Close()<br /> ms.Close()<br /> sr.Close()<br /><br /> Dim oMsg As New Net.Mail.MailMessage<br /> oMsg.From = New Net.Mail.MailAddress("support@mywebsite.com")<br /> oMsg.To.Add(New Net.Mail.MailAddress("admin@mywebsite.com"))<br /> oMsg.Subject = "New User Notification"<br /> oMsg.IsBodyHtml = True<br /> oMsg.Body = htm<br /> Dim smtp As New System.Net.Mail.SmtpClient()<br /> Try<br /> smtp.Send(oMsg)<br /> Catch ex As Exception<br /> End Try<br />End Sub<br /></code><br /><br />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:<br /><br /><code><br /><system.net><br /> <mailSettings><br /> <smtp from="support@mywebsite.com"><br /> <network host="localhost"/><br /> </smtp><br /> </mailSettings><br /></system.net><br /></code>Tim Andrewshttp://www.blogger.com/profile/10620609226621599080noreply@blogger.com0tag:blogger.com,1999:blog-2964080796911989873.post-67220501704079132712010-02-01T19:51:00.000-08:002010-02-01T20:59:22.485-08:00Custom Sitemap/Treeview NavigationThis 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.<br /><br />First, a couple of tables, Categories and Products.<br /><br />Categories:<br /><code><br />Categories_ID Category Parent_ID<br />1 Fruit NULL<br />2 Meat NULL<br />3 Dairy NULL<br />4 Apples 1<br />5 Oranges 1<br />6 Chicken 2<br />7 Beef 2<br />8 Milk 3<br />9 Cheese 3<br /></code><br /><br />Products:<br /><code><br />Products_ID ProductName Categories_ID<br />1 Red Apples 4<br />2 Green Apples 4<br />3 Bananas 1<br />4 Oranges 5<br />5 Chicken Wings 6<br />6 Breast Meat 6<br />7 Pork Chops 2<br />8 Ground Beef 7<br /></code><br /><br />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).<br /><br />Now for the stored procedure to build the navigation:<br /><br /><code><br />CREATE PROC [dbo].[mysp_catnav]<br /><br />@Categories_ID int=NULL<br /><br />AS<br /><br />SELECT Categories_ID, Category As CategoryName<br />FROM Categories Category<br />WHERE Parent_ID Is NULL<br />ORDER BY Category<br /><br />SELECT Categories_ID As Subcategories_ID, Category As SubcategoryName,<br />Parent_ID As Categories_ID<br />FROM Categories Subcategory<br />WHERE Parent_ID=@Categories_ID<br />OR Parent_ID=(SELECT Parent_ID FROM Categories WHERE Categories_ID=@Categories_ID)<br />ORDER BY Category<br /></code><br /><br />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.<br /><br /><code><br /><asp:treeview id="tvCats" runat="server" showlines="True" datasourceid="xmlCats"><br /> <databindings><br /> <asp:treenodebinding datamember="Categories" valuefield="ID" textfield="Name" navigateurlfield="URL" depth="0"><br /> <asp:treenodebinding datamember="Category" valuefield="ID" textfield="Name" navigateurlfield="URL" depth="1"><br /> <asp:treenodebinding datamember="Subcategory" value="ID" textfield="Name" navigateurlfield="URL" depth="2"><br /> </asp:treenodebinding><br /></asp:treenodebinding><br /><br /><asp:xmldatasource id="xmlCats" runat="server" transformfile="/nav.xsl" enablecaching="False"><br /></asp:xmldatasource></asp:treenodebinding></databindings></asp:treeview><br /></code><br /><br />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:<br /><br /><code><br />Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load<br />xmlCats.Data = xmlNav()<br />End Sub<br /></code><br /><br />Then the function to fill the dataset and manipulate it to fit a Treeview control:<br /><br /><code><br />Private oConn As New SqlConnection(ConnectionStrings("myConnectionString").ConnectionString)<br /><br />Function xmlNav() As String<br /> Dim daCats As New SqlDataAdapter("mysp_catnav", oConn)<br /> Dim dsCats As New DataSet("Categories")<br /> Dim sqlCats As SqlCommand = daCats.SelectCommand<br /> With sqlCats<br /> .CommandType = CommandType.StoredProcedure<br /> .Parameters.Add("@Categories_ID", SqlDbType.Int).Value = IIf(Request.QueryString("c") = "", DBNull.Value, Request.QueryString("c"))<br /> End With<br /><br /> daCats.Fill(dsCats, "Category")<br /> If dsCats.Tables.Count > 1 Then<br /> dsCats.Tables(1).TableName = "Subcategory"<br /> Dim dtCats As DataTable = dsCats.Tables("Category")<br /> Dim dtSubs As DataTable = dsCats.Tables("Subcategory")<br /> Dim relSC As New DataRelation("Subcategories", dtCats.Columns("Categories_ID"), dtSubs.Columns("Categories_ID"))<br /> relSC.Nested = True<br /> dsCats.Relations.Add(relSC)<br /> End If<br /><br /> Return dsCats.GetXml<br />End Function<br /></code><br /><br />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.<br /><br />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:<br /><br />nav.xsl:<br /><code><br /><xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:formatdata="urn:xsldataformatter"><br /> <xsl:template name="top" match="/Categories"><br /> <xsl:element name="Categories"><br /> <xsl:attribute name="Name">All Products</xsl:attribute><br /> <xsl:attribute name="ID"></xsl:attribute><br /> <xsl:attribute name="URL">products.aspx</xsl:attribute><br /> <xsl:apply-templates select="Category" mode="cat" /><br /> </xsl:element><br /> </xsl:template><br /><br /> <xsl:template name="cat" match="Category" mode="cat"><br /> <xsl:element name="Category"><br /> <xsl:attribute name="Name"><br /> <xsl:value-of select="CategoryName"/><br /> </xsl:attribute><br /> <xsl:attribute name="ID"><br /> <xsl:value-of select="Categories_ID"/><br /> </xsl:attribute><br /> <xsl:attribute name="URL">products.aspx?c=<xsl:value-of select="Categories_ID"/><br /> </xsl:attribute><br /> <xsl:apply-templates select="Subcategory" mode="child" /><br /> </xsl:element><br /> </xsl:template><br /><br /> <xsl:template name="sub" match="Subcategory" mode="child"><br /> <xsl:element name="Subcategory"><br /> <xsl:attribute name="Name"><br /> <xsl:value-of select="SubcategoryName"/><br /> </xsl:attribute><br /> <xsl:attribute name="ID"><br /> <xsl:value-of select="Subcategories_ID"/><br /> </xsl:attribute><br /> <xsl:attribute name="URL">products.aspx?c=<xsl:value-of select="Subcategories_ID"/><br /> </xsl:attribute><br /> </xsl:element><br /> </xsl:template><br /></xsl:stylesheet><br /></code><br /><br />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.<br /><br /><code><br />CREATE PROC [dbo].[mysp_products]<br /><br />@Categories_ID int=NULL<br /><br />AS<br /><br />SELECT Products_ID, ProductName<br />FROM Products<br />WHERE (Categories_ID=IsNULL(@Categories_ID, Categories_ID) OR Categories_ID IN (SELECT Categories_ID FROM Categories WHERE Parent_ID=@Categories_ID))<br />ORDER BY ProductName<br /></code><br /><br />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.Tim Andrewshttp://www.blogger.com/profile/10620609226621599080noreply@blogger.com0tag:blogger.com,1999:blog-2964080796911989873.post-47061190935070369322009-11-04T13:58:00.000-08:002009-11-04T14:31:56.417-08:00SQL 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.<br /><br />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.<br /><br />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:<br /><code>Convert(datetime, Convert(varchar(15), getdate(), 101))</code><br /><br />This converts '1/1/2000 4:20 PM' into '1/1/2000':<br /><code>Convert(varchar(15), getdate(), 101)</code><br /><br />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.<br /><br /><code>backup log [dbname] with truncate_only<br />go<br />dbcc shrinkfile('[logname]', 63)<br />go</code><br /><br />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.<br /><br />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:<br /><br /><code>DECLARE @[var1] varchar(8)<br />DECLARE [cursorname] CURSOR<br />FOR<br /> SELECT [column1] FROM [table]<br />OPEN [cursorname]<br /> FETCH NEXT FROM [cursorname] INTO @[var1]<br />WHILE @@FETCH_STATUS = 0 BEGIN<br /> [query]<br /> FETCH NEXT FROM [cursorname] INTO @[var1]<br /> END<br />CLOSE [cursorname]<br />DEALLOCATE [cursorname]</code><br /><br />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!<br /><br /><code>SELECT ROUTINE_NAME, ROUTINE_DEFINITION<br /> FROM INFORMATION_SCHEMA.ROUTINES<br /> WHERE ROUTINE_DEFINITION LIKE '%searchtext%'<br /> AND ROUTINE_TYPE='PROCEDURE'</code><br /><br />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:<br /><br /><code>SELECT name, compatibility_level, is_auto_shrink_on, recovery_model_desc FROM sys.databases<br />where is_auto_shrink_on=1 or recovery_model=1 or compatibility_level<90</code><br />(use compatibility_level<100 if you're on SQL 2008)<br /><br />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...<br /><br /><code>ALTER DATABASE [dbname] SET RECOVERY SIMPLE, AUTO_SHRINK OFF<br />EXEC dbo.sp_dbcmptlevel @dbname=N'[dbname]', @new_cmptlevel=90</code><br />(again, @new_cmptlevel=100 for SQL 2008)<br /><br />6. And finally, if you need to know if a temp table exists:<br /><br /><code>IF OBJECT_ID('tempdb..#temptablename') IS NOT NULL</code><br /> Tim Andrewshttp://www.blogger.com/profile/10620609226621599080noreply@blogger.com0tag:blogger.com,1999:blog-2964080796911989873.post-59993677498611905062009-11-04T13:41:00.000-08:002009-11-05T11:23:07.725-08:00Guitar scalesTime for a break from the IT stuff! I've been playing guitar for about 23 years and although I don't really give lessons, I often come across people who are picking it up and want to know more, or maybe they know a bunch of songs and want to learn more about soloing. I made up a cool little cheat sheet that shows the fretboard and the notes for the scales, I'm sure there are plenty of similar things on teh Interwebs but this is something I would just draw on a piece of paper in about 10 minutes. I recently created it in Notepad and it fits nicely on a single sheet of paper when you print it. It should be pretty self-explanatory if you know how to read tablature and chord fingerings.<br /><br /><code>E minor/G Major<br /><br />All positions<br /><br />0 3 5 7 9 12<br />E|---|-F#|-G-|---|-A-|---|-B-|-C-|---|-D-|---|-E-|<br />B|-C-|---|-D-|---|-E-|---|-F#|-G-|---|-A-|---|-B-|<br />G|---|-A-|---|-B-|-C-|---|-D-|---|-E-|---|-F#|-G-|<br />D|---|-E-|---|-F#|-G-|---|-A-|---|-B-|-C-|---|-D-|<br />A|---|-B-|-C-|---|-D-|---|-E-|---|-F#|-G-|---|-A-|<br />E|---|-F#|-G-|---|-A-|---|-B-|-C-|---|-D-|---|-E-|<br /><br /><br />Open position 2nd position<br /><br />0 3 5 3 5 7<br /> |---|-F#|-G-|---|-A-| |---|-G-|---|-A-|---|-B-|<br /> |-C-|---|-D-|---|-E-| |---|-D-|---|-E-|---|-F#|<br />G|---|-A-|---|-B-|---| |-A-|---|-B-|-C-|---|---|<br />D|---|-E-|---|-F#|---| |-E-|---|-F#|-G-|---|---|<br />A|---|-B-|-C-|---|---| |-B-|-C-|---|-D-|---|---|<br />E|---|-F#|-G-|---|---| |-F#|-G-|---|-A-|---|---|<br /><br /><br /><br />3rd position 5th position<br /><br /> 3 5 7 5 7 9 <br />|---|---|-A-|---|-B-|-C-| |---|---|-B-|-C-|---|-D-|<br />|---|---|-E-|---|-F#|-G-| |---|---|-F#|-G-|---|-A-|<br />|---|-B-|-C-|---|-D-|---| |-C-|---|-D-|---|-E-|---|<br />|---|-F#|-G-|---|-A-|---| |-G-|---|-A-|---|-B-|---|<br />|-C-|---|-D-|---|-E-|---| |-D-|---|-E-|---|-F#|---|<br />|-G-|---|-A-|---|-B-|---| |-A-|---|-B-|-C-|---|---|<br /><br /><br /><br />7th position Chords<br /><br /> 7 9 12 1 3 5 7<br />|---|-C-|---|-D-|---|-E-| E : E G B D (Em, Em7)<br />|---|-G-|---|-A-|---|-B-| F#: F# A C E (F#º, F#º m7)<br />|-D-|---|-E-|---|-F#|---| G : G B D F# (G, GM7)<br />|-A-|---|-B-|-C-|---|---| A : A C E G (Am, Am7)<br />|-E-|---|-F#|-G-|---|---| B : B D F# A (Bm, Bm7)<br />|-B-|-C-|---|-D-|---|---| C : C E G B (C, CM7)<br /> D : D F# A C (D, D7)<br /><br /><br />Modes<br />E : Aeolian (Minor)<br />F#: Locrian (partially diminished, Minor with flat 5 & 2)<br />G : Ionian (Major)<br />A : Dorian (Minor with raised 6)<br />B : Phrygian (Minor with lowered 2)<br />C : Lydian (Major with raised 4)<br />D : Mixolydian (Major with lowered 7)<br /><br />[Page Break]<br /><br />A minor/C Major<br /><br />All positions<br /><br />0 3 5 7 9 12<br />E|-F-|---|-G-|---|-A-|---|-B-|-C-|---|-D-|---|-E-|<br />B|-C-|---|-D-|---|-E-|-F-|---|-G-|---|-A-|---|-B-|<br />G|---|-A-|---|-B-|-C-|---|-D-|---|-E-|-F-|---|-G-|<br />D|---|-E-|-F-|---|-G-|---|-A-|---|-B-|-C-|---|-D-|<br />A|---|-B-|-C-|---|-D-|---|-E-|-F-|---|-G-|---|-A-|<br />E|-F-|---|-G-|---|-A-|---|-B-|-C-|---|-D-|---|-E-|<br /><br /><br />Open position 2nd position<br /><br />0 3 5 1 3 5 7<br /> |-F-|---|-G-|---|-A-| |---|-G-|---|-A-|---|-B-|<br /> |-C-|---|-D-|---|-E-| |---|-D-|---|-E-|-F-|---|<br />G|---|-A-|---|-B-|---| |-A-|---|-B-|-C-|---|---|<br />D|---|-E-|-F-|---|---| |-E-|-F-|---|-G-|---|---|<br />A|---|-B-|-C-|---|---| |-B-|-C-|---|-D-|---|---|<br />E|-F-|---|-G-|---|---| -F-|---|-G-|---|-A-|---|---|<br /><br /><br /><br />3rd position 5th position<br /><br /> 3 5 7 5 7 9 <br />|---|---|-A-|---|-B-|-C-| |---|---|-B-|-C-|---|-D-|<br />|---|---|-E-|-F-|---|-G-| |---|-F-|---|-G-|---|-A-|<br />|---|-B-|-C-|---|-D-|---| |-C-|---|-D-|---|-E-|---|<br />|-F-|---|-G-|---|-A-|---| |-G-|---|-A-|---|-B-|---|<br />|-C-|---|-D-|---|-E-|---| |-D-|---|-E-|-F-|---|---|<br />|-G-|---|-A-|---|-B-|---| |-A-|---|-B-|-C-|---|---|<br /><br /><br /><br />7th position Chords<br /><br /> 7 9 12 1 3 5 7<br />|---|-C-|---|-D-|---|-E-| A : A C E G (Am, Am7)<br />|---|-G-|---|-A-|---|-B-| B : B D F A (Bº, Bº m7)<br />|-D-|---|-E-|-F-|---|---| C : C E G B (C, CM7)<br />|-A-|---|-B-|-C-|---|---| D : D F A C (Dm, Dm7)<br />|-E-|-F-|---|-G-|---|---| E : E G B D (Em, Em7)<br />|-B-|-C-|---|-D-|---|---| F : F A C E (F, FM7)<br /> G : G B D F (G, G7)<br /><br />Modes<br />A : Aeolian (Minor)<br />B : Locrian (partially diminished, Minor with flat 5 & 2)<br />C : Ionian (Major)<br />D : Dorian (Minor with raised 6)<br />E : Phrygian (Minor with lowered 2)<br />F : Lydian (Major with raised 4)<br />G : Mixolydian (Major with lowered 7)<br /><br />[Page Break]<br /><br />D minor/F Major<br /><br />All positions<br /><br />0 3 5 7 9 12<br />E |-F-|---|-G-|---|-A-|-Bb|---|-C-|---|-D-|---|-E-|<br /> |-C-|---|-D-|---|-E-|-F-|---|-G-|---|-A-|-Bb|---|<br />G |---|-A-|-Bb|---|-C-|---|-D-|---|-E-|-F-|---|-G-|<br />D |---|-E-|-F-|---|-G-|---|-A-|-Bb|---|-C-|---|-D-|<br />A |-Bb|---|-C-|---|-D-|---|-E-|-F-|---|-G-|---|-A-|<br />E |-F-|---|-G-|---|-A-|-Bb|---|-C-|---|-D-|---|-E-|<br /><br /><br />Open position 1st position<br /><br />0 3 5 1 3 5 <br /> |-F-|---|-G-|---|-A-| |---|---|-G-|---|-A-|-Bb|<br /> |-C-|---|-D-|---|-E-| |---|---|-D-|---|-E-|-F-|<br />G |---|-A-|-Bb|---|---| |---|-A-|-Bb|---|-C-|---|<br />D |---|-E-|-F-|---|---| |---|-E-|-F-|---|-G-|---|<br />A |-Bb|---|-C-|---|---| |-Bb|---|-C-|---|-D-|---|<br />E |-F-|---|-G-|---|---| |-F-|---|-G-|---|-A-|---|<br /><br /><br /><br />3rd position 5th position<br /><br /> 3 5 7 5 7 9 <br />|---|---|-A-|-Bb|---|-C-| |---|-Bb|---|-C-|---|-D-|<br />|---|---|-E-|-F-|---|-G-| |---|-F-|---|-G-|---|-A-|<br />|-Bb|---|-C-|---|-D-|---| |-C-|---|-D-|---|-E-|---|<br />|-F-|---|-G-|---|-A-|---| |-G-|---|-A-|-Bb|---|---|<br />|-C-|---|-D-|---|-E-|---| |-D-|---|-E-|-F-|---|---|<br />|-G-|---|-A-|-Bb|---|---| |-A-|-Bb|---|-C-|---|---|<br /><br /><br /><br />7th position Chords<br /><br /> 7 9 12 1 3 5 7<br /> |---|-C-|---|-D-|---|-E-| D : D F A C (Dm, Dm7)<br /> |---|-G-|---|-A-|-Bb|---| E : E G Bb D (Eº, Eº m7)<br /> |-D-|---|-E-|-F-|---|---| F : F A C E (F, FM7)<br /> |-A-|-Bb|---|-C-|---|---| G : G Bb D F (Gm, Gm7)<br /> |-E-|-F-|---|-G-|---|---| A : A C E G (Am, Am7)<br />-Bb|---|-C-|---|-D-|---|---| Bb: Bb D F A (Bb, BbM7)<br /> C : C E G Bb (C, C7)<br /><br />Modes<br />D : Aeolian (Minor)<br />E : Locrian (partially diminished, Minor with flat 5 & 2)<br />F : Ionian (Major)<br />G : Dorian (Minor with raised 6)<br />A : Phrygian (Minor with lowered 2)<br />Bb: Lydian (Major with raised 4)<br />C : Mixolydian (Major with lowered 7)<br /></code>Tim Andrewshttp://www.blogger.com/profile/10620609226621599080noreply@blogger.com0tag:blogger.com,1999:blog-2964080796911989873.post-32815249609490494882009-11-04T12:43:00.000-08:002009-11-04T13:03:54.278-08:00Login scriptsWhile we're on the subject of VBS scripting, I have written some login scripts that can map drives and printers based on group membership, username, OU, etc. so you no longer need to have different scripts for different groups/OUs in Active Directory:<br /><br /><code>On Error Resume Next<br />Set oWSH = CreateObject("WScript.Shell")<br />Set oNet = CreateObject("WScript.Network")<br />Set oFS = CreateObject("Scripting.FileSystemObject")<br /><br />sUsername = LCase(oNet.Username)<br />sComputerName = oWSH.ExpandEnvironmentStrings("%COMPUTERNAME%")<br />sMyDocs = "\\fileserver\users\" & sUsername & "\My Documents\"<br /><br />Set oRootDSE = GetObject("LDAP://rootDSE")<br />Set oConnection = CreateObject("ADODB.Connection")<br />oConnection.Open "Provider=ADsDSOObject;"<br />Set oCommand = CreateObject("ADODB.Command")<br />oCommand.ActiveConnection = oConnection<br />oCommand.CommandText = "<LDAP://" & oRootDSE.get("defaultNamingContext") & _<br /> ">;(&(objectCategory=User)(samAccountName=" & sUsername & "));distinguishedName;subtree"<br />Set oRecordSet = oCommand.Execute<br />sDistinguishedName = oRecordSet.Fields("DistinguishedName")<br />oConnection.Close<br /><br />Set oUser = GetObject("LDAP://" & sDistinguishedName)<br />oGroups = oUser.GetEx("memberOf")<br />bFinance = False<br />bAP = False<br />bSD = False<br />For Each g In oGroups<br /> If InStr(g, "Finance")> 0 Then bFinance = True<br /> If InStr(g, "Accounts Payable")> 0 Then bAP = True<br /> If InStr(g, "San Diego")> 0 Then bSD = True<br />Next<br /><br />'Map drive for everyone<br />oNet.MapNetworkDrive "S:", "\\fileserver\shared"<br /><br />'Map drive by group<br />If bFinance Then<br /> oNet.MapNetworkDrive "P:", "\\fileserver\finance"<br />End If<br /><br />'Map printers by group<br />If bAP Then<br /> oNet.AddWindowsPrinterConnection "\\printserver\HP4100_AP"<br /> oNet.AddWindowsPrinterConnection "\\printserver\Canon3380"<br />End If<br /><br />'Map printer by location and computername prefix<br />If bSD And Left(sComputerName, 3) = "VDI" Then<br /> oNet.AddWindowsPrinterConnection "\\printserver\HP6300_SD"<br />End If<br /><br />'Map drive for one user on one workstation<br />If sUsername = "jsmith" And sComputerName = "VDI-XP-013" Then<br /> oNet.MapNetworkDrive "P:", "\\fileserver\marketing"<br />End If<br /><br />'Map drive for administrator<br />If sUsername = "administrator" Then<br /> oNet.RemoveNetworkDrive "T:"<br /> oNet.MapNetworkDrive "T:", "\\fileserver\install"<br />End If<br /><br />'Make sure My Documents was created under home dir<br />If Not oFS.FolderExists(sMyDocs) then<br /> oFS.CreateFolder(sMyDocs)<br />End If<br /><br />'Map printer for single user<br />If sUsername = "sjones" Then<br /> oNet.AddWindowsPrinterConnection "\\sjones\hp_p1006"<br />End If<br /><br />Function FindMapped(sLetter, sShare)<br /> bFound = False<br /> Set oDrives = oNet.EnumNetworkDrives<br /> For i = 0 to oDrives.Count - 1 Step 2<br /> If LCase(oDrives.Item(i)) = LCase(sLetter) Then<br /> bFound = (LCase(oDrives.Item(i+1)) = LCase(sShare))<br /> End If<br /> Next<br /> FindMapped = bFound<br />End Function<br /></code>Tim Andrewshttp://www.blogger.com/profile/10620609226621599080noreply@blogger.com0tag:blogger.com,1999:blog-2964080796911989873.post-39935299808927890022009-11-04T12:04:00.000-08:002009-11-04T14:54:03.554-08:00Desktop SettingsAs an IT Consultant I typically logon to many different servers and workstations and have found myself adjusting the default desktop settings hundreds, maybe thousands, of times to set my preferences. I absolutely hate the default settings provided by Microsoft and can't stand doing even simple file management tasks with those settings. Who the heck thought Large Icons in folders was a good idea anyway? And hiding file extensions has got to be the stupidest idea anyone ever thought of. Anyone remember annakournikova.jpg.vbs? Thanks, Microsoft for making our users even stupider.<br /><br />Anyway I spent some time building a VBS script to set all of my personal preferences in one shot. I store this file on my web server as a .txt file in case I'm at a client who has VBS downloads disabled, which is a pretty good idea in general. I just memorize the URL, download it to the desktop, change the file extension to .vbs, and run it. Some of the settings require explorer.exe to be closed so rather than kill the process with Task Manager, there is an easier way that lets it shut down cleanly. First open a cmd window or Task Manager, then go to Start, Shutdown, hold down Ctrl-Shift-Alt, and click Cancel. This will shut down explorer.exe and allow you to make the modifications. Using cmd or Task Manager's File | Run command, type in the path to the VBS file and run it. Then you can launch explorer.exe and view your changes.<br /><br />Pretty much all the settings modify the current user profile so it's safe to use on a machine that is used by someone else if you're logging on with different credentials. It does some fun things like telling IE that it has already run the welcome screen, sets my tab defaults and home page, disables the Outlook tray notifications, expands all of the Start Menu folders, sets Explorer to List View, increases the taskbar to two lines and turns on the clock (Terminal Servers hide the clock by default), lock the taskbar, shows the hidden desktop icons, gets rid of that stupid Language Bar, and removes the lame default sorting of the Start Menu.<br /><br />Most of the registry keys are pretty self-explanatory but some need clarification. StuckRects2 is a binary value that controls the taskbar, clock, and some other settings. I just copied the values from a system that is set exactly the way I want it and hardcoded the hex values into aStuckRects. Streams is another binary value that controls the folder view customizations (I like List view by default). Deleting a registry key with subkeys is not possible with RegDelete so there is a function to help with that. I'm sure there are a lot of annoying startup programs that I'm missing, I add them as I come across them.<br /><br />Here is the current version of my VBS file as of this posting. You can also download my latest version at <a href="http://www.ac64.com/download/timmyt.txt" target="_blank">ac64.com</a>.<br /><br /><code>'Create Objects<br />Set oWSH = CreateObject("WScript.Shell")<br />Set oNet = CreateObject("WScript.Network")<br />Set oReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")<br /><br />'Define constants and values for long registry keys<br />Const HKEY_CURRENT_USER = &H80000001<br />sUsername = LCase(oNet.Username)<br />sOLTray03 = "HKCU\SOFTWARE\Microsoft\Office\11.0\Outlook\Display Types\Balloons"<br />sOLTray07 = "HKCU\SOFTWARE\Microsoft\Office\12.0\Outlook\Display Types\Balloons"<br />sRunU = "HKCU\SOFTWARE\Microsoft\Windows\CurrentVersion\Run"<br />sRunM = "HKCU\SOFTWARE\Microsoft\Windows\CurrentVersion\Run"<br />sIE = "HKCU\Software\Microsoft\Internet Explorer"<br />sExp = "HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer"<br />sStream = "SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Streams"<br />sStuckRects = "SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\StuckRects2"<br /><br />'Set personal settings<br />aStream = Array(&H08,&H00,&H00,&H00,&H03,&H00,&H00,&H00,_<br /> &H00,&H00,&H00,&H00,&HE0,&HA5,&H1F,&H0E,_<br /> &H73,&H35,&HCF,&H11,&HAE,&H69,&H08,&H00,_<br /> &H2B,&H2E,&H12,&H62,&H04,&H00,&H00,&H00,_<br /> &H01,&H00,&H00,&H00,&H43,&H00,&H00,&H00)<br />aStuckRects = Array(&H28,&H00,&H00,&H00,&HFF,&HFF,&HFF,&HFF,_<br /> &H02,&H00,&H00,&H00,&H03,&H00,&H00,&H00,_<br /> &H3C,&H00,&H00,&H00,&H37,&H00,&H00,&H00,_<br /> &HFE,&HFF,&HFF,&HFF,&HEB,&H02,&H00,&H00,_<br /> &H02,&H05,&H00,&H00,&H22,&H00,&H00,&H00)<br />sHomePage = "http://www.google.com"<br /><br />On Error Resume Next<br /><br />'Outlook Settings<br />oWSH.RegWrite sOLTray03 & "\Exchange", 0, "REG_DWORD"<br />oWSH.RegWrite sOLTray03 & "\NetConn", 0, "REG_DWORD"<br />oWSH.RegWrite sOLTray03 & "\NetWarn", 0, "REG_DWORD"<br />oWSH.RegWrite sOLTray07 & "\Exchange", 0, "REG_DWORD"<br />oWSH.RegWrite sOLTray07 & "\NetConn", 0, "REG_DWORD"<br />oWSH.RegWrite sOLTray07 & "\NetWarn", 0, "REG_DWORD"<br /><br />'Windows Explorer/Desktop Settings<br />oWSH.RegWrite sExp & "\Advanced\EnableBalloonTips", 0, "REG_DWORD"<br />oWSH.RegWrite sExp & "\Advanced\Start_AdminToolsRoot", 2, "REG_DWORD"<br />oWSH.RegWrite sExp & "\Advanced\StartMenuAdminTools", 1, "REG_DWORD"<br />oWSH.RegWrite sExp & "\Advanced\Start_LargeMFUIcons", 0, "REG_DWORD"<br />oWSH.RegWrite sExp & "\Advanced\Start_NotifyNewApps", 0, "REG_DWORD"<br />oWSH.RegWrite sExp & "\Advanced\Start_ShowHelp", 0, "REG_DWORD"<br />oWSH.RegWrite sExp & "\Advanced\Start_ShowMyComputer", 2, "REG_DWORD"<br />oWSH.RegWrite sExp & "\Advanced\Start_ShowControlPanel", 2, "REG_DWORD"<br />oWSH.RegWrite sExp & "\Advanced\Start_ShowMyDocs", 2, "REG_DWORD"<br />oWSH.RegWrite sExp & "\Advanced\Start_ShowMyMusic", 0, "REG_DWORD"<br />oWSH.RegWrite sExp & "\Advanced\Start_ShowMyPics", 0, "REG_DWORD"<br />oWSH.RegWrite sExp & "\Advanced\Start_ShowNetConn", 2, "REG_DWORD"<br />oWSH.RegWrite sExp & "\Advanced\Start_ShowNetPlaces", 1, "REG_DWORD"<br />oWSH.RegWrite sExp & "\Advanced\Start_ShowPrinters", 1, "REG_DWORD"<br />oWSH.RegWrite sExp & "\Advanced\Start_ShowRun", 1, "REG_DWORD"<br />oWSH.RegWrite sExp & "\Advanced\TaskbarSizeMove", 0, "REG_DWORD"<br />oWSH.RegWrite sExp & "\Advanced\StartMenuFavorites", 2, "REG_DWORD"<br />oWSH.RegWrite sExp & "\Advanced\ServerAdminUI", 1, "REG_DWORD"<br />oWSH.RegWrite sExp & "\Advanced\HideFileExt", 0, "REG_DWORD"<br />oWSH.RegWrite sExp & "\Desktop\CleanupWiz\NoRun", 1, "REG_DWORD"<br />oWSH.RegWrite sExp & "\HideDesktopIcons\NewStartPanel\{20D04FE0-3AEA-1069-A2D8-08002B30309D}", 0, "REG_DWORD"<br />oWSH.RegWrite sExp & "\HideDesktopIcons\NewStartPanel\{450D8FBA-AD25-11D0-98A8-0800361B1103}", 0, "REG_DWORD"<br />oWSH.RegWrite sExp & "\HideDesktopIcons\NewStartPanel\{208D2C60-3AEA-1069-A2D7-08002B30309D}", 0, "REG_DWORD"<br />oWSH.RegWrite sExp & "\HideDesktopIcons\NewStartPanel\{871C5380-42A0-1069-A2EA-08002B30309D}", 0, "REG_DWORD"<br />oWSH.RegWrite "HKCU\Software\Microsoft\CTF\LangBar\ShowStatus", 3, "REG_DWORD"<br />oReg.SetBinaryValue HKEY_CURRENT_USER, sStream, "Settings", aStream<br />oReg.SetBinaryValue HKEY_CURRENT_USER, sStuckRects, "Settings", aStuckRects<br />oWSH.RegWrite sIE & "\Main\StatusBarOther", 1, "REG_DWORD"<br /><br />'Internet Explorer Settings<br />oWSH.RegWrite sIE & "\Main\Start Page", sHomePage, "REG_SZ"<br />oWSH.RegWrite sIE & "\Main\AlwaysShowMenus", 0, "REG_DWORD"<br />oWSH.RegWrite sIE & "\Main\RunOnceHasShown", 1, "REG_DWORD"<br />oWSH.RegWrite sIE & "\Main\IE8RunOncePerInstallCompleted", 1, "REG_DWORD"<br />oWSH.RegWrite sIE & "\Main\IE8TourShown", 1, "REG_DWORD"<br />oWSH.RegWrite sIE & "\Main\IE8RunOnceLastShown", 1, "REG_DWORD"<br />oWSH.RegWrite sIE & "\TabbedBrowsing\Groups", 0, "REG_DWORD"<br />oWSH.RegWrite sIE & "\TabbedBrowsing\NewTabPageShow", 1, "REG_DWORD"<br />oWSH.RegWrite sIE & "\TabbedBrowsing\OpenInForeground", 1, "REG_DWORD"<br />oWSH.RegWrite sIE & "\TabbedBrowsing\PopupsUseNewWindow", 0, "REG_DWORD"<br />oWSH.RegWrite sIE & "\TabbedBrowsing\ShowTabsWelcome", 0, "REG_DWORD"<br />oWSH.RegWrite sIE & "\TabbedBrowsing\UseHomepageForNewTab", 1, "REG_DWORD"<br />oWSH.RegWrite sIE & "\TabbedBrowsing\WarnOnClose", 0, "REG_DWORD"<br />oWSH.RegWrite sIE & "\PhishingFilter\Enabled", 0, "REG_DWORD"<br />oWSH.RegWrite sIE & "\PhishingFilter\EnabledV8", 0, "REG_DWORD"<br />oWSH.RegWrite "HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\IEHarden", 0, "REG_DWORD" <br />oWSH.RegWrite "HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings\IEHardenIENoWarn", 0, "REG_DWORD"<br /><br />'Delete registry entries including annoying startup programs<br />DeleteRegEntry HKEY_CURRENT_USER, "Software\Microsoft\Windows\CurrentVersion\Explorer\MenuOrder\Start Menu2"<br />DeleteRegEntry HKEY_CURRENT_USER, "Software\Microsoft\Windows\CurrentVersion\Explorer\MenuOrder\StartMenu"<br />DelReg "\MsnMsgr"<br />DelReg "\AdobeUpdater"<br />DelReg "\Adobe Reader Speed Launcher"<br />DelReg "\DVDLauncher"<br />DelReg "\SunJavaUpdateSched"<br />DelReg "\My Web Search Bar Search Scope Monitor"<br />DelReg "\MyWebSearch Email Plugin"<br />DelReg "\iTunesHelper"<br />DelReg "\QuickTime Task"<br />DelReg "\HP Software Update"<br />DelReg "\HPUsageTracking"<br />DelReg "\BrStsWnd"<br />DelReg "\LXCYCATS"<br /><br />Sub DelReg(key)<br /> oWSH.RegDelete sRunU & key<br /> oWSH.RegDelete sRunM & key<br />End Sub<br /><br />Function DeleteRegEntry(sHive, sEnumPath)<br /> ' Attempt to delete key. If it fails, start the subkey enumeration process.<br /> lRC = oReg.DeleteKey(sHive, sEnumPath)<br /><br /> ' The deletion failed, start deleting subkeys.<br /> If (lRC <> 0) Then<br /> lRC = oReg.EnumKey(sHive, sEnumPath, sNames)<br /><br /> For Each sKeyName In sNames<br /> If Err.Number <> 0 Then Exit For<br /> lRC = DeleteRegEntry(sHive, sEnumPath & "\" & sKeyName)<br /> Next<br /><br /> ' At this point we should have looped through all subkeys, trying to delete the registry key again.<br /> lRC = oReg.DeleteKey(sHive, sEnumPath)<br /> End If<br />End Function<br /></code>Tim Andrewshttp://www.blogger.com/profile/10620609226621599080noreply@blogger.com0tag:blogger.com,1999:blog-2964080796911989873.post-711656804698618222009-11-04T12:01:00.000-08:002009-11-04T12:04:02.724-08:00Welcome to my blogHopefully I'll make regular postings here, I tend to get into it for a while then forget about it, then come back to it again later. My goal for this is to provide some technical information that I've come across in my many years as an IT Consultant, helpful tips and programming samples that have made my life easier.Tim Andrewshttp://www.blogger.com/profile/10620609226621599080noreply@blogger.com0