Processing Errors in Reporting Services

Last Post 05 Jan 2007 07:03 AM by jstroope01. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
jstroope01
New Member
New Member

--
04 Jan 2007 01:40 PM
Hi,

I'm relatively new to SQL Server and Reporting Services. I have Visual Studio 2003 and SQL Server 2000 installed on my computer. I have a report that uses a stored procedure as its dataset. The dataset's name is 'sp_Top_5'. The report has two parameters: ServerID and Year(@ServerID and @Year in the stored procedure). The stored procedure resides on my local SQL Server installation. The stored procedure runs a query against one of several remote SQL Server databases depending on value of the ServerID parameter.

The report works fine except for an annoying quirk - every once in a while, I get an error message as follows:

Processing Errors

An error has occurred during report processing.
Query execution failed for data set 'sp_Top_5'.
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

So, I have to go to query analyzer and execute the stored procedure. Then the report will run again for a while. This is a hassle.

Here's the query string:

set @strSQL =
'INSERT INTO #DownTimeTotal (Year, Month, Total)

SELECT DATEPART(year, m.DateTimeValue) AS Year, DATEPART(month, m.DateTimeValue) AS Month,
SUM(m.Duration*1.0/3600) AS Total

FROM OPENDATASOURCE(''SQLOLEDB'',
''Data Source= ' + @Server + ';Integrated Security=SSPI'')
.MinsterDatabase.dbo.DTCByMonth m
WHERE m.Available = 1 AND DATEPART(year, m.DateTimeValue) = ' + @Year + ' AND m.DateTimeValue <
(
select cast(cast(datepart(year, getdate()) as char(4)) + ''-'' +
cast(datepart(month, getdate()) as varchar(2)) + ''-01 00:00:00.000'' as datetime)
)
GROUP BY m.DateTimeValue'

Does anyone know what's going on here?

Thanks,

Jeff.
JHunter
New Member
New Member

--
05 Jan 2007 12:19 AM
It will be something to do with the windows authentication being reset, try using sql authentication rather than a trusted connection in your OPENDATASOURCE function.

Jamie
jstroope01
New Member
New Member

--
05 Jan 2007 07:03 AM
Thanks Jamie!
You are not authorized to post a reply.

Acceptable Use Policy