July 17, 2008 at 6:35 am
Hi all
I'm having a hard time setting up a report. I have a sproc that runs without a problem in Management Studio that creates and populates a temporary table, then selects data from it.
If I call the sproc from SSRS as a datasource I get nothing returned. I've read other posts here and elsewhere that say that I need to enter the column names manually when defining the datasource, but this hasn't helped.
I've created a new proc for test purposes like so:
Create Proc TestProc
AS
Create Table #vtblResult
(FeeEarnervarchar(255))
Insert Into #vtblResult
Select FEName
From admAccessFEarners
Select FeeEarner From #vtblResult
I made a new Datasource, Command Type of Stored Procedure, and manually entered the Field Name on the fields tab. When I try and run the report, no data is returned. I've checked all permissions, and they're fine as far as I can tell.
Anyone got an idea?
Thanks
Chris
Do not write in this space.
July 17, 2008 at 6:44 am
I use stored procedures on every report I write, I simply don't have any problems with them at all, and I have to say that I have never entered the field names manually into SSRS.
A question though.
You say you have created a new datasource......
Do you really mean datasource, or do you mean dataset?
Your datasource should be set up at the project level and should point to the appropriate server/database on which the stored procedure exists.
You dataset should be set up at the report level and this one should call the stored procedure.
Another thing to try, which sometimes I have had to do, change the definition of the dataset to Text and then in the query area type in the command:
EXEC @StoredProcName
Regards,
Nigel West
UK
July 17, 2008 at 6:58 am
Sorry, my fault - I do mean dataset every time.
I have no problems with stored procedures that select from an existing table (using the example above there would be no problem with 'Select FEName From admAccessFEarners'), but only where I am creating a temporary table in the stored procedure.
Chris
Do not write in this space.
July 17, 2008 at 7:01 am
Try changing your table to a memory table rather than disk based.
DECLARE @TempTable Table
INSERT INTO @TempTable
If this works then it points to a permissions issue on creating temp tables on the hard disk, for reference, the above tables should be faster most of the time.
Regards,
Nigel West
UK
July 17, 2008 at 7:04 am
This gives the same result unfortunately - nothing!
Do not write in this space.
July 17, 2008 at 7:34 am
Try putting:
SET NOCOUNT ON
at the top of your proc.
July 23, 2008 at 6:42 am
For the benefit of anyone else reading this who is experiencing similar problems, it looks like the problem may well have been related to the fact that my datasource was an ODBC connection to the SQL Server.
I replaced the datasource with a new one where the datasource type was Microsoft SQL Server and used the same login name and password that the ODBC link was using.
When I created a dataset using this new datasource I had no problems with it running any stored procedures, or with it returning data from temporary table variables.
Hope someone can feel the benefit of my pain one day 😀
Chris
Do not write in this space.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply