Can a stored procedure use the OpenRowSet function to get DB2 data

If so, does anyone have an example?
Background: We have a MSFT Reporting Services report that uses the
OpenRowSet function to get both DB2 and SQL Server data in one SQL statement
.
Since userid and psw are parms to this function, it is not an option to
hard-code them into the OpenRowSet parms.
So our solution is to write a stored procedure that the report will call.
This stored procedure will have the SQL statement, including the OpenRowSet
function WITH the userid and psw hard-coded in, and then the stored procedur
e
will be encrypted.
I'm presently writing the stored procedure and read the following in the
article entitled "External Data and Transact-SQL" (SQL Server books online):
"Stored procedures are supported only against SQL Server data sources."
Here's the stored procedure I'm writing. It doesn't pass the Syntax Checker
yet, so whatever assistance anyone can provide would be great.
Thanks!
========================================
==========
CREATE PROCEDURE SSP_AUTHS_BY_FI_BY_ACCT_TXT
@.inst varchar,
@.stat char(1),
@.ret_cd char(1) OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @.err_cd smallint,
@.rowcount int,
@.error int
SET @.ret_cd = 0
SELECT DISTINCT DB1.EFF_STAT,
DB1.FIN_INST_NM,
DB1.ADDR_LINE_1,
DB1.ADDR_LINE_2,
DB1.ADDR_LINE_3,
DB1.ADDR_CTY,
DB1.ADDR_ST,
DB1.ADDR_POSTAL_CD,
DB1.PHONE_NR,
DB1.CONTACT_NM,
DB1.ACCT_NR,
DB1.ACCT_NM_1,
DB1.ACCT_NM_2,
DB1.PERMVAL_NM,
DB1.EMP_AUTH_NM,
DB1.NE_AUTH_NM,
DB1.NON_EMP_INDIC,
DB1.COUNTRY
FROM OPENROWSET
('IBMDADB2'
,'dsnp_r';'userid';'passwd'
,'''SELECT DISTINCT DBT1.EFF_STAT,
DBT3.FIN_INST_NM,
DBT3.ADDR_LINE_1,
DBT3.ADDR_LINE_2,
DBT3.ADDR_LINE_3,
DBT3.ADDR_CTY,
DBT3.ADDR_ST,
DBT3.ADDR_POSTAL_CD,
DBT3.PHONE_NR,
DBT3.CONTACT_NM,
DBT1.ACCT_NR,
DBT1.ACCT_NM_1,
DBT1.ACCT_NM_2,
AUTH.AUTH_STAT,
AUTH.PERMVAL_NM,
AUTH.NON_EMP_INDIC,
AUTH.EMP_AUTH_NM,
AUTH.NE_AUTH_NM,
DBT50.PERMVAL_NM AS COUNTRY
FROM TRSPROD.CTRS01_ACCOUNT_TB DBT1
INNER JOIN TRSPROD.CTRS03_FININST_TB DBT3
ON DBT1.FIN_INST_ID_NR = DBT3.FIN_INST_ID_NR
LEFT JOIN (SELECT DISTINCT A.ACCT_ID_NR
, A.NON_EMP_INDIC
, C.NAME AS EMP_AUTH_NM
, D.NAME AS NE_AUTH_NM
, A.EFF_STAT AS AUTH_STAT
, B.PERMVAL_NM
FROM TRSPROD.CTRS04_AUTHRITY_TB A
INNER JOIN TRSPROD.CTRS50_PERMVAL_TB B
ON A.AUTH_TYPE_ID_NR = B.PERMVAL_ID_NR
LEFT JOIN BACPROD.PS_PERSONAL_DATA C
ON A.EMPLID = C.EMPLID
LEFT JOIN TRSPROD.CTRS20_NONEMP_TB D
ON A.EMPLID = D.NON_EMPLID
WHERE A.EFF_DT = (SELECT MAX(EFF_DT)
FROM TRSPROD.CTRS04_AUTHRITY_TB E
WHERE A.ACCT_ID_NR = E.ACCT_ID_NR)) AUTH
ON DBT1.ACCT_ID_NR = AUTH.ACCT_ID_NR
LEFT JOIN TRSPROD.CTRS50_PERMVAL_TB DBT50
ON DBT1.CNTRY_ID_NR = DBT50.PERMVAL_ID_NR'') DB1
WHERE (DB1.FIN_INST_NM LIKE ''' + @.INST + '%'')
AND (DB1.AUTH_STAT = ''A'')
AND (DB1.EFF_STAT LIKE ''' + @.STAT + '%'')
ORDER BY DB1.EFF_STAT
, DB1.FIN_INST_NM
, DB1.ACCT_NR
, DB1.PERMVAL_NM''
) DB2')
Select @.error=@.@.error, @.rowcount=@.@.rowcount
IF @.error <> 0
BEGIN
RAISERROR('Read of database failed ',16,1)
SET @.ret_cd = 1
RETURN 1
END
IF @.rowcount = 1
BEGIN
SET @.ret_cd = 0
RETURN 0
END
SET NOCOUNT OFF
RETURN 0
/ ****************************************
***********************************
********************
******************************* End of Procedure SQL
****************************************
***
****************************************
************************************
*******************/
END
GO
========================================
============
--
Joe Palm
Senior Technical Developer
Madison, WICan you add a linked server?, this way you do not have to embed the userid
and password in your query.
Creating a linked server to DB2 using Microsoft OLE DB provider for DB2
http://support.microsoft.com/defaul...kb;en-us;222937
AMB
"Joe Palm" wrote:

> If so, does anyone have an example?
> Background: We have a MSFT Reporting Services report that uses the
> OpenRowSet function to get both DB2 and SQL Server data in one SQL stateme
nt.
> Since userid and psw are parms to this function, it is not an option to
> hard-code them into the OpenRowSet parms.
> So our solution is to write a stored procedure that the report will call.
> This stored procedure will have the SQL statement, including the OpenRowSe
t
> function WITH the userid and psw hard-coded in, and then the stored proced
ure
> will be encrypted.
> I'm presently writing the stored procedure and read the following in the
> article entitled "External Data and Transact-SQL" (SQL Server books online
):
> "Stored procedures are supported only against SQL Server data sources."
> Here's the stored procedure I'm writing. It doesn't pass the Syntax Check
er
> yet, so whatever assistance anyone can provide would be great.
> Thanks!
> ========================================
==========
> CREATE PROCEDURE SSP_AUTHS_BY_FI_BY_ACCT_TXT
> @.inst varchar,
> @.stat char(1),
> @.ret_cd char(1) OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.err_cd smallint,
> @.rowcount int,
> @.error int
> SET @.ret_cd = 0
> SELECT DISTINCT DB1.EFF_STAT,
> DB1.FIN_INST_NM,
> DB1.ADDR_LINE_1,
> DB1.ADDR_LINE_2,
> DB1.ADDR_LINE_3,
> DB1.ADDR_CTY,
> DB1.ADDR_ST,
> DB1.ADDR_POSTAL_CD,
> DB1.PHONE_NR,
> DB1.CONTACT_NM,
> DB1.ACCT_NR,
> DB1.ACCT_NM_1,
> DB1.ACCT_NM_2,
> DB1.PERMVAL_NM,
> DB1.EMP_AUTH_NM,
> DB1.NE_AUTH_NM,
> DB1.NON_EMP_INDIC,
> DB1.COUNTRY
> FROM OPENROWSET
> ('IBMDADB2'
> ,'dsnp_r';'userid';'passwd'
> ,'''SELECT DISTINCT DBT1.EFF_STAT,
> DBT3.FIN_INST_NM,
> DBT3.ADDR_LINE_1,
> DBT3.ADDR_LINE_2,
> DBT3.ADDR_LINE_3,
> DBT3.ADDR_CTY,
> DBT3.ADDR_ST,
> DBT3.ADDR_POSTAL_CD,
> DBT3.PHONE_NR,
> DBT3.CONTACT_NM,
> DBT1.ACCT_NR,
> DBT1.ACCT_NM_1,
> DBT1.ACCT_NM_2,
> AUTH.AUTH_STAT,
> AUTH.PERMVAL_NM,
> AUTH.NON_EMP_INDIC,
> AUTH.EMP_AUTH_NM,
> AUTH.NE_AUTH_NM,
> DBT50.PERMVAL_NM AS COUNTRY
> FROM TRSPROD.CTRS01_ACCOUNT_TB DBT1
> INNER JOIN TRSPROD.CTRS03_FININST_TB DBT3
> ON DBT1.FIN_INST_ID_NR = DBT3.FIN_INST_ID_NR
> LEFT JOIN (SELECT DISTINCT A.ACCT_ID_NR
> , A.NON_EMP_INDIC
> , C.NAME AS EMP_AUTH_NM
> , D.NAME AS NE_AUTH_NM
> , A.EFF_STAT AS AUTH_STAT
> , B.PERMVAL_NM
> FROM TRSPROD.CTRS04_AUTHRITY_TB A
> INNER JOIN TRSPROD.CTRS50_PERMVAL_TB B
> ON A.AUTH_TYPE_ID_NR = B.PERMVAL_ID_NR
> LEFT JOIN BACPROD.PS_PERSONAL_DATA C
> ON A.EMPLID = C.EMPLID
> LEFT JOIN TRSPROD.CTRS20_NONEMP_TB D
> ON A.EMPLID = D.NON_EMPLID
> WHERE A.EFF_DT = (SELECT MAX(EFF_DT)
> FROM TRSPROD.CTRS04_AUTHRITY_TB E
> WHERE A.ACCT_ID_NR = E.ACCT_ID_NR)) AUTH
> ON DBT1.ACCT_ID_NR = AUTH.ACCT_ID_NR
> LEFT JOIN TRSPROD.CTRS50_PERMVAL_TB DBT50
> ON DBT1.CNTRY_ID_NR = DBT50.PERMVAL_ID_NR'') DB1
> WHERE (DB1.FIN_INST_NM LIKE ''' + @.INST + '%'')
> AND (DB1.AUTH_STAT = ''A'')
> AND (DB1.EFF_STAT LIKE ''' + @.STAT + '%'')
> ORDER BY DB1.EFF_STAT
> , DB1.FIN_INST_NM
> , DB1.ACCT_NR
> , DB1.PERMVAL_NM''
> ) DB2')
> Select @.error=@.@.error, @.rowcount=@.@.rowcount
> IF @.error <> 0
> BEGIN
> RAISERROR('Read of database failed ',16,1)
> SET @.ret_cd = 1
> RETURN 1
> END
> IF @.rowcount = 1
> BEGIN
> SET @.ret_cd = 0
> RETURN 0
> END
> SET NOCOUNT OFF
> RETURN 0
> / ****************************************
*********************************
**********************
> ******************************* End of Procedure SQL
> ****************************************
***
> ****************************************
**********************************
*********************/
> END
> GO
> ========================================
============
> --
> Joe Palm
> Senior Technical Developer
> Madison, WI