SQL Server - strawberry perl and sql server

Asked By dba.999 on 28-Oct-09 11:19 AM
I need to connect to sql server from strawberry perl. Is anyone aware of
any free driver for this.

thanks.




Erland Sommarskog replied on 30-Oct-09 11:17 AM
(dba.9999@gmail.com) writes:

The best API to connect to SQL Server from Perl is Win32::SqlServer,
http://www.sommarskog.se/mssqlperl/index.html. But keep in mind that
it is designed for SQL Server only, and should not use it you need to
be portable.

I will have to throw in the disclaimer that I do not know if it runs
with Strawberry Perl. Someone mailed me as he he tried to build it on
Strawberry Perl, and had some problems. When I researched it, I found
that Strawberry Perl assumes DMAKE and GNU-CC. I have only built
Win32::SqlServer with Visual C++, and playing with GNU-CC is not really
in my realm. The other person ended up using ActivePerl instead.

There is a binary distribution, which you can try, but I would expect it
to be compatible with something built with a different compiler.

Other Perl alternatives are Win32::ODBC and the DBD::ADO, and I think
there is a DBD for FreeTDS as well, see
http://www.sommarskog.se/mssqlperl/alternatives.html for what I have
listed there.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
dba.999 replied on 28-Oct-09 07:25 PM
In article <Xns9CB2AA23EFA6DYazorman@127.0.0.1>, Erland Sommarskog says...


OK I installed Win32::SQLServer

my connection is failing:

use Win32::SqlServer ;
my $server = 'OWNER_PC\SQLEXPRESS' ;
my $user = 'sa' ;
my $pw = '<MYPASSWORD>' ;
my $database = 'master' ;
my $provider = 'SQLNCLI10' ;
$sqlsrv = Win32::SqlServer->sql_init($server, $user, $pw, $database, $provider);


this gives this error

SQL Server message 53, Severity 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [53].
Message 08001 from 'Microsoft SQL Server Native Client 10.0', Severity: 16
A network-related or instance-specific error has occurred while establishing a
connection to SQL Server. Server is not found or not accessible. Check if
instance name is correct and if SQL Server is configured to allow remote
connections. For more information see SQL Server Books Online.
Message HYT00 from 'Microsoft SQL Server Native Client 10.0', Severity: 16
Login timeout expired
Terminating on fatal error at test.pl line 7

The error is same when I change $provider to SQLOLEDB

thanks a lot.
Erland Sommarskog replied on 30-Oct-09 11:17 AM
(dba.9999@gmail.com) writes:

That's a generic connection error meaning that you were not able to find
the SQL Server instance at all. I would expect that you would get the
same error message if you tried

sqlcmd -S OWNER_PC\SQLEXPRESS -U sa -P password

from a command-line window.


Is SQL Server running at all?
Is it running on the same machine as the Perl script?
If it runs on a different machine, make sure that remote connections are
enabled; by default it it is not for SQL Express. Also make sure that
the SQL Browser service is running and that there are no firewalls in the
way.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
dba.999 replied on 29-Oct-09 05:03 AM
In article <Xns9CB36285BA9DFYazorman@127.0.0.1>, Erland Sommarskog says...


No, sqlcmd works fine.


yes, otherwise sqlcmd would also report error.


yes, both on my laptop.
Erland Sommarskog replied on 30-Oct-09 11:17 AM
(dba.9999@gmail.com) writes:

The error here is that sql_init is a global method, and it should be called
as Win32::SqlServer::sql_init. Or just simply sql_init, as it exported by
default. When you use -> instead, Win32::SqlServer will try to connect
to the server Win32::SqlServer which of course will not succeed. (But I
should probably make a change to permit this, to avoid that people fall
into this trap.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
S3v3n11 replied to dba.999 on 20-Jan-10 12:57 PM
Did you find a solution for this problem?
Erland Sommarskog replied to S3v3n11 on 20-Jan-10 07:03 PM
S3v3n11 (S3v3n11@) writes:

The error is that the call should be:

$sqlsrv = Win32::SqlServer::sql_init($server, $user, $pw, $database,
$provider);

I guess I should put in some warning if $server eq "Win32::SqlServer"
to avoid people wasting time on this error which is easy to make.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx