Execute DB2 iSeries Stored Procedure from a SQL 2005 Linked Server

Question

I am trying to execute a stored procedured from a linked database in MS SQL 2005. The linked database is a db2 database on a iseries server. I am using the iSeries IBMDASQL service provider. I am able to query the stored procedure without problems using a simple vbscript and ado. When I try to execute the same stored procedure in query analyzer on the SQL Server, I never see any results. Query analyzer always show 'command executed' and when I execute the select * on the temp table, no values appear. What am I doing wrong????? Thanks!

--code from query analyzer below--

DECLARE @emailToAddress      char(50)
DECLARE @emailFromAddress    char(50)

set @emailToAddress = '[email protected]'
set @emailFromAddress = '[email protected]'

If Object_ID('tempdb..#tmpResultTbl') Is Not Null
    Drop Table #tmpResultTbl

Create Table #tmpResultTbl
(
OUTPGMID Char(150))

--SET FMTONLY Off
Set NoCount On
Insert Into #tmpResultTbl 
EXEC ('CALL abicqual.VP_GETCCEPGMID(?, ?) ', @emailToAddress, @emailFromAddress) AT MAM400 

Select * From #tmpResultTbl

UPDATED: Here is the updated code to use openquery... still no luck :(

DECLARE @TSQL varchar(8000) DECLARE @emailToAddress varchar(50) DECLARE @emailFromAddress varchar(50) SET @emailToAddress = '[email protected]' SET @emailFromAddress = '[email protected]' SET @TSQL = 'SELECT * FROM OPENQUERY(MAM400,''CALL abicqual.VP_GETCCEPGMID(''''' + @emailToAddress + ''''', ''''' + @emailFromAddress + '''''' + ')''' + ')' Print @TSQL EXEC (@TSQL)

--output below-- SELECT * FROM OPENQUERY(MAM400,'CALL abicqual.VP_GETCCEPGMID(''[email protected]'', ''[email protected]'')') Msg 7357, Level 16, State 2, Line 1 Cannot process the object "CALL abicqual.VP_GETCCEPGMID('[email protected]', '[email protected]')". The OLE DB provider "IBMDASQL" for linked server "MAM400" indicates that either the object has no columns or the current user does not have permissions on that object.

Answer 1

Try using OPENQUERY. This blog posting shows an example of passing variables into an OPENQUERY statement.

Answer 2

Turns out that there are a number of issues with the IBMDASQL provider that I was using. Believe it or not it is faster and more reliable to use the Microsoft OLE DB Provider for ODBC Drivers. After creating a new linked server with this provider, all of my earlier sql attempts worked properly. Cheers!

Answer 3

call oracle procedure (function) via linked server and get its result (return value)

--input parameters p1, p2, p3
declare @SQL nvarchar(1000)
declare @param nvarchar(100)
declare @result varchar(20) -- numbers may cause data type error

-- (...custom actions, i.e. insert to local table)

set @SQL = N'set @result = (select * from openquery(myLinkedServer, ''select OwnerName.Function_Insert(' + cast(@p1 as varchar) + ', ' + cast(@p1 as varchar) + ', ' + cast(@p3 as varchar) + ') from dual''))'
set @param = '@result varchar output'
EXEC sp_executesql @SQL, @param, @result output 

if @result  '0' goto ERROR

-- (...custom actions)

return 1 -- OK

ERROR:
-- (...custom actions, i.e. delete from local table with(rowlock))
return 0 -- KO