Error In OPEN QUERY

Question

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

.

Answer 1

0

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

answered Mar 09 '11 at 10:52 AM

Kev Riley ♦♦
46k ● 38 ● 43 ● 69

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 '11 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 '11 at 03:23 AM Kev Riley ♦♦

both server has same version and config...

Mar 10 '11 at 03:26 AM basit 1

MMFP09 is Oracle server.

Mar 10 '11 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 '11 at 03:34 AM Kev Riley ♦♦

(comments are locked)

Answer 2

0

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...)

answered Mar 10 '11 at 03:42 AM

ThomasRushton ♦
29.2k ● 6 ● 9 ● 36

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 '11 at 03:44 AM basit 1

Do the servers have the same data?

Mar 10 '11 at 03:45 AM ThomasRushton ♦

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

Mar 10 '11 at 03:46 AM basit 1

(comments are locked)

Answer 3

0

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.

answered Mar 10 '11 at 04:02 AM

ThomasRushton ♦
29.2k ● 6 ● 9 ● 36

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 '11 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 '11 at 05:48 AM ThomasRushton ♦

(comments are locked)

Error In OPEN QUERY

Follow this question

Related Questions