D J Horton Consulting Ltd

Personal reference to useful tips and tricks for SQL Server, SQL Server Reporting Services and Crystal Reports.



My alternative to scraps of paper lying about.



Others may find bits of it useful!








Wednesday, 30 September 2009

SQL Server 2005 Failed to install due to ASP Account not being setup - SQL

Whilst attempting to set up a work computer as a testbed I was going through the process of installing SQL Server 2005 server on XP. I had previously installed all of the components including IIS, .Net framework, etc. In the middle of the install, I got the following error:

SQL Server Setup failed to obtain system account information for the ASPNET account

To proceed, reinstall the .NET Framework, and then run SQL Server Setup again.
The problem is that the user ASPNET was not created at any of the previous installs. To create this user, I did the following (note that framework version may have a different version on your machine)

cd C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
aspnet_regiis -i

This creates the ASPNET account. After the account is created, SQL Server 2005 installed normally.

This tip was gleaned from:

http://paulsiu.wordpress.com/2007/01/19/sql-server-2005-failed-to-install-due-to-asp-account-not-being-setup/

Tuesday, 15 September 2009

Format Dates in formula - CR

ToText({fldName}, "dd-MMM-yyyy")

Friday, 4 September 2009

Keep Group Together - SSRS

I believe SSRS lacks the functionality to keep groups of data together.

Say I have a table containin one grouping of data. The report contains enough groups to use more that one 'page' when rendered. The report splits an individual group if it appears at the bottom of the page amd continues with the details on the subsequent page. There seems to be no method of keeping the group together i.e. if the indivual group will not completely fit on the bottom of the page, it will be rendered in its entirety on a fresh page. Thinking about it, if this functionality were available what would happen to a group of data that naturally exceeds one rendered page?

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126895

A work-around that may work....keep a tally of the the number of rows returned and insert a page break after x.....will look into this...

Wednesday, 2 September 2009

Blank space appears in the file when you export a report to a physical page format file in SQL Server 2005 Reporting Services

Blank space appears in the file when you export a report to a physical page format file in SQL Server 2005 Reporting Services or in SQL Server 2000 Reporting Services
http://support.microsoft.com/kb/938943

Reporting Services Login prompt when deploying solution - SSRS

Just spent 20 minutes fathoming out this issue. I've been transferring a SSRS solution between a 'test' laptop and my work PC, both using local reporting services servers. Whilst deploying an rdl I got a Reporting Services Login prompt, having never seenone before in BIDS I had to do some google searching. Inially I thought it was something to do with IIS security settings but eventually realised that TargetServerURL value was pointing to my other server. So it was simply a case of adjusting this value. Suprised I haven't witnessed this before as I'm sure I've made this mistake in the past