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

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.

---