Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Development Tools
 Reporting Services Development
 Query TopN into #Temp problem

Author  Topic 

blodzoom
Starting Member

28 Posts

Posted - 2015-02-24 : 13:15:45
I have an SSRS report with a fairly simple select. I set it up with a parameter so that the user can control how many records with

SELECT TOP (Convert(int,@CustNum))

I don't know if it's related to the current problem, but I had to use the explicit convert even though @CustNum is an int type.

This works. But then I tried to go further and use a #temp table in the query so that I could get a comma separated list of the customerkey.

As soon as I add INTO #Temp to the query, even if I do nothing else, I get "Top or FETCH contains an invalid value" error. If I get rid of INTO #Temp or change it to TOP 10 instead of SELECT TOP (Convert(int,@CustNum)) it works again.

My query works great in SSMS. Is this a known limitation or bug?

TL;DR I can't use TOP (@Parameter) in combination with a #Temp table.

blodzoom
Starting Member

28 Posts

Posted - 2015-02-24 : 13:20:27
As a followup, is there some way to base one dataset on a query against another? Basically use a dataset as a temp table? I don't see a way, but I'm wracking my brain here for a way around this.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-24 : 14:08:41
Wrap the code into a stored procedure instead of doing this in the report, have the report use the stored procedure.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

blodzoom
Starting Member

28 Posts

Posted - 2015-02-24 : 15:31:19
tkizer,

Thank you, that would be an excellent suggestion. Problem is, I don't have access to create procs on the data source and the users won't have access to my sandbox environment where I can create procs.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-24 : 15:47:13
Then get access so that you can create stored procs as this is an issue with SSRS query syntax. You could file a bug with MS also.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

blodzoom
Starting Member

28 Posts

Posted - 2015-02-26 : 14:09:11
If only I worked in that type of world. I figured out an embarrassingly stupid workaround that I won't bother sharing here because it's not going to help anyone and your suggestion is the actual correct answer.
Go to Top of Page

newwaysys
Starting Member

9 Posts

Posted - 2015-04-10 : 05:36:01
I don't know if it's related to the current problem, but I had to use the explicit convert even though @CustNum is an int type.

This works. But then I tried to go further and use a #temp table in the query so that I could get a comma separated list of the customerkey.

As soon as I add INTO #Temp to the query, even if I do nothing else, I get "Top or FETCH contains an invalid value" error. If I get rid of INTO #Temp or change it to TOP 10 instead of SELECT TOP (Convert(int,@CustNum)) it works again.
Recently I generate Code 39 barcode in Reporting Service with this barcode tool unspammed. I encounter a problem. When I try to encode the data in my file, it appears error. Why? Can anyone tell me what kind of data can Code 39 encode? Did I choose the wrong type of barcode or it was the prolem of the barcode tool? I read the barcode creation guide in Reporting Service
Go to Top of Page
   

- Advertisement -