Search code examples
stored-procedureshistorianproficy

Executing a SQL Server stored procedure from a historian calculation

Using Historian 5.5 and SQL Server 2012.

I have a stored procedure in SQL Server called perfEng_RWtopits and I want to call this procedure from within a calculation tag in Historian Administrator.

The stored procedure returns one float value.

I have the following code so far:

   Dim sql
   Dim con 
   Dim cmd
   Dim value

   sql = "perfEng_RWtopits"
   Set con = CreateObject("ADODB.Connection")
   con.ConnectionString =  "myconnectionstring"
   con.Open                        
   Set cmd = CreateObject("ADODB.Command")
   cmd.ActiveConnection = con
   cmd.CommandText = sql                   
   value = cmd.Execute

   con.close
   Set cmd = Nothing
   Set con = Nothing

When I test the calculation I get a value of zero and a quality of bad. If I execute the stored procedure within SQL Server I get 17.123554 (which is correct). Also if I add the following to the end.

Result = value

I get the following error message.

Historian Error Message

Can anyone help?

Solution

  • You have an error in your vb script. To bad that we can't debug that in Historian Administrator in calcullation tab.

    When using a parameterless function you can execute it by writing: FunctionName or FunctionName(), but when you want to pass the result to a variable then you must use () like this value = FunctionName().

    You are using function cmd.Execute without () and returning the result to value. Just fix the line value = cmd.Execute to value = cmd.Execute()