The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

  • Jack Corbett

    SSC Guru

    Points: 184372

    Don't know why it was affect a linked server query, but the only difference I see (other that Standard vs. Enterprise) is that is works on Windows SP1 and not Windows SP2.

    Have you tried it on an XP machine or Windows Server 2003?

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • john_gleason

    SSC-Addicted

    Points: 453

    All boxes are Server 2003

  • Jeff Moden

    SSC Guru

    Points: 994667

    Isn't a DB2 timestamp is listed in seconds since some date? Are we chasing a ghost just because the OP said "it used to work"?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • ChrisM@Work

    SSC Guru

    Points: 186045

    I'm sure you've checked this out, John, but BOL says of connection-level settings...

    "SQL Server evaluates datetime constants at run time. A date string that works for the date formats expected by one language may be unrecognizable if the query is executed by a connection using a different language and date format setting. For example, this view works correctly for connections made with the language set to U.S. English, but not for connections made using other languages: "

    Have you tried issuing SET DATEFORMAT YMD as the first statement in your batch? I apologise if I'm stating the 'bleedin' obvious' but it hasn't yet been mentioned in this thread.

    Cheers

    ChrisM

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • john_gleason

    SSC-Addicted

    Points: 453

    All boxes are set the same.

    I think I am going to go down the path of getting the boxes in sync and seeing if that gets things in line.

    Thanks everyone for their two cents... Once, the box is configured to resemble the other two boxes are will let you know if this resolved the issue

  • Mike01

    SSChampion

    Points: 11070

    We are still having an issue with this. The interesting thing is when we eliminate the date range in the criteria, the datetime formats are different.

    Server that works (Enterprise Edition)

    2005-03-11 11:42:42.447

    Server that doesn't work (Standard Edtion)

    2005-03-11-11.42.42.447431

    Note the decimals and dashes on the Standard Edition. The time regional settings on both boxes are the same (h:mm:ss tt). Any ideas?

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Jeff Moden

    SSC Guru

    Points: 994667

    You got that from a "SELECT GETDATE()" on both boxes??? If not, what are you using to get this date and what happens if you try "SELECT GETDATE()" on both boxes?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Johan Bijnens

    SSC Guru

    Points: 134265

    Mike Martin (6/5/2008)


    We are still having an issue with this. The interesting thing is when we eliminate the date range in the criteria, the datetime formats are different.

    Server that works (Enterprise Edition)

    2005-03-11 11:42:42.447

    Server that doesn't work (Standard Edtion)

    2005-03-11-11.42.42.447431

    Note the decimals and dashes on the Standard Edition. The time regional settings on both boxes are the same (h:mm:ss tt). Any ideas?

    IMO you aren't using datetime datatyped columns to store this data on your standard edition.

    SQLdatetime only goes 0,997 seconds (not 0,997999).

    Store the query results in a temp table and you'll find out which datatype is in use ! (implicit conversion changed ?? :crazy: -> that's why you should avoid them !)

    So I guess it all comes back to using the correct datatype.

    Maybe post the full query (not just 'select * ' )

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution πŸ˜€

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • john_gleason

    SSC-Addicted

    Points: 453

    I created the temp tables and one created as char(26) and the other as datetime. So, how do I go about fixing this issue?

  • Mike01

    SSChampion

    Points: 11070

    We resolved this issue. Everything was the same except the ODBC Driver's ini file. The ini file on the one that didn't work was setup to treat timestamp columns as char(26) instead of datetime. Thank you for everyone's responses

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Johan Bijnens

    SSC Guru

    Points: 134265

    Glad you resolved the issue.

    Can you post the tricky parameter(s) or odbc set ?

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution πŸ˜€

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • Mike01

    SSChampion

    Points: 11070

    in the DB2CLI.INI file, there was a setting that looked like this:

    Patch1=131072

    This code translates to "Describe time stamps as a char(26)". This setting was only on the box that had the Standard Edition, When we removed this setting from the file, the behavior reverted back to treating timestamps as datetime. There are a number of different values for this parameter which can be found in the Advanced settings of the ODBC Driver. Once in the Advanced Settings, Click on the Service tab to see/modify the options. You will need write access to this file to make the changes

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Johan Bijnens

    SSC Guru

    Points: 134265

    thank you for the feedback.

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution πŸ˜€

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

Viewing 13 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply