October 9, 2013 at 9:41 am
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
October 10, 2013 at 1:56 pm
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.
October 10, 2013 at 2:04 pm
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.
October 11, 2013 at 9:05 am
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.
October 14, 2013 at 8:59 am
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().
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply