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

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.

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

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.

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

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 <ConnectionString>Data Source={server},{port}; Database....</ConnnectionString> 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.

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

Thanks