Linked Server error 7357

Question

Linked Server error 7357

joffwilliams

Default port

Points: 1481
More actions
October 9, 2013 at 9:41 am

#281114

We're in the process of moving a database from an old SQL Server 2000 instance to SQL Server 2012.
Both servers have a linked server which connects to an Oracle 10g database to perform some data retrieval. On 2012, Oracle's ODBC driver is installed, System DSN set up and the linked server (named "ABC" below) tested - everything looks good. Some quick testing using OPENQUERY can select rows as expected.
What isn't working is the execution of an Oracle function from a package. Executed from the 2000 instance it is successful and returns the data expected. Executed from the 2012 instance, the exact same code throws the following:
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "{CALL UB.WRAPADDR.CS2ADDR(1,'4310','E','TRENT','AVE',{resultset 10,ubaddr_ubilacct,ubaddr_buildingnumber, ubaddr_predirection,ubaddr_streetname,ubaddr_streetsuffix})}". The OLE DB provider "MSDASQL" for linked server "ABC" indicates that either the object has no columns or the current user does not have permissions on that object.
Is there a configuration change I need to make on the ODBC DSN, or in the linked server? I don't believe it's a permissions issue - both 2000 and 2012 are using the same Oracle login and I (with sysadmin) get the error.
October 10, 2013 at 7:58 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

joffwilliams Default port Points: 1481 More actions · Answer 1

Stewart "Arturius" Campbell (10/10/2013)
Have you considered using the WITH RESULT SET option of the EXECUTE command?

Thanks for the suggestion. Explicitly defining result sets using WITH RESULT SETS (...) returns the same error, as does WITH RESULT SETS UNDEFINED.

joffwilliams Default port Points: 1481 More actions · Answer 2

Here's the full SQL Server stored procedure for reference. Yes, the Oracle procedure returns 5 result sets each with one column (don't ask... I have no good answer and the vendor is no longer in business). Actual server names modified to protect the (not so) innocent.

ALTER PROCEDURE [dbo].[usp_GetByAccountByAddressTest]

@UB_Account int,

@StreetNumber varchar(50),

@StreetDirection varchar(5),

@StreetName varchar(100),

@StreetSuffix varchar(5)

AS

SET ANSI_NULLS ON

SET ANSI_WARNINGS ON

DECLARE @qstring nvarchar(4000)

DECLARE @param nvarchar(300)

set @param = ltrim(str(@UB_Account)) + ',''''' + @StreetNumber + ''''',''''' + @StreetDirection + ''''',''''' + @StreetName + ''''',''''' + @StreetSuffix + ''''''

SET @qstring = N'SELECT * FROM OPENQUERY(ABC, ''{CALL UB.WRAPADDR.CS2ADDR(' + @param + ',{resultset 10,ubaddr_ubilacct,ubaddr_buildingnumber,ubaddr_predirection,ubaddr_streetname,ubaddr_streetsuffix})}'')';

PRINT @qstring; --for my own reference

EXECUTE (@qstring) WITH RESULT SETS

(

(ubaddr_ubilacct int),

(ubaddr_buildingnumber varchar(2048)),

(ubaddr_predirection varchar(2048)),

(ubaddr_streetname varchar(2048)),

(ubaddr_streetsuffix varchar(2048))

);

SET ANSI_NULLS ON

SET ANSI_WARNINGS ON

Output:

SELECT * FROM OPENQUERY(ABC, '{CALL UB.WRAPADDR.CS2ADDR(1,''4310'',''E'',''TRENT'',''AVE'',{resultset 10,ubaddr_ubilacct,ubaddr_buildingnumber,ubaddr_predirection,ubaddr_streetname,ubaddr_streetsuffix})}')

Msg 7357, Level 16, State 2, Line 1

Cannot process the object "{CALL UB.WRAPADDR.CS2ADDR(1,'4310','E','TRENT','AVE',{resultset 10,ubaddr_ubilacct,ubaddr_buildingnumber,ubaddr_predirection,ubaddr_streetname,ubaddr_streetsuffix})}". The OLE DB provider "MSDASQL" for linked server "PCSA" indicates that either the object has no columns or the current user does not have permissions on that object.

lrosini Default port Points: 1480 More actions · Answer 3

Looks like a permissions issue to me - "current user does not have permissions on that object". Check that the user you are executing as has permissions to the funciton and all other objects needed.

joffwilliams Default port Points: 1481 More actions · Answer 4

lrosini (10/11/2013)
Looks like a permissions issue to me - "current user does not have permissions on that object". Check that the user you are executing as has permissions to the funciton and all other objects needed.

What do you suggest I check permissions on? I'm using the same Oracle login on both linked servers and am sysadmin on both SQL Server instances. Necessary firewall ports are open and the ODBC DSN is configured and accessible via OPENQUERY().