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.
| 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 withSELECT 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. |
 |
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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. |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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. |
|
|
|
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 |
|
|
|
|
|
|
|
|
Query TopN into #Temp problem