remote server vierd query behavior

  • hello everyone,

     

    Any help on this would be appreciated. I have two servers - A & B.

    on server B I have a table with three columns:

    col1 int, col2 int, col3 datetime. If on server B I do:

    select col1,col2,col3 from table - everything is fine

     

    but if on server A I do:

    select col1,col2,col3 from B.dbname.dbo.table - I get

    Error converting data type DBTYPE_DBTIMESTAMP to datetime

     

    any ideas?

  • Both are sql server 2000?

    Same collation?

    Are you sure that col3 on A isn't a timestamp?

  • I am not sure how collation would be relevant since we are talking about a datetime field, but yes, both servers are SQL 2000 and both DBs are of the same collation. And col3 is defiantely not a timestamp.

  • Do both servers have the same OS level Regional Settings?

    Are there NULLs in the Resultset of the failing query?

    Stuart

    "Chance is always powerful. Let your hook be always cast. In the pool where you least expect it, will be a fish" - Ovid

  • Yes to regional settings

    no to nulls

  • Apologies if you've already gone through this litany of questions, Mordechai. Just doing a little fishing...

    I see this is not Linked Server or Distributed Query. Both SQL Server 2000/same SP?

    Using Query Analyzer? Session Connection Properties identical?

    Stuart

    "Chance is always powerful. Let your hook be always cast. In the pool where you least expect it, will be a fish" - Ovid

  • See what happens when you toggle the Query Analyzer Connection option 'Use regional settings when outputting currency, number, dates, and times'

    Stuart

    "Chance is always powerful. Let your hook be always cast. In the pool where you least expect it, will be a fish" - Ovid

Viewing 7 posts - 1 through 6 (of 6 total)

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