Win has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

The following code produces the following report. Any explanations?

#! perl -w scipt use strict; use DBI; use DBD::ODBC; my ( $data_source, $database, $user_id, $password ) = qw( matt First_t +rial matt gold ); my $conn_string = "driver={SQL Server};Server=$data_source;Database=$d +atabase;UID=$user_id;PWD=$password"; my $dbh = DBI->connect( "DBI:ODBC:$conn_string" ) or die $DBI::errstr; my $sql = "SELECT * FROM Doncaster_1975_onwards LIMIT 100"; my $sth = $dbh->prepare( $sql ); $sth->execute; my $out = "out_connection.txt"; open (OUT, "$out"); while ( my $result = $sth->fetchrow_hashref ) { # Your fields can be accessed through the $result hashref, for exa +mple: print OUT "$result\n"; } $dbh->disconnect; C:\Documents and Settings\MattR\2003\Perl\November\5_12_04>perl Data_A DBI connect('driver={SQL Server};Server=matt;Database=First_trial;UID= +matt ;PWD=gold','',...) failed: [Microsoft][ODBC SQL Server Driver][Named P +ipes]S pecified SQL server not found. (SQL-08001) [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (Create +File()). ( SQL-01000)(DBD: db_login/SQLConnect err=-1) at line 15 [Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL server n +ot found. (SQL-08001) [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (Create +File()). ( SQL-01000)(DBD: db_login/SQLConnect err=-1) at line 15.

Replies are listed 'Best First'.
Re: Access to MS Server
by mpeppler (Vicar) on Nov 05, 2003 at 17:17 UTC
    The SQL Server name ("matt") that you are trying to connect to doesn't exist. I don't know much about ODBC, but you must specify a server name that is known to the system - i.e. that maps to a running server somewhere on your network...


Re: Access to MS Server
by iburrell (Chaplain) on Nov 05, 2003 at 18:04 UTC
    I have found that if you are going to access the same database repeatedly, it is better to create a system ODBC data source. The wizard checks that the server, username, and password exist and work.
    my $conn_name = 'matt'; my $dbh = DBI->connect("dbi:ODBC:$conn_name", $username, $password);
Re: Access to MS Server
by tcf22 (Priest) on Nov 05, 2003 at 19:23 UTC
    It could be a few things. From the error messages it looks like it can't find the SQL server matt. I'm gonna assume that the server does exist. Check that the SQL server allows Named Pipe connections. I've worked in places that only allowed tcp connections to sql servers for security reasons.

    I'm not sure how to change the connection method using DBI, i've always just setup an ODBC connection, and setup the connection properties through the windows interface.

    Depending on how complicated the setup is, it could be a networking issue. I've ran into this problem before at work, and the SQL servers only allowed access from certain systems, one of which wasn't mine.

    - Tom

Re: Access to MS Server
by monktim (Friar) on Nov 06, 2003 at 16:39 UTC
    You can write code to set up the data source. I've only done it using Win32::ODBC.
    use strict; use warnings; use Win32::ODBC; my $server = 'matt'; my %data_source = Win32::ODBC::DataSources(); if (not defined($data_source{$server})) { # # Create a system DSN to the SQL Server # if (!Win32::ODBC::ConfigDSN(ODBC_ADD_SYS_DSN, 'SQL Server', ( "DSN=$server", "DESCRIPTION=$server", "SERVER=$server", 'DATABASE=First_trial', 'NETWORK=DBMSSOCN' ) )) { die "Unable to create DSN for $server"; } }
      Other things that confuse me about this code:

      Should DESCRIPTION not be put down as DATA_SOURCE_DESCRIPTION?

      When I run this code I get:

      Undefined subroutine &Win32::ODBC::DataSources called at lin +e 13.

      What is the subroutine that I need for this code to work?

      Can I specify the Server as 'local'?

      I have tried switching Win32 to WinNT.

      When do I use Win32::ODBC and when do I use DBD::ODBC?

      Please answer any points that you can.
        Hi Win,

        The code should work as is. Try to run this code by itself and then check the ODBC data sources in the Control Pannel. You should see "matt" or whatever you assign $server to.

    A reply falls below the community's threshold of quality. You may see it by logging in.