Writing from EPIC to SQL Server database

groov EPIC
#1

I am trying to write 3 tags (int, string, and float) from an EPIC PR1 controller to a SQL Server database and am having no luck.
These are triggered writes that happen when an integer tag goes true, and they only happen every hour or so, and just need to write one record. I have been trying to use OptoDataLink but to no avail. Can’t get the connection to happen. We know the server, database name, table, and login and password are all correct as we can login in to the database with them, but it won’t work with OptoDataLink.
We are using PAC Project 10.4.
We can ping the EPIC from the computer that the server resides on, so we know that there is network connectivity. My application (waste water pH control) on the EPIC is running great - we just can’t get the data to log to the database.

One of the errors we are getting is:
“Could not connect to database [ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error
ERROR [01000][Microsoft][ODBC SQL Server Driver][DBNETLIB] ConnectionOpen(SECDoClientHandshake()).”

There are other PC’s on this subnet that are using OptoDataLink to write from PAC R1’s to a different database on the same SQL Server machine. We looked at how they are setup and tried that as well. No luck.

Is there an easier way to do this? Some have suggested Node Red, but you have to find a node that does this, and I have not used Node Red before, so it needs to be pretty straightforward.

thanks for any help!

  • Kurt
#2

It will take me 2-3 weeks to set up and get up to speed on OptoDataLink again… or you can spin up Node-RED on the EPIC, install the MSSQL node, look at the example code in the Forums here and be up and running in 10-15 minutes.

Either way, the fact that other deices work, but this EPIC throws a ‘could not connect’ error makes it sound like a networking / DNS configuration issue so either way might be harder than it should be.

Can you ping the MSSQL computer from the EPIC? Im guessing not, but its worth a try from the network tools in groov Manage.

#3

On the PC you are running OptoDataLink on, you may try installing newer SQL OLE DB drivers and see if you have better luck:

I recommend using Node-Red for this type of task for any future development though.

1 Like
#4

Thanks Ben - I will look into the Node Red. Where is the MSSQL node? Is that one I have to load?

Thanks

Kurt

#5

For installing the MSSQL node:

For using the Node:
https://forums.opto22.com/search?q=mssql

@torchard has a video on the subject here:

#6

Thank you Ben! For a novice at Node Red, this looks kind of daunting

I will keep at it.

Kurt

#7

Also, while I have extension experience programming in PAC Control and in ladder, I have no experience with java script, so I need a node for writing tags to SQL Server that does not involve me writing javascript code.

Best regards

Kurt

#8

So we ended up getting OptoDataLink to work. It ended up being some network and ODBC driver issues that the client had to fix. I’ve asked them for a summary of what they did and I will post if here. We are successfully logging triggered data writes so SQL server from the EPIC.
Thanks for all the suggestions everyone. - Kurt

#9

BTW, the SQL Server database resides on a Windows Server machine. Both are 2016 editions, however in OptoDataLink we had to select the the SQL 2000 version.

#10

Given your comments about JavaScript, I am glad you got the network issues sorted and DataLink up and running.
Thanks for reporting back and letting us know what you found.

#11

Quick summary of issues we ran into when setting the Datalink. Hopefully this will be of use to others. The SQL Server application is running on a Windows Server machine that runs 24/7. The OptoDataLink setup is running on a Windows 10 Enterprise machine (they call it their Utility PC) on the same subnet as the EPIC controller and the Windows Server machine - and it also runs 24/7.

In OptoDataLink the Database drop-down menu did not include an option for SQL Server 2016, which we are using. We tried utilizing the most recent option “SQL Server 2012 – 2014” but we couldn’t establish a connection to the database. We ended up having to use the “SQL Server 2000” option. This was also true for some other earlier installations within the company that are PAC R series controller based.
We were unable to establish a connection to the database when using an Active Directory service account for Windows authentication. I verified the credentials of the user by logging into SQL Server Management Studio as that user and confirmed it had full access to the database. We had also previously used a different application to write to the same database and tables with that same service account. We tried both domain\username, and username, but neither would take. We could ping the EPIC controller from the Windows Server machine, and from the “Utility PC” mentioned above, and in both directions each PC could see the other. I checked and confirmed the spelling and password multiple times and cannot explain why it wouldn’t connect. Only when I created a local SQL server account for SQL authentication and used that was I finally able to connect from OptoDataLink to the SQL Server app. Now it works fine and has been running flawlessly. This last paragraph is from the client’s IT guy.

1 Like