It is currently Sun Aug 31, 2014 11:35 pm




Post new topic Reply to topic  [ 7 posts ] 
 SQL Native Connection with SQL Express 2008 R2 
Author Message
Captain
Captain

Joined: Wed Aug 15, 2007 5:14 pm
Posts: 81
Post SQL Native Connection with SQL Express 2008 R2
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

_________________
Julio Delgado
SSA Sterilization Systems


Tue Jun 29, 2010 11:42 am
Profile
Moderator
Moderator

Joined: Sun Apr 02, 2006 2:46 pm
Posts: 1982
Location: Sacramento, CA
Post Re: SQL Native Connection with SQL Express 2008 R2
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.

_________________
Travis Cox
Inductive Automation
Technical Support Rep.


Tue Jun 29, 2010 12:07 pm
Profile WWW
Captain
Captain

Joined: Wed Aug 15, 2007 5:14 pm
Posts: 81
Post Re: SQL Native Connection with SQL Express 2008 R2
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

Travis.Cox wrote:
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.

_________________
Julio Delgado
SSA Sterilization Systems


Tue Jun 29, 2010 1:40 pm
Profile
Moderator
Moderator

Joined: Sun Apr 02, 2006 2:46 pm
Posts: 1982
Location: Sacramento, CA
Post Re: SQL Native Connection with SQL Express 2008 R2
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 Cox
Inductive Automation
Technical Support Rep.


Tue Jun 29, 2010 2:04 pm
Profile WWW
Captain
Captain

Joined: Wed Aug 15, 2007 5:14 pm
Posts: 81
Post Re: SQL Native Connection with SQL Express 2008 R2
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

Travis.Cox wrote:
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.

_________________
Julio Delgado
SSA Sterilization Systems


Tue Jun 29, 2010 2:21 pm
Profile
Moderator
Moderator

Joined: Sun Apr 02, 2006 2:46 pm
Posts: 1982
Location: Sacramento, CA
Post Re: SQL Native Connection with SQL Express 2008 R2
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.

_________________
Travis Cox
Inductive Automation
Technical Support Rep.


Tue Jun 29, 2010 3:24 pm
Profile WWW
Captain
Captain

Joined: Wed Aug 15, 2007 5:14 pm
Posts: 81
Post Re: SQL Native Connection with SQL Express 2008 R2
Thanks Travis, it works. Like always Inductive Automation always have great support, keep the good work guys.

Thanks

_________________
Julio Delgado
SSA Sterilization Systems


Wed Jun 30, 2010 5:05 am
Profile
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to: