Relational Population Service - SQL Set Options

[Michael.Duraj]

I have RPS running Ok, and today I receive this error.

RPS Target Relational DB - SQL operation failed;
[Microsoft][SQL Server Native Client 10.0][SQL Server]UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.;

ODBC Connection has;
[x] Use ANSI quoted identifiers
[x] Use ANSI nulls, paddings and warnings
The above proc is actually performing an UPDATE to a Table = Customer

Running a SQL Trace
JADE calls SET ANSI_PADDING OFF
INSERT INTO JADE_TRANSACTIONS (TRANID) VALUES (2540982127)
....
...
exec jadeCustomerUpdate ......

The stored procedure fails because ANSI_PADDING needs to be ON

SQL Server -> SQL 2008 R2

I have worked out if I alter the stored procedure as

Code: Select all

SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[jadeCustomerUpdate] .. .. .. GO

That I address the QUOTED_IDENTIFIER issue, but I can not do anything about ANSI_PADDING?
Adding SET ANSI_PADDING On inside the proc has no affect.

The fact that the JADE app has issued SET ANSI_PADDING OFF has an affect on subsequent calls to the stored procedure

[Michael.Duraj]

I have set my RPS Node Configuration -> Database Type to: SQL 2005, Should have been 2008
Is this the reason for my error?

RPS Manager -> RPS Node configuration -> Database Type: SQL Server 2005
If I change it I get prompted -> "Ok to modify Relational Database for new Database Type?"

Any clues as to what this will do?
I dont want to have to setup my DB again from scratch.

[Michael.Duraj]

Any reason why Jade issues the following?

SET ANSI_PADDING OFF ?
-------------------------------

1) Initial connection to database

-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

2) Jade issues SET ANSI_PADDING OFF

SET ANSI_PADDING OFF
go
INSERT INTO JADE_TRANSACTIONS (TRANID) VALUES (2540982127)
go
exec jadeSOSalesOrderInsert 1,'2013-10-21 10:16:16.1990000','LEAVE IF SAFE AT FRONT DOOR','MARJORIE STOTT','1',NULL,1.00000000,NULL,NULL,'2013-10-21 00:00:00',0,'2013-10-21 00:00:00',0,'2013-10-21 10:16:15.4510000','QMO',0,6.00,0,0.60,1,0,0,0,0,0,'2013-10-21 10:16:15.4510000','QMO','12082.0002557809','02540.0000000005','02547.0000000001','02550.0000000193','02554.0000017438','00000.0000000000','00000.0000000000','00000.0000000000','03180.0000000002','04020.0000000004','00000.0000000000','02755.0000000001','02755.0000000001','00000.0000000000','02569.0000000050','03188.0000000016','00000.0000000000','03493.0001206029','5002252',0,'Greentree',NULL,'2013-10-21 00:00:00',NULL
go
exec sp_executesql N'UPDATE SOSalesOrder SET addressNumber=@P1 WHERE oid = @P2',N'@P1 varchar(max),@P2 char(16)','1','03493.0001206029'
go
exec sp_executesql N'UPDATE SOSalesOrder SET additionalDeliveryText=@P1 WHERE oid = @P2',N'@P1 varchar(max),@P2 char(16)','LEAVE IF SAFE AT FRONT DOOR','03493.0001206029'
go
exec sp_executesql N'UPDATE SOSalesOrder SET addressName=@P1 WHERE oid = @P2',N'@P1 varchar(max),@P2 char(16)','MARJORIE STOTT','03493.0001206029'
go
exec jadeCRMAlertBucketTargetObjectInsert '08370.0004514550','08211.0004514547'
go
exec sp_executesql N'UPDATE SOSalesOrder SET entryUser=@P1 WHERE oid = @P2',N'@P1 varchar(max),@P2 char(16)','QMO','03493.0001206029'
go
exec jadePARSODocumentLinkInsert 1,'2013-10-21 10:16:16.1990000','03493.0001206029','00000.0000000000','00000.0000000000','00000.0000000000','00000.0000000000','00000.0000000000','00000.0000000000','00000.0000000000','00000.0000000000','00000.0000000000','00000.0000000000','00000.0000000000','00000.0000000000','00000.0000000000','11802.0002526541','00000.0000000000','00000.0000000000','03493.0001206029','00000.0000000000','00000.0000000000','00000.0000000000','00000.0000000000','11871.0002526532'
go
exec jadePARSODocumentInsert 1,'2013-10-21 10:16:16.1990000',NULL,0,NULL,NULL,NULL,NULL,NULL,'2013-10-22 00:00:00',0,0,NULL,NULL,NULL,'2013-10-22 00:00:00',NULL,NULL,NULL,NULL,NULL,0,0,0,0,0,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,'12272.0002526725','00000.0000000000','12672.0002525396','AIDS AND APPLIANCES TEAM','11747.0000000001','00000.0000000000','00000.0000000000','11776.0000000007','11772.0000000008','11871.0002526532','00000.0000000000',NULL,NULL,NULL,NULL,'11802.0002526541','PHONE',0,NULL,NULL,NULL,0,NULL,'00000.0000000000',NULL,NULL,NULL,NULL,NULL,0,0,'2013-10-22 00:00:00','2013-10-22 00:00:00','2013-10-22 00:00:00','2013-10-22 00:00:00','2013-10-22 00:00:00'
go
exec sp_executesql N'UPDATE PARSODocument SET myInvoiceName=@P1 WHERE oid = @P2',N'@P1 varchar(max),@P2 char(16)','AIDS AND APPLIANCES TEAM','11802.0002526541'
go
exec jadeUTAddressPAROrderDelivInsert 1,'2013-10-21 10:16:16.1990000',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'04015.0000000002','11802.0002526541','12272.0002526725',NULL,NULL,NULL,NULL,NULL,NULL,NULL
go
exec jadeSOSOINLineItemInsert 1,'2013-10-21 10:16:16.1990000',1.00000000,'2013-10-21 00:00:00',0,'2013-10-21 00:00:00',0,NULL,6.00,0,0,0.60,1,0,0,0,0,0,0,6.00,0,0,0.60,6.0000,100.0000,'00000.0000000000','00000.0000000000','02162.0000000128','00000.0000000000','00000.0000000000','00000.0000000000','00000.0000000000','00000.0000000000','00000.0000000000','00000.0000000000','00000.0000000000','00000.0000000000','03180.0000000002','02550.0000000193','02576.0000012996','03139.0000000001','03493.0001206029','03493.0001206029','02755.0000000001','03188.0000000016','02576.0000012996','02715.0000038088','02573.0000000002','02162.0000000002','03145.0000000003','00000.0000000000','03133.0000000001',0,'03489.0003493796','2013-10-21 00:00:00',0,0,1.0000,0,0,0,1.0000,NULL,'2013-10-21 00:00:00',1.0000,10.0000,'2013-10-22 00:00:00','3489.3493796,',0,6.0000
go

3) Previous inserts and updates ok then error on the next line

exec jadeStockLocationUpdate 202308,'2013-10-21 10:16:16.1990000',0,NULL,0,'02716.0001778315','03180.0000000002','02716.0006044953','02576.0000012996','00000.0000000000','00000.0000000000',NULL,'02715.0000038088',0,999999999.0000,117.0000,0,1.0000,0,0,0,0,0
go

10:48:07> ** DataPump application aborted
10:48:07> RDB ODBC Error: Update object;Sql execute - tranId: 2540982127; oid: [2715.38088:202308];
Sql: {? = call jadeStockLocationUpdate (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) };
RPS Target Relational DB - SQL operation failed;
[Microsoft][SQL Server Native Client 10.0][SQL Server]UPDATE failed because the following SET options have incorrect settings: 'ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.;
State: 42000, native: 1934
10:48:07> ** Tracking halted due to error **

[Michael.Duraj]

FYI - File versions in use

jrpsodbc.dll 6.3.5.41
jade.exe 6.3.5.42

[Michael.Duraj]

Still stuck any thoughts would be appreciated

[Michael.Duraj]

My client had started using a SSIS or another development tool which has created views and index views in replication database.

Removing these views and indexed views allowed the DataPump to flow again.

The question still remains;

What is the technical reason for Jade to issue SET ANSI_PADDING OFF?
If it doesn't then the RPS generated stored procedure->table can update without error.

Therefore the client can create indexed views on the replication database which given its purpose is not un-reasonable.

According to Microsoft “We recommend that ANDI_PADDING always bet set to ON” or “In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error”