SQL Server 2000 Notes

We are purchasing some software at work that uses SQL Server 2000 as a back end. I’ve been bringing those systems up and relearning SQL Server since I haven’t look at it in more than five years. We’ll be importing some existing data for this software so I’ve started writing up a few scripts. I’m using PHP with ADOdb for database access. For the most part things have gone well, with some small expections/gotchas. Fortunately none of the problems I noticed were hard to fix, but they did seem a little odd sometimes. Here are some notes on my experiences with this process thus far:

1. FreeTDS – Build

   In order to get PHP talk to SQL Server 2000 I’m using FreeTDS on a FreeBSD system. The first thing to note is that SQL Server 2000 uses 8.0 TDS. When building from FreeTDS from the ports collection be sure to override the default, which is 5.0. You’ll also need to enable msdblib in FreeTDS, so make sure that WITH_MSDBLIB is enabled during the build also. Then all that is needed for PHP4 to speak to SQL Server is the php4-mssql port.

2. FreeTDS – Config

   In recent versions of FreeTDS they’ve started using a freetds.conf file to configure connections. There is an example in there for connecting to SQL Server 2000, here is what I ended up using:
   
[SQL2K]
host = xxx.xxx.xxx.xx
port = 1433
tds version = 8.0
; dump file = /var/log/freetds.log
; dump file append = yes
; debug level = 99


   I had a few problems connecting initially, so it was helpful to run with debug on until I got things straightened out. In PHP you’d use SQL2K to connect instead of the host name or IP address.

3. FreeTDS – Domain Logins

   Our user accounts are all managed by Active Directory, besides the default accounts in SQL Server 2000 all of the permissions are handled via Windows domain accounts. This means that all of your usernames have to be prefixed with the Windows domain. Something like EXAMPLEusername. If you run into problems check out the FreeTDS documentation about domain logins.

4. SQL Server 2000 – Quoting

   Note that quotes are escaped with quotes, not back slashes. I personally don’t care for this method, but it seems to be the only one that SQL Server 2000 supports. If you are going to use CSV to import data you need to pay attention to this. Although CSV is supported I ended up going to tab delimited instead. This is the default that SQL server expects and it made some of the quoting issues easier.

5. ADOdb – rs2tabout()

   For the sake of consistency I’m using ADOdb to deal with database access in PHP. One neat feature I found was the ability to easily export a result set to CSV or tab delimited formats. This was exactly what I needed to do, so this seemed like a great solution. Unfortunately rs2tabout() tries to escape quotes, which SQL Server choked on. The quotes didn’t need to be escaped since tabs were being used as delimiters, so I poked at the code to see what was happening. All of the export functions derive from a base function with several options, so I was able to call that function directly and override the defaults. If you want data tab delimited with quote escaping turned off you can do that with:

   print(_adodb_export($rs, "t", " ", true, false, false));

   or

   $line = _adodb_export($rs, "t", " ", false, false, false);

6. bcp

   Importing lots of data with bcp is much easier than clicking through the import/export wizard. To make life easier you’ll need a format file for the table that your are importing data into. The easiest way I’ve found to generate the format file is to let bcp do it for you:
   
bcp testdb.dbo.tablename format tablename.dat -U username -P password -f tablename.fmt -c

   Then importing is done like this:
   
bcp testdb.dbo.tablename in tablename.tsv -U username -P password -f tablename.fmt -e tablename.err

   If bcp has any problems importing data will be logged in the tablename.err file. If it runs into enough errors the import will terminate, the default is ten. In the test table that I’ve been using has 98,000+ records in it. The process of retrieving the data and formating into tab separated values (tsv) takes about 110 seconds. The import with bcp takes less than 5 seconds. I like it when things go fast 🙂

That pretty much covers it for now.