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