How Can I Maintain Timestamp Column In Replication Environment
May 27, 2008
Hi Friends
I have transactional replication,
The publisher DB contains table call Courser with timestamp column, this column values are unique for the publisher DB
The subscriber DB also contains same copy of data in publisher DB Course table, but the timestamp column values are different.
So my problem is how can I keep this two tables (Course) identically, (same timestamp column vales in both table)
NOTE: Publisher and Subscriber DB reside under two different SQL server instance
We are setting up the clients' database with transactional replication that allows the subscriber to send updates by pull subscriptions.
The plan is to create a second database so the users can keep working on the original database while we get the replication set up and working. Once we feel confident that the replication is working on the second database, the plan was to backup the old one, then detach it, and then restore the new database with the latest data from the old one. My question is whether this would preserve all the replication configurations that we set up? Since the replication adds a column to each table what would happen to that column? Or what is an alternative, which would allow us to set up the replication without disturbing the user's work, and then implement the replication with the latest data?
I am also wondering how to set the synchronization to happen once daily? I do not see where I can set that. I only see options for continuous vs on demand. Does on demand mean I can somehow schedule with an external program to run once a day?
Thanks for your patience, and I hope my questions make sense.
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?
Hi, from the url--- http://support.microsoft.com/default.aspx?scid=kb;en-us;820675 I did the steps to implement the replication. However, i did not include a timestamp column in the tables. Is it important as to what would happen if i did not include this? I have the rowguidcol though. I thought this could would help to identify the various row. Help pls?
The docs state that a timestamp column is required for bi-directional replication. I am not syncing the databases since they are too big and it is a new project so I can guarantee at start that there will be two exact copies of data. The data is being converted from a FoxPro db to sqlserver.
The question is: How do you design the timestamp columns for partioned bi-directional replication? Does the publisher get the timestamp and the subscriber get a binary(8)? Since there are two publishers is a distinct timestamp and binary pair required for each server? See below...
Do the binary(8) columns need to be removed from the articles? Can anyone explain how the timestamp is used to stop loop back? Is the loop back check done on the Publisher or Subscriber?
I have a production server log shipping to a secondary server every 30 minutes (both SQL 2000), which the second server is used for both a warm standby server and for reporting from users. Issue: the log shipping locks the DB so reporting can't be done until the load is finished, the load to the second set of databases has taken up to 15 minutes to finish allowing the users only 15 minutes to run reports, this is not acceptable. The server also needs to be used for DR.
I am looking for another solution, I can't use Transactional Log shipping as not all of the tables in the databases have a primary key identified. So, I am looking for a real-time or near real-time reporting server that is more available to running reports and a warm standby server for Disaster recovery. I am trying to figure out what SQL Server 2000 has to provide (or even 2005 or 2008?) or I am also looking at some third party software, but not sure what is the best for a reasonable price.
I am trying to replicate tables in a sql server 6.5 which contain the timestamp field. An error occurs and it shuts the replication task off and it says it cannot update the timestamp field. If I choose vertical partitioning and do not replicate the timestamp field then an error occurs that insert value list does not match column list. Is it the case that the subscriber cannot have this field in the table? Besides deleting the timestamp field from the table completely, is there another solution or am I forgetting to set something?
1) I have the Active/Passive cluster environment (using Microsoft cluster service). 2) SQL2000 is installed on the cluster (Virtual instance). 3) Virtual instance of SQL Server is used as Publisher in Transactional replication with updatable subscribers. 4) Subscribers are passing updates to subscriber using MSMQ 5) The problem is : after I establish replication , the Queue Agent fails with following error (“Failed to initiate search for MSMQs”)
We are working in a Merge replication environment where we have SQL Server 2005, 11 publications and 2 subscribers.We used to get lot of incidents from the Application owner for blockings, recently we faced a situation where the lead blocker is in sleeping state and the session was used by the merge agent.Checked the query that the session was running, it was sys.sp_MSenumgenerations90;1.
We are running 2 SQL Server and both run in failover clustered Environment. The Problem is now we need to Replicate a Database from one Virtual SQL Sever to the other.
The Second one (clusterd environment)is stroing their database localy while the First One (clustered environment)is storing database in a shared storage. Note that Both Server are used for sperate purpose , but we now need to set replication on the Other Clustered Setup for Reporting Purpose.
Will it work if we configure replication from One SQL Server Clustered Setup to the Other Clstered Setup. If yes, then please let me know how it can be done ?
I created transactional replication on a database and setup pull subscriptions on each subscriber to run at a scheduled time once a day. The scheduled start time on each subscriber can differ. The transaction log on the publishing database will eventually consume all possible disk space. Is it possible (and safe) to shrink or truncate the transaction log file for the publishing database before all the subscribers completed running its daily pull subscription? If not, how can I manage disk space for the transaction log on the publishing database and ensure all transaction are replicated to the subscriber?
Setting up Transaction Replication in test environment. I am willing to bet that most of you take a production backup (if so, how, and using what?), restoring the database to your test environment, then running a snapshot to your subscriber and away you go.
But perhaps you take a backup of your publisher and subscriber, if so, how do you know there are no inconsistences because there were transactions sitting on the distributor?
What do you do if you have additional indexes on the subscriber for reporting, that are not on the publisher?
Here at work we are having issues with getting consistent databases set up with T Rep, missing rows, duplicate keys at subscriber etc. How to avoid these issues.
I have a mobile device application using mobile sql 2005 replicating with sql 2000 in a x86 environment. This works fine!
I'm having issues getting this to work under Windows Server 2003 X64.
I've got all the components installed under the X64 environment including CLR 2.0 X64 and the mobile sql tools. the but when I run the Configure Web Synchronization Wizard I get the following error. SQL Server 2005 Mobile Edition Server Tools were not found on the IIS server. Run the SQL Server 2005 Mobile Edition Server Tools installer....
My question is: Were do I get the X64 version of these tools?
sqlce30setupen.msi sql2Ken@P4.msi
The SQL environment is X86 as follows: SQL2000 SP4
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
I have a table with a [Timestamp] field which is a datetime data type. What I need to do is add one hour to the timestamp. Each entry in the [Timestamp] field looks like this: 2008-03-09 16:44:06.313
What is the best way to do this?
(Should I be using UPDATE [Tablename] or Alter Table [Tablename])
ASP.NET application, MS SQL DB, and a table with a timestamp(8) column.
Error: Cannot insert a non-null value into a timestamp column. Use INSERT with a column list or with a default of NULL for the timestamp column.
I'm using an SQLDataAdapter and typed DataSet, inserting a row into the dataset, and calling Update() to send changes to the DB.
The thing that baffles me about receiving this error, is that the DB column can be null and I get this error wheter I attempt to insert NULL or a valid byte array into the column.
(No - I am not trying to insert a datetime into the column.)
The error just doesn't seem too descriptive of the problem I'm having, and I'm quite confused.
I have a question regarding the rowversion / timestamp column. I want to use it to track changes in some tables and I created a non clustered index on the timestamp column (with three includes) to get a better performance while searching / gathering the data. It works very well, but I experienced a big performance hit on the inserts and my first thoughts are that it has something to do with this index.
I know that this NCI has to be updated with every insert and so this could have an impact on performance, but we have a lot of other indexes as well, which are also get updated with every insert. Why this index / column has such a big impact? Our application use a lot of dynamic cursors, maybe it has something to do with that?
I can not insert CURRENT_TIMESTAMP into column type timestamp I defined a data Table that has column type timestamp. I did not achieve insert CURRENT_TIMESTAMP data into column typed timestamp. My statement is below.What is wrong with it? Thanks set @cmdS2 = 'Insert Into TABLOLAR Values(' + CHAR(39) + @TABLO + CHAR(39) + ',' + CHAR(39) + @TABLO_ACIKLAMASI + CHAR(39) + ',' + CHAR(39) + CURRENT_TIMESTAMP + CHAR(39) + ',' + CHAR(39) + @KLLNC + CHAR(39) + ')'
I have been provided with a table where one of the columns is of TimeStamp data type. My question is how to insert and update data in this column through my SQL Statement? When I run my SQL statement, it gives me an error with this column name in the error.
Im trying to insert the values from this query into a table, so I can later check the history of memory usage:
SELECT [total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB] ,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB] ,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB] ,[available_page_file_kb] / 1024 AS [Available_Page_File_MB] ,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB] ,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB] ,[system_memory_state_desc] AS [System_Memory_State_Desc] FROM [master].[sys].[dm_os_sys_memory]
What I'm missing is a way to insert the current timestamp every time I insert to the table.My plan is to use the insert into command.
I am using SSMA 6.0 for DB2. When trying to migrate data with a table have timestamp column, it fails with an error "Hour, Minute, and Second parameters describe an unrepresentable DateTime." however i don't see any issues with the source data.
What I'm after is to create a new [dtP] column where the [dtP] is the [P] value at least 5 seconds later. I would assume I should use the dateadd() function but I've tried [Timestamp] = dateadd(ss,5,[Timestamp]) in my WHERE clause and to no avail. Any suggestions on the best way to proceed?
We're trying to perform an upgrade test against a copy (backup/restore) of our customer database as target. There are some tables with timestamp column in the database. The way we do this is by having a database project with a publish profile targeting that copy of customer database and then with TFS build server is used to build the database but only to generate a publish script (/p:UpdateDatabase=False) set in the build definition - msbuild argument.
Example of table definition:
CREATE TABLE dbo.CodeTable1 (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY ,Code CHAR(6) ,[Timestamp] TIMESTAMP NULL);
We would like to have the "Code" column to have CHAR(7), so in the project we modify the table definition:
CREATE TABLE dbo.CodeTable1 (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY ,Code CHAR(7) ,[Timestamp] TIMESTAMP NULL);
Expecting SSDT build will generate alter script:
ALTER TABLE dbo.CodeTable1 ALTER COLUMN Code CHAR(7);
To our surprise the generated script was:
ALTER TABLE dbo.CodeTable1 ALTER COLUMN Code CHAR(7); ALTER TABLE dbo.CodeTable1 ALTER COLUMN [Timestamp] TIMESTAMP NULL;
Which will cause error when the script is executed: "Cannot alter column 'TIMESTAMP' to be data type timestamp."
Why is SSDT generating the change script for that timestamp column??
We then try a local build in VS, the issue is not happening, SSDT correctly generates alter script only for the "Code" column to CHAR(7);
Both local machine and TFS Build server are having VS 2013 Update 4- SSDT 12.0.50318.0 installed.
As we tried to troubleshoot further, we found out that it seems it only happens on a restored database from a backup copy of our customer database. It doesn't happen for databases created by SSDT build from scratch or that we manually created. We've tried make sure all database properties are the same as the database that correctly built.
But still if the target database is the one we restored from a customer's copy, SSDT always tries to alter timestamp column (on server build).
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Disallowed implicit conversion from data type datetime to data type timestamp, table 'ClientDB.dbo.timestampTable', column 'c2'. Use the CONVERT function to run this query. at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source) at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source) at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken(Unknown Source) at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(Unknown Source) at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(Unknown Source) at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown Source) at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType(Unknown Source) at com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown Source) at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown Source) at com.microsoft.jdbc.base.BasePreparedStatement.post ImplExecute(Unknown Source) at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown Source) at com.microsoft.jdbc.base.BaseStatement.executeInter nal(Unknown Source) at com.microsoft.jdbc.base.BasePreparedStatement.exec ute(Unknown Source) at JDBC.TestSQLServer.testTIMETAMPDataTypes(TestSQLSe rver.java:75) at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:324) at com.daffodilwoods.tools.testworker.TestRunner.runM ethod(TestRunner.java:159) at com.daffodilwoods.tools.testworker.TestRunner.runI nSequence(TestRunner.java:83) at com.daffodilwoods.tools.testworker.TestRunner.runT estCase(TestRunner.java:4
After several hours of trying, I trow the towel in the ring and come here to ask a question.
Source system uses a timestamp column in the transaction tables. which is equal to a non-nullable binary(8) datatype (sql 2000 bol).
What I want to do is get the timestamp at the start of the transfer and at the end of the transfer of data. and store these in a controltable
I try to do this in 2 sql execute tasks:
sqltask 1: "select @@DBTS AS SourceTimestamp" and map the resultset to a variable. Here come's the first problem what variable type to take ?
DBNULL works (meaning it doesn't give errors) (BTW: is there a way to put a variable as a watch when debugging sql tasks ?)
INT64 and UINT64 don't work error message that types for column and parameter are different
STRING works
Then I want to store this variable back in a table of a different data source
sqltask2: "insert into controltable values(getdate(), ?)" and make an input parameter that takes the previous timestamp ...
if I took DBNULL as a type for the variable there doesn't seem to be a single parameter type that works ???
if i take STRING as a type for the variable I have to modify the sql to do the explicit conversion from string to binary so I change CAST(? as binary). It doesn't return any error but the value stored in the table is 0x00000000000 and not the actual timestamp.
Any help on this one ? Why are the INT64/Bigint not working here, you can perfectly do a convert(bigint, timestampfield) in sql ?
How came the SQL datatypes, and the variable datatypes, parameter datatypes are so badly alligned to each other (and all seem to use different names) ?
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.
I cannot manage to fetch the new timestamp value inside a TSQL Transaction. I have tried to Select "@LastChanged" before committing the transaction and after committing the transaction. A TimestampCheck variable is used to get the timestamp value of the Custom Business Object. It is checked against the row updating to see if they match. If they do, the Update begins as a Transaction. I send @LastChanged (timestamp) and an InputOutput param, But I also have the same problem sending in a dedicated timestamp param ("@NewLastChanged"): 1 select @TimestampCheck = LastChanged from ADD_Address where AddressId=@AddressId 2 3 if @TimestampCheck is null 4 begin 5 RAISERROR ('AddressId does not exist in ADD_Address: E002', 16, 1) -- AddressId does not exist. 6 return -1 7 end 8 else if @TimestampCheck <> @LastChanged 9 begin 10 RAISERROR ('Timestamps do not match up, the record has been changed: E003', 16, 1) 11 return -1 12 end 13 14 15 Begin Tran Address 16 17 Update ADD_Address 18 set StreetNumber= @StreetNumber, AddressLine1=@AddressLine1, StreetTypeId=@StreetTypeId, AddressLine2=@AddressLine2, AddressLine3=@AddressLine3, CityId=@CityId, StateProvidenceId=@StateProvidenceId, ZipCode=@ZipCode, CreateId=@CreateId, CreateDate=@CreateDate 19 where AddressId= @AddressId 20 21 select @error_code = @@ERROR, @AddressId= scope_identity() 22 23 if @error_code = 0 24 begin 25 commit tran Address 26 27 select @LastChanged = LastChanged 28 from ADD_Address 29 where AddressId = @AddressId 30 31 if @LastChanged is null 32 begin 33 RAISERROR ('LastChanged has returned null in ADD_Address: E004', 16, 1) 34 return -1 35 end 36 if @LastChanged = @TimestampCheck 37 begin 38 RAISERROR ('LastChanged original value has not changed in ADD_Address: E005', 16, 1) 39 return -1 40 end 41 return 0I do not have this problem if I do not use a TSQL Transaction. Is there a way to capture the new timestamp inside a Transaction, or have I missed something?Thank you,jspurlin
How to get the details of a stored proc or sql query which updated a particular table for specified time stamp or interval. Is there any query to get this?
