1

I'm new to Perl. I'm trying to connect to MSSQL 2016 from Perl. Below is the code I have written and it is working fine in my personal account .

Where as in my organization account MS-MSQL server is running in different machine and I want to connect to it .

Note: running this in Linux machine

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

use DBI;

my $user = 'SA';
my $password = '';


my $dbh = DBI->connect('dbi:ODBC:Driver={/opt/microsoft....};Server=localhost;Database=testsb;UID=$user;PWD=$password')
    or die "Can't connect ";

My question is where will I get this driver path since server is running in different machine. Please help ..

TLP
  • 66,756
  • 10
  • 92
  • 149
Prakruthi Alosri
  • 27
  • 3
  • Have you looked at this question https://stackoverflow.com/q/4905624/725418 – TLP Jan 12 '21 at 17:44
  • There are actually a lot of relevant-looking matches if I google "mssql perl dbi", have you checked those? – TLP Jan 12 '21 at 17:55
  • Also, what is not working? Not answering debugging questions is not a good idea if you want to get answers. – TLP Jan 12 '21 at 18:43
  • Yes... But I'm not clear.. should I instal MSSQL ODBC server in my machine to get the driver path ? Or should I ask DBA to give me the driver path from where ms SQL server SQL server is running? – Prakruthi Alosri Jan 12 '21 at 18:50
  • Everything is working in my local machine.. in my organization machine I don't have that driver path /opt.... Just wanted to check where cn u get tht.. is it something present in the machine where SQL is running? – Prakruthi Alosri Jan 12 '21 at 19:15

2 Answers2

0

You don't change the path to the driver. That path is local to the machine where your Perl script is running. It's the SQL Server that's remote, so you need to change the Server=localhost section to use the hostname (or better, FQDN) to the remote server.

Also, maybe it's a Linux thing, but in Windows I just have to specify the driver like DBI:ODBC:Driver={SQL Server}, so there's no path involved at all.

jimtut
  • 2,366
  • 2
  • 16
  • 37
  • it's not working.. it's telling can't open lib SQL Server :file not found . (SQL-01000) – Prakruthi Alosri Jan 12 '21 at 18:39
  • What platform are you on? The `/opt` path implies a Linux or macOS machine, but if so, how did you ever get a local MS SQL Server running on one of those? You said it worked locally? Or were you running MySQL locally? I assume there is a Linux OLDB driver for SQL Server, so install that and set that in the path. But, then from your DBA, you need hostname/FQDN, port, DB name, username, and password. Put all those things in your connection string and it will work! – jimtut Jan 13 '21 at 03:37
  • I'm using liux platform.. I mean to say it worked fine on my personal laptop.. in my personal laptop I has SQL server installed along with odbc driver... But in my organization setup.. Im not able to get this path – Prakruthi Alosri Jan 13 '21 at 08:47
  • Install the SQL Server ODBC driver for Linux: https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server. Then get the other conn-string parameters from your DBA. – jimtut Jan 13 '21 at 12:45
  • Prakruthi, did you ever get this working? – jimtut Feb 03 '21 at 04:55
  • Yes... We have install microsoft odbc driver in the machine where we are running Perl script – Prakruthi Alosri Feb 08 '21 at 16:07
  • OK, great! If this helped, please upvote or "accept" this answer. – jimtut Feb 08 '21 at 16:24
0

There are two components that I think you've confused.

  1. There's a database server that's running on a remote machine.

  2. You want to create a database client running on some other machine.

Anything dealing with the client needs to be on that machine, including the DBD::ODBC driver. The driver path is how the client machine decides with locally installed code to use. That path is for something local. However, I haven't used DBD::ODBC in awhile, so I don't know what details you need.

When you want to connect to the server, you fill in the DSN (...Server:...) with the details for the server. The example you've shown uses localhost for the server, which is not the server you want.

brian d foy
  • 129,424
  • 31
  • 207
  • 592