Archive for 2009

Testing datetime dependent Stored Procedures

This week I was tasked with testing a stored procedure that was meant to output data on certain days. This was over a 120 day period, so I wanted to find some automated way of doing this, rather than changing the server date manually for each execution. The method I came up with involves the […]

TSQL: Query Pipe-Delimited text files with OPENROWSET

Sometimes, when working with extracts of data, it can be a pain to have to load these files into a database in order to work with them. It’s easy to use OPENROWSET to save yourself a little time. Here’s a basic example; ?View Code TSQLSELECT * FROM OPENROWSET (’MSDASQL’, ‘Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\Users\Rhys\Desktop\csv;Extended […]

Get database size With T-SQL and MySQL

I’ve recently been busy documentation various systems at work and came up with these queries to get a list of databases and their sizes for each SQL Server. These queries will show the server name, database names, and their sizes in KB, MB and GB. For SQL Server 2005 & 2008 ?View Code TSQL– SQL […]

Converting CSV FileS to XML with Powershell

Powershell is a pretty cool tool for many things including working with data. It’s just such a great time saver if you have to deal with multiple files or need to change them into different formats. Here’s how easy it is to turn a csv file into well-formed xml. ?View Code POWERSHELL# csv file to […]

SSIS: Writing to the Windows Event Log

Here’s a quick VB.Net snippet that allows you to write messages to the Windows Event Log from your packages. Nothing to configure here, just change the log message to something appropriate. Obviously, in order to work, the package would have to be run under an account that has permission to write to the event log. […]