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

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 2

I'm using the Microsoft DB2OLEDB provider which is available as part of the Feature Pack for SQL 2005 - April 2006. It's also part of Host Integration Services. It hooks into the AS/400 using the DRDA (Oracle style) protocol.

You can download it seperately but it will only install on SQL standard, enterprise or developer (not express).

I also find that building the complete query that I want to run on the DB2/400 box and then wrapping it in a VARCHAR(MAX) string which also includes the "SELECT * FROM OPENQUERY (--linkedservername--,--DB2/400 query with params--)" and then using the sp_execute or EXEC commands returns the best results.

It's even possible to wrap up RPG code within DB2/400 SQL procedures and then call them (with params) from the MS SQL Server and return any result set as a MS SQL table.

Answer 3

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

Answer 4

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

Answer 5

Solution is to add brackets around the call statement. You still won't be able to select into a table at SQL Server end though unless you have installed support for distributed transactions. I am not sure why it needs a transaction, but it won't work unless you have this set up.

EXEC ('{CALL abicqual.VP_GETCCEPGMID(?, ?)}', @emailToAddress, @emailFromAddress) AT MAM400