SQL Native Connection with SQL Express 2008 R2

Archived Topics
1

Hi,

I am trying to make a database connection to SQL Express 2008 R2 running on Windows 2008 Server 64 Bit but the connection is failing when I try to connect. I check connecting remotely with the SQL Server Studio and DSN and I don’t have any issues. Is there a known issue using a Native Connection with FSQL and SQL Express 2008 R2. I used the native connection all the time with SQL 2008 Server. JIC I am using Hostname\SQLExpress when defining the host.

Thanks

2

You have to use the appropriate translator for SQL Server in FactorySQL. If you are using Windows auth you can use that translator. If not we may have to make a XML change.

3

Hi Travis,

Can you elaborate more on what I need to change on the XML file. I cannot used the ODBC connection because I get error msg on the the store procedure parameter saying that they are missing, but I can test against my SQL 2008 Server and it execute fine. But the project is going to run against a SQL 2008 Express R2.

Thanks

4

Take a look at this article http://www.inductiveautomation.com/news/item/227/5 for more information on the translators. Look at the section FactorySQL: How to Connect to Microsoft SQL Server. Let us know if you have any more questions.

5

Travis,

Can I use SQL Authentication with SQL Express using the native connection ?

I don't understand why we cannot just provide the host including the instance name, on the host.

Thanks

6

You can connect to SQL Server using SQL authentication and instance name. That combination will not work out of the box in FactorySQL since we are missing a translator. However, you can get it to work by altering the SQLServer.XML. Here are the steps:

  1. Edit the SQLServer.xml in C:\Program Files\Inductive Automation\FactorySQL\database, remove the “,{port}” (excluding the quotes of course) in the line Data Source={server},{port}; Database… and Save.

  2. Use the following parameters:

Name: any name
Connection Type: Native Connection
Driver Type: SQL Server
Translator: SQL Server
Host: IPAddress\SQLEXPRESS
Port: Leave Blank
Database: db name
Extra Connection Parameters: leave blank
Username: SQL auth user
Password: SQL auth password

That combination should work. The {port} in the connection path is what is limiting you.

7

Thanks Travis, it works. Like always Inductive Automation always have great support, keep the good work guys.

Thanks