[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2007-10-18
4
Medium Priority
?
1,210 Views
Last Modified: 2008-01-09
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
Comment
Question by:anders8
  • 2
  • 2
4 Comments
 

Author Comment

by:anders8
ID: 20101550
Didn't realize how many points I had to use... First timer. :)
0
 
LVL 25

Accepted Solution

by:
clockwatcher earned 2000 total points
ID: 20106820
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
 

Author Comment

by:anders8
ID: 20111449
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
 
LVL 25

Expert Comment

by:clockwatcher
ID: 20113323
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question