Returning Stored Procedure Values from OpenQuery

Question

Returning Stored Procedure Values from OpenQuery

ccroasmun

Valued Member

Points: 72
More actions
May 5, 2020 at 6:17 pm
Go to Answer
#3749040

I am having issues trying to return fields from an OpenQuery embedded in TransAct. In the code below ManuItem and ManuItemDesc come back as NULL. Any help would be appreciated:

USE [MyDataBase]
GO
/****** Object: StoredProcedure [dbo].[My-StoredProcedure] Script Date: 5/5/2020 1:26:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[My-StoredProcedure]
@ItemNo char(25),
@ManuItem char(25) OUTPUT,
@ManuItemDesc char(40) OUTPUT
AS
BEGIN
DECLARE @TSQL varchar(8000)
SELECT @TSQL =
'SELECT ''''' + @ManuItem + ''''' = ManuItem , ''''' + @ManuItemDesc + ''''' = ManuItemDesc
FROM
OPENQUERY(ISERIES,
''SELECT
PRODDTA.F4101.IMLITM
PRODDTA.F4101.IMDSC1
FROM PRODDTA.F4101
WHERE PRODDTA.F4101.IMLITM = ''''' + @ItemNo + ''''''')'
EXEC (@TSQL)
END

frederico_fonseca

SSCoach

Points: 16272
More actions
May 6, 2020 at 9:37 am
Answer
#3749222

you are missing the commas separating the columns

Viewing 8 posts - 1 through 8 (of 8 total)

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

frederico_fonseca SSCoach Points: 16272 More actions · Answer 1

that code isn't setting the variables - but rather concatenating the contents of the variables with the remaining of the SQL statement

following should do the trick

ALTER PROCEDURE [dbo].[My-StoredProcedure]
@ItemNo char(25),
@ManuItem char(25) OUTPUT,
@ManuItemDesc char(40) OUTPUT
AS
BEGIN

DECLARE @TSQL varchar(8000)

declare @tbl table 
(ManuItem varchar(25)
,ManuItemDesc varchar(40)
)

SELECT @TSQL =

'SELECT ManuItem, ManuItemDesc
FROM
OPENQUERY(ISERIES,
''SELECT
PRODDTA.F4101.IMLITM
PRODDTA.F4101.IMDSC1
FROM PRODDTA.F4101
WHERE PRODDTA.F4101.IMLITM = ''''' + @ItemNo + ''''''')'

insert into @tbl
EXEC (@TSQL)

select @manuitem = manuitem, @ManuItemDesc = ManuItemDesc
from @tbl

ccroasmun Valued Member Points: 72 More actions · Answer 2

Thanks,

Not working as you described but sends me down a new path

ccroasmun Valued Member Points: 72 More actions · Answer 3

OLE DB provider "MSDASQL" for linked server "ISERIES" returned message "[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token . was not valid. Valid tokens: , FROM INTO.".

Msg 7321, Level 16, State 2, Line 3

An error occurred while preparing the query "SELECT

PRODDTA.F4101.IMLITM

PRODDTA.F4101.IMDSC1

FROM PRODDTA.F4101

WHERE PRODDTA.F4101.IMLITM = '7525 '" for execution against OLE DB provider "MSDASQL" for linked server "ISERIES".

ccroasmun Valued Member Points: 72 More actions · Answer 4

I'm sorry, I'm having a heck of a time with this. Below is the latest code and error.

USE [MyDB]

GO

/****** Object: StoredProcedure [dbo].[SP_MyProcedure] Script Date: 5/5/2020 1:26:19 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[SP_MyProcedure]

@ItemNo char(25),

@ManuItem char(25) OUTPUT,

@ManuItemDesc char(40) OUTPUT

AS

BEGIN

DECLARE @TSQL varchar(8000)

DECLARE @Tbl TABLE (ManuItem char(25), ManuItemDesc char(40))

SELECT @TSQL = 'SELECT ManuItem, ManuItemDesc

FROM

OPENQUERY(ISERIES,

''SELECT

PRODDTA.F4101.IMLITM,

PRODDTA.F4101.IMDSC1

FROM PRODDTA.F4101

WHERE PRODDTA.F4101.IMLITM = ''''' + @ItemNo + ''''''')'

INSERT INTO @Tbl

EXEC (@TSQL)

SELECT @ManuItem = ManuItem, @ManuItemDesc = ManuItemDesc

FROM @tbl

END

Msg 207, Level 16, State 1, Line 3

Invalid column name 'ManuItem'.

Msg 207, Level 16, State 1, Line 3

Invalid column name 'ManuItemDesc'.

(0 row(s) affected)

(1 row(s) affected)

ccroasmun Valued Member Points: 72 More actions · Answer 5

Got it. Needed to specify the actual field names in the select statement SELECT IMLITM AS ManuItem......etc. Thanks alot

frederico_fonseca SSCoach Points: 16272 More actions · Answer 6

not needed any more

This reply was modified 5 years, 5 months ago by frederico_fonseca.