VBScript: Retrieve all SQL Server instance names

I've seen vbscript code that is supposed to retrieve all the SQL Server instances from a fqdn, but when I implemented them, to my great disappointment, none worked. So, I wrote this function, which has worked well for me. It works for SQL Server 2000, 2005, and 2008.  I included a little bit of extra code so you can see how to use it.

option explicit
const HKEY_LOCAL_MACHINE = &H80000002

dim aryInstances, intInstance

aryInstances=sql_server_instances("computername.domain.fqdn.com")

if not isempty(aryInstances) then
    for intInstance=0 to ubound(aryInstances)
        wscript.echo aryInstances(intInstance)
    next
else
    wscript.echo "no SQL Server instances were found"
end if
wscript.quit

' - strServername: "computername.domain.fqdn.com"
function sql_server_instances(strServername)
    dim objReg, strKeypath, aryEntrynames, aryValuetypes, intCount, strInstance, strObject, strValue
    dim arySQLEntrynames, arySQLValuetypes, intEntryname, strSQLKeypath, arySQLKeys, intSQLKeys
    dim strSQLKey, arySQLInstanceKeys, strSQLInstanceKey, intSQLInstanceKey, strSQLInstanceKeyPath
   
    aryInstances=array
   
    strObject="winmgmts:{impersonationLevel=impersonate}!\\" & strServername & "\root\default:StdRegProv"
    if can_getobject(strObject) then
        set objReg=getobject(strObject)
        strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server"
        objReg.enumkey HKEY_LOCAL_MACHINE, strKeyPath, arySQLKeys

        if not isnull(arySQLKeys) then
            for intSQLKeys=0 to ubound(arySQLKeys)
                strSQLKey=arySQLKeys(intSQLKeys)
                strSQLKeypath=strKeyPath & "\" & strSQLKey
                objReg.enumkey HKEY_LOCAL_MACHINE, strSQLKeypath, arySQLInstanceKeys
                if not isnull(arySQLInstanceKeys) then
                    for intSQLInstanceKey=0 to ubound(arySQLInstanceKeys)
                        strSQLInstanceKey=arySQLInstanceKeys(intSQLInstanceKey)
                        if lcase(strSQLInstanceKey)="mssqlserver" and ((left(lcase(strSQLKey), len("mssql"))="mssql" and right(lcase(strSQLKey), len(".mssqlserver"))=".mssqlserver") or instr(strSQLKey, ".")=0) then
                            redim preserve aryInstances(ubound(aryInstances)+1)
                            if right(lcase(strSQLKey), len(".mssqlserver"))=".mssqlserver" then
                                strSQLKey="(default)"
                            end if
                            aryInstances(ubound(aryInstances))=strSQLKey
                        end if
                    next
                end if
            next
        end if
    else
        aryInstances=empty
    end if
    sql_server_instances=aryInstances
end function

Windows Server 2008 and SQL Server 2008 - Database Mail Setup

I set up Database Mail on Windows Server 2008 and SQL Server 2008.  It wasn't easy, so I hope this will help some of you.  For Database Mail, you have to install IIS (why, Microsoft, why?) but SQL Server Agent does not have to be running (which I find counter-intuitive).
 
Start / All programs / Admin tools / Server manager

In Server Manager, click Features / Add features (on the right)

At the "Add Features Wizard" / "Select Features" dialog, in the list of features, locate and enable SMTP Server
At the "Add Features Wizard" / "Add role services ..." dialog, click "Add Required Role Services"

Back at the "Add Features Wizard" / "Select Features" dialog, click Next
At the "Add Features Wizard" / "Web Server IIS" dialog, click Next
At the "Add Features Wizard" / "Select Role Services" dialog, in the list of role services, click Next
Click "Install," wait for it to finish, and click "Close."

Start / All programs / Admin tools / Internet Information Services (IIS) 6.0 Manager

Expand local computer / right-click smtp virtual server / properties
At the SMTP Virtual Server dialog / Access tab / Authentication
Uncheck anonymous, uncheck basic, enable Integrated windows authentication / OK

At the SMTP Virtual Server dialog / Messages tab
Keep or change the Badmail directory / OK

Expand local computer / smtp virtual server / highlight domains / right-click domain name on the right / properties
Keep or change the mailroot drop directory / OK

Make sure the SMTP service is set to start automatically:
Start menu / Administrative tools / Services

Find and right-click "Simple Mail Transfer Protocol" / Properties
General tab / Startup type: Automatic / OK

Launch SSMS
Click New Query, and execute this query:
--- start of query ---

sp_CONFIGURE 'show advanced', 1

GO

RECONFIGURE

GO

sp_CONFIGURE 'Database Mail XPs', 1

GO

RECONFIGURE

GO

--- end of query ---

Note: if you receive an error like "The affinity mask specified conflicts with the IO affinity mask specified. Use the override option to force this configuration", change the RECONFIGURE command to RECONFIGURE WITH OVERRIDE.  I do not know how to correct the underlying error yet.

Run SSMS / Management / right-click Database Mail / Configure Database Mail

At the "Select Configuration Task," choose "Set up database mail..." / Next

When a developer runs sp_send_dbmail to email through this server, they will need to specify a profile name as a string.
For the Profile name:
The profile name can have letters, number, spaces, hyphens, etc.
I recommend keeping it short, and including the server name.
Example Profile name: "servername-db email"
At the SMTP accounts area, click Add
At the New Database Mail Account dialog
For the Account name:
This will distinguish identifying one smtp server from another.
Example Account name: "servername-db account"This will distinguish identifying one smtp server from another.
Database Mail will attempt to use each Account in this Profile until it succeeds.
For the Email address, Display name and Reply email:
Since it's not coming from a person, I recommend bounceback information: (do not include quotes)
Email address: "do-not-reply@organization.com"
Display name: "Big Organization"
Reply email: "do-not-reply@organization.com"
Server name: "localhost"
Enable Windows Authentication (since our SMTP virtual server is using Integrated windows authentication)
Click OK

At "Manage Profile Security," click Next
At "Configure System Parameters," click Next
At "Complete the Wizard," click Finish
At "Configuring," after the actions complete, click Close

Back at SSMS / Management / right-click Database mail
Click Send Test Email

Select your Database Mail Profile (perhaps "servername-db email")
To: recipient-email@organization.com
In the Subject field, I recommend appending a 1, 2, 3... in case you need to troubleshoot
At the Database Mail Test Email dialog, click OK.
You should recieve the email soon.

Another way to send email:
--- start of query ---

exec msdb.dbo.sp_send_dbmail

@profile_name='servername-db email' /* type your database profile */

,@recipients='recipient-email@organization.com'

,@subject='Test message 2'

,@body='This is the body of the test message.

Database Mail Sent Successfully.'

--- end of query ---

Slightly more complicated email:
--- start of query ---

declare @strBody varchar(150)

set @strBody='Database Mail works on '+@@servername+'.'

set @strBody=@strBody+ 'The time is '+cast(getdate() as varchar(20))

exec msdb.dbo.sp_send_dbmail

@profile_name='servername-db email' /* type your database profile */

,@recipients='to_recipient@organization.com'

,@copy_recipients='cc_recipient@organization.com'

,@from_address='from_sender@organization.com'

,@body_format='html' /* text or html, default is text */

,@subject='Database Mail 3'

,@body=@strBody

--- end of query ---

To check for problems:
1. SSMS / Management / right-click Database mail / View Database Mail log
2. SSMS / new query, execute these queries:

select * from msdb.dbo.sysmail_mailitems order by send_request_date desc

select * from msdb.dbo.sysmail_log order by log_date desc

TSQL - Automate DB restore

This script restores a database without having to know the logical file names. It also lets you place the MDF, LDF and NDF files wherever you want them.  I mainly use it to run a nightly restore on my local machine.  The line with "business logic" does a quick test to see if the database has been changed at all.  There's no need to restore a database if it hasn't been changed, so the "business logic" line is how I tell it to skip the restore step.

/* Automate restore w/o needing to know the logical file names. */

 -- BEGIN - MODIFY THIS CODE - create a blank db
if not exists(select * from master.sys.databases where [name]='DBNAME')
begin
    create database DBNAME
end
go
-- END - MODIFY THIS CODE - create a blank db

declare @strDatabase varchar(130)='DBNAME' -- MODIFY THIS LINE - db name
declare @strBackupFile varchar(500)='e:\docs\db-backups\dbname_backup_file.bak' -- MODIFY THIS LINE - source db backup file
declare @strRestoreMDFFilesTo varchar(500)='e:\docs\sqldata\default\' -- MODIFY THIS LINE - destination restore directory for main files
declare @strRestoreLDFFilesTo varchar(500)='c:\docs\sqldata\default\' -- MODIFY THIS LINE - destination restore directory for tlog files
declare @strRestoreNDFFilesTo varchar(500)='c:\docs\sqldata\default\' -- MODIFY THIS LINE - destination restore directory for non-main files

-- other variables used
declare @strSQL nvarchar(max)
declare @strOriginalPhysicalName varchar(150)
declare @strPhysicalName varchar(150)
declare @strLogicalName varchar(150)
declare @intReturn int

-- begin restoring
begin try
    drop table #tmpFilelist
end try
begin catch
end catch
create table #tmpFilelist (
    LogicalName varchar(64), PhysicalName varchar(130), [Type] varchar(1), FileGroupName varchar(64), Size decimal(20, 0)
    ,MaxSize decimal(25, 0), FileID bigint, CreateLSN decimal(25,0), DropLSN decimal(25,0), UniqueID uniqueidentifier
    ,ReadOnlyLSN decimal(25,0), ReadWriteLSN decimal(25,0), BackSizeInBytes decimal(25,0), SourceBlockSize int
    ,filegroupid int, loggroupguid uniqueidentifier, differentialbaseLSN decimal(25,0), differentialbaseGUID uniqueidentifier
    ,isreadonly bit, ispresent bit, TDEThumbpr decimal
)
if not exists(select * from DBNAME.sys.tables) -- MODIFY THIS LINE - business logic to see if we need to restore the database at all
begin
    print 'Restoring '+@strDatabase+' db ...'
    use master
    exec msdb.dbo.sp_delete_database_backuphistory @database_name = @strDatabase
    use [master]
    exec('alter database '+@strDatabase+' set single_user with rollback immediate')
    use [master]
    exec('drop database '+@strDatabase)
    insert into #tmpFilelist
        exec('restore filelistonly from disk = '''+@strBackupFile+'''')
    set @strSQL='restore database ['+@strDatabase+'] from disk='''+@strBackupFile+''' with '
    set @strSQL=@strSQL+ 'file=1 '
    set @strSQL=@strSQL+ ',nounload '
    set @strSQL=@strSQL+ ',replace '
    set @strSQL=@strSQL+ ',stats=10 '
    while exists(select * from #tmpFilelist)
    begin
        select top 1 @strOriginalPhysicalName=PhysicalName, @strLogicalName=LogicalName from #tmpFilelist
        set @strPhysicalName=@strOriginalPhysicalName
        set @strPhysicalName=reverse(@strPhysicalName)
        set @strPhysicalName=left(@strPhysicalName, charindex('\', @strPhysicalName)-1)
        set @strPhysicalName=reverse(@strPhysicalName)
        set @strPhysicalName=replace(@strPhysicalName, '.', '_'+@strDatabase+'.')
        if @strPhysicalName like '%.mdf'
            set @strPhysicalName=@strRestoreMDFFilesTo+@strPhysicalName
        else if @strPhysicalName like '%.ldf'
            set @strPhysicalName=@strRestoreLDFFilesTo+@strPhysicalName
        else
            set @strPhysicalName=@strRestoreNDFFilesTo+@strPhysicalName
        set @strSQL=@strSQL+ ',move '''+@strLogicalName+''' to '''+@strPhysicalName+''' '
        delete from #tmpFilelist where PhysicalName=@strOriginalPhysicalName
    end
    execute @intReturn=sp_executesql @strSQL
end

SSIS - FTP using Script Task

I was asked to write a SSIS package to download a file from a website (using FTP with a WWW... weird) at 2:00 am automatically. SSIS's FTP tool couldn't do it because it passes the username and password on the URL. (Rotten idea from a security POV, but that's what I was given.) It took me about two weeks, but I cobbled this code together. I had to perform several more major steps after getting the code to run, and I hope I remember them all here. To get it to run automatically:

1. Create your SSIS package (.dtsx)
2. Copy just the .dtsx file to the directory where you want to run it from
3. Create a new domain user with "Log on as a batch file" permissions
4. If your SSIS package connects to a database, add that user as a login on your SQL Server (Server / Security / Logins).
5. On the database server you want to run it on, create a batch file to execute your SSIS package. The "output.txt" file pipes the output to a separate file, which can be very handy.
   
   d:
   cd \directory
   dtexec /FILE "your-file-name.dtsx" /CHECKPOINTING OFF /REPORTING E > "your-file-name-lastrun-output.txt"
6. Launch Task Scheduler on the machine you want to run it on, give it the name of your batch file, give it a schedule, and tell it to execute as that new domain user.

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Net

Imports System.IO

Imports System.Collections.Generic

Imports System.Text

<system.addin.addin("scriptmain", )="" ,="" description:="" publisher:="" version:="1.0"> _

<system.clscompliantattribute(false)> _

Partial Public Class ScriptMain

    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults

        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success

        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

    End Enum

    Public Sub Main()

        Try

            Dim restURL As New StringBuilder()

            Dim objRequest As FtpWebRequest

            Dim objResponse As FtpWebResponse

            Dim objStream As StreamReader

            Dim arySourceFiles(0) As String

            Dim aryDestinationFiles(0) As String

            Dim intFile As Int16

            ' - original file names from FTP site

            arySourceFiles(0) = "original_filename.txt"

            ' - new file names - rename arySourceFiles(n) to aryDestinationFiles(n)

            aryDestinationFiles(0) = "\\servername\d$\directory\new_filename.txt"

            ' - download each file

            For intFile = 0 To UBound(arySourceFiles)

                objRequest = DirectCast(FtpWebRequest.Create("ftp://nt_domainname%5Cusername:password@www.website.com/directory1/directory2/" & arySourceFiles(intFile)), FtpWebRequest)

                objResponse = DirectCast(objRequest.GetResponse(), FtpWebResponse)

                objStream = New StreamReader(objResponse.GetResponseStream())

                File.WriteAllText(aryDestinationFiles(intFile), objStream.ReadToEnd)

            Next

            Dts.TaskResult = ScriptResults.Success

        Catch webEx As WebException

            Dim [error] As New StringBuilder()

            'catch protocol errors

            If webEx.Status = WebExceptionStatus.ProtocolError Then

                [error].AppendFormat("Status code: ", DirectCast(webEx.Response, HttpWebResponse).StatusCode)

                [error].AppendFormat("Status description: ", DirectCast(webEx.Response, HttpWebResponse).StatusDescription)

                ' post the error message we got back.  This is the old error catch code that might work better with SSIS.

                Dts.Events.FireError(0, String.Empty, webEx.Message.ToString(), String.Empty, 0)

                Dts.TaskResult = ScriptResults.Failure

            End If

        End Try

    End Sub

End Class