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
 Not getting any fields on refresh

Author  Topic 

itmaster
Starting Member

28 Posts

Posted - 2014-08-11 : 15:39:53
Hi,
When I run this SQl in SQL server 2000 it runs and returns the data. However, in reporting services it does not return any fields when I hit the refresh button. It does not give me an error either. I have been looking at this on line and I do not know what is wrong.
Any ideas would be really appreciated.
Note @CUNO, @Yr, @Mt are input parameters, that I have tryed not decaling and declaring and bothways does not work.

Thank you

My code:
DECLARE @CUNO int
DECLARE @Yr nvarchar(4)
SET @Yr = RIGHT(@Yr, 2)
DECLARE @Mt nvarchar(5)
IF LEN(@Mt) < 2 BEGIN
SET @Mt = '0' + @Mt
END
DECLARE @dt3 nvarchar(8)
SET @dt3 = @Yr + @Mt
DECLARE @TbHold nvarchar(10)
SET @TbHold = 'RAP' + @dt3
DECLARE @strSQL nvarchar(2000)
SET @strSQL = ' SELECT RACUST,
RAACCT,
RADATE,
RATIME,
RAOPHN,
RTRIM(LTRIM(RACITY))+''/''+RTRIM(LTRIM((RASTAT))) as CITYSTATE,
RATYPE,
RADUR,
RATREV,
RAREV,
RAAUTH,
BillName,
CASE WHEN RATYPE in (''8DI'',''8GI'',''8I'',''8X'',''8D'')
THEN raauth
ELSE RAPHN
END AS RAPHN,
RADATETIME
FROM ' + @TbHold + ' LEFT OUTER JOIN CallTypes ON RATYPE = UsageName ' + ' WHERE RACUST = ' + Cast(@CUNO AS varchar(20))
+ ' OR RACUST IN (SELECT distinct CGCUST FROM TCPCGCM WHERE CGCPCU = ' + Cast(@CUNO AS varchar(20)) + ')'
PRINT @strSQL EXEC Sp_executeSql @strSQL

dmckinney
Starting Member

3 Posts

Posted - 2014-08-12 : 05:33:37
This has got to be a permissions thing, with your dynamic sql.
I suggest you do a test where you replace your sql string with 'select 1 from table1' where table1 is a table that exists. i.e. remove all the 'complexity' leaving just something simple that doesn't work!

If 'select 1 from table1' doesn't work as dynamic sql (in a string) then try the same, but not dynamically, i.e. select 1 from table1 directly in the body of the sql. If that works (which it should) then you know your problem is about executing dynamic sql under the account used for reporting services.

Hope this helps get you closer.

David McKinney.
Go to Top of Page

itmaster
Starting Member

28 Posts

Posted - 2014-08-12 : 08:03:26
quote:
Originally posted by dmckinney

This has got to be a permissions thing, with your dynamic sql.
I suggest you do a test where you replace your sql string with 'select 1 from table1' where table1 is a table that exists. i.e. remove all the 'complexity' leaving just something simple that doesn't work!

If 'select 1 from table1' doesn't work as dynamic sql (in a string) then try the same, but not dynamically, i.e. select 1 from table1 directly in the body of the sql. If that works (which it should) then you know your problem is about executing dynamic sql under the account used for reporting services.

Hope this helps get you closer.

David McKinney.



Thanks I will check that out.
Go to Top of Page
   

- Advertisement -