Solved

Cannot get SQL Query to Query Oracle tables from a SQL Stored Procedure

Posted on 2008-10-23
2
364 Views
Last Modified: 2012-05-05
Below query will not run.

DBA has created a linked server to oracle server I am running query against.

First time trying to access Oracle tables from SQL.
 
USE [RMReports]
GO
/****** Object:  StoredProcedure [dbo].[usp_test]    Script Date: 10/23/2008 13:50:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_test]
      
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;



SELECT  [GETPAIDD.CN.CA]..GPCOMP1.GPCOLL.TEAM, [GETPAIDD.CN.CA]..GPCOMP1.GPCOLL.COLLNAME, [GETPAIDD.CN.CA]..GPCOMP1.GPCUST.CUSTNO, [GETPAIDD.CN.CA]..GPCOMP1.GPCUST.COMPANY, [GETPAIDD.CN.CA]..GPCOMP1.GPCUST.NPEST, [GETPAIDD.CN.CA]..GPCOMP1.GPCRDAGEPERF.AVGDAYSPAYP2,
           [GETPAIDD.CN.CA]..GPCOMP1.GPCUST.NPEST - [GETPAIDD.CN.CA]..GPCOMP1.GPCRDAGEPERF.AVGDAYSPAYP2 AS UPDOWN
FROM    [GETPAIDD.CN.CA]..[GPCUST], [GETPAIDD.CN.CA]..[GPCRDAGEPERF], [GETPAIDD.CN.CA]..[GPCOLL]
WHERE  [GETPAIDD.CN.CA]..GPCOMP1.GPCUST.CUSTNO = [GETPAIDD.CN.CA]..GPCOMP1.GPCRDAGEPERF.CUSTNO AND [GETPAIDD.CN.CA]..GPCOMP1.GPCUST.COLLECTOR = [GETPAIDD.CN.CA]..GPCOMP1.GPCOLL.COLLCODE AND (([GETPAIDD.CN.CA]..GPCOMP1.GPCOLL.TEAM <> 'DEFAULT') AND
            ([GETPAIDD.CN.CA]..GPCOMP1.GPCRDAGEPERF.AVGDAYSPAYP2 <> 0) AND ([GETPAIDD.CN.CA]..GPCOMP1.GPCUST.NPEST = 0) OR
            ([GETPAIDD.CN.CA]..GPCOMP1.GPCOLL.TEAM <> 'DEFAULT') AND ([GETPAIDD.CN.CA]..GPCOMP1.GPCRDAGEPERF.AVGDAYSPAYP2 = 0) AND ([GETPAIDD.CN.CA]..GPCOMP1.GPCUST.NPEST <> 0) OR
            ([GETPAIDD.CN.CA]..GPCOMP1.GPCOLL.TEAM <> 'DEFAULT') AND ([GETPAIDD.CN.CA]..GPCOMP1.GPCRDAGEPERF.AVGDAYSPAYP2 <> 0) AND ([GETPAIDD.CN.CA]..GPCOMP1.GPCUST.NPEST <> 0))
ORDER BY GPCOMP1.GPCUST.NPEST DESC


End


Get this error when executing this stored procedure.

Msg 208, Level 16, State 1, Procedure usp_test, Line 12
Invalid object name 'GETPAIDD.CN.CA..GPCUST'.

(1 row(s) affected)



0
Comment
Question by:thayduck
2 Comments
 
LVL 3

Expert Comment

by:richard_crist
ID: 22789513
It may be because of the double dots in CA..GPCUST
0
 

Accepted Solution

by:
thayduck earned 0 total points
ID: 22792055
You need the double dots.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now