Connect to a Microsoft SQL Server from Linux

 Networking, Perl, SQL  No Responses »
Feb 222011
 

Introduction

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.

Basics

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.

Programmatic

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

References

  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
Share

Analyzing A Remote Microsoft SQL Database

 Networking, SQL  No Responses »
Feb 222011
 

Introduction

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
GO

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
GO

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

SELECT * FROM INFORMATION_SCHEMA.TABLES
GO

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

sp_help
GO

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:

SELECT * FROM sysobjects WHERE TYPE='s' OR TYPE='u' ORDER BY NAME
GO

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;
GO

Listing All Columns

Try this:

SELECT name, object_name(id) FROM sysindexes

Determining DB Size

Try this:

EXEC sp_spaceused [tablename]
GO
Share

How To Check MS-SQL Columns of Unknown Name for Values

 Networking, SQL  No Responses »
Feb 222011
 

Problem

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:

SELECT * FROM myTable

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

SELECT DISTINCT * FROM myTable

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:

DECLARE @myColumnNames NVARCHAR(MAX)
 
SELECT @myColumnNames=COALESCE(@myColumnNames + ',', '') + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME='myTable') AND (COLUMN_NAME LIKE 'UDA_%')
 
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

DECLARE @myColumnNames NVARCHAR(MAX)
 
SELECT @myColumnNames=COALESCE(@myColumnNames + ',', '') + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME='myTable') AND (COLUMN_NAME LIKE 'UDA_%')
 
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!

Share