• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1225
  • Last Modified:

Perl & DBI: Won't connect to ODBC on MS Server 2003

Cliff Notes: can't connect using Perl, DBI, ODBC.. end result:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL-IM002)(DBD: db_login/SQLConnect err=-1) at dbtest.pl line 22.

My guesses:
Connection string? (seems ok)
Need a different DBI driver?
Something outside of Perl needs to be set up for the database to listen?

Ok, so here's the Perl:

#Perl Script on Win2003 Server R2: dbtest.pl
#ActiveState Perl v5.8.8 build 822
#MS SQL Server 2005 v9.00.2047.00

use strict;
use warnings;

use DBI;
my  @driver_names = DBI->available_drivers;
print join(", ", @driver_names), "\n\n";
#Result of print: DBM, ExampleP, File, Gofer, ODBC, Proxy, SQLite, Sponge


my $username = 'myusername';
my $password = 'mypassword';
my $database = 'P21PlayODBC';
my $hostname = 'Server1';

my $dbh = DBI->connect("dbi:ODBC:dbname=$database;host=$hostname;port=3306",
                                 "$username",
                                 "$password") or die "\n\nD'oh! Could not connect:\n" . DBI->errstr;

$dbh->disconnect();


The database that I'm trying to connect to is actually called P21Play. However, the P21PlayODBC is what I set up using the ODBC Data Source Administrator. Honestly, I don't know if I need to to do that step or not, but here is the settings I used:
-User DSN, Play21ODBC as name, Driver SQL Server.

Now I was wondering if ODBC was listening at all, so I used the old ODBCping.exe:
D:\SecureData\P21Data\PricingServiceImport\Raw>odbcping -S Server1

CONNECTED TO SQL SERVER

ODBC SQL Server Driver Version: 03.86.3959

SQL Server Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
        Apr 14 2006 01:12:25
        Copyright (c) 1988-2005 Microsoft Corporation
        Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Ok, so it's at least *there*. Now, is it getting anything from the Perl script? I tried turning on the trace via ODBC Data Source Administrator. Here are those results:

perl dbtest     1e4-1270      ENTER SQLAllocHandle
            SQLSMALLINT                  1 <SQL_HANDLE_ENV>
            SQLHANDLE           00000000
            SQLHANDLE *         01A8F968

perl dbtest     1e4-1270      EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
            SQLSMALLINT                  1 <SQL_HANDLE_ENV>
            SQLHANDLE           00000000
            SQLHANDLE *         0x01A8F968 ( 0x003c1c18)

perl dbtest     1e4-1270      ENTER SQLSetEnvAttr
            SQLHENV             003C1C18
            SQLINTEGER                 200 <SQL_ATTR_ODBC_VERSION>
            SQLPOINTER          0x00000003
            SQLINTEGER                  -6

perl dbtest     1e4-1270      EXIT  SQLSetEnvAttr  with return code 0 (SQL_SUCCESS)
            SQLHENV             003C1C18
            SQLINTEGER                 200 <SQL_ATTR_ODBC_VERSION>
            SQLPOINTER          0x00000003 (BADMEM)
            SQLINTEGER                  -6

perl dbtest     1e4-1270      ENTER SQLAllocHandle
            SQLSMALLINT                  2 <SQL_HANDLE_DBC>
            SQLHANDLE           003C1C18
            SQLHANDLE *         01A8FFD4

perl dbtest     1e4-1270      EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
            SQLSMALLINT                  2 <SQL_HANDLE_DBC>
            SQLHANDLE           003C1C18
            SQLHANDLE *         0x01A8FFD4 ( 0x003c1cc0)

perl dbtest     1e4-1270      ENTER SQLDriverConnectW
            HDBC                003C1CC0
            HWND                00000000
            WCHAR *             0x48897CF4 [      -3] "******\ 0"
            SWORD                       -3
            WCHAR *             0x48897CF4
            SWORD                        2
            SWORD *             0x00000000
            UWORD                        0 <SQL_DRIVER_NOPROMPT>

perl dbtest     1e4-1270      EXIT  SQLDriverConnectW  with return code -1 (SQL_ERROR)
            HDBC                003C1CC0
            HWND                00000000
            WCHAR *             0x48897CF4 [      -3] "******\ 0"
            SWORD                       -3
            WCHAR *             0x48897CF4
            SWORD                        2
            SWORD *             0x00000000
            UWORD                        0 <SQL_DRIVER_NOPROMPT>

            DIAG [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0)

perl dbtest     1e4-1270      ENTER SQLErrorW
            HENV                003C1C18
            HDBC                003C1CC0
            HSTMT               00000000
            WCHAR *             0x0140EB40 (NYI)
             SDWORD *            0x0140ED88
            WCHAR *             0x0140E740
            SWORD                      511
            SWORD *             0x0140EDA6

perl dbtest     1e4-1270      EXIT  SQLErrorW  with return code 0 (SQL_SUCCESS)
            HENV                003C1C18
            HDBC                003C1CC0
            HSTMT               00000000
            WCHAR *             0x0140EB40 (NYI)
             SDWORD *            0x0140ED88 (0)
            WCHAR *             0x0140E740 [      91] "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"
            SWORD                      511
            SWORD *             0x0140EDA6 (91)

perl dbtest     1e4-1270      ENTER SQLErrorW
            HENV                003C1C18
            HDBC                003C1CC0
            HSTMT               00000000
            WCHAR *             0x0140EB40 (NYI)
             SDWORD *            0x0140ED88
            WCHAR *             0x0140E740
            SWORD                      511
            SWORD *             0x0140EDA6

perl dbtest     1e4-1270      EXIT  SQLErrorW  with return code 100 (SQL_NO_DATA_FOUND)
            HENV                003C1C18
            HDBC                003C1CC0
            HSTMT               00000000
            WCHAR *             0x0140EB40 (NYI)
             SDWORD *            0x0140ED88
            WCHAR *             0x0140E740
            SWORD                      511
            SWORD *             0x0140EDA6

perl dbtest     1e4-1270      ENTER SQLErrorW
            HENV                003C1C18
            HDBC                00000000
            HSTMT               00000000
            WCHAR *             0x0140EB40 (NYI)
             SDWORD *            0x0140ED88
            WCHAR *             0x0140E740
            SWORD                      511
            SWORD *             0x0140EDA6

perl dbtest     1e4-1270      EXIT  SQLErrorW  with return code 100 (SQL_NO_DATA_FOUND)
            HENV                003C1C18
            HDBC                00000000
            HSTMT               00000000
            WCHAR *             0x0140EB40 (NYI)
             SDWORD *            0x0140ED88
            WCHAR *             0x0140E740
            SWORD                      511
            SWORD *             0x0140EDA6

perl dbtest     1e4-1270      ENTER SQLFreeHandle
            SQLSMALLINT                  2 <SQL_HANDLE_DBC>
            SQLHANDLE           003C1CC0

perl dbtest     1e4-1270      EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
            SQLSMALLINT                  2 <SQL_HANDLE_DBC>
            SQLHANDLE           003C1CC0

perl dbtest     1e4-1270      ENTER SQLFreeHandle
            SQLSMALLINT                  1 <SQL_HANDLE_ENV>
            SQLHANDLE           003C1C18

perl dbtest     1e4-1270      EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
            SQLSMALLINT                  1 <SQL_HANDLE_ENV>
            SQLHANDLE           003C1C18
0
anders8
Asked:
anders8
  • 2
  • 2
1 Solution
 
anders8Author Commented:
Didn't realize how many points I had to use... First timer. :)
0
 
clockwatcherCommented:
Your connection string looks like it's part ODBC, part MySQL connection string, definitely doesn't look right.  Try:

  my $dbh = DBI->connect("dbi:ODBC:P21PlayODBC", $user, $password);
0
 
anders8Author Commented:
Awe.
Some.

So, in conclusion, yes, one *does* need to use the ODBC data source manager. The connection string provided by clockwatcher is correct.

:)

Anders
0
 
clockwatcherCommented:
You don't need to use a defined DSN.  A dsnless ODBC connection to SQL server for your database would look like:

  my $dsnless = "Driver={SQL Server};Server=Server1;Database=P21Play;Uid=$username;Pwd=$password";
   my $dbh = DBI->Connect("dbi:ODBC:$dsnless");

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now