Processing Errors in Reporting Services

Last Post 05 Jan 2007 08:03 AM by jstroope01. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

04 Jan 2007 02:40 PM

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

''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?


New Member
New Member

05 Jan 2007 01: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.

New Member
New Member

05 Jan 2007 08:03 AM
Thanks Jamie!

Acceptable Use Policy