Fail to insert data after 32767 rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Fail to insert data after 32767 rows

Author

Topic

garyyeung
Starting Member

6 Posts

Posted - 2010-03-22 : 04:25:40

I have implemented a system that write audit log into a table in SQL server 2000 using powerbuilder. However, recently, it was found that the system fails to insert log data into the table after 32767 rows but I have checked that there should have no such limitation for SQL server 2000, right? So, what's the actual problem?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-22 : 04:29:47

what's the error message ?

What's the primary key of the log table ?

KH
[spoiler]Time is always against us[/spoiler]

garyyeung
Starting Member

6 Posts

Posted - 2010-03-22 : 04:30:39

There is no error message.
The keys of the tables are (locationID + runseq) where locationID is the location of the site using the system and the runseq is the runing sequence that is start from 0, 1, 2...
Another table stores the information of the local locationID and the next runseq number.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-22 : 04:48:57

Check the runseq datatype and limitation of the datatype!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-22 : 04:51:55

the runseq is generating from your PB or it is an identity column ?

if the runseq is generated from your PB, change the data type to long.

KH
[spoiler]Time is always against us[/spoiler]

garyyeung
Starting Member

6 Posts

Posted - 2010-03-22 : 04:57:13

Originally, a table, tb_mylocation, stores the following information,

Location******Runseq
-------------*------
Mylocation****1

My PB program reads the 'runseq' from the table and insert a record with runseq = '1' then my PB program updates tb_mylocation to set runseq = runseq + 1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-22 : 04:58:29

your runseq must be an integer. Integer in PB is smaller than SQL Server's integer

KH
[spoiler]Time is always against us[/spoiler]

garyyeung
Starting Member

6 Posts

Posted - 2010-03-22 : 04:59:42

Oh.... then how can I do in PB? Is "Double" solve this problem?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-22 : 05:18:41