I am busy designing an ETL application and was wondering if the Timestamp column would be a good way of getting only the changed data.
Background
We have approximately 35 similar OLTP systems with the same table structures (most of the time) in them. I am building a consolidated WH that will pull data from all of these and condense it into a single warehouse system. All of the data I require is replicated which I will use as my source. All of the required tables have got a Timestamp column on them. The replicate db only has the primary keys on the tables, no other indexes as the additional indexes place too much strain on the I/O system with every update/insert.
The idea is to use the Timestamp to only extract the new/changed data due to the volumes involved.
As far as I understand, the Timestamp is unique at a database level, so it would be possible to get the @@DBTS value before I start the extracts on each DB as my next starting point for all tables in that DB. Is this a correct understanding of the TS use?
Would it be worth converting the stored Starting Timestamp to BigInt, subtracting a number from it and converting it back. This would "go back in time" and bring changes in that would already be on the warehouse. This will try ensure that we do not miss any data if the timing goes slightly wrong.
This is going to be a very complicated ETL process so I am hoping that this method will allow for smooth accurate data extraction for the WH loads. Any input or feedback on this method would be very welcome.
Ok, i am finally giving in on this one and asking for some help! I am trying to set up a T-SQL Statement that will extract data from all the tables in the current database to a csv file including Column names!
I know that bcp can not handle the column names, so i tried to get around this with an append of the column names from a select, but unfortunatly the select gives me the names in Alpha order and not the order of the fields.
I have tried putting in an order by on the select, but this does not seem to have any effect. I have included the snippet of my script that is causing the problem here :
-- set up the echo command select @colcommand= 'exec master..xp_cmdshell' + ' ''' + 'echo ' + @names + ' >> c:cp' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + '.txt' + '''' from INFORMATION_SCHEMA.TABLES where TABLE_NAME=@TABLE
and just in case you are interested in the rest of the script, the full monster is included at the bottom of the post. Also if you can see any more efficient ways of doing what i am trying to do, please let me know!
-- Script to create a csv file of data from all tables inside current database
-- declare all variables declare @command varchar(200) -- command used for bcp declare @fetch_status int-- variable for fetch status in cursor
declare @TABLE varchar (200)-- Variable to hold table name declare @colcommand varchar (200)-- Variable to hold column creation command declare @count int-- Variable used to determine first itteration of Column loop declare @names varchar(100) -- variable used for the column names declare @delimiter varchar(10)-- variable used for delimiter in column names
SET @delimiter = ','-- set up the delimiter to comma select @count=0-- initialises the COUNT variable
-- setup cursor to create the bcp command to backup the data files to csv format declare bcpcommand cursor READ_ONLY FOR select 'exec master..xp_cmdshell' + ' ''' + 'bcp' + ' ' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + ' out' + ' c:cp' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + '.txt' + ' -c -t,' + ' -T' + ' -S' + @@servername + ''''from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'
-- setup cursor to pick up all the tables in the given database (used for column names section) declare dbtables cursor READ_ONLY FOR select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'
open bcpcommand
select @fetch_status=0
while @fetch_status=0 begin fetch next from bcpcommand into @COMMAND select @fetch_status=@@fetch_status if @fetch_status<>0 begin continue end
-- print 'Command to be run : ' + @COMMAND EXEC (@COMMAND) end
-- close and tidy up close runme deallocate runme
-- now create the fieldname files and then echo the 2 files together!
open dbtables select @fetch_status=0 while @fetch_status=0 begin fetch next from dbtables into @TABLE select @fetch_status=@@fetch_status
if @fetch_status<>0 begin continue end
SELECT @names = COALESCE(@names + @delimiter, '') + name FROM syscolumns where id = (select id from sysobjects where name=@TABLE)
-- due to the concatonation used, the second itteration onwards has a , attached to the front of the line -- this section removes the first char if @count <> 0 begin Select @names=SUBSTRING(@names,2,198) end
-- set up the echo command select @colcommand= 'exec master..xp_cmdshell' + ' ''' + 'echo ' + @names + ' >> c:cp' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + '.txt' + '''' from INFORMATION_SCHEMA.TABLES where TABLE_NAME=@TABLE
-- print 'COMMAND : ' + @colcommand
exec (@colcommand)
-- reset @names variable for next itteration, and set count to 1 to trigger IF above select @names='' select @count=1 end
-- close and tidy up close dbtables deallocate dbtables
I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:
[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid date format".
I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:
To convert Oracle timestamp to Sql Server timestamp
If Row.CALCULATEDETADATECUST_IsNull = False Then
If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then
dt = Row.CALCULATEDETADATECUST
Row.CALCULATEDETADATECUSTD = dt
End If
End If
I don't know if my code is right . Please inform, how i can achieve this.
For starters, please feel free to move this if it is in the wrong forum.
The issue I have is this. I have been asked to delete all information from a table that was inserted before May 12 this year. The issue is that when the DB was created, whoever designedd it neglected to add a timestamp column for the user data table (the one I need to purge). Does SQL, by default, happen to store insert times? Would it be something that might hide ina log file somewhere?
I have a timestamp in a date field of a table I have but when run it does not enter the date automatically and all I get is system.byte[] display where the dtae and time should be, how can i get it so this field places the date in automatically.???
I am retrieving data from a database, and have been noticing some really strange timestamps. Has anyone ever seen a timestamp that looks like this: 16777215Karls
I just started using SQL and i don't really know too much syntax.
I have an ASP page that is inserting data into a table called YellowAlerts. The first field in this table is AlertDate. I thought I read somewhere that there is a timestamp feature. If anyone could help me out that would be great.
I believe that all created tables have got by default a timestamp field. I tried to find out how to read this field with a Transact-SQL statement. And so I don't know the syntax, is anyone can help me ?
If i have a table with a 3 fields and one of them has a timestamp as a field type and I want to do an insert or update what is the value I am inserting for the column timestamp.
hello I put a time stamp in one of my column and it says binary data in the field. How do i make that binary data turn into a real date and time. If this is possible how do I separate the date from the time in the results or would I have to do this myself. Also, I need to sort the results by date can this be done?
Sorry for the stupid questions my sql book (sql demystified) does not elaborate. Thanks
hello, m new to sql server.can any one tell me is it possible to insert only time value in timestamp datatype. i have table which have datatype datetime.and i have to insert only time.i can't change the datatype datetime to any other one.p plz any one can suggest me the solution..
Hello I'm not a SQL Expert but i'm using it for a few months. Now i need to use a variable that everytime that is called to a select into it gives the timestamp (or Stamp) to put on a specific field. The problem is that i need this variable not in bd conotation (like 2007-02-12 12:00:44:33) but like this 2007021212004433. Can someone help me? Thanks in advance.
Is there any reason to have a TimeStamp column onto a table that has aPrimaryKey when using SQL Server and an Access front end?I read that doing so will always eliminate that write conflict error.But I notice that when pulling a recordset of 10K records, it takes 3Xless time if I delete the timestamp column on the table before runningthe SP.lq
Is there any chance to get the content of a timestamp field?I am trying to get the content via Visual Objects and I am receiving onlynothing.Not NIL but "".Is it possible to get any useful information out of this field in anylanguage?(btw I know that the MS-Timestamp is not a timeformat)THXJens
HI every one I have a timestamp column in my database. I want to use this column to get current changes but i am not able to get desired results when i use to compare that field in C#. Thanks in advance Take Care ALLAH HAFIZ
Hi folks, I am binding a gridview to a SQLDataSource that contains a timestamp column. My SQLDatasource contains an update method (via a stored procedure) that takes all of the data fields from the grid as input parameters. I am getting an error stating 'sql_variant is incompatible with timestamp' . The parameter definition for the update method specifies the timestamp column as type 'Object'. <UpdateParameters> <asp:Parameter Name="timestamp" Type="Object" /> ... </UpdateParameters> When the data is initially retrieved, the timestamp column is converted to a System.Byte[] however I cannot specify System.Byte[] in the parameter definition (different error) I have read other post with this issue but none seemed to have been solved (while still using SQLDataSource and binding).I have tried other datatypes and they do not work as well. Any help would be greatly appreciatedThanksTom
I have a database that I don't want to lock. I decided that before any updates can occur I would check a timestamp value and ensure that nobody else updated before I did (avoiding the 'last update wins' scenario).
I have a problem, I can read the Timestamp from the db when I read the record. I currently use the data to pre-fill a form (gee go figure ;) ) and the user changes some values and updates.
I don't know what to DO with the timestamp value while I am holding it. I have tried putting it into a hidden field on the form but the value does not seem to translate back and forth.
Do I have to store it as in memory as part of the session or can I somehow convert to and from text??
I have about 0 experience working with byte arrays so the best answer is one with an example.
Hi world,I would like to know how to maintain a table with a "timestamp" column. I mean a column that automatically set the current datetime when the row is updated.I don't want to go trought my application and set that in the update or insert command. I would like to know if there is a possibility of putting a trigger or something.thx
I have a table in SQL server 6.5 which has two columns. One is a timestamp column and another is an int column. Now I need to change the int column to numeric (16). For this I cannot use the alter table statement as it is not there in sql server 6.5. I tried creating another table with two columns- one as numeric(16) and the other as timestamp. I transferred the data from he old table to the new table and dropped the old table and finally renamed the new table as the name of the old table. But the problem is that the timestamp column now contains data that is different from the data that was there in the original column. I cannot update the timestamp column as that is done automatically neither can I insert the values. How do I make sure that the data in the timestamp column remains the same.
Actually 'TIMESTAMP' is used to monitor the INSERTION AND UPDATION on a table.Based on that 'TIMESTAMP', is it possible to extract any details by appling some QUERY in QUERY ANALYZER?. If anybody gives a solution to this I will be thankful to them.
I'm having trouble converting a timestamp value passed into a stored procedure as a char to binary(8). For example, the value passed in is '00000000000002CA'. I want to convert to a binary(8) value so I can use this in the where clause of a query.
I can't seem to get the conversion to work correctly.
hi all, I have a desktop edition of SQL server installed on nt.4.00.sp 5 from what i know..a timestamp field should insert current date & time...right? So.. if i run these statements: /****** Object: Table [dbo].[T1] Script Date: 12/7/99 10:45:49 AM ******/ if exists (select * from sysobjects where id = object_id(N'[dbo].[T1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[T1] GO
/****** Object: Table [dbo].[T1] Script Date: 12/7/99 10:45:51 AM ******/ CREATE TABLE [dbo].[T1] ( [column_1] [int] IDENTITY (1, 1) NOT NULL , [column_2] [varchar] (30) NULL , [column_3] [int] NULL , [column_4] [timestamp] NULL ) ON [PRIMARY] GO INSERT INTO T1 (column_2,column_3)VALUES ('new', 123) INSERT INTO T1 (column_2,column_3)VALUES ('new1', 1234) INSERT INTO T1 (column_2,column_3)VALUES ('new2', 12345) go SELECT cast(column_4 as smalldatetime) FROM T1 go
Hi! I have timestamp column and as you know the data there is not in readable format. what should I do to get normal date to find out when row was updated by user.
