Chris Pietschmann

husband, father, hacker, entrepreneur, futurist, innovator, autodidact


SQL Azure: Calculate Database Usage Percentage of Max Size

Some times it can be useful to programmatically monitor your SQL Azure database usage statistics. Luckily, there is some short SQL code that can be run on the database to check how much disk space is currently used and what the currently allotted max database size is. The following script does just that. DECLARE @dbName nvarchar(255) = '{database_name}'; DECLARE @Max BIGINT = CONVERT(BIGINT, (SELECT DATABASEPROPERTYEX(@dbName , 'MaxSizeInBytes'))); DECLARE @Used BIGINT = ( SELECT ... [More]

SQLinq: Use LINQ to generate Ad-Hoc, strongly typed SQL queries

SQLinq is a new library that allows ad-hoc SQL code to be generated at runtime in a strongly typed manner that allows for compile time validation of your SQL code. Why SQLinq? SQLinq is built with the core idea of simplicity and ease of use. SQLinq wont get in your way like other Data Access Layers will. SQLinq is not so much a Data Access Layer (DAL) as it is a code generation tool. Although, it’s not a code generator like others you may be used to. If you look at Entity Framework, you’ll se... [More]

SQL Azure: REBUILD All Indexes in Database – Alternative to DBCC DBREINDEX

Unfortunately there are some differences between SQL Server and SQL Azure. One of these differences is that SQL Azure does not support the “DBCC DBREINDEX” command. Thankfully there is an alternative you can use to rebuild the indexes within your SQL Azure databases. The alternative is to use “ALTER INDEX” instead. ALTER INDEX ALL ON TableName REBUILD Here’s an example that loops through all the tables in a database and rebuilds all their indexes: DECLARE @TableName... [More]

Using LINQ to SQL with SQL Server Compact Edition

I'm currently building a desktop application that needs to store a bunch of data on the users computer, so I've decided to use SQL Server Compact 3.5 for this. There's only one problem, Visual Studio 2008 doesn't support using LINQ to SQL with SQL Server Compact. However, the .NET Framework does and there's a work around to get it working in VS'08 that I'll describe below. In case you aren't aware of what SQL Server Compact 3.5 is, here's the short description that Microsoft gives on their webs... [More]

SQL Compact And The "Unable to load DLL 'sqlceme35.dll'" Runtime Exception

I installed SQL Server Compact 3.5 x86 and started testing it out when I got a nice "Unable to load DLL 'sqlceme35.dll" runtime exception. This seems like odd behaviour for a clean install. In a fairly quick search I found an article on the SQL Server Compact Team Blog that covers a couple possible causes/solutions for this exception. My Solution - Target x86 Only Even though there are 5 possi... [More]

SQL 2008 RTM Released, and includes .NET 3.5 SP1

Yesterday, Microsoft released the final RTM release of SQL Server 2008. Finally, the long anticipated release of SQL 2008 is here. Unfortunately, the Express edition of SQL 2008 isn't available yet (and according to the previous link, it wont be until the end of August), but if you have an MSDN Subscription you can download any of the other editions today. I'm currently installing SQL 2008 Developer Edition, and before the installation could proceed, it needed to install an update to the .NET F... [More]

Plot ZipCode Boundaries on a Map: Part 2 - Import Zip Code (U.S. Census ZCTA) Data Into A Database

Now that we've Made Sense of the U.S. Census ZCTA ARC/INFO Ungenerate (ASCII) files in Part 1 of this series, we are ready to import the U.S Census ZCTA Zip Code data into a database. In Part 2, we'll create database tables and import the Zip Code Boundary data into those tables in a MS SQL 2005 database. Create SQL 2005/2008 Database Tables First lets create a couple database tables to hold all the Zip Code boundary data. One table will hold the ZipCodes, the other will hold all the Boundary Po... [More]

T-SQL: Parse a delimited string

Parsing a delimeted string in T-SQL is fairly simple to do, but it does take a nice little chunk of code. I most commonly use this chunk of code when I need to pass an array from an application into a SQL Stored Procedure. The best way to implement this code into an application would be to create a SQL Function that you pass in a delimted string and it returns a table of values. This way you don't have to duplicate the code everytime you want to use it. The following example parses the delimete... [More]

T-SQL: Join Tables by a Field that contains a delimited string

This is the first time I ran into a situation where I needed to Join two tables (one with a varchar field and one with a varchar field that contains pipe delimited data) so I decided to post it for other who may not know you can do this. Below is a simplified example with solution of getting all rows of the first table whos values are contained in the pipe delimited field of the second table. This is really simple to do and you don't even have to use any User-Defined Functions or Cursors to... [More]