Thursday, July 30, 2009

Real simple to connect to MSSQL from Perl

Again, this took me a little while but here I am. Connecting to a database ( SQL SERVER 2008 ) from a Windows Server 2008 VM I own ( thanks TECH ED 2008 ) and Active State Perl.

Install Scite cos it's a nice editor and you are away laughing...


use DBI;

my $DSN = 'driver={SQL Server};Server=localhost; database=AdventureWorks;TrustedConnection=Yes';
my $dbh = DBI->connect("dbi:ODBC:$DSN") or die "$DBI::errstr\n";

my $sth = $dbh->prepare('select top 10 * from Sales.vSalesPerson')
or die "Couldn't prepare statement: " . $dbh->errstr;


while( @data = $sth->fetchrow_array())
foreach(@data) {
print "[$_]";
print "\n\n";



[268][][Stephen][Y][Jiang][][North American Sales Manager][238-555-0197][stephen][0][2427 Notre Dame Ave.][][Redmond][Washington][98052][Un
ited States][][][][677558.4653][.0000]

[275][][Michael][G][Blythe][][Sales Representative][257-555-0154][michael9@adven][1][8154 Via Mexico][][Detroit][Michigan][48226][United States][N
ortheast][North America][300000.0000][4557045.0459][1750406.4785]

[276][][Linda][C][Mitchell][][Sales Representative][883-555-0116][linda3@adventu][0][2487 Riverside Drive][][Nevada][Utah][84407][United States][Sou
thwest][North America][250000.0000][5200475.2313][1439156.0291]

[277][][Jillian][][Carson][][Sales Representative][517-555-0117][jillian0@advent][1][80 Sunview Terrace][][Duluth][Minnesota][55802][United States]
[Central][North America][250000.0000][3857163.6332][1997186.2037]

[278][][Garrett][R][Vargas][][Sales Representative][922-555-0165][garrett1@adven][0][10203 Acorn Avenue][][Calgary][Alberta][T2P 2G8][Canada][Cana
da][North America][250000.0000][1764938.9859][1620276.8966]

[279][][Tsvi][Michael][Reiter][][Sales Representative][664-555-0112][tsvi0@adven][1][8291 Crossbow Way][][Memphis][Tennessee][38103][United States
][Southeast][North America][300000.0000][2811012.7151][1849640.9418]

[280][][Pamela][O][Ansman-Wolfe][][Sales Representative][340-555-0193][pamela0@a][1][636 Vine Hill Way][][Portland][Oregon][97205][United Stat
es][Northwest][North America][250000.0000][.0000][1927059.1780]

[281][][Shu][K][Ito][][Sales Representative][330-555-0120][shu0@adventure-works.
com][2][5725 Glaze Drive][][San Francisco][California][94109][United States][Sou
thwest][North America][250000.0000][3018725.4858][2073505.9999]

[282][][JosΘ][Edvaldo][Saraiva][][Sales Representative][185-555-0169][josΘ1@adve][0][9100 Sheppard Avenue North][][Ottawa][Ontario][K4B 1T7][Cana
da][Canada][North America][250000.0000][3189356.2465][2038234.6549]

[283][][David][R][Campbell][][Sales Representative][740-555-0182][david8@adventu][0][2284 Azalea Avenue][][Bellevue][Washington][98004][United State
s][Northwest][North America][250000.0000][3587378.4257][1371635.3158]



David Latham said...

Oh Yeah - SQL Server 2008 needed some basic configuration.

1. Enable TC-IP ( 1433 ) connections using the Configuration Manager
2. Ensure the windows authenticated user executing the perl script is also permitted to access the database and whatever part of the database you are accessing. I just made my self an administrator and allows my username full access to MSSQL Server.
3. Make sure there are firewall holes.

Anyone else got any gotchas?

Chirag K. Shah said...

I am trying to connect to a SQL server. Tried your Perl program but no where closer to get it working. How can server be localhost when the server is located out of the system.


David Latham said...

So if the server is located out of the system (on for example) then the server=localhost could change to or even simply the ip address of the server will do.

my $DSN = 'driver={SQL Server};; database=AdventureWorks;TrustedConnection=Yes';

my $DSN = 'driver={SQL Server};Server=; database=AdventureWorks;TrustedConnection=Yes';

if your database server is (as it very well should be) on a different physical machine to the application and the name of this machine is or it's ip address is

Substitute your own information for your own network.

HoosierDaddy said...

Any thoughts on connecting a perl program running on a Linux or OS X machine without using ODBC?

I looked in CPAN but most of those packages are from back in the day with Microsoft SQL Server and Sybase were in bed together...a loooooong time ago.

David Latham said...

Unfortunately no. I think that ODBC is almost always required for connecting to Microsoft SQL from Non MS environments. It's kind of the purpose of ODBC.

It's been a long time since I worked on any of this so I am probably no longer the right person to ask.