Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  New to SQL Server Administration  The problem with linkserver to oracle, "TypeName"

Author  Topic  zhangn Starting Member 29 Posts Posted - 2010-02-02 : 08:28:36 i have tried to pull out some data from oracle database in sql server 2008 by using linkserver. The funny thing is i am not able to define the where condition. See my query below, the bold word is the key: declare @timestamp as varchar(10) set @timestamp = '2010-01-01' SELECT * FROM (SELECT fcast.GASDAY, cast(fcast.TOTALFORECAST / 1000000 AS int) TOTALFORECAST, slot.SLOTID FROM (SELECT *, datepart(hour, UPDATETIME) AS [HOUR], cast(GASDAY AS float) - floor(CAST(UPDATETIME AS float)) AS [ADVANCEDAYS] FROM PGASFO02..DEVELOPER.NGCFORECAST WHERE GASDAY >= @timestamp) fcast INNER JOIN PGASFO02..DEVELOPER.NGCFORECASTSLOT slot ON fcast.ADVANCEDAYS = slot.ADVANCEDAYS WHERE fcast.HOUR >= slot.STARTHOUR AND fcast.HOUR <= slot.ENDHOUR) AS A PIVOT (AVG(TOTALFORECAST) FOR SLOTID IN ([NGC2100D0], [NGC1600D0], [NGC1300D0], [NGC1000D0], [NGC0000D0], [NGC1600D1], [NGC1300D1])) AS pvt Then, the system return OLE DB provider "MSDAORA" for linked server "PGASFO02" returned message "Type name is invalid.". Msg 7322, Level 16, State 2, Line 9 A failure occurred while giving parameter information to OLE DB provider "MSDAORA" for linked server "PGASFO02". However, if i put put '2010-01-01' directly in the query. it works see the query below, SELECT * FROM (SELECT fcast.GASDAY, cast(fcast.TOTALFORECAST / 1000000 AS int) TOTALFORECAST, slot.SLOTID FROM (SELECT *, datepart(hour, UPDATETIME) AS [HOUR], cast(GASDAY AS float) - floor(CAST(UPDATETIME AS float)) AS [ADVANCEDAYS] FROM PGASFO02..DEVELOPER.NGCFORECAST WHERE GASDAY >= '2010-01-01') fcast INNER JOIN PGASFO02..DEVELOPER.NGCFORECASTSLOT slot ON fcast.ADVANCEDAYS = slot.ADVANCEDAYS WHERE fcast.HOUR >= slot.STARTHOUR AND fcast.HOUR <= slot.ENDHOUR) AS A PIVOT (AVG(TOTALFORECAST) FOR SLOTID IN ([NGC2100D0], [NGC1600D0], [NGC1300D0], [NGC1000D0], [NGC0000D0], [NGC1600D1], [NGC1300D1])) AS pvt can anyone example to me why? Is there any way to fix the problem? Very appreciated! Ning