Hi Team,

When I am Running the Query :

SELECT* FROM OPENQUERY (MMFP09, 'select * from ctb_owner.ctb_funding_contract where contract_id =100190')

I am getting the Error in one Server , The Error MSG :

Msg 8114, Level 16, State 8, Line 1 Error converting data type DBTYPE_DBTIMESTAMP to datetime

But the Same Query is working fine on other server of same version.

The Query is using same link server

Thanks Basit


more ▼

asked Mar 09, 2011 at 09:19 AM in Default

avatar image

basit 1
509 57 65 91

all servers are SQL Server?

Mar 09, 2011 at 10:48 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

This can occur if the source data has values that are outside of the valid range for datetime - or possibly if the dates are ambiguous and the servers have different localisation settings i.e 1/2/11 could be 1 Feb or 2 Jan, but 1/30/11 can only be 30 Jan, however when read by something expecting dd/mm/yy can error.

Depending on what the root cause is, you may be able to use ISDATE() to remove the 'bad' data

more ▼

answered Mar 09, 2011 at 10:52 AM

avatar image

Kev Riley ♦♦
66.3k 48 65 81

the Source data is same for both enviorment... MMFP09 is linked server that is created on both server. where should i check for that column.

Mar 10, 2011 at 02:57 AM basit 1

so you have 2 servers: say serverA and serverB, both talking to the linked server MMFP09. The query works fine on serverA but not on serverB? And both serverA and serverB are the same version/config/sp level etc

Mar 10, 2011 at 03:23 AM Kev Riley ♦♦

both server has same version and config...

Mar 10, 2011 at 03:26 AM basit 1

MMFP09 is Oracle server.

Mar 10, 2011 at 03:29 AM basit 1

in that case there must be something different either about the linked server definition, the connection being used, or the config of the target (SQL) server

Mar 10, 2011 at 03:34 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

If MMFP09 is an Oracle server, it sounds then as though the data being brought through is outside the range of dates that SQL Server can handle.

SQL Server's datetime format can handle dates from years 1753 to 9999.

The Oracle DBTYPE_DBTIMESTAMP format can handle dates from year 0 (I think...)

more ▼

answered Mar 10, 2011 at 03:42 AM

avatar image

ThomasRushton ♦♦
42.1k 20 57 53

but the same Query is running fine on one Server but giving the Error on Other server.

One server is our QA and other is PROD. ON QA is working fine but on PROD is giving error

Mar 10, 2011 at 03:44 AM basit 1

Do the servers have the same data?

Mar 10, 2011 at 03:45 AM ThomasRushton ♦♦

data is fetching from Oracle Link server MMFP09. and that is same for both.

Mar 10, 2011 at 03:46 AM basit 1
(comments are locked)
10|1200 characters needed characters left

OK, so given that both SQL Servers are querying the same Oracle server, and therefore returning (or attempting to return) the same data, then it would feel as though it's a localisation setting on the SQL Server that's to blame.

Check the localisation / date format on Windows. Check the language settings for the user accounts in SQL Server.

more ▼

answered Mar 10, 2011 at 04:02 AM

avatar image

ThomasRushton ♦♦
42.1k 20 57 53

i have check the Character set on both of SQL server by SP_helpsort Query , it give the same result on both the server.

Mar 10, 2011 at 05:30 AM basit 1

I was thinking of the language associated with a user's details within SQL Server. As in this query:

 select name, language from syslogins

(you'll need to filter this for the user ID that's running the query...)

Mar 10, 2011 at 05:48 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 09, 2011 at 09:19 AM

Seen: 2045 times

Last Updated: Mar 09, 2011 at 11:07 AM

Copyright 2018 Redgate Software. Privacy Policy