Replicating Across Time Zones

May 10, 1999

We're considering one database in the far east, using merge replication with a database in London.

There's a time difference of maybe 7 hours between the two sites - has anyone ideas on conflict resolution (thinking about the Far East updating a record at 16:00 their time, London updates the same record at 15:30 GMT - how does SQL Server know that London's time is the correct record to use?).

Any info gratefully accepted.

Thanks
Dan

View 2 Replies


ADVERTISEMENT

Handle Time Zones And Daylight Savings Time In SQL Server

Dec 19, 2006

I wanted to know how we can handle Time Zones and Daylight Savings Time in SQL Server 2000 as well as 2005.

Any pointers would be helpful.

Pranav

View 1 Replies View Related

SQL Date Problem With Time Zones

Nov 5, 2007

 I just started having this problem since the change of clocks for end of DST. I am running a client application that updates data in a SQL server via a web service.The Client app is located in the Easter Time Zone (EST)The Server is located in the Central Time Zone (CST)I have a database in which a date/time value is entered.  The Data Type for this field on the sql server is DateTimeHow i have it working is, I send the Table, Fields and Values from the client app to the web service.  The web service constructs the sql statement and executes it.  Similarly, when I want to get data, i send the webservice the Table and Filter, and the webservice returns a DataSet.The problem I am having, is when I send (Insert or update) a date to the WebService to update the SQL Database, it inserts/updates with the date I send.  However, when I request that data, it then adjusts for the time zone difference.Example:Lets say it is November 11, 2007 at 12:35:10.000, (EST)First, from the client, I insert a record with the date field as Now().  When I look in the SQL Database, I see the value is 2007-11-5 12:35:10.000  - Which is correct.However, when I do query on that record, it returns the value of 2007-11-5 13:35:10.000 - adjusted One Hour for the time zone difference, even though if i look back at the SQL Server, it still has the original 12:35 value Why is it adjusting for Time Zone difference only when I reqest data?  How can I stop this?  Or, How can I have it adjust on Insert/Update as well (I don't care if it adjust - i just need it to be the same/consistent on both ends)Thanks 

View 1 Replies View Related

Transact SQL :: Get List Of All Time Zones / Offsets And DST Changes

Aug 28, 2015

I need to build a reference table that will contain the time zone, offset value and DST changes for all time zones.  Is there a way I can pull this from the windows server hosting sql server?

View 4 Replies View Related

Converting UTC Datetime Values To Local Time Zones

May 20, 2008



I would like to convert a UTC datetime value to any Local Time Zone. Below is some code I'm trying to ensure works correctly. Anyone care to comment?

I have used the following lines before the function to obtain the 2nd parameter:


SELECT GETUTCDATE()

SELECT GETDATE()

SELECT DATEDIFF(hh,'2008-05-20 20:08:01.020', '2008-05-20 16:08:01.020')

DECLARE @UTC DATETIME

EXECUTE @UTC = UTCtoLocalDate '2008-05-20 20:08:01.020', -4


-- =============================================

-- Description: <Converts local datetime values to UTC datetime values, using the built-in GETUTCDATE() function.

-- <Parameter two (@TZ) represents the time zone difference from UTC/GMT. To obtain this value

-- <run GETUTCDATE() and GETDATE(), then determine the timezone from the difference between the two.>

-- =============================================

CREATE FUNCTION [dbo].[UTCtoLocalDate] (@UTCDate DATETIME, @TZ INT)

RETURNS DATETIME AS

BEGIN

IF ( DATEPART(hh, @UTCDate) <> 0 )

BEGIN

DECLARE @LocalDate DATETIME

DECLARE @UTCDelta INT

DECLARE @thisYear INT

DECLARE @DSTDay INT

DECLARE @NormalDay INT

DECLARE @DSTDate DATETIME

DECLARE @NormalDate DATETIME

SET @thisYear = YEAR(@UTCDate)

IF (@thisYear < 2007 )

BEGIN

SET @DSTDay = ( 2 + 6 * @thisYear - FLOOR(@thisYear / 4) ) % 7 + 1

SET @NormalDay = ( 31 - ( FLOOR( @thisYear * 5 / 4) + 1) % 7)

SET @DSTDate = '4/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

SET @NormalDate = '10/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

END

ELSE

BEGIN

SET @DSTDay = ( 14 - ( FLOOR( 1 + @thisYear * 5 / 4 ) ) % 7 )

SET @NormalDay = ( 7 - ( FLOOR ( 1 + @thisYear * 5 / 4) ) % 7 )

SET @DSTDate = '3/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

SET @NormalDate = '11/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

END

IF ((@UTCDate > @DSTDate) AND (@UTCDate < @NormalDate))

BEGIN

SET @UTCDelta = @TZ + 1

END

ELSE

BEGIN

SET @UTCDelta = @TZ

END

-- now convert utc date to local date

SET @LocalDate = DATEADD(Hour, @UTCDelta, @UTCDate)

END

ELSE

BEGIN

SET @LocalDate = @UTCDate

END

RETURN(@LocalDate)

END

GO

View 6 Replies View Related

SQL Express: Connection Trouble With Trusted/DMZ Zones

Apr 26, 2006

Here is the way SQL Server 2005 Express is currently setup:

SQL server has IP of 192.168.8.200. (DMZ zone - 192.168.8.*)

Web server has IP of 192.168.8.81. (DMZ zone - 192.168.8.*)

My workstation has IP of 192.168.1.100. (Trusted zone - 192.168.1.*)

So, I've been developing a web application that was stored in the trusted zone, and this application didn't have any problem connecting to the database on *.8.200. I even have SMSE running and I can manage the database without any connection problems. I moved the web application to the web server, without changing the web.config file, and now it will not connect from the webserver.

In the web.config file, the connection string is pointed to 192.168.8.200SQLEXPRESS. I'm confused because it seems that since the SQL server and the Web server are in the same zone, there shouldn't be any connection problem.

In the Firewall, I have port 1433 open for TCP/UDP for ANY -> ANY...so I think the firewall is configured correctly?

Please advise! Thanks!

View 3 Replies View Related

Reporting Services And Sharepoint Integration - Sharepoint Alternate Access Mappings (Zones)

Mar 2, 2007

I get the following error when I try to navigate to a report/model/data source, stored in a Sharepoint Document Library using a Sharepoint URL based on an Extranet or Intranet zone - but it works OK using the url for the Default zone:

System.Web.Services.Protocols.SoapException: The specified path refers to a SharePoint zone that is not supported. The default zone path must be used. ---> Microsoft.ReportingServices.Diagnostics.Utilities.SecurityZoneNotSupportedException: The specified path refers to a SharePoint zone that is not supported. The default zone path must be used.


Is this a limitation of the SQLRS-WSS3 integration or is there a workaround?

Many thanks to anyone who can help!





View 22 Replies View Related

How To Convert UTC Time (retrieved From SQL) To Local Time In Reporting Services Based On Time Zone

Aug 7, 2007



Hi all,

I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.

I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.

For example if the date is August 07, 2007 10:00 AM UTC,

then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.


Similarly for other Time Zones it should display the time accordingly.

Is this possible in SSRS 2005?

Any pointers will be usefull...

Thanks in advance
sudheer racha.

View 5 Replies View Related

Replicating From 7.0 To 6.5

Aug 30, 1999

I need to replicate from a SQL 7.0 server to SQL 6.5 server.
Has anyone done it, or does anyone know if it can be done?


Thanks in advance

Joe Gentile

View 2 Replies View Related

SP2 Replicating With Pre-SP2?

Jul 23, 2007

Hi gang,



I'm 99% sure this is possible, but I wanted to confirm before I go upgrading one box in our replication scheme without having to do all the others (which are geographically dispersed):



Can an SP2 box replicate (merge replication in our case) with pre-SP2 servers? Most of our servers don't even have SP1 applied, and we're ready to upgrade, but I want to be sure I can do them one at a time rather than all at once.



Cheers,

Matt

View 9 Replies View Related

Replicating Views

Jan 11, 2002

Can we replicate views in SQL 2000 or SQL 7.0 ? If yes please let us know How


Thanks

View 1 Replies View Related

Replicating Permissions

Feb 22, 2001

We are performing snapshot replications from one server to another, however the permissions are not being taken across.

When we manually reset them, the next replication will quite happily remove all permissions.

Any help appreciated.

Peter

View 2 Replies View Related

Replicating Design Changes

Mar 5, 2005

I am trying to create an auto off-site backup of an entire database. This would include databases and users. It should also include changes made throughout the day.

Something challenging about this is I want it to also include design changes that may have been done throughout the day.

I understand log shipping or replication can deal with the data part of my solution. But how can I copy over the logins, users, and design changes?

Is it possible to have design changes replicated from publishers to subscribers?

View 3 Replies View Related

Replicating Database

Mar 5, 2004

I am using Sql2000 and have 6 servers. On this 6 servers 4 servers have the same database

MY QUESTION is i need a script or advice that will help to do this:

Every time data chages in 1 of the 4 servers that have the same database, I want all changes to happen in the other 3 so that they must always have the same information

View 1 Replies View Related

Replicating SysTables

Apr 21, 2004

Good Day Gurus

I posted a question about replicating logins to the database and the answer i got about doing a DTS to transfer logins is not good for me, is it possible to replicate syslogins tables so that i can do this if so, how because they are not listed Database and Publications when i try to create a publication, only individual created databases can be seen.

The reason for this is because when the DBA decides to change the user permission, i want the info to be merge replicated to the subscriber. At this moment i can run the DTS to transfer the login but it won't know when the logins have been updated and hence i won't know when to run it.

Thanks in advance

View 2 Replies View Related

Replicating Data

Mar 31, 2008

Hi,

I am very new to SQL Server. Plenty of SQL knowledge but the whoe SQL server enviornment is new.

I am working with SQL Server 2005. My task is to generate reports without affecting our live database. I have setup a second server and installed SQL Server 2005 on that too. My thought was that maybe I could mirror or replicate the table I require over to this new server and run my queries from here. Is this easy to do ?

I read that mirroring might not work as it is solely for back up /fall over purposes and that data on the mirrored server would not be accessible.

I have also been looking at SSIS but at the moment this is all a bit like double dutch to me ! Can anyone point me in the right direction, preferably somewhere beginner friendly ie not overly complicated !!

Thanks a mill,

Sophie

View 3 Replies View Related

Replicating DDL In Sql 2005

Aug 11, 2006

I am using Sql 2005 and merge replication. I am relying on the feature where schema changes are replicated to subscribers but I have come across a situation where schema changes stop being replicated.

This is the scenario:

I create a database and publish it for merge replication.

I add subscribers.

If I need to change the published database I can use ALTER TABLE ddl and the subscriber gets the changes.

If I have to add or remove a merge article as part of a database change I specify the @force_invalidate_snapshot=1, @force_reinit_subscription=1. No any ALTER TABLE statements following the article change will NOT be replicated.



Is this a known 'feature'? Is it because @force_reinit_subscription is set to 1?

Can anyone help me?

Graham

View 5 Replies View Related

Help Please Some Data Not Replicating

Oct 10, 2006

Hello,

We have four mobile devices that are set up for merge replication via the web. We are not receiving errors but some of the data is not coming over to the devices. If we manually add a record that record will come over, but there is data that is on the server that isn't on the devices. If we run the snapshot for each device (We're using host_name as a filter) nothing happens. If we do validation check we get errors. If we reinitialize all devices it works but the next days data (sql job populates data to the publisher db at night) isn't on the device after syncing the next morning. Any help would be appreciated.

John

View 5 Replies View Related

Replicating UDT's To Subscribers

May 21, 2007

I have a sql 2005 publisher and distributor and a sql 2000 subscribers. for some reason on one of the subscribers i'm getting errors that it can't replicate the UDT's. i tried a new snapshot and made sure it was set not to replicate UDT's but i'm still getting Create Type errors.



would anyone have any idea why it's trying to create UDT's at the subscriber when i specify not to replicate UDT's?

View 5 Replies View Related

Replicating Schema Changes

Jun 26, 2006

Hi there,

I'm using Sql 2005 merge replication and I have noticed something, I'm not sure if this is true or not:...

My publication is set to replicate schema changes (replicate_ddl = 1). Now, I have noticed that schema changes are only replicated if the current snapshot is valid. Is this right? If so why?

My next question carries on from the first. If I'm about to run a TSQL script on my publisher that will add a column or two to a published table, how do I ensure my snapshot is valid inorder for the ddl changes to be replcated? Should I be using:

EXEC sp_mergearticlecolumn

@publication = <publicationname>,

@article = <article name>,

@force_invalidate_snapshot = 1,

@force_reinit_subscription = 1

on each table I modifiy, after I have added the new column?

Thanks for your help

Graham

View 30 Replies View Related

Replicating Schema Only

Mar 13, 2006

Hi,

I have a database that is being set up for merge replication (Sql 2005), but there is one table that I only want the schema replicating, not the data - I never want the data to be replicated in either direction. I can see from sp_addmergearticle that you can do something like this for sp's or functions but is it possible to do this for tables?

Regards

Graham

View 1 Replies View Related

Replicating Roles

Nov 13, 2006

Hello,

I use a merge replication between Sql Server and Sql Server Express.

When I enable a DB for .NET features (eg RoleManager), I have new tables and roles that are created and some GRANT are given on SPs.

When I replicate these DB to another one, none of my roles are replicated and I also loose my roles. Is there a way to replicate also the roles and the permissions ?

Thanks

Pierre-Emmanuel Dautreppe

View 1 Replies View Related

Replicating From SQL 2000 To 2005

Jun 26, 2007

I am wondering if there is a way to replicate changes in a SQL 2000 DB to 2005 without backing up the DB and restoring it in 2005.
We are running an ERP system using SQL 2000 and are moving to a later version that supports 2005 and we want to test it out before going live but I'd like to sync with the current system from time to time instead of having to convert the DB and get it ready again and again everytime I want to update the data.
 Thanks for any help you can offer,
Chad

View 1 Replies View Related

Replicating &#34;join&#34; Tables

May 13, 2001

Hi,

I have a number of "join" tables ie joins records from two other tables for example, an employee may be responsible for more than one product so the join table would look like this:

table name: employee_products
Employee_id foreign key from employee table
product_id goreign key from products table


My question is, how do I replication this table? Replication requires all table to have a primary key field. In this case, both fields are foreign keys and I dont have a primary key as the same data appears regularly in either field.

How should I get around this so I can implement replication? I dont want to have to add another field to be the primary key field.

Thanks

View 1 Replies View Related

REPLICATING STORED PROCEDURE

Apr 4, 2000

Hi,
My transaction replication is working perfecly.
I am doing some changes at publication database ,altering stored procedure
code , now i want that changes should also take place at subsciber
any one has idea please suggest me
Thank u.
Nil

View 2 Replies View Related

Replicating Structure As Well As Data

Dec 27, 1999

Are there any known problems with replicating structure changes (New table, etc...), as well as data in MSSQL Server 7.0?

View 2 Replies View Related

Replicating Tables In SQL Server

Sep 23, 1998

I have a table that is used for reporting, the problem is that the data in the table is refreshed every 30 minutes with a bulk insert. I am trying to find a way to have two tables that are mirror images of each other and when the loading table is loaded, then the table assumes the identity of the reporting table. The basic prinicpal is I need to have the table be available almost all the time and when the bulk insert is happening, users cannot query. Any help would be greatly appreciated.

Thanks in advance
Marc Brown

View 1 Replies View Related

Replicating Data Across Servers

Jun 17, 1999

We have a database that I would like to replicate on another server but am unable to use regular replication via publish/subscribe due to the fact that the production database has no primary keys on tables, only clustered indexes. The backup db needs to be as close to real-time synchronized as possible and will be in fairly active use most of the time. Has anyone had success in developing such a system? How did you do it and what are the pitfalls? Any advice would be greatly appreciated. Thank you.
W.

View 1 Replies View Related

Replicating Data Over The Internet

Mar 5, 2005

I have SQL Server running on my internal LAN. I want to have a second SQL Server running on a hosted (shared) website. I then would like these servers to talk to each other. At some scheduled time I need to publish data to the web, and I then need to subscribe to data input on the web by various clients. My internal LAN can see the Internet via our cable modem.

What is the best way to do this? What software will I need to run. I'm looking for the big picture.

Thanks for the help everyone.

View 4 Replies View Related

Replicating To An Archive Database.

Nov 27, 2005

I want to replicate to an archive database. This means that the subscriber will have data that has been removed from the publisher. In my reading, I haven't seen any discussion of this specific scenario.

Here's what I imagine the solution might be:

EXEC sp_addpublication_snapshot
@publication = N'My_Publication',
@frequency_type = 1 -- only create the snapshot once
GO

...

EXEC sp_addarticle
@publication = N'My_Publication',
@article = N'My_Table',
@source_owner = N'dbo',
@source_object = N'My_Table',
@del_cmd = 'NONE',
GO

I set the publication snapshot to only execute once, that would be during the maintenance window when it is initially installed. Then, on the tables that will contain archived data, I specify that deletes aren't replicated.

Here's my concern: aren't there times when you need to resync?

If you could push a new snapshot that dropped the tables on the subscriber and built the thing up from scratch, then things would sync-up just fine. But in this scenario if you drop the subscriber tables then you've just lost your archive.

What's the best way to handle this?

Thanks,

-=michael=-

View 6 Replies View Related

Replicating Only One Table, What's The Best Approach ?

Apr 6, 2008

I have a single table on database A that needs to be replicated to two databases B and C in as-near-to-real-time as possible. The table is cleared every morning, and receives about 40,000 records per hour. Records are only added to the table, never updated or deleted.

I'm wondering what is the best approach to replicate this table :

1 - Transactional Replication
2 - A trigger on the INSERT on table in A that then inserts the record into B and C
3 - Timed jobs running on B and C that pull new records from A

thanks all in advance.

View 1 Replies View Related

Replicating Using ODBC Connection

May 7, 2007

Hi

I have EE 05 installed and need to replicated tables from an odbc connection. How would i go about doing this? I cant find a way to like import tables that i need from the odbc connection.

Thanks!

View 3 Replies View Related

Replicating Over Citrix Metaframe

Jan 21, 2008

What do I have to do to set up replication over Citrix Metaframe? Right now, the best I can do is to export tables from one database, and then import them into the other database over the Citrix link.

Thanks in advance for any help.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved