Solved

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

Posted on 2008-10-23
2
363 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

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…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

706 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

20 Experts available now in Live!

Get 1:1 Help Now