September 18, 2009 at 2:42 pm
Greetings,
Creating a stored procedure which executs a SELECT statement against DB2 using ad-hoc OPENQUERY statement returns the following error:
Msg 7357, Level 16, State 2, Procedure usp_bbbb_aaaa_xxxx, Line 21
Cannot process the object "SELECT DISTINCT
Looking for insight to understand the concept of how stored procedures are getting saved and causing this kind of error
Thanks
September 18, 2009 at 2:56 pm
I don't believe the code run via OPENQUERY is parsed by the SQL server you are storing your SP on, so it wouldn't cause errors until you attempt to run it.
Can you post the SP? Someone may be able to give you a better answer after seeing the code.
September 18, 2009 at 3:04 pm
It follows the proc 🙂
USE [MSA_AE]
GO
/****** Object: StoredProcedure [dbo].[usp_bbbb_aaaa_xxxx] Script Date: 09/17/2009 18:26:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_aaaa_bbbb_xxxx] AS
SELECT * INTO #A FROM OPENQUERY(linkedServer,
'SELECT DISTINCT
AP.RELT_ID,
TS.ACC_ID AS RELT1_ACC_ID,
AP.ADD_1 AS RELT1_ADDR_1,
AP.ADD_2 AS RELT1_ADDR_2,
AP.CITY AS RELT1_CITY,
AP.ST_CD AS RELT1_ST_CD,
AP.ZIP AS RELT1_ZIP,
AP.RECEIVED_DT AS RELT1_RECEIVED_DT,
AP.VAL AS RELT1_VAL,
AP.APPR_TYPE_DESC AS RELT1_APPR_TYPE_DESC,
AP2.ACC_ID AS RELT2_ACC_ID,
AP2.ADDR_1 AS RELT2_ADDR_1,
AP2.ADDR_2 AS RELT2_ADDR_2,
AP2.CITY AS RELT2_CITY,
AP2.ST_CD AS RELT2_ST_CD,
AP2.ZIP AS RELT2_ZIP,
AP2.RECEIVED_DT AS RELT2_RECEIVED_DT,
AP2.VAL AS RELT2_VAL,
AP2.APPR_TYPE_DESC AS RELT2_APPR_TYPE_DESC,
DATE(CURRENT_DATE) AS ASOFDATE
FROM
nnnnn.ZZZ_ACCOUNT ACC
JOIN nnnnn.ZZZ_ACCOUNT_MSR MSR ON MSR.CUST_ACC_ID = ACC.CUST_ACC_ID
JOIN nnnnn.ZZZ_ACC_PRODUCT PRD ON MSR.ACC_PROD_GEN_ID = PRD.ACC_PROD_GEN_ID
JOIN ooooo.D_WASP TS ON TS.SHAW_ACC_NUM = ACC.OPR_ACC_NR
JOIN (SELECT AP.ACC_ID,
TA.RELT_ID,
RE.ADDR_1,
RE.ADDR_2,
RE.CITY,
RE.ST_CD,
RE.ZIP,
AP.RECEIVED_DT,
AP.VAL,
ATL.APPR_TYPE_DESC
FROM ooooo.T_APPR AP
JOIN ooooo.T_ACC TA ON TA.ACC_ID = AP.ACC_ID
JOIN ooooo.T_RELT_ACC_SOR_ALL RAS ON RAS.ACC_ID = TA.ACC_ID AND RAS.ACCS_RELT_NUM > 1
JOIN ooooo.T_REAL_ESTATE RE ON RE.CLTRL_ID = AP.CLTRL_ID
JOIN ooooo.T_APPR_TYPE_LOOKUP ATL ON ATL.APPR_TYPE_ID = AP.APPR_TYPE_ID
WHERE TA.SYS_STAT_ID 6
AND AP.RECEIVED_DT = (SELECT MAX(AP1.RECEIVED_DT) RECEIVED_DT
FROM ooooo.T_APPR AP1
WHERE AP1.ACC_ID = AP.ACC_ID
and ap1.received_dt is null or date(ap1.received_dt) > ''1980-01-01'')) AP ON AP.ACC_ID = TS.ACC_ID
JOIN (SELECT TA.ACC_ID,
TA.RELT_ID,
RE.ADDR_1,
RE.ADDR_2,
RE.CITY,
RE.ST_CD,
RE.ZIP,
AP.RECEIVED_DT,
AP.VAL,
AP.APPR_TYPE_DESC
FROM ooooo.T_ACC TA
JOIN ooooo.T_RELT_ACC_SOR_ALL RAS ON RAS.ACC_ID = TA.ACC_ID AND RAS.ACCS_RELT_NUM > 1
LEFT OUTER JOIN ooooo.T_ACC_CLTRL TAC ON TAC.ACC_ID = TA.ACC_ID
LEFT OUTER JOIN ooooo.T_REAL_ESTATE RE ON RE.CLTRL_ID = TAC.CLTRL_ID
LEFT OUTER JOIN (SELECT AP.ACC_ID,
AP.RECEIVED_DT,
AP.VAL,
ATL.APPR_TYPE_DESC
FROM ooooo.T_APPR AP
JOIN ooooo.T_APPR_TYPE_LOOKUP ATL ON ATL.APPR_TYPE_ID = AP.APPR_TYPE_ID
WHERE AP.RECEIVED_DT = (SELECT MAX(AP1.RECEIVED_DT) RECEIVED_DT
FROM ooooo.T_APPR AP1
WHERE AP1.ACC_ID = AP.ACC_ID
AND ap1.received_dt is null or date(ap1.received_dt) > ''1980-01-01'')) AP ON AP.ACC_ID = TA.ACC_ID
WHERE TA.SYS_STAT_ID 6
AND ((TAC.CLTRL_ID = (SELECT MAX(TAC1.CLTRL_ID)
FROM ooooo.T_ACC_CLTRL TAC1
WHERE TAC1.ACC_ID = TAC.ACC_ID))
OR (TAC.CLTRL_ID IS NULL))) AP2 ON AP2.RELT_ID = AP.RELT_ID AND AP2.ACC_ID AP.ACC_ID
WHERE
MSR.ACC_STATUS_CD = ''OPEN''
AND PRD.COLL_CD BETWEEN ''5000'' AND ''5999''
AND SUBSTR(ACC.OPR_ACC_NR,1,3) NOT IN (''116'',''141'')
AND ((AP2.VAL AP.VAL) OR (AP2.VAL IS NULL))')
)
September 18, 2009 at 3:54 pm
I ran the CREATE PROC statement including just a fraction of the OPENQUERY statement and was able to receive more detailed error message
Error Message:
Msg 7357, Level 16, State 2, Procedure usp_APPR_VAR_RELT_ACCTS_DB2OLEDB, Line 21
Cannot process the object "SELECT AP.ACCT_ID
FROM aaaaaa.T_APPR AP". The OLE DB provider "IBMDADB2.DB2COPY1" for linked server "abcdef_OLEDB" indicates that either the object has no columns or the current user does not have permissions on that object.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply