Access to MS Server

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
+.pl
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 Data_A.pl 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 Data_A.pl line 15.
[download]

Comment on Access to MS Server Download Code

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... Michael	[reply]
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);` [download]	[reply] [d/l]
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	[reply]
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"; } }` [download]	[reply] [d/l]
Re: Re: Access to MS Server by Win (Novice) on Nov 10, 2003 at 10:40 UTC
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 Data_B.pl lin +e 13.` [download] 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.	[reply] [d/l]
Re: Re: Re: Access to MS Server by monktim (Friar) on Nov 12, 2003 at 22:48 UTC
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.	[reply]
A reply falls below the community's threshold of quality. You may see it by logging in.


Keep It Simple, Stupid
	PerlMonks