June 14, 2013 at 8:18 am
Hi all,
I am attempting to run a stored procedure with a single output parameter and then map it to a variable. Seems simple in theory, but I keep getting the error
"There is an invalid number of result bindings returned for the ResultSetType"
After looking at all the answers i can find, none seem to answer my problem.
the SP:-
Create Procedure ReturnsessionVariables(@sessionid INT,@groupshare varchar(2000),@returnvalue nvarchar(max) output)
as
begin
DECLARE @GUID NVARCHAR(8)
SELECT @GUID = guid from THANDLERSESSION where sessionid = @sessionid
select @returnvalue = substring((SELECT ','+keyname+'='+case when CHARINDEX(@guid,keyvalue) != 0 and keyvalue != @GUID then @groupshare + right(keyvalue,len(keyvalue)-CHARINDEX(@guid,keyvalue)) else keyvalue end
from THANDLERPARAMS HP
where sessionid = @sessionid
FOR XML PATH ('')),2,2500)
end
Basically i am reading all the parameters from a table and creating a concatenated string that can then be passed to the next step in my package.
Output Example :-
hrfile=tvdev2.xlsx,skipexceptions=0,appenddata=-1,emailpassword=-1
this proc works fine and returns the data when I run it in SQL.
In SSIS i am attempting to run the proc and return the value to a variable.
As far as i can see I am doing everything ok, but i cannot work out what I must be doing wrong. This is a single return value proc so no issue with more than 1 row, object assignment etc. So what i am doing wrong?
Thanks
June 14, 2013 at 8:26 am
Try putting
SET NOCOUNT ON
at the top of your proc.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 14, 2013 at 8:29 am
Hi,
Thanks for the suggestion
Unfortunately, it has made no difference. Still the same error getting returned in SSIS when I try to run it.
June 14, 2013 at 8:43 am
It's amazing what randomly changing things does.
It seems that because I had a result set as singleRow it was expecting a dataset to come back , and it does not interprete an output value from a Stored Proc as a dataset.
By simply changing the resultset value to "None", the issue has gone away and my value is being returned fine.
Cheers
June 14, 2013 at 8:46 am
Do you have anything on the resultset tab?
Edit - well done on solving it.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply