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 [...]

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 [...]

Today I needed to order some data by specific column value and I recalled the really handy FIELD function in MySQL. Here’s a demo of this feature in MySQL ?View Code MYSQL# Test table CREATE TABLE City ( Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, City VARCHAR(50) );   # Insert some test data INSERT [...]

So exactly what is a computed column? MSDN has this to say A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators. The expression cannot be a [...]

Jorge Segarra (Blog | Twitter) posed the question System Documentation: What’s your method?. In my experience documentation has either been nonexistent, out of date or even worse, plain wrong. These situations often get blamed on lack of time dedicated to documentation tasks. Therefore I’ve always aimed at making documentation easy or even fun. I’ve blogged [...]

Steve Novoselac posted a good article about using CROSS APPLY with TSQL. This is a really useful technique for transforming data into grouped lists. It made me think of a similar feature in MySQL, a function, called GROUP_CONCAT. Here’s a demonstration of CROSS APPLY with TSQL and GROUP_CONCAT in MySQL to achieve the same thing. [...]

Previously I wrote an article about fulltext searching with MySQL and thought I’d redo the same article but for SQL Server users. Depending of which side of the database wars you’re on SQL Server has either a more advanced or complicated way of doing things. Here’s a very brief introduction to the fulltext search features [...]

Asking for database documentation in many tech shops will result in blank stares. Other places do see the value of but it forever remains on the to-do list. There are a few commercial products available hoping to help with this; SchemaToDoc – http://www.schematodoc.com/ SqlSpec – http://www.elsasoft.org/ SQL Doc – http://www.red-gate.com/products/SQL_Doc/index.htm I’m not convinced of their [...]

I do a fair bit of work with Linked Servers and cross-database queries  and sometimes come across the following error when joining between databases with different collations; Msg 468, Level 16, State 9, Line 1 Cannot resolve the collation conflict between ‘Latin1_General_CI_AS’ and ‘SQL_Latin1_General_Pref_CP850_CI_AS’; in the equal to operation. To replicate this error run the [...]

If you ever need to call a MySQL procedure from SQL Server it’s fairly simple thanks to ODBC and Linked Servers. This will allow you to reuse any logic already invested in MySQL Stored Procedures saving you from rewriting them. Here’s a simple example on how you can do it; Create the following procedure in [...]