TryCatchFinally.net Some SQL, some .NET, and whatever else

29Apr/1210

Powershell command to get current sessions on an IIS site

After a Powershell session at SQL Saturday (Phoenix #131) this weekend, I'm now suddenly on the lookout for handy powershell commands. The first one lets you see the number of Active* sessions on your IIS site (* because HTTP is stateless, it's really the number of connections that have been opened recently - not what's currently active, which is likely next to zero. I tried browsing around on a test site and it showed only my one user connected).

To get the currently active user count, here's the powershell:

# Ensure you use the server's actual name, not LOCALHOST, which won't work
$Servername = "Your Server Name"
$Sitename = "Name of your IIS Site"

Get-Counter "\\$ServerName\web service($SiteName)\current connections"
9Apr/120

Presenting a pair of lightning sessions at SQL Saturday in Phoenix

I'll be presenting a pair of lightning sessions at SQL Saturday in Phoenix, AZ, on April 28th - if you're in the area and up for a day of free training on everything database-related, as well as some networking time with your fellow DBAs and Developers, please join us!

Here's the summary for my two lightning sessions (they'll be 15 minutes each):

Automating SQL Server source control - This session will look at a quick and easy process that takes regular snapshots of object definitions in the database and stores any changes.

Representing SQL Server data spaces visually - This session will take a quick look at a Codeplex project that does this, SQL Space Map, and how it accomplishes it by leveraging a library from Microsoft Research

If you'd like to see what else is planned for the day, http://sqlsaturday.com/131/schedule.aspx. You can also follow the event on twitter at #sqlsat131

6Apr/120

Export from SQL Server to XLS and email results

Sometimes you want to take some query results and export them directly to an XLS file - here's how you can set that up in SQL Server. The biggest caveat is that you need to run it from an x86 instance of SQL Server - the x64 instance won't have access to the Jet driver needed to write the Excel file (Microsoft.Jet.OLEDB.4.0), where the x86 version will. In fact, we maintain an older x86 instance of SQL Server for random processes like this that need it - x64 is better in almost every case, but we can't see to completely ditch x86... 🙂

I use a stored proc that I call from a SQL Agent Job, which works great. The actual process is a bit awkward - for starters, you'll need access to xp_cmdshell. SQL Server can't create a new Excel file from scratch, so you have to keep a blank Excel file around, make a copy of it, and then insert into the copy to get your final result.

That said, here's the code to generate the XLS file from your query results:

SELECT Column1, Column2, Column3, Column4
  INTO ##YourTempTable
  FROM SomeOtherTable

SET @Folder = 'C:\Temp\'
SET @DocumentBlank = 'Your Document - Blank'
SET @DocumentLong = 'Your Document - ' + CONVERT(VARCHAR(10), GETDATE(), 120)

DECLARE @CMD NVARCHAR(4000)
SET @CMD = 'COPY "' + @folder + @DocumentBlank + '.xls" "' + @Folder + @DocumentLong + '.xls"'
exec master..xp_cmdshell @CMD

-- Export the Excel sheet
SET @CMD = 'insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
	''Excel 8.0;Database=' + @Folder + @DocumentLong + '.xls;'',
	''SELECT * FROM [Sheet1$]'')
	select Column1, Column2, Column3, Column4 from ##YourTempTable'

exec sp_executesql @CMD

Once that's exported, you can just set up the email process using sp_send_dbmail and attach the file you just generated:

DECLARE @Body VARCHAR(2000)

SET @Attachments = @Folder + @DocumentLong  + '.xls'
SET @Body = 'Your file has been generated for ' + CONVERT(VARCHAR(10), GETDATE(), 120)

exec msdb..sp_send_dbmail @profile_name = 'YourMailProfile',
	@Recipients = 'Recipients@YourDomain.biz',
	@subject = 'Your file is ready',
	@Body = @Body,
	@file_attachments = @DocumentLong