Connecting to SQL Server using Perl using DBI?

Question

I am trying to connect to SQL server using Perl DBI module, I have tried all the connection string format still Perl is throwing invalid connection string error.

I have already tried code snippet available on perl monk website.

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

# Set up variables for the connection
my $server_name = '00.120.124.1;3181';

my $database_name = 'abcd';
my $database_user = 'kkkk';
my $database_pass = 'password';

my $DSN = 'driver={SQL Server};server=$server_name;da
+tabase=$database_name;uid=$database_user;pwd=$database_pass;'; 
my $dbh = DBI->connect("DBI:ODBC:$DSN") || die "Couldn't open database
+: $DBI::errstr\n";

Expected result is to connect to Database.

failed: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (SQL-01000) [state was 01000 now 01S00] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute (SQL-01S00) at perl.pl line 16. Couldn't open database +: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (SQL-08001) [state was 08001 now 01000] Invalid connection string attribute (SQL-01S00)

Do you have a literal newline and + in the DSN string your real code or is that just some strange copy & paste error? — Shawn
– Shawn, Commented Aug 19, 2019 at 20:06
failed: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (SQL-01000) [state was 01000 now 01S00] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute (SQL-01S00) at perl.pl line 16. Couldn't open database +: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (SQL-08001) [state was 08001 now 01000] Invalid connection string attribute (SQL-01S00) — Abhijeet Khandelwal
– Abhijeet Khandelwal, Commented Aug 19, 2019 at 20:25
That should go in your post, not a comment, so it has a chance of being readable. You should also include the value of the DSN string as it's being passed to DBI->connect(). — Shawn
– Shawn, Commented Aug 19, 2019 at 20:42

mob · Accepted Answer · 2019-08-19 21:53:11Z

2

The lines of your post that begin with +

+tabase=$database_name;uid=$database_user;pwd=$database_pass;'; 

+: $DBI::errstr\n";

were incorrectly copied and pasted from perlmonks.org. The leading + sign is a convention that indicates a long line was split. You should delete the + and join the line to the previous line, so that your code will read

my $DSN = 'driver={SQL Server};server=$server_name;database=$database_name;uid=$database_user;pwd=$database_pass;'; 
my $dbh = DBI->connect("DBI:ODBC:$DSN") || die "Couldn't open database: $DBI::errstr\n";

answered Aug 19, 2019 at 21:53

mob

119k18 gold badges159 silver badges291 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dave Cross · Accepted Answer · 2019-08-20 08:49:57Z

Your error message contains the following:

SQL Server does not exist or access denied

Your set-up code contains the following:

my $server_name = '00.120.124.1;3181';

'00.120.124.1;3181' is not a valid server name or IP address. You should correct the IP address section (it can't start with '00') and remove the port into a separate parameter.

You also have the username and password as part of your DSN. I don't know if DBD::ODBC supports that usage (it's not mentioned in the documentation) but it's more traditional to have those as separate parameters to the connect() call.

All in all, I think you want something more like this:

my $server_name = '00.120.124.1'; # But this needs to be corrected
my $server_port = 3181;

my $database_name = 'abcd';
my $database_user = 'kkkk';
my $database_pass = 'password';

my $DSN = "driver={SQL Server};server=$server_name;port=$server_port;database=$database_name"; 

my $dbh = DBI->connect("DBI:ODBC:$DSN", $database_user, $database_pass)
  || die "Couldn't open database: $DBI::errstr\n";

Also note that I've changed the quote characters used to create your $DSN variable from single quotes to double quotes. Variables are not expanded in signel quotes, so you weren't getting the values of $server_name, etc. in your DSN.

lordadmira · Accepted Answer · 2019-08-25 22:36:33Z

0

The better way to connect is with the DBD::Sybase module. The name wouldn't suggest it but SQL Server takes its lineage from Sybase. That way you can avoid ODBC. http://metacpan.org/pod/DBD::Sybase#Using-DBD::Sybase-with-MS-SQL

answered Aug 25, 2019 at 22:36

lordadmira

1,8677 silver badges14 bronze badges

Collectives™ on Stack Overflow

Connecting to SQL Server using Perl using DBI?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related