Use DBI for pc-name\SQLEXPRESS

wcyoong has asked for the wisdom of the Perl Monks concerning the following question:

Hi Perl Monks!

My colleague created 1 table named <pc-name>\EXPRESS:Yield. I want to use DBI to connect it but somehow it couldn't >_<

i have created new connection in ODBC.ini as below:

 
[PC-NAME]
Description=ODBC Data Sources
Driver=FreeTDS
Port=1433
Server=PC-NAME\SQLEXPRESS
TDS_Version=7.1
Trace=No
[download]

My code to connect to the SQLEXPRESS:

$dbh=DBI->connect("dbi:MTODBC:PC-NAME:Yield","<usr>","<pw>") || die "$
+DBI::errstr\n";
[download]

Error message as below:

checkErrorODBC3 E SQLException: SQLState=[08001] Code=[0] Msg=[[mtodbc
+]: Connecting: [unixODBC][FreeTDS][SQL Server]Unable to connect to da
+ta source]
DBI connect('PC-name:Yield','usr',...) failed: (mtodbc): Connecting: [
+unixODBC][FreeTDS][SQL Server]Unable to connect to data source (SQLST
+ATE:08001) (CODE:0) (SEVERITY:SQLException)
DBD: [dbd_db_login6/checkOutConnectionW(login)] RetCode=[-1] at ../dat
+a_extraction_sqlite.pl line 615
(mtodbc): Connecting: [unixODBC][FreeTDS][SQL Server]Unable to connect
+ to data source (SQLSTATE:08001) (CODE:0) (SEVERITY:SQLException)
DBD: [dbd_db_login6/checkOutConnectionW(login)] RetCode=[-1]
[download]

Can any1 help me to improve my knowledge on this? im able to connect his database in SSMS but why my unix cant~~ >_<

Appreciate ur feedback!

Comment on Use DBI for pc-name\SQLEXPRESS Select or Download Code

Replies are listed 'Best First'.
Re: Use DBI for pc-name\SQLEXPRESS by FloydATC (Deacon) on Aug 06, 2018 at 10:40 UTC
This may seem silly but have you configured your SQL Server Express to accept remote connections? Unlike the regular SQL Server, by default Express will only accept local connections. read more The username/password must be for an SQL user account (with sufficient privileges), I could never get it to work with an AD account. I can't see what yours is so I just wanted to mention it. Each SQL Server instance can be configured to support one, the other, or both ("mixed mode"). -- FloydATC I got 99 problems, most of them have to do with printers.	[reply]
Re^2: Use DBI for pc-name\SQLEXPRESS by wcyoong (Initiate) on Aug 07, 2018 at 07:37 UTC
1. Thanks~ im able to connect his database in SQL Server Studio Management in my computer, so i believe the SQL Server Express can accept remote connection. Am i right?	[reply]
Re^3: Use DBI for pc-name\SQLEXPRESS by FloydATC (Deacon) on Aug 07, 2018 at 08:32 UTC
Yes, if you can remotely connect using SQL Management Studio, DBI should be able to connect as well. -- FloydATC I got 99 problems, most of them have to do with printers.	[reply]
Re: Use DBI for pc-name\SQLEXPRESS by anonymized user 468275 (Curate) on Aug 06, 2018 at 09:35 UTC
The syntax is:- `use DBI; $dbh = DBI->connect('dbi:ODBC:DSN=mydsn', 'user', 'password');` [download] See DBD::ODBC One world, one people	[reply] [d/l]
Re^2: Use DBI for pc-name\SQLEXPRESS by FloydATC (Deacon) on Aug 06, 2018 at 11:20 UTC
I don't use "DSN=" in my working code, just the exact DSN name as it appears between brackets in my odbc.ini Works fine against several different instances with different versions, default and named instances both Express, Standard and Enterprise. `my $dbh = DBI->connect("dbi:ODBC:$instance", "########", "########");` [download] For the sake of completeness, here are some example entries (anonymized) from odbc.ini: `[SRV2] Description = A default instance and port specified because server isn +'t running a "browser" listener. Driver = FreeTDS Server = n.n.n.n Port = 1433 Database = whatever TDS_Version = 9.0 try server login = yes [SRV1-INST1] Description = A named instance, no port specified because so it will u +se the "browser" listening port Driver = FreeTDS Server = n.n.n.n\INST1 Database = whatever TDS_Version = 9.0 try server login = yes` [download] -- FloydATC I got 99 problems, most of them have to do with printers.	[reply] [d/l] [select]
Re: Use DBI for pc-name\SQLEXPRESS by wcyoong (Initiate) on Oct 04, 2018 at 08:37 UTC
Just to close this loop: End up i am able to using code above to connect SQL/EXPRESS which is: `$dbh=DBI->connect("dbi:MTODBC:PC-NAME:Yield","<usr>","<pw>") \|\| die "$ +DBI::errstr\n";` [download] The problem i met is ODBC.ini setup incorrectly especially the port, we have to setup properly, but how? here's some code to help: 1) tsql -LH <SERVER_NAME> => use this to get port and server name information. `output: ServerName ABC InstanceName SQLEXPRESS IsClustered No Version 12.0.xxxxxxx tcp xxxxx np \\ABC\pipe\MSSQL$SQLEXPRESS\sql\query` [download] 2) In SSMS (SQL query), type command like "select distinct local_net_address, local_tcp_port from sys.dm_exec_connections where local_net_address is not null" this shows the port and local net address. Thanks and Cheers!!!!! :)	[reply] [d/l] [select]