x

Error In OPEN QUERY

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

basit 1 gravatar image

basit 1
499 51 62 85

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()][1] to remove the 'bad' data

[1]: http://msdn.microsoft.com/en-us/library/ms187347.aspx
more ▼

answered Mar 09, 2011 at 10:52 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.2k 47 49 76

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

ThomasRushton gravatar image

ThomasRushton ♦
34k 18 20 44

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

ThomasRushton gravatar image

ThomasRushton ♦
34k 18 20 44

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x26

asked: Mar 09, 2011 at 09:19 AM

Seen: 1766 times

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