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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Reporting Services Development
 Query TopN into #Temp problem
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

blodzoom
Starting Member

USA
28 Posts

Posted - 02/24/2015 :  13:15:45  Show Profile  Reply with Quote
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

USA
28 Posts

Posted - 02/24/2015 :  13:20:27  Show Profile  Reply with Quote
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

USA
38200 Posts

Posted - 02/24/2015 :  14:08:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
28 Posts

Posted - 02/24/2015 :  15:31:19  Show Profile  Reply with Quote
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

USA
38200 Posts

Posted - 02/24/2015 :  15:47:13  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
28 Posts

Posted - 02/26/2015 :  14:09:11  Show Profile  Reply with Quote
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

India
9 Posts

Posted - 04/10/2015 :  05:36:01  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000