netnerds.net

The other day, I was texting a friend and some guy looked at me and said "Oh hey, 2002 called, it wants its T9 back." Haha, slightly cliche but I laughed anyway. Then I went and bought myself a Blackberry.

I totally love it, I won't lie. So I imported my contacts into Outlook from a Sony Ericsson mobile phone and they came out kinda chu-chu. All the contacts came in as "Work" numbers and were all prefixed by ";\M". I cleaned the M's out manually but then wrote a script to list all the numbers as Mobile numbers. For consistency's sake, I also cleaned up all of the numbers themselves to just be, for instance, 2345551212. Here's the script in case you would like to do a mass cleanse as well.

Ouu my grammar has gone downhill since I spent a weekend in Louisiana.

Looking for the T-SQL (somewhat) equivalent to VBScript's FormatDateTime function? I've been too, for years. I finally found it within the CONVERT() function. As stated in SQL Server Books Online:

In CONVERT ( data_type [ ( length ) ] , expression [ , style ] ), style is the style of the date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types); or the string format used to convert float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types). When style is NULL, the result returned is also NULL.

Manuj Bahl wrote a nice article covering date and time manipulation in SQL Server 2000 and it in, he summarized BOL's table that lists the different style types. It went something like this:

Try this out with GETDATE() by running the following statement:
SELECT CONVERT(VARCHAR,GETDATE(),7) AS currentdate

Your results should look something like this: Jul 03, 07 Nice! Have fun

Someone recently asked why I don't use SQL Mail on my SQL Servers (2000 and below). The answer is: I don't want to install Outlook on my SQL Servers. Sure, there are ways around it but I prefer just using "net send" to alert the Operator (moi) then forwarding those messages on to my inbox and phone.

Although my employer offered me a Blackberry, I never really cared for them. The battery life is just too short and I don't like talking into something the size of my hand. So.. I use a regular phone that doesn't receive e-mails easily. It does, however, love to receive texts. Cingular even gave me an email address to go with my phone and this is what I use to Alert me if any of my jobs (Backups, Optimizations, etc) fail.

First, I created a DNS CNAME record to my workstation called DBAWorkstation. Then I created an operator named DBA Workstation. I like to keep things generic in the event that I leave or my workstation name changes. In the event that happens, there's just one change at the DNS level. So next, I set the jobs to alert this operator via net send if any jobs fail. Finally, I set up a script that runs all the time on my workstation which has IIS's SMTP installed locally:

Run that script as a service and you're set with SMS/E-mail notifications in SQL Server 2000 and below. SQL Server 2005 can do the same thing..but e-mail is much more accessible within the service itself.

Recently, someone asked if there was a way to be notified when servers have been added to an Active Directory domain. I looked around the Internet and it seems there's not a direct way to do this without some large software package. So in lieu of having an instant notification, I created a script that tallies up newly added user and computer accounts and emails the admin with all the info. This script is initially setup to be run once a day, but you can modify to whatever frequency you want.

I really expected this to take at most a couple hours to write but parsing through all the data turned out to take a heckofa lot of time. From converting the desired comparison date to UTC to parsing the AD attribute memberOf, I spent a good day working on this. I learned a lot, though and found this page which details the AD schema to be very helpful.

If you need anything more than this, you may want to consider an enterprise-type administration package such as Microsoft's MOM.

ADAddedUsersNComputers.vbs

To schedule this, save the above code as ADAddedUsersNComputers.vbs in C:\scripts (for ex.) and use Scheduled Tasks to run the following command: %windir%\system32\wscript.exe C:\scripts\ADAddedUsersNComputers.vbs. I suggest running it daily at the end of each workday.

This is more of a post for proper keywords. My previous entry titled "Import MaxMind City CSVs into SQL Server 2005" assumes the user has already conducted research to find that MaxMind's free GeoIP database provides the functionality to look up the location of an IP address. So, if you wish to quickly and easily find an IP address' location, do the following.

1. Ensure you have SQL Server installed somewhere. If you don't currently have it installed,
    you can download SQL Server 2005 Express for free.
2. Read this post about importing the necessary (and free) database for IP lookups.
    a. Download zip & unzip
    b. Read README.txt
    c. Copy the unzip.exe to your Windows directory
    d. Run the included SQL File
    e. Run the included VBS file
3. Create a VBS file to access the information.
4. Double-click the VBS file and enter the IP address

LookupIPAddress.vbs

Then, finding the location is as easy as double-clicking the file, entering an IP, and pressing "OK"....

In less than 1 millisecond, (after the initial stored procedure execution), your results will show up

Update: If you are an iTunes user that needs VBScript, this page won't help you. Please visit the following two sites for two different solutions: Apple Docs and KeathMilligan.net.

This is an old script I dug up which I call "fileFetch". It's a script that downloads a binary file using XMLHTTP and saves it using an ADO stream. This is handy for nightly downloads and will work within SQL Server DTS packages.

Quick Code

I've used CDONTS for years, even though it hasn't been included in a Windows Server release since NT4. Anytime I needed to send mail, I'd copy over CDONTS.dll, register it then use my old CDONTS code. Not sure why I resisted it for so long.. I think the weird Configuration Fields were too odd to accept. But I've finally accepted them.. about 3 good years after ASP went out of style. Here's a less-than-timely code snippet:

This is the sound of settling.

This is some super old code but I used it recently and figured I'd archive it on this site for my future reference. The sample code below aims to allow authenticated users to download files which are not available via direct download (ie. files within the web root). The script accomplishes this by doing the following:

1. Checks to see if the user is logged in (your method may vary)
2. Sets the root directory location
3. Checks to see if the file exists, if so...
4. Retrieves the filesize and adds the appropriate HTTP headers including content disposition, filename, content type and filesize.
5. Uses a binary stream to "push" the download

Save this file as download.asp and call it with the filename in the querystring. Example: http://domain.com/downloads/download.asp?filename=myfile.pdf. Also, be sure to give read permissions to IUSR_SvrName to the root directory. Change the authentication requirements as needed:

download.asp

Note: Even if the file is a PDF and a third-party application such as Adobe Reader is set to open the file within the browser, the code below will override that and force a download (by using the "application/x-msdownload" content type).

[The UPDATE below is no longer accurate as an alternative solution has been given below in the comments and subsequently, was added to the code (Thanks a bunch, David!). I wanted to leave it for Googlers looking for a solution, however]

UPDATE: Someone wrote to let me know that they were encountering the error "Response Buffer Limit Exceeded". As it turns out, IIS 6's ASPBufferingLimit is set to a measly 4MB (4194304 bits) so any file over 4MB would produce this error. To fix this issue, you will have to have access to IIS either via the command line or the MMC. Here's how to change the buffering limit via the command line:

************ NOTE: An easier solution is to use the updated Do While/Flush procedure given in the code *****************

That's a buffering limit of more than 4 Gigabytes. Personally, I'd lop off the last digit and make that number closer to 430MB. Running that script worked immediately on my test machine, even though I do not have "Enable Direct Metabase Edit" checked in IIS' Properties. If it doesn't work for you, restart IIS and see if it works.

So if you have SQL Server installed locally, you're lucky enough to have access to SQLDeeMO. You can then easily enumerate SQL Servers with the following code. Note: If you do decide to use VBScript and SQLDeeMO, you will need to remove the "ee" in the script below. I didn't want to put in the actual object name so that people searching Google for sites that do not contain the phrase SQLDeeMO will still find my site.

SQLDeeMO VBScript Action

But if you don't have SQL Server installed locally.. here's a hack that grabs the name of all Windows Servers in AD and then checks their registry for instances of SQL Server.

Enumerate All SQL Servers and Instance Names on a Domain

If you are looking for the version of each SQL Server.. you can either query it with SELECT @@VERSION or it may be easily found in the registry. It's 10:47pm and I'm still at work so I'm heading out

Damn, the ADsDSOObject rocks! This script, which weighs in at less than 20 lines, finds all machines running any form of Windows Server on a given domain. Note that this script isn't useful in finding domain controllers, but rather any machine running Windows Server.

Awesome

Also, I found this nice reference of Command One Liners while searching the web. Totally handy!