How do I connect with Perl to SQL Server?

perl dbi
Bharanikumar picture Bharanikumar · Feb 5, 2011 · Viewed 48.9k times · Source

I have a user id, password, database name and datasource details. I want to connect with Perl to a MSSQL server. I just used the following snippet, but I am getting an error.

#!/usr/bin/perl -w
use strict;

use DBI;

my $data_source = q/dbi:ODBC:192.168.3.137/;
my $user = q/bharani/;
my $password = q/123456/;

# Connect to the data source and get a handle for that connection.
my $dbh = DBI->connect($data_source, $user, $password)
    or die "Can't connect to $data_source: $DBI::errstr";

My error is:

DBI connect('192.168.3.137','bharani',...) failed: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL-IM002) at my sqlconnect.pl line 14
Can't connect to dbi:ODBC:192.168.3.137: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL-IM002) at mysqlconnect.pl line 14.

The SQL server runs on another system, I am just trying to connect with above details. Please tell me, should I crease DSN in my system, or is anything missing in my program?

Answer

bvr picture bvr · Feb 5, 2011

Everything following 'dbi:ODBC:' in your connection string is passed to the ODBC driver. For MSSQL, try this connection string:

DBI->connect("dbi:ODBC:Driver={SQL Server};Server=192.168.3.137;UID=$user;PWD=$password")

You can find some more alternatives on connectionstrings.com

Related questions

How do I know how many rows a Perl DBI query returns?

I'm trying to basically do a search through the database with Perl to tell if there is an item with a certain ID. This search can return no rows, but it can also return one. I have the following code: …

Read More
Fetch all records using Perl DBI

I have a Perl script that uses a DBI connection. I open and read the SQL script file using a sub routine. I am printing only one record, where I should have two more (three records total). How do I …

Read More
How can I fetch the last row I inserted using DBI?

How can I fetch the last row that was inserted using DBI (DBD::mysql)? Code sample: my $sth = $dbh->prepare('INSERT INTO a ( x, y, z ) VALUES ( ?, ?, ? )'); $sth->execute( $x, $y, $z ); How can I get access …

Read More