@@DBTS function in SQL 2000
-
Hello,
I've recently converted a db from sql 7.0 to sql 2000 using the backup / restore method. Everything looks good except for one table. The table has a timestamp field and a field called new_posting_key (varbinary (8)). For some reason, wheneve I update or insert a record, the new_posting_key field is supposed to get updated with the value of the timestamp field, which is guaranteed to be unique. The timestamp field value changes, but the new_posting_key field does not change. It carries the last value of the timestamp field for all insertions or updates. I've tested this on a test table with the same fields and it works. Why is it not working on my main table? I've even tried creating the main table from scratch on sql 2000, but I still get the same behavior. Is there something special with @@DBTS in sql 2000? Any help would be appreciated! Thanks FT.
-
do you have any triggers on you "Main" table?
can you post the query you are using ?
* Noel -
Hi Noel,
I don't have any triggers and here is the code I'm executing:
insert into ft_work.dbo.rp_work
(
post_prefix,
post_no,
record_type,
proj_no,
emp_no,
form,
tran_date,
line,
post_date,
old_posting_key,
--invoice_key,
new_posting_key,
serv_code,
hrs,
rate,
distance,
workplace,
city,
[description],
amount,
product_no,
glacct,
prov_state,
enter_div,
tax_code,
emp_div,
sup_div,
std_rate,
std_cost,
expense_type,
prod_type_code,
multiplier,
totvatax,
admin_amt,
company,
prod_rev_code,
proj_rev_code,
div_rev_code,
serv_rev_code,
intco_type,
post_code,
reason_rev_code,
acct_date,
acct_year,
acct_period,
open_flag,
open_tran_date,
closed_tran_date,
tag,
postrev_id,
matrix_match,
with_company,
debit_admin,
credit_admin,
debit_vatax,
credit_vatax,
ledger_curr_code
)
select
posting_revenue.post_prefix,
posting_revenue.post_no,
posting_revenue.record_type,
posting_revenue.proj_no,
posting_revenue.emp_no,
posting_revenue.form,
posting_revenue.tran_date,
posting_revenue.line,
'07/19/2005',
posting_revenue.posting_key,
@@DBTS,
posting_revenue.serv_code,
posting_revenue.hrs,
posting_revenue.rate,
posting_revenue.distance,
posting_revenue.workplace,
posting_revenue.city,
posting_revenue.[description],
posting_revenue.amount,
posting_revenue.product_no,
posting_revenue.glacct,
posting_revenue.prov_state,
posting_revenue.enter_div,
posting_revenue.tax_code,
posting_revenue.emp_div,
posting_revenue.sup_div,
posting_revenue.std_rate,
posting_revenue.std_cost,
posting_revenue.expense_type,
posting_revenue.prod_type_code,
posting_revenue.multiplier,
posting_revenue.totvatax,
isnull(posting_revenue.admin_amt,0),
posting_revenue.company,
posting_revenue.prod_rev_code,
posting_revenue.proj_rev_code,
posting_revenue.div_rev_code,
posting_revenue.serv_rev_code,
posting_revenue.intco_type,
posting_revenue.post_code,
posting_revenue.reason_rev_code,
posting_revenue.acct_date,
posting_revenue.acct_year,
posting_revenue.acct_period,
'N',
posting_revenue.acct_date,
posting_revenue.acct_date,
'N',
posting_revenue.timestamp,
'N',
posting_revenue.company,
0,
0,
0,
0,
project.ledger_curr_code
from posting_revenue, project
where posting_revenue.post_prefix = 'CEN'
and posting_revenue.post_no = 241917
and posting_revenue.proj_no = project.proj_no
-
I don't see anything wrong with your code Have you tried to change compatibility levels?
* Noel -
Are you referring to compatability at the db side? Change it back to sql 7.0?
-
YES!
* Noel -
I did try switiching the db compatability back to 7.0 and I'm still getting the same behavior. This does not make sense....
-
can you post the definition of : ft_work.dbo.rp_work
* Noel -
In sql2000 @@DBTS will change anytime a user on any connection makes a change to the
state of any table that has a timestamp column. It just gives you what the
last timestamp value that was used/store in a table.
Not sure the changes between 7 and 2000 but @@DBTS in sql2000 is unique at datbase level.
Eg. Sybase @@DBTS is unique at server level.
If you do "select @@DBTS" -- you will always get the same value regardless the number of time you run this query unless there are changes in a table where the exist a timestamp field.
You may need to alter your table.new_posting_key to timestamp type and let the system populates this field whenever there is an insert/update.
-
Could someone please run the below code and see if you get the same problem that I am getting with regard to the new_posting_key field is not displaying the updated timestamp value:
1. Create 2 tables:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[posting_revenue4]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[posting_revenue4]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rp_work3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[rp_work3]
GO
CREATE TABLE [dbo].[posting_revenue4] (
[record_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tran_date] [datetime] NULL
 
ON [PRIMARY]GO
CREATE TABLE [dbo].[rp_work3] (
[new_posting_key] [varbinary] (8) NULL ,
[type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[currdate] [datetime] NULL ,
[old_posting_key] [varbinary] (8) NULL ,
[tstamp] [timestamp] NULL
) ON [PRIMARY]
GO
2. Insert values into the posting_revenue4 table:
insert into posting_revenue4
(type, currdate) values ('T', '7/18/2005')
then insert two more records, changing the date to '7/19/2005' and '7/20/2005', so that there is a total of 3 records on the table.
3. Run the insert code below to insert the above values into the rp_work3 table. ** The desired result should be 3 records in the rp_work3 table, each having a unique value in the new_posting_key field (I'm currently getting the SAME value, which I am baffled, because the timestamp field is getting updated! PLEASE HELP!!!
insert into rp_work3
(type,new_posting_key, currdate)
select
posting_revenue4.record_type,
@@DBTS,
posting_revenue4.tran_date
from posting_revenue4
select * from rp_work3
-
* Please do the above table creation/code execution in sql 200 sp3. This currently works in sql 7.0.
-
Frank, try this:
insert into rp_work3
(type, currdate)
select
posting_revenue4.record_type,
posting_revenue4.tran_date
-
It will allow me to do the insert if I don't specify the @@DBTS function, but that does not get me what I need. I need the unique value for each new row inserted.
-
Frank I ran you code in SQL 2000 and is working as per BOL!
if you run your last inserts as
insert into rp_work3(type,new_posting_key, currdate) values ('T',@@DBTS, '7/18/2005')
insert into rp_work3(type,new_posting_key, currdate) values ('T',@@DBTS, '7/19/2005')
insert into rp_work3(type,new_posting_key, currdate) values ('T',@@DBTS, '7/20/2005')
then you will get one ts per transaction
if you run it with a select it will be only one transaction and you get the last value used right before start!
This is the way it is supposed to be in SQL 2000. I have no clue why it was working with the select in 7.0 as you said!
If you really need it to work like that here is a workaround!
create View dbtsfix -- View to accomodate the function
as
select @@DBTS as dbts7
create function fn_dbtsfix() -- Function to force Record by record evaluation
returns varbinary(8)
as
begin
return (select dbts7 from dbtsfix )
end
-- Your "new" statement
insert into rp_work3(type,new_posting_key, currdate)
select record_type , dbo.fn_dbtsfix() , tran_date
from posting_revenue4
select * from rp_work3
Good Luck!
* Noel -
Noel,
Thanks for explaining this behavior. I was unaware the the 'select' statement was considered one transaction. I guess the behavior changed since it was introduced in version 7.0. Anyway, your work around is exactly what I needed. Everything works now. Thanks for all your help!!
Frank
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply