| Groups | Blog | Home |
sql server dts : Data overflow
Access2002 to Sql Server 2000, using the Transfer Data Task tool. When I run the package I get this error: The number of failing rows exceeds the maximum specified. Insert error, column 40 ('DelDate',DBTYPE_DBTIMESTAMP), status 6: Data overflow. Invalid character value for cast specification. Field DelDate is a DateTime field and the Access table and datetime in the sqlserver table. Allow Nulls is True and I have no Time Stamp field in the SQL table. Thanks for any help and God Bless, Mark A. Sam
Hello Allan,
I guess I am not following. My Access table display the dates as mm/dd/yyyy. Why would the day and month be reversed do to syntax of the area they are in? Or are you saying that SQL Server formats it as dd/mm/yyyy? I will check into ranges however. It is a good start. The only issue I was aware of was that Access's starting and ending date ranges were different which would not be a concern for current years. God Bless, Mark [quoted text, click to view] "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message news:eQ7GBhbYDHA.1744@TK2MSFTNGP12.phx.gbl... > Hello, Mark! > > In BOL look up the possible ranges for a SQL Server datetime column. Access > has different ranges. > If that is not the problem then what is most probably happening is that SQL > Server is interpreting the dat differently so say you have 13/12/2003 in > Access SQL Server may well be interpreting this as the 12th of the 13th > month. > > If you know the format of your access date data then you can look to format > it like this > > Formatting Character Data into Datetime fields > http://www.sqldts.com/default.aspx?6,103,249,0,1 > > -- > > Allan Mitchell (Microsoft SQL Server MVP) > MCSE,MCDBA > www.SQLDTS.com > I support PASS - the definitive, global community > for SQL Server professionals - http://www.sqlpass.org > > : When I run the package I get this error: > > : The number of failing rows exceeds the maximum specified. > : Insert error, column 40 ('DelDate',DBTYPE_DBTIMESTAMP), status 6: Data > : overflow. > : Invalid character value for cast specification. > > : Field DelDate is a DateTime field and the Access table and datetime in the > : sqlserver table. > : Allow Nulls is True and I have no Time Stamp field in the SQL table. > > : Thanks for any help and God Bless, > > --- Microsoft Outlook Express 6.00.2800.1158 > >
Hello, Mark!
In BOL look up the possible ranges for a SQL Server datetime column. Access has different ranges. If that is not the problem then what is most probably happening is that SQL Server is interpreting the dat differently so say you have 13/12/2003 in Access SQL Server may well be interpreting this as the 12th of the 13th month. If you know the format of your access date data then you can look to format it like this Formatting Character Data into Datetime fields http://www.sqldts.com/default.aspx?6,103,249,0,1 -- Allan Mitchell (Microsoft SQL Server MVP) MCSE,MCDBA www.SQLDTS.com I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org : When I run the package I get this error: : The number of failing rows exceeds the maximum specified. : Insert error, column 40 ('DelDate',DBTYPE_DBTIMESTAMP), status 6: Data : overflow. : Invalid character value for cast specification. : Field DelDate is a DateTime field and the Access table and datetime in the : sqlserver table. : Allow Nulls is True and I have no Time Stamp field in the SQL table. : Thanks for any help and God Bless, --- Microsoft Outlook Express 6.00.2800.1158
I have found that the best way to send dates to SQL Server is yyyymmdd. It
is more than likely going to be in the formatting of the dates coming from Access. Here is a truth table I give to developers to show the impact of User language and locale. Basically SQL Server will look at the user's language setting and interpret the date format from there. (K = OK , OOR = Out of Range) USER/LOCALE settings Locale, User, 17/1/2001, 1/17/2001, US, US, OOR, K US, UK, K, OOR UK, UK, K, OOR UK, US, OOR, K Explanation: User = UK Server = US Date 17/1/2001 SQL Server will pass the date as 17/1/2001 and although the server is set to US the translation will be done to 1/17/2001. User = UK Server = US Date = 1/17/2001 SQL Server will pass the date as 1/17/2001 and the server will do the translation from UK to US for the server to 17/1/2001 which is OOR for the US User = US Server = UK Date = 17/1/2001 SQL Server will pass the date as 17/1/2001 but thinking that the user has passed a US date it will turned around to 1/17/2001 which is OOR for the UK User = US Server = UK Date = 1/17/2001 SQL Server will pass the date as 1/17/2001 and the server knowing the user is american will do the translation to UK i.e 17/1/2001 -- ---------------------------- Allan Mitchell (Microsoft SQL Server MVP) MCSE,MCDBA www.SQLDTS.com I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org [quoted text, click to view] "Synergy" <NoName@NoMail.Com> wrote in message news:OKdYh5bYDHA.1680@tk2msftngp13.phx.gbl... > Hello Allan, > > I guess I am not following. My Access table display the dates as > mm/dd/yyyy. Why would the day and month be reversed do to syntax of the > area they are in? Or are you saying that SQL Server formats it as > dd/mm/yyyy? > > I will check into ranges however. It is a good start. The only issue I was > aware of was that Access's starting and ending date ranges were different > which would not be a concern for current years. > > God Bless, > > Mark > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > news:eQ7GBhbYDHA.1744@TK2MSFTNGP12.phx.gbl... > > Hello, Mark! > > > > In BOL look up the possible ranges for a SQL Server datetime column. > Access > > has different ranges. > > If that is not the problem then what is most probably happening is that > SQL > > Server is interpreting the dat differently so say you have 13/12/2003 in > > Access SQL Server may well be interpreting this as the 12th of the 13th > > month. > > > > If you know the format of your access date data then you can look to > format > > it like this > > > > Formatting Character Data into Datetime fields > > http://www.sqldts.com/default.aspx?6,103,249,0,1 > > > > -- > > > > Allan Mitchell (Microsoft SQL Server MVP) > > MCSE,MCDBA > > www.SQLDTS.com > > I support PASS - the definitive, global community > > for SQL Server professionals - http://www.sqlpass.org > > > > : When I run the package I get this error: > > > > : The number of failing rows exceeds the maximum specified. > > : Insert error, column 40 ('DelDate',DBTYPE_DBTIMESTAMP), status 6: Data > > : overflow. > > : Invalid character value for cast specification. > > > > : Field DelDate is a DateTime field and the Access table and datetime in > the > > : sqlserver table. > > : Allow Nulls is True and I have no Time Stamp field in the SQL table. > > > > : Thanks for any help and God Bless, > > > > --- Microsoft Outlook Express 6.00.2800.1158 > > > > > >
Thanks again Allen,
I don't see that the format is the issue then. The table that gave me a problem was from a different db than the one I need to send to SQL Server. I was experimenting. I am working remotely now playing with the clients database, working out issues, and the date doesn't seem to be one of them. The issue is that if I use the upsizing wizard in Access, I cannot export all of the tables at once. The tables get created but no data is transferred. But if I export 10 tables at a time, the data gets moved. So I am just going to think the date issue is just another quirk. God Bless, Mark [quoted text, click to view] "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message news:e0OblsiYDHA.2200@TK2MSFTNGP09.phx.gbl... > I have found that the best way to send dates to SQL Server is yyyymmdd. It > is more than likely going to be in the formatting of the dates coming from > Access. > > Here is a truth table I give to developers to show the impact of User > language and locale. Basically SQL Server will look at the user's language > setting and interpret the date format from there. > (K = OK , OOR = Out of Range) > > USER/LOCALE settings > > > > Locale, User, 17/1/2001, 1/17/2001, > > US, US, OOR, K > > US, UK, K, OOR > > UK, UK, K, OOR > > UK, US, OOR, K > > > > Explanation: > > User = UK > > Server = US > > Date 17/1/2001 > > SQL Server will pass the date as 17/1/2001 and although the server is set to > US the translation will be done to 1/17/2001. > > User = UK > > Server = US > > Date = 1/17/2001 > > SQL Server will pass the date as 1/17/2001 and the server will do the > translation from UK to US for the server to 17/1/2001 which is OOR for the > US > > User = US > > Server = UK > > Date = 17/1/2001 > > SQL Server will pass the date as 17/1/2001 but thinking that the user has > passed a US date it will turned around to 1/17/2001 which is OOR for the UK > > User = US > > Server = UK > > Date = 1/17/2001 > > SQL Server will pass the date as 1/17/2001 and the server knowing the user > is american will do the translation to UK i.e > > 17/1/2001 > > > -- > > ---------------------------- > Allan Mitchell (Microsoft SQL Server MVP) > MCSE,MCDBA > www.SQLDTS.com > I support PASS - the definitive, global community > for SQL Server professionals - http://www.sqlpass.org > > > > "Synergy" <NoName@NoMail.Com> wrote in message > news:OKdYh5bYDHA.1680@tk2msftngp13.phx.gbl... > > Hello Allan, > > > > I guess I am not following. My Access table display the dates as > > mm/dd/yyyy. Why would the day and month be reversed do to syntax of the > > area they are in? Or are you saying that SQL Server formats it as > > dd/mm/yyyy? > > > > I will check into ranges however. It is a good start. The only issue I > was > > aware of was that Access's starting and ending date ranges were different > > which would not be a concern for current years. > > > > God Bless, > > > > Mark > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > news:eQ7GBhbYDHA.1744@TK2MSFTNGP12.phx.gbl... > > > Hello, Mark! > > > > > > In BOL look up the possible ranges for a SQL Server datetime column. > > Access > > > has different ranges. > > > If that is not the problem then what is most probably happening is that > > SQL > > > Server is interpreting the dat differently so say you have 13/12/2003 in > > > Access SQL Server may well be interpreting this as the 12th of the 13th > > > month. > > > > > > If you know the format of your access date data then you can look to > > format > > > it like this > > > > > > Formatting Character Data into Datetime fields > > > http://www.sqldts.com/default.aspx?6,103,249,0,1 > > > > > > -- > > > > > > Allan Mitchell (Microsoft SQL Server MVP) > > > MCSE,MCDBA > > > www.SQLDTS.com > > > I support PASS - the definitive, global community > > > for SQL Server professionals - http://www.sqlpass.org > > > > > > : When I run the package I get this error: > > > > > > : The number of failing rows exceeds the maximum specified. > > > : Insert error, column 40 ('DelDate',DBTYPE_DBTIMESTAMP), status 6: Data > > > : overflow. > > > : Invalid character value for cast specification. > > > > > > : Field DelDate is a DateTime field and the Access table and datetime in > > the > > > : sqlserver table. > > > : Allow Nulls is True and I have no Time Stamp field in the SQL table. > > > > > > : Thanks for any help and God Bless, > > > > > > --- Microsoft Outlook Express 6.00.2800.1158 > > > > > > > > > > > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |