Feb 222011


Sometimes, you may only be able to connect to a Microsoft SQL (MS-SQL, or MSSQL) server through its SQL port, so you cannot use RDP to access the Enterprise Manager or other graphical tools on the host.  Other times, you may simply want to leverage the power of a Linux box.  😉  Regardless of the reason, if you want to connect to a MS-SQL server from a Linux box, read on…  As in previous posts, any installation or configuration instructions pertain to Gentoo.  Please adapt as necessary. … Also, these instrcutions were tested on MS-SQL Server 2000, so some instructions may need to be adapted depending on your version of MS-SQL.


FreeTDS offers an opensource command line client, tsql.  This is comparable to using Microsoft’s OSQL command line interface, although the arguments to launch the client are different.  Although it has various options, you launch tsql, like so:

tsql -S <sql_server_name> -U <user_name> [-P <password>]

If you are comfortable with OSQL, you will have no problem using this basic SQL CLI client.


Packagers are available for Perl, PHP, Python, and several other scripting languages, which provide an extensive, programmatic interface to the remote MS-SQL server.


  1. http://members.cox.net/midian/howto/phpMSSQL.htm
  2. http://coding.derkeiler.com/Archive/Perl/perl.dbi.users/2006-09/msg00108.html
  3. http://www.easysoft.com/developer/languages/perl/sql_server_unix_tutorial.html
  4. http://www.easysoft.com/developer/interfaces/odbc/linux.html
Feb 222011


Suppose you have access to a remote MS-SQL database, and you need to analyze it.  Why?  Let’s pretend that you intend to migrate the contents to another server or database, like MySQL.  😉  Anyway, how do you discover various essentials about the database? … These instructions are for Microsoft SQL Server 2000.  The following instructions and results will probably vary wildly for other versions of MS-SQL.

Connect with TSQL

First, let’s connect to the remote MS-SQL using the tsql command line client:

tsql -S <sql_server_name> -U <user_name>

You may have another favorite method to gain command line access to the SQL database, but this is my current favorite.  🙂

Listing All The Tables in the Database

In our open SQL CLI, one site posted doing something like:

EXEC sp_tables

Unfortunately, this does not list all the tables for some unknown reason.  I could see several more tables listed in MS SQL Enterprise Manager.  Most of my “user” tables were not reported by the above method, although some were reported correctly.

Another site indicated listing special system table contents, which I think is actually specific to ORACLE and newer versions of MS-SQL.  Maybe it will work for you?

SELECT * FROM sys.tables

The same site also suggested the following, but it also returned partial results for some reason unknown to me.


Others recommend using “sp_help” with no arguments, like so:


However, I found that it returned far too much information, much more than just the list of all tables, although that did seem to be included…

Ultimately, I used this:


This returned a filtered list of system objects that had a type of either “system” or “user” table.  Very nice! 😀

Listing Table Structure

The structure of a particular structure can be listed, like so:

sp_help &lt;table_name&gt;

Listing All Columns

Try this:

SELECT name, object_name(id) FROM sysindexes

Determining DB Size

Try this:

EXEC sp_spaceused [tablename]
Feb 222011


Imagine you have an MS-SQL table that has LOTS of columns.  Some of the column names are known, but others are dynamically generated, and their names are not known at run time.  Now imagine that you suspect most of those unknown columns are empty.  How do you determine if any rows contain values in the columns, whose names are unknown?

Incremental Solutions

You could always just show all the values for every column of every row:


However, if your table is large, this may return too much data.  So, you could look for unique values, like so:


This works better.  However, if the known columns contain lots of unique data, the above T-SQL command may still return too much data.  Let’s try to focus on returning the unique values of just the unknown columns!

Final Solution

In our problem, all of the unknown columns begin with a known prefix, “UDA_”.  So, we need to get a list of the column names compiled in a comma separated list, suitable for a second SELECT statement.  We can do this, like so:

SELECT @myColumnNames=COALESCE(@myColumnNames + ',', '') + COLUMN_NAME
SELECT @myColumnNames

These statements initialize a local variable, @myColumnNames, and then COALESCE, or compile all the column names from the myTable, which begin with the prefix, “UDA_”, into a comma separated list.  The last statement prints the variable value for debugging purposes, which might look like:

Finally, a simple select statement can be used to return the distinct values of these columns from our table.  The only problem is that you cannot substitute a variable directly into a T-SQL statement, so you have to build the necessary statement string and execute it, like so:

EXEC('SELECT DISTINCT ' + @myColumnNames + ' FROM myTable')

Putting It All Together

SELECT @myColumnNames=COALESCE(@myColumnNames + ',', '') + COLUMN_NAME
EXEC('SELECT DISTINCT ' + @myColumnNames + ' FROM myTable')

If the above statements yield a single row with nothing but NULLs, then you know those columns are all empty!  Anything else indicates that at least one of the table rows, for at least one of your columns of unknown name, contains a value!

Jan 232009

Problem Introduction

I use an open-source solution for hosting my family’s photos, called Gallery. Recently, my gallery2 install crashed, and I lost my online database. “No big deal”, I thought. “I have all the pictures saved on my local computer.” WRONG-O!!! I hit two major snags: One, gallery can stumble very easily while doing a bulk upload from a local server. Two, all of the album dates were stamped with the time of my recent upload, which destroyed by chronological sorting of the albums.

I started manually “editing” each album through Gallery2’s web interface, but that gets old real fast, so I decided to try a little MySQL wizardry.


Gallery2 stores most of its data, except for the photos and movies, in a MySQL database on the web-server. Of course, you can use other back-ends, but MySQL is very popular. The database can be manipulated using your favorite MySQL monitor, whether that occurs through a command shell or a web-interface, like phpMyAdmin.

My solution was to use MySQL to search through each album, find the oldest picture, and update the album’s origination date to match the oldest picture.

Using my favorite MySQL interface, I crafted the following SQL statement to examine the problem:

SELECT g_id AS albumId, g_title, g_originationTimestamp, (
SELECT MIN(g_originationTimestamp)
FROM g2_Item
INNER JOIN g2_ItemAttributesMap
ON g2_Item.g_id = g2_ItemAttributesMap.g_itemId
WHERE g_parentSequence LIKE CONCAT( '%/', albumId, '/' )
) AS oldestPicture
FROM g2_Item
INNER JOIN g2_ItemAttributesMap ON g2_Item.g_id = g2_ItemAttributesMap.g_itemId
WHERE g_canContainChildren &gt; 0

This produced results like:

albumId albumTitle currentTime oldestPicture
7 Gallery 1232042846 NULL
58896 Caleb’s Turn 1232650930 1175871585
59400 Originals 1232650977 1175871585
59416 Landscapes and Nature 1232651065 1232651128
59417 Blossoms and Sunsets 1232651128 1081207812
59600 icebergs 1232651167 1232651167
59633 More Sunsets and Bathtime 1232651168 1174399484
59634 Originals 1232651168 1174399529
59943 Trees and Geese 1232651264 1081295988
60013 Family Photos 1232653712 978487347

As you can see, many of the albums had pictures much older than the timestamp on the folder. The theory appears sound. Let’s experiment!

Attempt #1

Since we are experimenting on your gallery database, obviously you want to back it up first. If I have to tell you that, you are in over your head. 😉

Using a modified version of the above query, I tried to update the origination timestamp to the value of the oldest picture, like so:

UPDATE g2_Item AS albumId SET g_originationTimestamp =
SELECT MIN(g_originationTimestamp)
FROM g2_Item INNER JOIN g2_ItemAttributesMap
ON g2_Item.g_id=g2_ItemAttributesMap.g_itemId
WHERE g_parentSequence LIKE CONCAT('%/', albumId.g_id, '/')
) WHERE g_canContainChildren &gt; 0

Unfortunately, this produces the following error:

ERROR #1093 - You can't specify target table 'albumId' for update in FROM clause

Apparently, UPDATE will not allow you to modify a table that is part of the query. That seems reasonable. However, that is exactly what I needed to do. This forced me to create a temporary table to hold the intermediate results, and then use those results to update the desired table.

Attempt #2

Here was my final solution, which required 4 separate statements:


This first statement is only necessary, if you iterate and experiment with this approach. It deletes the temporary table, if it exists, which may happen after you tweak something and try again, depending on your connection method.

CREATE TEMPORARY TABLE newAlbumTimes (albumId INT(11), albumTitle VARCHAR(128), albumTimeStamp INT(11), oldestPicture INT(11))
SELECT g_id AS albumId, g_title AS albumTitle, g_originationTimestamp AS albumTimeStamp, (
SELECT MIN(g_originationTimestamp )
FROM g2_Item
INNER JOIN g2_ItemAttributesMap
ON g2_Item.g_id = g2_ItemAttributesMap.g_itemId
WHERE g_parentSequence LIKE CONCAT( '%/', albumId, '/' )
) AS oldestPicture
FROM g2_Item
INNER JOIN g2_ItemAttributesMap ON g2_Item.g_id = g2_ItemAttributesMap.g_itemId
WHERE g_canContainChildren &gt;0
ORDER BY oldestPicture;

Now that is the “brains” of the operation. First, notice the select statement, very similar to the original query. However, these results are being fed into a “CREATE TEMPORARY TABLE” statement, which catches the results. Notice, we had to tell MySQL the structure of this temporary table, which should generally match the structure of the output columns. This temporary table will be destroyed when the connection closes. However, we will use this table in the meantime, to update the timestamps based on the oldest picture’s timestamp.

UPDATE g2_Item INNER JOIN newAlbumTimes
ON g2_Item.g_id = newAlbumTimes.albumId
SET g_originationTimestamp=oldestPicture
WHERE oldestPicture AND oldestPicture &lt; albumTimeStamp;

Using the temporary table, the origination time is updated for all albums in the item table, but only if the oldest picture column is non-NULL and if the picture is stamped older than the album. Otherwise, we assume the time stamp for the album is better than what we have calculated, so we leave it alone.

UPDATE g2_Entity INNER JOIN newAlbumTimes
ON g2_Entity.g_id = newAlbumTimes.albumId
SET g_creationTimestamp=oldestPicture, g_modificationTimestamp=oldestPicture
WHERE oldestPicture AND oldestPicture &lt; g_creationTimeStamp;

We also have to update the creation time stamps, which is the date actually displayed and permitted to be edited for the album. This is the statement that does what we want!


Running the above 4 statements in order produces the desired affect.  Preferably, these should be executed as a single entry to avoid the temporary table disappearing. However, this procedure only “bubbles up” the oldest time stamp by one level.  Most albums will be updated correctly after this.  However, if you have nested albums (albums inside albums), you will have to run this procedure at least once for each level of album (or folder) hierarchy.  This will cause your top level album to show a creation date equal to or older than the oldest picture anywhere in its hierarchy.


If you are running an older version (Gallery1) or newer version (Gallery3, currently in early development), you will obviously have to modify the above statements to match the your database structure. Also, these statements were executed using MySQL 5.0.70. If you use an older version, like MySQL 4.0, you may find the nested queries do not work. This will require you to create an additional temporary table to cache those intermediate results – much more complicated.  It may be better to upgrade, if you can.

You can include additional constraints on the WHERE clause of the final two statements to only modify certain albums.  This can be useful if you are only want to fix a certain subset of albums in your gallery.