SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Reporting Services Development
 Not getting any fields on refresh
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

itmaster
Starting Member

25 Posts

Posted - 08/11/2014 :  15:39:53  Show Profile  Reply with Quote
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

Edited by - itmaster on 08/11/2014 16:01:39

dmckinney
Starting Member

France
3 Posts

Posted - 08/12/2014 :  05:33:37  Show Profile  Reply with Quote
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

25 Posts

Posted - 08/12/2014 :  08:03:26  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New 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.06 seconds. Powered By: Snitz Forums 2000