Error on Delete request

More
5 years 11 months ago #10951 by Sebastien
Sebastien created the topic: Error on Delete request
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 :
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 :
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 :
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 :
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)
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:

Please Log in or Create an account to join the conversation.

More
5 years 11 months ago #10952 by Moderator
Moderator replied the topic: Re: Error on Delete request
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!

Please Log in or Create an account to join the conversation.

More
5 years 11 months ago #10955 by Sebastien
Sebastien replied the topic: Re: Error on Delete request
Hello,

Do you have a solution or workaround about this bug ?

Currently, our production is blocked.

Thanks for advance.
Best Regards.

Please Log in or Create an account to join the conversation.

More
5 years 11 months ago #10984 by Sebastien
Sebastien replied the topic: Re: Error on Delete request
Up.

Please Log in or Create an account to join the conversation.

More
5 years 11 months ago #11040 by Sebastien
Sebastien replied the topic: Re: Error on Delete request
A solution ??

Please Log in or Create an account to join the conversation.

More
5 years 10 months ago #11086 by Moderator
Moderator replied the topic: Re: Error on Delete request
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.

Please Log in or Create an account to join the conversation.

More
5 years 10 months ago #11095 by Sebastien
Sebastien replied the topic: Re: Error on Delete request
Hello,

After installed the version 1.4.0.3248, I restart the tests.

And, unfortunately, I have a another error.

First step : A insertion
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
UPDATE dbo.StringMaps 
SET ShortValue = 'AHAH' 
WHERE StringMapId = 'F3F5F9DB-4E2D-439B-9A41-B4FA1F2489F0';
Fourth step : Exactly, the same update.
UPDATE dbo.StringMaps 
SET ShortValue = 'AHAH' 
WHERE StringMapId = 'F3F5F9DB-4E2D-439B-9A41-B4FA1F2489F0';

And Error !
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.

Please Log in or Create an account to join the conversation.

More
5 years 10 months ago #11123 by Moderator
Moderator replied the topic: Re: Error on Delete request
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!

Please Log in or Create an account to join the conversation.

Time to create page: 0.094 seconds
Powered by Kunena Forum