AnsweredAssumed Answered

RDBMS interface query using @rows_dropped variable

Question asked by John Messinger Champion on Aug 2, 2017
Latest reply on Aug 3, 2017 by John Messinger

I'm trying to execute an RDBMS interface query against a SQL Server 2016 data source, and need to make use of the @rows_dropped variable functionality to capture failed data rows and write them to another table. Unfortunately I can't seem to make this work at all, with differing errors depending on which driver I use (SQL Server ODBC driver, SQL Native Client, etc).

 

I'm using PI-RDBMS interface version 3.23.3.7 (64 bit), against SQL Server 2016 (not SP1).

 

Currently, the error I have for this is as follows (I've included the full log entry for context):

 

Wed Aug 02 13:07:01 2017 [INFO]: 

Starting RDBMSPI interface version 3.23.3.7 as an NT service.

Wed Aug 02 13:07:01 2017 [INFO]:  Uniint version 4.6.2.4

Wed Aug 02 13:07:01 2017 [INFO]:  API version 1.6.8.22

Wed Aug 02 13:07:01 2017 [INFO]:  Server Version PI 3.4.410.1256

Wed Aug 02 13:07:01 2017 [INFO]:  Pipe RDBMSPIPIPE01 created.

Wed Aug 02 13:07:01 2017 [INFO]:  Successfully logged into Metering datasource as MDM.

Wed Aug 02 13:07:01 2017 [DEB-1]: ODBC Environment Attributes :

SQL_ATTR_CONNECTION_POOLING SQL_CP_OFF

SQL_ATTR_CP_MATCH SQL_CP_STRICT_MATCH

SQL_ATTR_ODBC_VERSION SQL_OV_ODBC3

SQL_ATTR_OUTPUT_NTS SQL_TRUE

 

 

Wed Aug 02 13:07:01 2017 [DEB-1]: ODBC Connection Attributes :

SQL_ATTR_ACCESS_MODE SQL_MODE_READ_WRITE

SQL_ATTR_ASYNC_ENABLE SQL_ASYNC_ENABLE_OFF

SQL_ATTR_AUTO_IPD SQL_TRUE

SQL_ATTR_AUTOCOMMIT SQL_AUTOCOMMIT_ON

SQL_ATTR_CURRENT_CATALOG Metering

SQL_ATTR_LOGIN_TIMEOUT 15

SQL_ATTR_METADATA_ID SQL_FALSE

SQL_ATTR_ODBC_CURSORS SQL_CUR_USE_DRIVER

SQL_ATTR_PACKET_SIZE 4096

SQL_ATTR_TRACE SQL_OPT_TRACE_OFF

SQL_ATTR_TRACEFILE C:\Users\RDBMSPI2\AppData\Local\Temp\SQL.LOG

SQL_ATTR_TRANSLATE_LIB

SQL_ATTR_TRANSLATE_OPTION 0

SQL_ATTR_TXN_ISOLATION 2

 

 

Wed Aug 02 13:07:01 2017 [DEB-1]: ODBC General Info :

SQL_CURSOR_COMMIT_BEHAVIOR SQL_CB_CLOSE

SQL_DBMS_NAME Microsoft SQL Server

SQL_DBMS_VER 13.00.1728

SQL_DEFAULT_TXN_ISOLATION SQL_TXN_READ_COMMITTED

SQL_DESCRIBE_PARAMETER Y

SQL_DRIVER_NAME SQLSRV32.DLL

SQL_DRIVER_ODBC_VER_SUPPORTED 03.52

SQL_DRIVER_VER 06.03.9600

SQL_ODBC_API_CONFORMANCE SQL_OAC_LEVEL2

SQL_ODBC_SQL_CONFORMANCE SQL_OSC_CORE

SQL_SQL_CONFORMANCE SQL_SC_SQL92_ENTRY

SQL_SERVER_NAME PI-DEVELOPMENT

SQL_TXN_CAPABLE SQL_TC_ALL

 

Wed Aug 02 13:07:01 2017 [DEB-1]: Status SUCCESS Area :

?????????? 0

 

Wed Aug 02 13:07:01 2017 [DEB-1]: Status BAD Area :

?1 1

 

Wed Aug 02 13:07:01 2017 [INFO]:  Interface number: 1; Debug level: 2

Wed Aug 02 13:07:01 2017 [INFO]:  Interface RDBMSPI version 3.23.3.7 started.

 

Wed Aug 02 13:07:01 2017 [DEB-1]: Input point - [Redacted] : added to sc 1.

Wed Aug 02 13:07:01 2017 [DEB-1]: Input point - [Redacted] : added to sc 1.

Wed Aug 02 13:07:01 2017 [DEB-1]: Input point - Metering_Distributor : added to sc 1.

Wed Aug 02 13:07:01 2017 [INFO]:  Input history recovery on the interval <02-Aug-2017 13:02:01.000 , 02-Aug-2017 13:07:01.000> with step 86400 sec started.

Wed Aug 02 13:07:01 2017 [DEB-1]: Point - Metering_Distributor : SQL statement(s) : SELECT Cast(PITagName AS varchar(50)) AS PI_TAGNAME

,IntervalTime AS PI_TIMESTAMP

,Readingvalue AS PI_VALUE

,Cast(CASE

WHEN Quality = N'A' THEN 0

ELSE 1

END AS int) AS PI_QUESTIONABLE

FROM Metering.dbo.MeterData_Staging

WHERE rowRead = 0

ORDER BY IntervalTime ASC;

UPDATE Metering.dbo.MeterData_Staging

SET rowRead=1

WHERE rowRead=0

 

 

WHILE @rows_dropped

BEGIN

INSERT INTO Metering.dbo.MeterData_StagingErrors (PITagName, Readingvalue, IntervalTime, PI_QUESTIONABLE)

VALUES (?,?,?,?)

END;

Wed Aug 02 13:07:01 2017 [IFC-E]: SQLBindParameter> Point - Metering_Distributor : bindParam () : [S] [07006]: [Microsoft][ODBC SQL Server Driver]Restricted data type attribute violation

Wed Aug 02 13:07:01 2017 [INFO]:  Processing the input history recovery interval <02-Aug-2017 13:02:01.000 , 02-Aug-2017 13:07:01.000>.

Wed Aug 02 13:07:01 2017 [INFO]:  Input history recovery completed.

Wed Aug 02 13:07:01 2017 [INFO]:  Interface continues normal operations.

 

Here is the query I'm trying to execute:

 

SELECT Cast(PITagName AS varchar(50)) AS PI_TAGNAME
     ,IntervalTime AS PI_TIMESTAMP
     ,Readingvalue AS PI_VALUE
     ,Cast(CASE 
          WHEN Quality = N'A' THEN 0
          ELSE 1
          END AS int) AS PI_QUESTIONABLE
FROM Metering.dbo.MeterData_Staging
WHERE rowRead = 0
ORDER BY IntervalTime ASC;
UPDATE Metering.dbo.MeterData_Staging 
SET rowRead=1 
WHERE rowRead=0

WHILE @rows_dropped
BEGIN
INSERT INTO Metering.dbo.MeterData_StagingErrors (PITagName, IntervalTime, Readingvalue, PI_QUESTIONABLE)
VALUES (?,?,?,?)
END;

 

I've tried reducing the number of columns that I insert into the output table, to narrow down which column is returning the above error, and it seems to be the IntervalTime column (which is defined as a datetime data type in both source and destination tables). Googling this error indicates data type compatibility issues between SQL Server versions, however my source table for reading input data and the table for receiving undistributed data are located within the same database, so I believe this to be something a bit deeper and more subtle.

 

My question here is twofold - firstly, has anyone successfully implemented this interface functionality (using @rows_dropped and outputting rows to another table) against SQL Server; and secondly, any clues as to the "Restricted data type attribute violation" error returned to the interface by the ODBC driver?

 

Cheers,

John

Outcomes