Linking to Stored Procedures in Reporting Sevices
-
I have recently started using Reporting Services and am having a couple of problems with 2 reports that are linked to stored procedures in EM. The first one no mater what I have tried I can not get the fields list to fill. If I add the field I get column headers but no data. If I execute the SP more than once, I get another error that says 'An error occurred while trying to retrieve the parameters in the query. Derive parameters require an open and available connection. The connections current state is closed.'
2nd I have another report that I have again calls on a stored procedure and I get a message that states 'An error occured while reading data from the query result set. Conversion Overflow'
Both of the title bars for the error state "Microsoft Development Enviroment" Has any one else had a problem like this and know of a work around. The stored procedures have been used for over a year in Crystal and have never had a problem similar to this.
Thanks in advance for any help recieved
Kurt
Kurt Kracaw
-
I haven't seen these errors, have you tried just editing the dataset down to only information on that first tab? Don't give it the parameters at all, then hit the refresh fields button and it should work, at least whenever i've had trouble with the datasets/sprcs this worked for me
-
I am not exactly sure what you asking me to do, Hoepfully I can shed alittle more light on this. When I run the query I do get a result set returned. At that point I have hit the refresh button and still have not been able to fill any of the fields. I have tried adding them exactly as they are listed in the results. If I do this and go to the Layout tab in VisualStudio and create a report the data does not come over for the fields I have entered previously. And nothing shows in the preview tab except the column headers,which I entered. This make no sense at all to me. If I can give you any other info let me know.
Thanks Again
Kurt Kracaw
-
in the parameters tab delete your parameters, on the main tab make sure it has Stored Procedure selected and your text it just the sproc name. then leave everything else blank and hit ok. Next click on the refresh button it should prompt you for values of the parameters and the sproc will simulate running, afterwards the fields should be there.
-
I deleted all of the parameters on the Parameter tab. Then went to the 'QUERY' or main tab and all that appears is the NAME, DATA SOURCE, STORED PROCEDURE and the name of the stores procedure (all of which are filled in properly), click OK and then clicked the refresh button on the data tab and nothing happens if I hit the '!' or run button I get prompted for the parameters and I can hit OK and leave them blank and I get a error returned, string returned is not recoginized as a valid date time. and again nothing happens. If I enter the parameters I do get a data return just no fields. All of the columns have the field listed that area called out in the stored procedure. The only thing that I can figure is something in the StoredProcedure must be different from all the others. (I have several reports with the same parameters from the same tables that work fine). Thanks Again
Kurt Kracaw
-
hmm i'd delete the dataset and start over..sounds like some field has a format being applied to it and it isn't passing that format action, could be a cast inside the sproc too hard to tell without seeing it.
-
I looked at the StoredProcedure and there are not cast in side it... but I do have a CASE
case strTimeBasisCode
when '1' then 0.1
when '2' then 0.01
when '3' then 0.001
when '4' then 0.0001
when 'H' then 1
end as fMultiplier,
This is two times in the procedure once on a active table on once on a hisory table. Used exactly the same in both instances
Wonder if this might be part of the problem, I checked some of the other StoredProcedures and see if I use it anywhere else. I have several of the SProc that have CAST only this only this one and the other report that I am having problems with have the CASE, this might be where I am having a problem??? Any suggestion?
Kurt Kracaw
-
i dont think that's it you would have gotten a sql error if it was, can't think of anything else besides making sure that all the fields are not on that window and no where in the report layout yet..it sounds like report designer trying to format some of the output and the output is all screwwed up
-
Thanks for all of yur suggestions I wil keep plugging away and post what ever I find does the trick.
Kurt Kracaw
-
After looking through some of the other forums and saw a couple of things that made a question or two pop up. Could this be caused by using temp tables, there are temp tables that are used in this paticular StoredProcedure and they are #tempTable. I saw a post that said try using @temptable. Are these used the same way or do you have to use them differently?
Kurt Kracaw
-
#temp should work fine now, it is going away with Yukon however, all temp tables will need to be @temp instead..
That is most likely not the issue
-
When I had this problem in VB it was solved by adding
SET NOCOUNT ON
at the front of the sp.
-
I already have that at the front of the Stored procedure and it hasn't made a difference. Thanks for the suggestion!
Kurt Kracaw
-
I'm trying to use a stored procedure (my first reporting services report trying to use one) and I can't figure out how to reference the names of the fields returned in the table. The stored procedure is doing a select on an table as the resutls set. I'm expecting this table to be visiable in the report. I have gotten the values to retreive in the data window. But it will not link the fields into the dataset window. If I manually add each one they show up as a field. Do I have to manually do this for every field or is there something I have not done properly?
-
Once you have the results back in the dataset, you need to click the refresh button. The fields should now be available to you.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply