Forcing Tags to WonderWare SQL

[macgioo2]

Hi Guys,

I'm trying to do something similar but I'm way back at the beginning. Is there a written procedure anywhere.

I have wonderware version 10.1 and SQLExpress2008R2 mounted on the same pc.

I have very little idea how to set it up!

Any help would be great.

 

[jshiepe]

Do you have an InTouch application? Have you tested it and does it function as intended? Do you have tags defined in that application for the data points you want to collect in Historian? Is this a standalone or managed application (meaning you are using Application Server)? Provide as much information as you are willing to share about your system and your goals.

 

[macgioo2]

Well I have created an InTouch application on my local PC (Windows7) and I have mounted the SQL Server on the same PC. For now it is a standalone application. I have created several tags in intouch which I want to log. I am familiar with the bind list and table template concepts presented in the help files.

The ultimate goal is for example to have machine statistics pass/fail etc written to a database on the SQL server and to be able to define some queries and place them behind buttons on the HMI that will allow the operator to run them.

The most important aspect of this project is to have data stored on a remote server and not within the application as I'm sure this is an alternative.

Need anything else?

 

[jshiepe]

You haven't mentioned if you're using the Historian product (InSQL), which is Wonderware's data collection engine. If you are, you have the option of using that depending on what data you want to collect, how often it changes, if it needs to be trended, etc. There are a lot of options available to you if using that product.

If you're planning on just using straight SQL statements in your app, then it sounds like you've started familiarizing yourself with what's needed to do that. In the previous posts, Chris could have done this as well, and was actually heading down that road originally, but since he had the Historian set up and had imported tags from his application, it was much easier for him to take that route.

With the direct SQL route, you have just about all the functionailty you need for what you've defined so far. You can insert the data into a table using some trigger in the app and then retrieve it and populate a grid or some other display. When you get to that point and have some specific questions about using the SQL statements, maybe I can help you more. At this point, it sounds like you still have a lot of detail to work out.

 

[macgioo2]

Hi,

No I'm not using Historian, I do have a specific question that perhaps you can help me with. I run the script:

SQLConnect(ConnectionID, "Provider=SQLOLEDB.1;User ID=MODAUTO\o_mcbride; Password=;
Initial Catalog=SQL_Test;Data Source=MAC509-PC\SQLEXPRESS;");

I use the validate command in wonderware to check the syntax, all ok.

But how do I confirm I have made a connection, is there some status flag I can access?

 

[jshiepe]

It would be beneficial if you would change your statement to:

ResultCode = SQLConnect(ConnectionID, "Provider=SQLOLEDB.1;User ID=MODAUTO\o_mcbride; Password=;
Initial Catalog=SQL_Test;Data Source=MAC509-PC\SQLEXPRESS;");

Immediately after that, you should add the following statement:

ErrorMessage = SQLErrorMsg (ResultCode);
IF ResultCode <> 0 THEN
LogMessage( "SQLConnect problem, ResultCode = " + Text ( ResultCode, " # " ) + " - " + ErrorMessage);
ENDIF;

You will need to create an integer tag (ResultCode) and a message tag (ErrorMessage). What this will do will post any errors from the connection process to the WW logger. You should also use this for any SQL statements you run in your app to help you flag errors and troubleshoot. Basically, if your SQLConnect is successful, you will not see any error messages and an integer will be assigned to your ConnectionID tag.

Another tip: If using SQLSelect at any point, ALWAYS make sure there is a corresponding SQLEnd for every SQLSelect. If you don't, you will leak memory like a sieve.

 

[macgioo2]

when you say WWLogger do you mean the Archestra logger as I'm having serious issues with this.

I can't run window viewer without having the logger disabled in services!

btw thanks for your help

 

[jshiepe]

Yes it is the Archestra logger. Not sure why you would have issues running Windowviewer and the logger at the same time. You might have an installation issue or Windows security setting problem. Unfortunately, I have to log off for the day, but if you have access to WW tech support through either your distributor or through the web, you might want to start there.

 

[chrisj91]

Hi macgioo2,

Just noticed the new posts on this thread. I don't see how Archestra Logger should impact upon running WindowViewer, unless any of the points made by Jeff are correct or if you are running any conflicting software?

For example, I have used FactoryTalk a lot, and it appears having FactoryTalk and WonderWare installed on the same server does not make them play nicely.

Hope you find a solution to your problem, Jeff is a very helpful guy

 

[macgioo2]

Hi Guys,

Ok for sure I have had some issues over the last couple of days, but I have worked around it and here is the config of the system as it stands.

SQL Express is hosted on my Win7 pc, with Windows and SQL authentication enabled and I can log onto it using a local SQL account I created.

The Intouch application is running on my VMWare WinXP machine mounted on the same PC. I have set up an ODBC data source "Native Client" on this and tested the connection with the SQL server successfully.

I run the the script -


SQLConnect(ConnectionID, "Provider=SQLOLEDB.1;User ID=o_mcbride; Password=Control3;
Initial Catalog=SQL_Test;Data Source=MAC509-PC\SQLEXPRESS;");
ErrorMessage = SQLErrorMsg (ResultCode);


IF ResultCode <> 0 THEN
LogMessage( "SQLConnect problem, ResultCode = " + Text ( ResultCode, " # " ) + " - " + ErrorMessage);
ENDIF;


-on start up where ConnectionID is defined as a memory integer and when I check the logger I get the errors shown in the attached file.

Can any of you help here, I can't be this difficult!!!

 

[jshiepe]

First, you need to change your statement to:

ResultCode = SQLConnect.......

This will return the result code and may be more helpful than the error you are seeing. Without this, ResultCode is not being changed from it's default value.

Second, if you are connecting with SQLOLEDB, you do not need to define an ODBC data source. However, you do need to make sure that the user you have in your statement, "o_mcbride", is a configured user in SQLExpress and that the database, or catalog, you are trying to connect to is assigned to that user as an accessible database. You would do this through SQL Manager.

 

[jshiepe]

Just took another quick look at your SQLConnect statement and noticed the data source as:

Data Source=MAC509-PC\SQLEXPRESS

Unless the '\SQLEXPRESS' is part of the machine name, you do not need it. I'm assuming the complete machine name is 'MAC509-PC', which is all you need. Sorry I didn't notice that earlier.

Hope this helps.

Jeff

 

[macgioo2]

OK I have done this and the result code is returned as -1 after it fails to connect. what does this tell me?

 

[macgioo2]

Actually I found the result code list in the help of wonderware but still I have problems the logger has reported


7884 20/04/2011 15:42:59 1212 952 Info WWSQL Error: Connection failed Provider=SQLOLEDB.1;User ID=o_mcbride; Password=Control3;
Initial Catalog=SQL_Test;Data
7885 20/04/2011 15:42:59 1212 952 Info WWSQL Error: Connection failed Source=MAC509-PC;
7886 20/04/2011 15:42:59 1212 952 Info WWSQL -1: Microsoft OLE DB Provider for SQL Server: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
7887 20/04/2011 15:42:59 1212 952 Info VIEW Loading Script DLL: WWSCRIPT.DLL
7888 20/04/2011 15:42:59 1212 952 Info WWSCRIPT 10.1.200
7889 20/04/2011 15:42:59 1212 952 Info WWSCRIPT Message: SQLConnect problem, ResultCode = -1 - Microsoft OLE DB Provider for SQL Server: [DBNETLIB][ConnectionOpen (Connect()).]SQL Serve
7890 20/04/2011 15:42:59 1212 952 Info UNKNOWN WNAL - using "AlarmLst.dll"
7891 20/04/2011 15:42:59 1212 952 Info UNKNOWN WNAL - Loaded AlarmLst DLL "AlarmLst.dll" version "10.1"
7892 20/04/2011 15:43:00 2152 3192 Info AlarmMgr Connected to Provider '\intouch'
7893 20/04/2011 15:43:00 2152 3192 Info AlarmMgr Provider '\intouch' is disconnected
7894 20/04/2011 15:43:00 2152 3192 Info AlarmMgr Connected to Provider '\intouch'
7895 20/04/2011 15:43:00 3072 732 Info WC WC shutting down...
7896 20/04/2011 15:43:00 1212 952 Info INTSPT Node "localhost" connected
7897 20/04/2011 15:43:20 1212 952 Info INTSPT Node "192.168.0.186" disconnected
7898 20/04/2011 15:43:20 1212 952 Info INTSPT Node "192.168.0.186" disconnected


Anyone help?

 

[jshiepe]

As a test, try creating an ODBC connection to the database with the same parameters you use in your SQLConnect() statement.

Also, have you checked to make sure that o_mcbride has the necessary rights to the SQL_Test database? Are you able to log into SQL Server Management Studio with this user and password and access the SQL_Test database that way?