openquery invalid table name - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

openquery invalid table name

Author

Topic

somenoob
Posting Yak Master

112 Posts

Posted - 2011-10-12 : 03:28:27

Hi everyone please help

select * from openquery(serverlinkeodwmain,

'SELECT COUNT(*) AS Cnt FROM [DB].[DBO].[V_TEST]

WHERE BATCH_ID = dateadd(day, datediff(day, 0, getdate()), 0)')

when i try this, it does not work. it says that it has invalid table name.

but when i try this,

SELECT COUNT(*) AS Cnt FROM [DB].[DBO].[V_TEST]
WHERE BATCH_ID = dateadd(day, datediff(day, 0, getdate()), 0)

it works. please help

somenoob
Posting Yak Master

112 Posts

Posted - 2011-10-12 : 03:48:39

any help? please

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 04:54:36

do you've the table [DB].[DBO].[V_TEST] in server which you've linked via linked server serverlinkeodwmain?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

somenoob
Posting Yak Master

112 Posts

Posted - 2011-10-12 : 05:07:04

yes i have it. when i try other queries like
select * from openquery(serverlinkeodwmain, 'SELECT CASE WHEN TID IS NULL THEN ''OTHER LOCATION'' ELSE TERMINAL_ID END TERMINAL, nvl(SUM(STAY), 0) NoStayDays, COUNT(CNTR) total, ROUND(NVL(SUM(STAY), 0) / COUNT(CNTR), 0) AveDays FROM V_TEST GROUP BY TID ORDER BY TID')
it works.

when i try the
SELECT COUNT(*) AS Cnt FROM [DB].[DBO].[V_TEST]
WHERE BATCH_ID = dateadd(day, datediff(day, 0, getdate()), 0)

it works. but when i try this

select * from openquery(serverlinkeodwmain,'SELECT COUNT(*) AS Cnt FROM [DB].[DBO].[V_TEST]
WHERE BATCH_ID = dateadd(day, datediff(day, 0, getdate()), 0)')

it does not work

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 05:57:04

are you sue its in data base [DB] under scheme [DBO]?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2011-10-12 : 08:46:59

Are you able to see the table in linked server catalog in SSMS ?

PBUH

somenoob
Posting Yak Master

112 Posts

Posted - 2011-10-12 : 20:41:44

if i got it to look like this,

select * from openquery(serverlinkeodwmain, 'SELECT COUNT(CNTR) cnt FROM V_TEST
WHERE BATCH_ID = DateADD(day , datediff(day, 0, getdate()), 0)')

OLE DB provider "MSDAORA" for linked server "serverlinkeodwmain" returned message "ORA-00904: "DATEADD": invalid identifier
".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 02:14:25

thats because DATEADD is not available in Oracle. See whats the corresponding function in Oracle and use it instead

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources