MS SQL stored proc

[JEMIAZ]

Hi
I have a problem with MS SQL stored procedure if anyone can suggest ideas.

Using the CALL [?=] format I can't get my procedure to return multiple output rows from a query. It will work with the EXEC option but then I can't specify other return codes.

I've tried with/without the ?= option and with/without output variables - ? in command

Is this something that 6.7.1 does not support with the call option?
Am i possibly writing my SP incorrectly - simple version below

CREATE PROCEDURE SDS269
@I_CompanyProfile VARCHAR(100)
AS
BEGIN
SELECT TRAN_LOG_TIMESTAMP, TRAN_LOG_MESSAGE
FROM X_TRANSACTION_LOG_SUMMARY
WHERE TRAN_LOG_PARTNER_ID =@I_CompanyProfile
return
end
GO



any suggestions welcome

tim

 

[JuJutsu]

What version of the product? If you have simple test case send to support.


JuJutsu - Jeff S.
Support Analyst

 

[eyetry]

Are you executing the stored procedure again an f_map or a runmap?

The only way we were able to get multiple rows from a stored procedure was by executing a runmap with the procedure as input.

eyetry

 

[JuJutsu]

If you are calling the SP in a map rule, the data may be multiple rows, but will need to be a text blob to fit in the rule. Depends on what you need.



JuJutsu - Jeff S.
Support Analyst

 

[JEMIAZ]

Hi
I'm was running the command from a rule, but not in a Func Map and outputting to a text blob.
=dblookup(call ?= SDS269 (ABCD), connection options)

Mercator 6.7 and SQL 2000 on Windows 2000 server

I tried using a ? return variable in the parameter list (and changed the SP to match)

In the end I switched to using staight SQL from the dblookup and the problems went away. The question was 'for the next time' as much as anything.
Anyone got an example that worked in this scenario?

Thanks for the feed back so far.
Tim