Cant use temp tables in stored procs with Sql Reporting Services 2005 Report Wizard

You receive something like:
Invalid object name '#Temp'

The wizard sets "SET FMTONLY ON" before running your procedure - which tells sql server to run the query without processing the results, and just sent over the data format (ie columns, types)

You can add "SET FMTONLY OFF" to the top of your procedure to bypass the wizard check.
http://msdn.microsoft.com/en-us/library/ms173839.aspx

After you do the wizard, you can remove this change.

Select Top X from Grouped Results

Every now and then because of a particular design I need to get top X number from grouped results.

In this particular case, each record has a status history with it and the status changes are tracked in a related history table.
In this case we'll call it
Job
JobStatusHistory

I want the most recent status from the JobStatusHistory, but there can be many status changes per record. Luckily the solution is easy (there are others including cursor based ways, but this is a fairly elegant solution)


We can use the little known row_number over(parition by XX) t-sql command
It provides great functionality but you really don't see it too much.

This gets me all results plus a field named 'rownum' (you can call it anything since I'm just aliasing it here as another field)

select id,row_number()over(partition by jobid order by createddate desc) rownum
from jobstatushistory

So having numbered results, we can use this as a where clause to get each result.


select jobid,newstatus from(
select jobid,newstatus,row_number()over(partition by jobid order by createddate desc) row_num
from jobstatushistory) t
where row_num=1


This will number the rows and get only the first one from each group (in this case 'grouped' is ordered by created date, rather than an actual 'group by' clause.

more sql 2k5 items

While messing around with authentication options I receieved:

Server Error in '/Reports' Application.
Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[NullReferenceException: Object reference not set to an instance of an object.]
Microsoft.ReportingServices.UI.GlobalApp.Application_AuthenticateRequest(Object sender, EventArgs e) +73
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +79
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +176


Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433


To solve this: Change the asp.net configuration to be windows authentication rather than none on the reportmanager virtual directory (asp.net tab - edit configuration and change auth there).
This was my own fault but in case anyone else does the same thing while playing around with authentication options in reporting services this may help : )

Sql Reporting Services 2005 - The current action cannot be completed because the user data source credentials that are required to execute this report

Received: The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting)

This occured when using a report with an expression based connect string to connect to sql server.
The report data source is embedded and set to "No Credentials"
It works locally on my Vista machine/IIS 7 but on Windows 2003 R2 IIS 6 it does not.
So as per Microsoft on:
http://msdn.microsoft.com/en-us/library/ms160330.aspx

You can configure a data source connection to use no credentials. Microsoft recommends that you always use credentials to access a data sources; using no credentials is not advised. However, you may choose to run a report with no credentials in the following cases:

* The remote data source does not require credentials.
* The credentials are passed in the connection string (recommended only for secure connections).
* The report is a subreport that uses the credentials of the parent report.

Under these conditions, the report server connects to a remote data source using the unattended execution account that you must define in advance. Because the report server does not connect to a remote server using its service credentials, you must specify an account that the report server can use to make the connection. For more information about creating this account, see Configuring the Unattended Execution Account.




So go into the reporting services config tool and set an execution account, this worked for me.