I have been converting a perl cgi script from mySQL use to MS SQL. In doing so, I have had problems just connecting to MS SQL, so I tested code out at the command prompt. Server is Win 2003 x64 and using Active Perl. I have configured the ODBC manager in both 32 and 64 bit incarnations. The confusion is with the ODBC driver name. The code below is what I use to initiate a connection.

#!c:/perl/bin/perl

# DBI ODBC Test

BEGIN
{ 
push @INC, '/gfs/www/cgi-bin/pers/karlk';
}
# push @INC, '..';

use Time::localtime;
use DBI;
use strict;

my $db_name;
my $db_type = 'MSSQL';
my $db_access_path;
my $dbh;
my $db_err = '';
my $db_errstr = '';
my @dns;
my $d;
my %sql_srvr_type = ('mySQL' => 'mysql',
                     'MSSQL' => 'ODBC:DRIVER={MS SQL 2005}');  # SQL S
+erver
my %sql_srvr_name = ('mySQL' => 'localhost', 'MSSQL' =>'localhost'); #
+ \\SQLEXPRESS
my %sql_srvr_port = ('mySQL' => '3306', 'MSSQL' => '1433');
my %sql_user = ('mySQL' => 'xxx', 'MSSQL' => 'yyy');
my %sql_pwd = ('mySQL' => 'xxx', 'MSSQL' => 'yyy');
my $db_name = 'online_labs_qa';

# dbi:DriverName:database=database_name;host=hostname;port=port
$db_access_path = "DBI:$sql_srvr_type{$db_type};server=$sql_srvr_name{
+$db_type};database=$db_name;UID=$sql_user{$db_type};PWD=$sql_pwd{$db_
+type}";

print "DBI Connect:  $db_access_path\n";

my @dsns = DBI->data_sources('ODBC');
foreach my $d (@dsns)
{
  print "Driver = $d\n";
}

# connect
print "Get DBI handle\n";
$dbh = DBI->connect($db_access_path) || die "Could not connect to data
+base: $DBI::errstr";

if (!defined($dbh))
{
  # connect error
  $db_err = $dbh->err;
  $db_errstr = $dbh->errstr;
  print "Connect Error:  $db_err, $db_errstr\n";
}
else
{
  print "Handle = $dbh\n";
}
[download]

It prints out the connection string, what the available ODBC drivers are, and the handle if it connects. The confusion is with the ODBC driver name. If I use "{SQL Server}", it connects properly but that is not the ODBC System DSN I setup. That was "{MS SQL 2005}", and that shows up as one of the 2 ODBC drivers. When I use "{MS SQL 2005}", I get the following output.

DBI Connect:  DBI:ODBC:DRIVER={MS SQL 2005};server=localhost;database=
+online_labs_qa;UID=xxx;PWD=yyy
Driver = DBI:ODBC:MySQL
Driver = DBI:ODBC:MS SQL 2005
Get DBI handle
DBI connect('DRIVER={MS SQL 2005};server=localhost;database=online_lab
+s_qa;UID=xxx;PWD=yyy','',...) failed: [Microsoft][ODBC Driver Manager
+] Data source name not found and no default driver specified (SQL-IM0
+02)(DBD: db_login/SQLConnect err=-1) at odbc_test.pl line 49
Could not connect to database: [Microsoft][ODBC Driver Manager] Data s
+ource name not found and no default driver specified (SQL-IM002)(DBD:
+ db_login/SQLConnecterr=-1) at odbc_test.pl line 49.
[download]

So what's up? Why does one work and the other not? I have another issue in getting this to work via IIS6 in CGI, but that's another post. Any insight is greatly appreciated!