Baanboard.com

Baanboard.com (http://www.baanboard.com/baanboard/index.php)
-   Operating Systems & Databases (http://www.baanboard.com/baanboard/forumdisplay.php?f=4)
-   -   Sql Server Trigger (http://www.baanboard.com/baanboard/showthread.php?t=25254)

barkilic 4th October 2005 16:12

Sql Server Trigger
 
Hello,

We are new in Windows 2003/SQL Server 2000 Environment.

I created a Sql server trigger. But while it runs, this message pops up:

Fatal error : Error 1000(bdb_errno 520) on tiitm100138 in select
Fatal error : Cannot continue in tiitm0202s000 in DLL : otiitmdll0003(get.company)
Error : tiitm0202s000: A not terminated transaction is aborted


And these are the log records in Windows 2003 Event Viewer's application section:

9/29/2005 2:19:30 PM Baan Error Bshell 1000 TRABB01\BARKIL TRABB01ISAS99 "Env BaanIV (d:\baaniv)
Prog bshell file \logic\mir\mir\bdb_fun.c # 1101
Keyword DB error
Username barkil type S language 2
Process 0x3f34
Lasterror 0
Errno 0
bdberrno 520 (Error during buffered updates)
Message Error 1000 (bdb_errno 520) on tiitm100138 in SELECT
"
9/29/2005 2:19:30 PM Baan Error Msql7_srv 1000 TRABB01\BARKIL TRABB01ISAS99 "Env BaanIV (d:\baaniv)
Prog msql7_srv file \db\servers\MSQL\msql_row.c # 344
Keyword MSQL Error
Username barkil type S language 2
Process 0x3d78
Lasterror 0
Errno 0
bdberrno 0
Message
dbs_errno = 1000 Error 1000 occurred:
Error during execute insert (dbo.ttiitm100138).
Flushed at \db\servers\MSQL\msql_row.c : #244.
"
9/29/2005 2:19:30 PM Baan Error Msql7_srv 1000 TRABB01\BARKIL TRABB01ISAS99 "Env BaanIV (d:\baaniv)
Prog msql7_srv file \db\servers\MSQL\msql_error.c # 513
Keyword MSQL Messages
Username barkil type S language 2
Process 0x3d78
Lasterror 0
Errno 0
bdberrno 0
Message
dbs_errno = 1000 Error 1000 occurred:
Error sqlcode 0 (sqlstate: HY000, Return code: -1, diagRecNum: 1) during SQLExecute.
[Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt
Flushed at \db\servers\MSQL\msql_error.c : #518.
"
9/29/2005 2:19:30 PM Baan Error Msql7_srv 1000 TRABB01\BARKIL TRABB01ISAS99 "Env BaanIV (d:\baaniv)
Prog msql7_srv file \db\servers\MSQL\msql_error.c # 513
Keyword MSQL Messages
Username barkil type S language 2
Process 0x3d78
Lasterror 0
Errno 0
bdberrno 0
Message
dbs_errno = 1000 Error 1000 occurred:
Error sqlcode 0 (sqlstate: HY000, Return code: -1, diagRecNum: 1) during SQLExecDirect.
[Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt
Flushed at \db\servers\MSQL\msql_error.c : #518.


The trigger looks like this:

Create TRIGGER giris_138
ON ttiitm001138
AFTER INSERT AS
insert into ttiitm200138 - - - - >That's a table I created.
(t_item,t_stat)
select
ek.t_item,
1
from inserted ek
GO

dave_23 4th October 2005 19:16

Quote:

Originally Posted by barkilic
The trigger looks like this:

Create TRIGGER giris_138
ON ttiitm001138
AFTER INSERT AS
insert into ttiitm200138 - - - - >That's a table I created.
(t_item,t_stat)
select
ek.t_item,
1
from inserted ek
GO

Before you get too far into this.. Are you Level 1 or Level 2 driver. If you're level 1 driver, and you do that trigger, Baan won't be able to see the data in your ttiitm200138 table.

Dave

barkilic 5th October 2005 13:21

Quote:

Originally Posted by dave_23
Before you get too far into this.. Are you Level 1 or Level 2 driver. If you're level 1 driver, and you do that trigger, Baan won't be able to see the data in your ttiitm200138 table.

Dave

It's Level 2 as far as I remember. Is there a place in Baan to verify it?

Anyway, I found out that this error comes most probably because of "after write" IO transactions of "Maintain Item Data" session.

I tried the trigger with other tables - other maintain sessions. The trigger works with them.

Now, is there a way to avoid "after write" transactions? For instance, Main.Item Data inserts a few tables like tiitm100 after inserting to tiitm001.

And do you know what the hash field Baan creates during table creation is for? When I try to enter a record manually in SQL Ser. Ent.Man., it says hash field cannot be left blank, but it also doesn't allow editing

vahdani 5th October 2005 13:27

Hi,

I've done one project with Baan and Oracle-Triggers so I feel that I can comment here although I am not familiar with SQL-Server syntax:

The most probable reason for the error is that you may have an error in your trigger code:

I would have tried something like:

Code:

        Create TRIGGER giris_138
        ON ttiitm001138
        BEFORE INSERT AS
        insert into ttiitm200138
        (t_item,t_stat)
        values(new.t_item, 1)
        go


dave_23 5th October 2005 13:43

Quote:

Originally Posted by barkilic
It's Level 2 as far as I remember. Is there a place in Baan to verify it?

And do you know what the hash field Baan creates during table creation is for? When I try to enter a record manually in SQL Ser. Ent.Man., it says hash field cannot be left blank, but it also doesn't allow editing

IF you have hash columns then you are level 1. So this will not work.

In Level 1 Baan creates a hash field and then indexes that field, it was
a performance related fix back before technology caught up with data volume.

You'd need to convert your data to level 2.

Dave

vahdani 5th October 2005 15:11

Dave is right of course!

My target table was outside of Baan (= no Hash Fileds).

barkilic 5th October 2005 15:31

Quote:

Originally Posted by dave_23
IF you have hash columns then you are level 1. So this will not work.

In Level 1 Baan creates a hash field and then indexes that field, it was
a performance related fix back before technology caught up with data volume.

You'd need to convert your data to level 2.

Dave

Thank you both for your answers.

This might sound silly,but I have to ask: Is it possible to change the level on table basis? Just the tables I need?

Or is there anything else I could use instead of triggers?

dave_23 5th October 2005 17:06

Quote:

Originally Posted by barkilic
Thank you both for your answers.

This might sound silly,but I have to ask: Is it possible to change the level on table basis? Just the tables I need?

Or is there anything else I could use instead of triggers?

no, you can't do it on a per table bases.. you need to set db_resource
parameters which are global.

The problem is - you need to insert the record with the hash column..
so anything outside of Baan won't work.. You'd have to modify the session
to make this happen (since you're using Baan 4)

Dave

vahdani 5th October 2005 20:53

let me see :rolleyes:

the table tiitm001 is written to by Baan therfore the Hash columns are filled correctly.

The target table tiitm200 on the other hand is written to by the trigger which at the moment does not write the hash cloumn. As I see it the hash column of tiitm200 "_index1" (or whatever the name of the hash field is!) has only one column "item". Maybe the following should work:

Code:

        Create TRIGGER giris_138
        ON ttiitm001138
        BEFORE INSERT AS
        insert into ttiitm200138
        (t_item,t_stat, t_index1)
        values(new.t_item, 1, new.t_item)
        go

In any case you must be 100% correct with the syntax of your trigger, which I still think is the culprit! Maybe it is better if you test the syntax on two non Baan tables first. In any case don't give up. Test! You should be able to make it work even with level 1 driver.

PS: You should find a thread about determining the content of Hash Fields by searching Baanboard.

dave_23 6th October 2005 06:18

Yeah, there's a good chance that that will work... scary though!

Dave


All times are GMT +2. The time now is 15:22.


vB.Sponsors
©2001-2017 - Baanboard.com - Baanforums.com