Support for named instances in MS SQL

[RaoKrishna]

This might be not an issue but I need help with connecting to SQL server. I am trying to create a Phoenix application with Ecto and MS SQL database. I want to connect to a custom named instance in SQL server. Using TDS driver, we can mention it in config by using the variable "instance". Is there an equivalent in this driver. Please let me know. This is the config I am using:

config :sqlapp, Sqlapp.Repo, adapter: MssqlEcto, # Ecto.Adapters.SQL, database: "mydb", username: "username", password: "pwd", hostname: "devdb"

instance: "instancename"

Your Environment

• MssqlEcto version: 0.1
• Ecto version: 2.0
• Elixir version: 1.4.2
• Erlang version:
• Microsoft SQL Server version: 2014
• Operating System and version: Windows 2007
• Link to your project:

[javimolla]

I think that you have to specify the instance alongside the hostname, like in this example

So, for example, if you want to connect to an instance called SQLCustom in localhost, you have to write: hostname: "localhost\SQLCustom"

[RaoKrishna]

Thanks for the suggestion. I tried doing that but I still cannot connect. I get the following error:

_Compiling 12 files (.ex) Generated sqlapp app [info] Running Sqlapp.Endpoint with Cowboy using http://localhost:4000 [error] Mssqlex.Protocol (#PID<0.540.0>) failed to connect: ** (Mssqlex.Error) [ Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Connection to database failed. | ODBC_CODE IM002 | SQL_SERVERCODE 0

When I try to connect to the SQL server via the ODBC driver in erlang shell, I need to add a System DSN entry in ODBC Data Source Administrator. Do I need to add similar entry for this adapter to work?

[jbachhardie]

I haven't had the opportunity to test this on Windows, but I think the driver name is different.

Try setting odbc_driver: "{SQL Server Native Client 11.0}" in config, that's the driver that comes with SQL Server 2014 in Windows I believe.

I'll try to install everything on Windows to replicate this tomorrow.

[RaoKrishna]

Adding this new line in the config file worked! Thanks for your help.

[RaoKrishna]

The following configuration for named instances worked for the previous version (mssql_Ecto : 0.1.2, mssqlex : 0.6.4)

config :sqlapp, Sqlapp.Repo, adapter: MssqlEcto, username: "user", password: "password", database: "devdb", hostname: "servername\\instancename", port: "1433", odbc_driver: "{SQL Server Native Client 11.0}"}

However, I am unable to connect to the database after updating the driver to 0.2.0. I get the following error:

_** (EXIT) time out (db_connection) lib/db_connection/poolboy.ex:117: DBConnection.Poolboy.check out/3 (db_connection) lib/db_connection.ex:920: DBConnection.checkout/2 (db_connection) lib/db_connection.ex:742: DBConnection.run/3 (db_connection) lib/db_connection.ex:1133: DBConnection.run_meter/3 (db_connection) lib/db_connection.ex:584: DBConnection.prepare_execute/4 lib/mssql_ecto/connection.ex:58: MssqlEcto.Connection.execute/4 (ecto) lib/ecto/adapters/sql.ex:243: Ecto.Adapters.SQL.sqlcall/6 (ecto) lib/ecto/adapters/sql.ex:193: Ecto.Adapters.SQL.query!/5

Can you please look into the issue?

[shdblowers]

Hi @RaoKrishna , I'll take a look into this issue and the changes I did here: https://github.com/findmypast-oss/mssqlex/commit/81f0bbabe4a5fc6281d76234650c1e15bbcefaaf

[shdblowers]

Hi @RaoKrishna, I've just done a release of mssqlex and mssql_ecto which should fix this bug.

From this release you can now set the :instance_name option to set the named instance you want to use, as seen in the README.

Please let me know if this fixes your issue.

[RaoKrishna]

Hi @shdblowers,

Thanks for the new release. It solved this issue with named instances.

However, there is a minor issue. I had to remove the port from the config to connect to the database. It was not a problem for me as I was connecting to the default port. I am not sure if it would throw an error while connecting to custom port.

Since the new release solves the problem of named instances, I am closing this issue. Thanks for your help.