Error on Delete request

Report PGNP bugs.

Error on Delete request

Postby Sebastien » Mon Apr 07, 2014 1:33 am

Hello,

We try to prepare some triggers to able to synchronize 2 different tables ( always from MSSQL to PostgreSql ). This synchronization is done only when the AttributeName column is equal to 'Service'.

On MSSQL, we have a table with the following attributes :

Code: Select all
CREATE TABLE [dbo].[StringMaps](
   [StringMapId] [uniqueidentifier] NOT NULL,
   [AttributeName] [nvarchar](100) NOT NULL,
   [AttributeValue] [int] NOT NULL,
   [DisplayOrder] [int] NOT NULL,
   [ObjectTypeCode] [int] NOT NULL,
   [ShortValue] [nvarchar](255) NOT NULL,
   [Value] [nvarchar](255) NOT NULL,
   [CreatedBy] [uniqueidentifier] NULL,
   [CreatedOn] [datetime] NULL,
   [ModifiedBy] [uniqueidentifier] NULL,
   [ModifiedOn] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
   [StringMapId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


On PostgreSql, we have :
Code: Select all
CREATE TABLE oto_referentiel.service
(
  id serial NOT NULL,
  code_oto character varying(15),
  code_bddrh character varying(255),
  libelle character varying(255),
  u_creat character varying(20),
  d_creat date NOT NULL,
  u_maj character varying(20),
  d_maj date,
  id_bdd_rh character varying(64),
  CONSTRAINT pk_service PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
)


I have created 3 triggers on events INSERT, UPDAT and DELET.

The first and the second works well but the event "delete" never work.

Insert Trigger :
Code: Select all
CREATE TRIGGER TRIGGER_SERVICE_INSERT ON StringMaps FOR INSERT AS
BEGIN DISTRIBUTED TRANSACTION
IF EXISTS(SELECT * FROM Inserted where AttributeName = 'Service')
BEGIN
      INSERT OPENQUERY(wake, 'select code_oto, code_bddrh, libelle, id_bdd_rh, u_creat, d_creat from oto_referentiel.service')
      SELECT AttributeName + '_' + CAST(AttributeValue AS VARCHAR), ShortValue, Value, CAST(StringMapId AS VARCHAR(64)), 'MSSQL', CURRENT_TIMESTAMP FROM Inserted where AttributeName = 'Service';
END
COMMIT TRANSACTION;


Update Trigger :
Code: Select all
CREATE TRIGGER TRIGGER_SERVICE_UPDATE ON StringMaps FOR UPDATE AS
BEGIN DISTRIBUTED TRANSACTION
IF EXISTS(SELECT * FROM Updated where AttributeName = 'Service')
BEGIN
      UPDATE OPENQUERY(wake, 'select code_oto, code_bddrh, libelle, u_maj, d_maj, id_bdd_rh from oto_referentiel.service') 
      SET code_oto=AttributeName + '_' + CAST(AttributeValue AS VARCHAR), code_bddrh=ShortValue, libelle=Value, u_maj='MSSQL', d_maj=CURRENT_TIMESTAMP FROM Inserted
         WHERE AttributeName = 'Service' and id_bdd_rh=CAST(StringMapId AS VARCHAR(64));
END
COMMIT TRANSACTION;


Delete Trigger : (which doesn't work)
Code: Select all
CREATE TRIGGER TRIGGER_SERVICE_DELETE ON StringMaps FOR DELETE AS
BEGIN DISTRIBUTED TRANSACTION
IF EXISTS(SELECT * FROM Deleted where AttributeName = 'Service')
BEGIN
      DELETE OPENQUERY(wake, 'select id_bdd_rh from oto_referentiel.service') 
         WHERE id_bdd_rh in ( select CAST(StringMapId AS VARCHAR(64)) from Deleted where AttributeName = 'Service')
END
COMMIT TRANSACTION;


When I delete a row on StringMaps table on MSSQL, I have this error :

Msg 7394, Niveau 16, État 2, Procédure TRIGGER_SERVICE_DELETE, Ligne 5
Le fournisseur OLE DB "PGNP" du serveur lié "wake" a rapporté une erreur lors de la validation de la transaction en cours.


I can't understand this error.

I launched the PGNP Profiler, and in attached file, here the log about this error.

Servers configuration :

- MSSQL : Microsoft SQL Server Standard Edition (64-bit) 2008 R2
- PGNP : PGNP-Postgres-SE-Trial-1.4.0.3232 ( We have a licence but, since our production environment crash with PGNP, we prefer test on another server first ).
- PostgreSql : PostgreSQL 8.4.11 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit

Currently, we are blocked because each time a row is deleted, the error appear, and the following request give also a error.

Thanks for advance for your help.

Best Regards.

P.S. : Some messages are in french messages.
Attachments
2.jpg
2.jpg (23.26 KiB) Viewed 564 times
1.jpg
1.jpg (25.44 KiB) Viewed 564 times
sqlservr.exe_20140407_101620.pgl.zip
(529 Bytes) Downloaded 41 times
Sebastien
 
Posts: 5
Joined: Mon Apr 07, 2014 1:01 am

Re: Error on Delete request

Postby Moderator » Mon Apr 07, 2014 11:46 pm

We are working on the issue. We will update the thread as soon as we have more information. Thank you for the very detailed info!
User avatar
Moderator
Site Admin
 
Posts: 293
Joined: Wed Oct 29, 2008 11:27 pm

Re: Error on Delete request

Postby Sebastien » Thu Apr 17, 2014 5:38 am

Hello,

Do you have a solution or workaround about this bug ?

Currently, our production is blocked.

Thanks for advance.
Best Regards.
Sebastien
 
Posts: 5
Joined: Mon Apr 07, 2014 1:01 am

Re: Error on Delete request

Postby Sebastien » Mon Apr 28, 2014 2:08 am

Up.
Sebastien
 
Posts: 5
Joined: Mon Apr 07, 2014 1:01 am

Re: Error on Delete request

Postby Sebastien » Mon May 05, 2014 5:27 am

A solution ??
Sebastien
 
Posts: 5
Joined: Mon Apr 07, 2014 1:01 am

Re: Error on Delete request

Postby Moderator » Fri May 09, 2014 1:24 am

Sorry for the delay. Yes, we published new release for the bug: 1.4.0.3248. Please run PGNPUpdate utility to update to 3248, or later build.
User avatar
Moderator
Site Admin
 
Posts: 293
Joined: Wed Oct 29, 2008 11:27 pm

Re: Error on Delete request

Postby Sebastien » Wed May 21, 2014 8:06 am

Hello,

After installed the version 1.4.0.3248, I restart the tests.

And, unfortunately, I have a another error.

First step : A insertion

Code: Select all
INSERT INTO dbo.StringMaps (StringMapId,AttributeName,AttributeValue,DisplayOrder,ObjectTypeCode,ShortValue, Value,CreatedBy,CreatedOn, ModifiedBy, ModifiedOn)
    VALUES (NewId(),'Service', 20, 20, 1011, 'Dir.', 'Direction', NULL,  NULL,  NULL,  NULL );

Second step : Find the new Id in the StringMaps table

Third step : A update of the ShortValue column of the new line

Code: Select all
UPDATE dbo.StringMaps
SET ShortValue = 'AHAH'
WHERE StringMapId = 'F3F5F9DB-4E2D-439B-9A41-B4FA1F2489F0';

Fourth step : Exactly, the same update.

Code: Select all
UPDATE dbo.StringMaps
SET ShortValue = 'AHAH'
WHERE StringMapId = 'F3F5F9DB-4E2D-439B-9A41-B4FA1F2489F0';


And Error !

Code: Select all
Le fournisseur OLE DB "PGNP" du serveur lié "wake" a retourné le message "ERREUR:  erreur de syntaxe � la fin de l'entr�e
LINE 1: MOVE BACKWARD 1 FROM
                             ^
".
Msg 7343, Niveau 16, État 4, Procédure TRIGGER_SERVICE_UPDATE, Ligne 5
Le fournisseur OLE DB "PGNP" du serveur lié "wake" n'a pas pu UPDATE la table "[PGNP]".


The update do the same thing in MSSQL table and Postgresql Table.

I don't know what I must do ...

Thanks for advance for a solution.

Best Regards.
Sebastien
 
Posts: 5
Joined: Mon Apr 07, 2014 1:01 am

Re: Error on Delete request

Postby Moderator » Wed May 28, 2014 10:25 am

The missing cursor name issue was a side effect from a previous fix. We have fixed it in build 3250. Please obtain an update when it is released, or contact Support to obtain installation module with the fix. Thank you!
User avatar
Moderator
Site Admin
 
Posts: 293
Joined: Wed Oct 29, 2008 11:27 pm


Return to Report Bugs

Who is online

Users browsing this forum: No registered users and 1 guest

cron