Category Archives: MS SQL

SQL For Fun: Parsing the iTunes XML

What happens if one part of your personality is data-parsing nerd and another part is music lover nerd? You end up trying to answer questions like, “Which songs did I have rated with 4 stars last year that are lower … Continue reading

Posted in Apple, iTunes, MS SQL, Transact SQL | Leave a comment

Using Tally Table to Remove Invalid XML Characters

A couple of weeks ago, I was called in to troubleshoot an error occurring in a SQL stored procedure. The procedure was selecting a variety of information out as XML data. The error occurred because on of the columns being … Continue reading

Posted in MS SQL, REGEX, Transact SQL | Tagged , , , , | Leave a comment

Using SQL to Mege Three Incomplete Data Sets

You can join data sets. LEFT JOIN, RIGHT JOIN, even OUTER APPLY. There’s nothing to learn here. Last week, I had what seemed to be a simple join of three data sets. My permutation was a lot more complicated than … Continue reading

Posted in MS SQL, Transact SQL | Tagged , , , , , , , | Leave a comment

Find DTS and FTP Job Steps From msdb

I started a new full-time contract this month, which is always weird. Not having a full understanding of the project, access rights roadblocks, and unfamiliarity with the code base all team up to make days feel long and unproductive. One … Continue reading

Posted in MS SQL | Tagged , , | Leave a comment

Update Across Joined Tables in MySQL

I had to do a MySQL update on records that were retrieved from a two-table join today. I shouldn’t have been surprised to find that I needed a different syntax than I know from MS SQL, where I would write, … Continue reading

Posted in MS SQL, mysql, Transact SQL | Tagged , , , , | Leave a comment

Easy Foreign Key Drop and Restore for SQL Server

From time to time, we’ve all had a database query to run that was stymied by foreign key constraints. In my case, it was a request to restore a table’s values from one copy of the system to another. We … Continue reading

Posted in Database Design, MS SQL, Transact SQL | Tagged , , , , , , | Leave a comment

Assigning Variables When No Row Exists

A SQL Server tidbit that I never knew before today. This was a test on SQL 2000. I’d be interest to know if you get the same result on something more current. [Update: still just like this on SQL 2008] … Continue reading

Posted in MS SQL, Transact SQL | Leave a comment

Waitfor it

Suppose you are charged with system QA for a complicated system that expects dozens or hundreds of simultaneous users. The system has a user front end through either a browswer of through deployed software, and it sends transactions back and … Continue reading

Posted in MS SQL, Software QA/Testing, Transact SQL | Tagged , , , , , , , | Leave a comment

SQL Queries Using with nolock

Last year, I had an assignment to create some reports using SQL Server Reporting Services (SSRS). The database team at this company had created a reporting database, using data cubes, with information rolled up from a number of production systems. … Continue reading

Posted in MS SQL, Transact SQL | Tagged , , , , , , , | Leave a comment