locked
SQL Server 2005 - ROWVERSION with datatype timestamp RRS feed

  • Question

  •  

    Hi,

    i wanted to read some datasets from a table with the ROWVERSION. Then i wanted to save these records with the ROWVERION- Column in a temp table. Now it seems i cant explicitely write data in a ROWVERSION Column. As i understand its only possible to write a default value in such columns. Only SQL Server itself can write into ROWVERSION columns.

    Am i right with this meaning?

    Thx in advance...

     

    Greets Kamei

    Wednesday, May 28, 2008 11:22 AM

All replies

  • As you can read in BOL, they are semantically equivalent to using NOT NULL BINARY(8) or NULL VARBINARY(8).

     

    Example:

     

    create table dbo.t1 (

    c1 int not null identity(1, 1) primary key clustered,

    c2 rowversion not null,

    )

    go

     

    insert into dbo.t1 default values

    insert into dbo.t1 default values

    go

     

    select c1, cast(c2 as binary(8)) as c2

    into #t

    from dbo.t1

    go

     

    select *

    from tempdb.information_schema.columns

    where object_id('tempdb..#t') = object_id('tempdb..' + table_name)

    order by ordinal_position

    go

     

    drop table dbo.t1, #t

    go

     

     

    AMB

    Wednesday, May 28, 2008 11:46 AM
  • Thx for the help. Think its a good approach. But i have the problem that i need to insert records several times. And that is not possible with the select into method. SQL Server creates everytime the routine is called a new table. If the table exists i get an error message. Wanted to try out the cast operation with an insert select statement. But there i get the same error like before....

     

    INSERT INTO ##temp_tbl select UNITNO, cast(ROWVERSION as binary(8)) as ROWVERSION from [tablename] where FK_LOAD = 'value'

     

    Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

     

    At last i want to insert the actual tablename in the temp table in the same row as the selected record. The tablename is in a c# variable. Is it possible to make a statement which also writes the tablename in the temp table at once?

     

    Hope anybody can help.

     

    Greets Kamei

     

     

    Thursday, May 29, 2008 9:58 AM
  • Kamei,

     

    I do not understand well what you are trying to accomplish here; Why are you using a global temporal table?

     

    You can create the temporary table in advance, using CREATE TABLE statement.

     

    At last i want to insert the actual tablename in the temp table in the same row as the selected record. The tablename is in a c# variable. Is it possible to make a statement which also writes the tablename in the temp table at once?

     

    You will have to use dynamic sql, so I recommend you to read this article, before embarking in this ship.

     

    The Curse and Blessings of Dynamic SQL

    http://www.sommarskog.se/dynamic_sql.html

     

     

    AMB

    Thursday, May 29, 2008 12:26 PM
  • Im using a global temp table because i work with the database from a c# gui. Thats just for testing. My aims in this project are to build some c# assemblies that run on the server. But for testing i use the c# gui. With the assemblies i can use local temp tables. But from my test gui it is not possible to access them.

     

    Dynamic SQL isnt the problem in my case. I know the basics of this technic. I just didnt know how to include the tablename in the statement i pasted here. And that is the smaller part of my problem.

    My main problem is to get the ROWVERSIONs from my source tables to the temp table. Its like i said. Have to execute the select into statement (that was pasted here) several times. And that is not possible because SQL Server creates every time a new temp table and gives me an error. And the casts of the ROWVERSION for other statements dont work for me. Thats why i ask for an other solution to insert the ROWVERSIONS in one temp table more then once.

     

    Greets

    Thursday, May 29, 2008 12:52 PM