Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Operating Systems & Databases

User login

Frontpage Sponsor

Main

Poll
Does your use of MS-SharePoint requires interface with your ERP system?
We do not use MS-SharePoint
46%
We do not have a need for an interface between SharePoint and ERP
19%
We need to insert or update data in SharePoint from the ERP
10%
We need to access SharePoint data from the ERP
1%
We need to access and insert data to SharePoint from the ERP
12%
We already have a third party tool to create the interface between SharePoint and ERP
9%
Other - Please Comment
3%
Total votes: 78

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 4th October 2005, 16:12
barkilic barkilic is offline
Junior Member
 
Join Date: Oct 2005
Posts: 3
barkilic is on a distinguished road
Baan: BaanIVc - DB: SQL Server 2000 - OS: Windows
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
Reply With Quote
  #2  
Old 4th October 2005, 19:16
dave_23's Avatar
dave_23 dave_23 is offline
Guru
 
Join Date: Oct 2002
Location: Portland, OR
Posts: 1,303
dave_23 will become famous soon enough
Baan: All - DB: Oracle / MS SQL / DB2 - OS: All
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
Reply With Quote
  #3  
Old 5th October 2005, 13:21
barkilic barkilic is offline
Junior Member
 
Join Date: Oct 2005
Posts: 3
barkilic is on a distinguished road
Baan: BaanIVc - DB: SQL Server 2000 - OS: Windows
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
Reply With Quote
  #4  
Old 5th October 2005, 13:27
vahdani's Avatar
vahdani vahdani is offline
Guru
 
Join Date: Aug 2002
Location: Cologne, Germany
Posts: 375
vahdani is on a distinguished road
Baan: all - DB: all - OS: Unix / Win2K
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
__________________
May the force be with you!
Reply With Quote
  #5  
Old 5th October 2005, 13:43
dave_23's Avatar
dave_23 dave_23 is offline
Guru
 
Join Date: Oct 2002
Location: Portland, OR
Posts: 1,303
dave_23 will become famous soon enough
Baan: All - DB: Oracle / MS SQL / DB2 - OS: All
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
Reply With Quote
  #6  
Old 5th October 2005, 15:11
vahdani's Avatar
vahdani vahdani is offline
Guru
 
Join Date: Aug 2002
Location: Cologne, Germany
Posts: 375
vahdani is on a distinguished road
Baan: all - DB: all - OS: Unix / Win2K
Dave is right of course!

My target table was outside of Baan (= no Hash Fileds).
__________________
May the force be with you!
Reply With Quote
  #7  
Old 5th October 2005, 15:31
barkilic barkilic is offline
Junior Member
 
Join Date: Oct 2005
Posts: 3
barkilic is on a distinguished road
Baan: BaanIVc - DB: SQL Server 2000 - OS: Windows
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?
Reply With Quote
  #8  
Old 5th October 2005, 17:06
dave_23's Avatar
dave_23 dave_23 is offline
Guru
 
Join Date: Oct 2002
Location: Portland, OR
Posts: 1,303
dave_23 will become famous soon enough
Baan: All - DB: Oracle / MS SQL / DB2 - OS: All
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
Reply With Quote
  #9  
Old 5th October 2005, 20:53
vahdani's Avatar
vahdani vahdani is offline
Guru
 
Join Date: Aug 2002
Location: Cologne, Germany
Posts: 375
vahdani is on a distinguished road
Baan: all - DB: all - OS: Unix / Win2K
let me see

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.
__________________
May the force be with you!
Reply With Quote
  #10  
Old 6th October 2005, 06:18
dave_23's Avatar
dave_23 dave_23 is offline
Guru
 
Join Date: Oct 2002
Location: Portland, OR
Posts: 1,303
dave_23 will become famous soon enough
Baan: All - DB: Oracle / MS SQL / DB2 - OS: All
Yeah, there's a good chance that that will work... scary though!

Dave
Reply With Quote
Sponsored Links
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Baan and SQL Server delphin Performance & Tuning 1 21st April 2005 02:51
SQL Server 7.0 SP4 + BaanERP compatiblity Jabran Tools Administration & Installation 1 4th October 2004 12:49
Decision Manager & SQL Server 7.0 BisBoy Open World, Portal & Decision Manager 0 10th July 2003 14:11
Upgrade Baan IV (HP-UX, SQL Server 2000) cfasini Operating Systems & Databases 3 20th May 2003 14:56
Should Baan provide a level 2 database driver for SQL server 2000 on BaanIVc4? patvdv Polls and Surveys 6 28th March 2003 14:22


All times are GMT +2. The time now is 12:33.


©2001-2013 - Baanboard.com - Baanforums.com