Replication And Datetime Columns
Jun 26, 2007
I have an interesting server occuring. We have transactional replication running with 1 pub and 6 subscribers. On nearly all the tables we have some sort of date column that's a smalldatetime. If I look at the pub the data will appear like:
2006-11-13 09:59:00
However, on all the subs it appears like:
2006-11-13 09:59:00.000
It's the same column and record but for some reason it's become a datetime. I checked the column types and they are both smalldatetime's.
Why is this happening? What can be done?
Thanks,
Phil
View 3 Replies
ADVERTISEMENT
Sep 16, 2006
Hi,
I am planning to use transacational replication (instead of merge replication) on my SQL server 2000. My application is already live and is being used by real users.
How can I ensure that replicated data on different server would have exact same values of identity columns and date columns (where every I set default date to getdate())?
It is very important for me to have a mirror image of data (without using clustering servers).
Any help would be appreciated.
Thanks,
-Niraj
View 1 Replies
View Related
Sep 26, 2006
hi, is there a function in ms-sql to get the date only from a datetimecolumnexamplecolumn 2006-09-26 00:00:00i would like to see 2006-09-26tiaDave
View 4 Replies
View Related
Nov 30, 2007
We're experiencing the same issue as a few others that have posted on this forum (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=971931&SiteID=1) but I haven't seen any resolutions. The error is: There was an error with input column "MODIFY_DT" (4598) on input "OLE DB Destination Input" (4357). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
The package is executed from a SQL Server Agent job. After the failure, we can run the exact same job without changing any data in the source or destination tables and the package will work. Within the Advanced Editor the data type is consitent database timestamp [DT_DBTIMESTAMP] for all data flow components and the data type is consistent within the source and destination tables as well.
Any ideas????
Many thanks in advance!
View 11 Replies
View Related
May 3, 2008
Hi All,
I'm trying to find an efficient and elegant way to truncate datetime columns to a whole date. Currently, when I perform this operation I use something like the following.
TSQL: Select Convert(datetime, Convert(varchar, column_name, 112)) from table_name
PSQL: Select trunc(column_name, 'DDD') from table_name
I've been using this TSQL code ever sense version 7, but I know there has to be a more efficient way other than converting to a varchar and back to a datetime. Over the years, I've tried looking for a more suitable method to perform the conversion in TSQL but this has always been the most practical and efficient method. The PSQL select statement is fast and graceful.
Thanks
View 7 Replies
View Related
Feb 5, 2008
Ive created a Data Access Layer (xsd file) and some of the tables have DateTime columns. I need to allow NULL values to be saved, but when I try to do that I get an error
In my code (c#) ive used Nullable DateTime, but when I try to assign a nullable Datetime to a DateTime column, I get this error
cannot implicitly convert type 'System.DateTime?' to 'System.DateTime'
I understand the reasons why, so how do I save null DateTime values to the underlying database through a schema ?
In the schema file, the DateTime columns have <DBNULL> as the default value, AllowBDNull is true, but I am only allowed 'Throw exception' on the NULL value property
I could probably do this another way (by not using a schema and writing the code myself) but Id prefer to keep the strongly typed code.
I dont want to use MAx or MIn values for the default datetime values, so how can this be done ?
View 3 Replies
View Related
Jun 13, 2000
Does anyone have a recommendation for creating an index on a datetime column?
We use alot of dateranges in our statements and none of them perform very well.
Thanks
Pete Karhatsu
View 1 Replies
View Related
Jun 7, 2006
Hi All,Currently the query returns 2006-03-27 00:00:00, can I make it output03/27/2006, I want to truncate the time, and replace the hyphens withforward slashes. Any ideas?Thanks In Advance,~CK
View 6 Replies
View Related
Nov 7, 2007
Hi,
I need to convert all datetime columns to smalldatetime in the whole database. I really don't want to do it by hand and It would probably take me a whole day to figure out how to write such a procedure. If someone could help me out that would be great.
My database is divided into schemas just like AdventureWorks.
Also, no need to worry about date conversion, value could be set to current date.
Thanks
View 14 Replies
View Related
Apr 3, 2007
Hi there,
I'm trying to unpivot a table in SSIS: The pivoted table basically looks like
ID DATE1 TEXT1 DATE2 TEXT2
----------------------------------
ID1 D1 T1 D2 T2
...
The unpivoted result looks like
ID DATE TEXT
-------------------
ID1 D1 T1
ID1 D2 T2
€¦
It works, but the one problem I am facing is: If D1 IS NULL in the pivoted table then D1 in the unpivoted table contains some strange value that is neither NULL nor a valid datetime. In a data viewer I get the in the DATE field "Fehler: Die Parameter Year, Month and Day beschreiben eine nicht darstellbare Datetime." (i. e. "Error: The parameters Year, Month and Day form an invalid datetime."). The loading of the OLE DB target yields an error because the DATE value cannot be converted to a valid datetime.
Anybody having a solution to this?
View 6 Replies
View Related
Apr 22, 2015
I have a view in my database detailing the expiry date of each credential for each employee. The view is designed as to display one record per employee and in that record is the expiry date of each credential and the days remaining. So the columns are as follows:-
Employee CodeExpiry Date (x8 columns) (named as credential e.g. [Passport])
Days Remaining (x8 columns) (named as "TS_" + Credential)
I'm trying to use the CASE function to compare each DATETIME column with one another and retrieve the minimum. How can I return the minimum date as a run-time column and sort the view by this column? My code is as follows:-
SELECT [Passport],[TS_Passport],[Visa],[TS_Visa],[Civil_ID],[TS_Civil_ID],[KOC_Pass],[TS_KOC_Pass],[JO_Pass],[TS_JO_Pass],
[Ratqa_Pass],[TS_Ratqa_Pass],[Driving_License],[TS_Driving_License],[Health_Book],[TS_Health_Book], CASE
WHEN Passport <= Visa AND Passport <= Civil_ID AND Passport <= KOC_Pass AND Passport <= JO_Pass AND
[code]....
I've been told that this is the most efficient given the number of records in my database. The Min_Date is always NULL. I need the minimum of the 8 dates to be the Min_Date.
View 9 Replies
View Related
Mar 30, 2007
I used this query to get a result
select round(cast(DateDiff(ss, convert(datetime,rf.RECVD_DTTM), convert(datetime,con.ARRIVED_DTTM))/60 as float)/60,2) as LengthOfTime
from customer rf
but i am getting an error ?
"Difference of two datetime columns caused overflow at runtime."
Any idea ?
View 10 Replies
View Related
Jul 22, 2015
I am trying to spilt records into days by the start - End datetime.
I would send an image and data but because I am new to the forum, I am blocked sending images.
"Body text cannot contain images or links until we are able to verify your account"
How I can forward an image.
View 15 Replies
View Related
Sep 23, 2005
At my job is a dts package that is failing in SQL 2005. I am not a SQLexpert. I am just trying to fix. I put the query in Query Analyzerand get this error:(4322 row(s) affected)Server: Msg 535, Level 16, State 1, Line 1Difference of two datetime columns caused overflow at runtime.I am just trying to understand what this means, what I should belooking for and what could be wrong. Here is the query:SELECT i.SerialNumber, '' AS mac_number, DATEDIFF([second], 'Jan 1,1970', s.DateOrdered) AS Support_StartDt, DATEDIFF([second], 'Jan 1,1970',s.Warranty_Enddate) AS Support_EndDt,DATEDIFF([second], 'Jan 1, 1970', c.Registration_Date) ASRegistration_Date, c.FirstName AS enduser_fname,c.LastName AS enduser_lname, c.CompanyName ASenduser_companyname, c.ContactEmail AS enduser_email, c.Address ASenduser_address1,c.Address2 AS enduser_address2, c.City ASenduser_city, c.State AS enduser_state, c.Zip AS enduser_zip,c.WorkPhone AS enduser_phone,c.Fax AS enduser_fax, d.DealerName ASdealer_companyname, d.ContactFirstName AS dealer_fname,d.ContactLastName AS dealer_name,d.Address1 AS dealer_address, d.City ASdealer_city, d.State AS dealer_state, d.Zip AS dealer_zip,d.ContactPhone AS dealer_phone,d.ContactFax AS dealer_fax,ISNULL(SUBSTRING(p.ProductName, 11, LEN(p.ProductName) - 10), 'unknownIWP product') AS product_type, '' AS extra1,'' AS extra2, '' AS extra3, '' AS extra4, '' ASextra5, '' AS extra6, '' AS extra7FROM tblInventory i full outer JOINtblDealers d ON i.DealerID = d.DealerID fullOUTER JOINtblSupport s ON i.InventoryID = s.InventoryIDfull outer JOINtblCustomers c ON s.InventoryID = c.InventoryIDLEFT OUTER JOINtblProducts p ON LEFT(i.SerialNumber,PATINDEX('%-%', i.SerialNumber)) = p.SerialPrefixWHERE i.SerialNumber <> ''Any ideas would be greatly appreciated.
View 2 Replies
View Related
Aug 1, 2007
Hi,
I am trying to replication between a SQL Server 2005 on a desktop and SQL Mobile Edition on a mobile device. I followed the step-by-step guide and used the code here: http://msdn2.microsoft.com/en-US/library/aa454892.aspx
After replication, the sdf on my device has new columns which the original sdf does not have. The new columns seem to be associated with replication, and are as follows:
rowguid
_sysIG
_sysCG
_sysCD
_sysP1
_sysMC
_sysMCS
_sysSR
All columns contain null except for rowguid and _sysCD, and are making my sdf quite big. Is this to be expected? If yes, any references to explain what are these columns for?
Thanks for any help.
View 1 Replies
View Related
Jan 16, 2001
(Long post)
I have a production database with about half the tables using
IDENTITY columns for PRIMARY KEYS. This system is configured as both
a Publisher and a Distributor. We are using Transactional Replication
without updates. The SQL Server Agent runs every hour to pick up any
changes and replicate them to the Subscriber (another SQL Server
machine configured as our failover server).
Both servers are running SQL Server 7.0 (original, no service packs)
under Windows NT 4.0 (SP4).
The failover server (the replication Subscriber) will only be used if
the primary server goes down. And hopefully, only for a short time
before the primary server comes back online. During the time that the
failover is actually being used, the application will not make any
changes to the database.
The IDENTITY values that are replicated need to stay with their
original values. The replication process CANNOT assign new values to
these columns when there are inserted into the database on the
Publisher (i.e. failover server)
My question is: According to the documention, I can add 'NOT FOR
REPLICATION' to the IDENTITY columns and the values will be
preserved. But a collegue of mine says that resets the IDENTITY
sequence on the subscriber and the 2nd time a row gets inserted on
the Publisher, the values get messed up. On his system, he calls a
stored procedure for the tables with IDENTITY columns, and in the
stored procedure, he executes a 'SET IDENTITY_INSERT OFF', then
INSERTS the row, then 'SET IDENTITY_INSERT ON'. He claims that this
approach solved his issues with IDENTITY columns.
Who is right? Do I have to create a stored procedure for replication
for every table with an IDENTITY column, or can I just add 'NOT FOR
REPLICATION' and SQL Server will handle the rest?
NOTE: Upgrading to SQL Server 2000 is NOT an option right now.
Although, if a Service Pack for 7 fixes this, that might be an option.
Thanks in advance for any help you can shed on this issue.
Dave
View 3 Replies
View Related
Sep 8, 1999
We are trying to run replication from one server to another. Most of the tables in the publishing database have identity columns set. At one point the subscribing database had a match of identity columns and primary keys matching the publishing database. Obviously with a primary key set on the subscriber there would be conflicts with duplicate key inserts. We disabled the identity columms and droped the indexes and the data was able to replicate over. However, we discovered that the identity columns, which are used to generate id's on many of our tables, were not replicating over to the subscriber. In fact a null value was being inserted into the subsriber database.
Anyone seen this before? What, if there is one, is the solution?
Thanks,
Brad
View 1 Replies
View Related
Jan 17, 2000
hi there.
I was wondering is there any way that you can use an Identity Column on both a subcribing and Publishing table in Replication,
I am receiving errors when I run Replication with Identity Columns,
Thanks in advance
Fin
View 1 Replies
View Related
May 25, 1999
I think I want to use merge replication but filter out one column but when I'm setting up the publication I only see row filtering available?
Is this just not possible?
Thanks for any help
Dan
View 1 Replies
View Related
May 29, 2007
Hi ,
Plz help me.
The scenario is as follows
I have a db on sql server 2005.I have replicated the db on sql server 2000.All the data counts seems to be correct.But the online websites are not able to execute queries generating error(Microsoft OLE DB Provider for sql server error '80040e21'.Multiple step OLE DB operation generated errors.Check each OLE DB status value,if available.No work was done.)Same web aspcode is working for other db (which were replicated from sql server 2000).
Also one more thing that was observed is as follows:
1)Prmary key was replicated for some of the tables.When primary keys were removed manually report executed fine.
Thanks in advance
Satish
View 2 Replies
View Related
Oct 10, 2005
I added couple of new columns to the distribution database and those new columns are not being replicated.
View 3 Replies
View Related
Dec 4, 2006
Hi,
I am experiencing a wired problem withe merge replication and SQL 2000 SP4.
We had replication working for 4 years without a problem, one of the table that we replicate has been growing in columns, now it has 55 columns.
I have noticed a problem, when I update the last column on the table and waits for the change to be updated on the subscriber the change is not there. But if I replicate the first 40 columns it works.
Changes made:
1) Upgraded to SP4
2) Added some extra fields using sp_addreplcolumn
Is this a known bug? Any ideas?
Regards
View 3 Replies
View Related
Mar 28, 2007
Dear all,
We have an application that use merge replication between MSDE and Devices with SQL CE.
Due to a major application changes, we have to change our replication on all our workstation. Our process is the following:
- drop current replication
- recreate our replication
After first check our replication seems to work but after some test we have identify that all identity ranges have been reset on the workstation. As side effect, device start to reuse existing range and also existing value in the range.
- Have you ever encounter this type of issue?
- What can create it?
- Is our approach fully inappropriate?
Please let me know if you need more information,
Best regards,
View 3 Replies
View Related
Nov 29, 2004
Dear all participants,
I manage complete online system for two locations, connected via dialup lease line. I have to used merge replication for the same data should be at both locations. It is working fine, I don’t like disturbe it but current requirement is addition of two column in one table which one as also use in merge replication.
How I can add aforesaid columns in table x, column name c1 numeric , c2 character?
What is impact on replication?
Let me know step by step.
Thanks in advance.
Thanks R.Mall
View 1 Replies
View Related
May 12, 2004
One of our users tried to set replication solution in a sqlserver (the idea was given up).
SQL Server added in each table a column related to replication.
We want to remove theses columns and I used the following script :
select 'ALTER TABLE dbo.'+object_name(id)+' DROP CONSTRAINT '+object_name(constid)+' GO'
+'ALTER TABLE dbo.'+object_name(id)+' DROP COLUMN '+'msrepl_tran_version GO'
from sysconstraints where object_name(constid) like '%msrep%'
Question:
1. I want to know how to introduce a carraige return in order to have some thing like this :
...
ALTER TABLE dbo.T_CommandCopyFile
DROP CONSTRAINT DF__T_Command__msrep__44AB0736
GO
ALTER TABLE dbo.T_CommandCopyFile
DROP COLUMN msrepl_tran_version
...
2. Is there any other solution to do this more simply ?
View 3 Replies
View Related
Nov 3, 2015
I was reading this:
"Adding an identity column to a published table is not supported, because it can result in non-convergence when the column is replicated to the Subscriber. The values in the identity column at the Publisher depend on the order in which the rows for the affected table are physically stored. The rows might be stored differently at the Subscriber; therefore the value for the identity column can be different for the same rows."
I don't understand...
If I create a table with an identity column and publish it. Can the values on the subscriber be different when the data is replicated?
Suppose I have a this table:
1 Name1
2 Name2
3 Name3
Column 1 is identity field and column 2 the name of employees.
If I publish this table , the data can be inserted on the subscriber .pe, with 2 name1 and 1 name3 and 1 name2?
What about if the identify fields is a primary key?
View 6 Replies
View Related
Dec 21, 2005
Hi,
I read the BOL on how the publisher will had out identity ranges to subscribers, but it was not clear if this was also the case for anonymous subscribers. Will merge replication with identity columns work with anonymous subscribers that sync via HTTPS?
Thanks,
Darrell Young
View 1 Replies
View Related
Jul 15, 2007
In Transactional Replication -while Publisher, Distributer & Subscriber are on same instance- can I have additional columns in destination Table & set "Action if name is in use" for related Article to "Keep existing object unchanged"?
I do this but even Snapshot is not transfered to destination table!!
View 5 Replies
View Related
Dec 13, 2007
Hello:
I am running into an issue with RS2k PDF export.
Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
View 1 Replies
View Related
Jul 9, 2007
Hi,
I'm inserting a datetime values into sql server 2000 from c#
SQL server table details
Table nameate_test
columnname datatype
No int
date_t DateTime
C# coding
SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI");
connectionToDatabase.Open();
DataTable dt1 = new DataTable();
dt1.Columns.Add("no",typeof(System.Int16));
dt1.Columns.Add("date_t", typeof(System.DateTime));
DataRow dr = dt1.NewRow();
dr["no"] = 1;
dr["date_t"] = DateTime.Now;
dt1.Rows.Add(dr);
for(int i=0;i<dt1.Rows.Count;i++)
{
string str=dt1.Rows["no"].ToString();
DateTime dt=(DateTime)dt1.Rows["date_t"];
string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')";
SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase);
cmd.ExecuteNonQuery();
MessageBox.Show("saved");
}
When I run the above code, data is inserted into the table
The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column.
Is there any conversion needed for millisecond values?
thanks,
Mani
View 3 Replies
View Related
Dec 6, 2006
I'm getting error:
String was not recognized as a valid DateTime.
my insert parameter:
<asp:Parameter Name="LastModified" Type="DateTime" DefaultValue= "<%=DateTime.Now.ToString() %>"
my insert command:
InsertCommand="INSERT INTO [Product] ([Enabled], [ProductCode], [ProductName], [ProductAlias], [CarrierId], [DfltPlanId], [DoubleRating], [DoubleRateProductId], [ConnCharges], [StartDate], [EndDate], [Contracted], [BaseProductId], [LastModified], [LastUser]) VALUES (@Enabled, @ProductCode, @ProductName, @ProductAlias, @CarrierId, @DfltPlanId, @DoubleRating, @DoubleRateProductId, @ConnCharges, @StartDate, @EndDate, @Contracted, @BaseProductId, @LastModified, @LastUser)"
LastModified is a datetime field.
Running sql2005
View 1 Replies
View Related
Mar 14, 2007
Hi all, having a little problem with saving dates to sql databaseI've got the CreatedOn field in the table set to datetime type, but every time i try and run it i get an error kicked up Error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."I've tried researching it but not been able to find something similar. Heres the code: DateTime createOn = DateTime.Now;string sSQLStatement = "INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" + name + "','" + description + "','" + userName + "','" + createOn + "')"; Any help would be much appreciated
View 4 Replies
View Related
Mar 17, 2007
Hey :)I'm facing a lot of troubles trying to create a new pause/break-system. Right now i'm building up the query that counts how many records that is inside 2 fields. Let me first show you my table:
ID (int) | stamp_start (Type: DateTime) | stamp_end (Type: DateTime) | Username (varchar)0 | 17-03-07 12:00:00 | 17-03-07 12:30:00 | Hovgaard
The client will enter a start time and a end time and this query should then count how many records that are inside this periode of time.
Example: The client enter starttime: 12:05 and endtime: 12:35.The query shall then return 1 record found. The same thing if the user enters 12:20 and 12:50.My current query looks like this:SELECT COUNT(ID) AS Expr1 FROM table WHERE (start_stamp <= @pausetime_start) AND (end_stamp >= @pausetime_end)But this will only count if I enter the exact same times as the one inside the table.Any ideas how I can figure this out?Thanks for your time so far :)/Jonas Hovgaard - Denmark
View 2 Replies
View Related
