Archive for the ‘SQL Server’ Category

SSRS Stops Working Overnight

June 2, 2015

Working one day, gone the next! SSRS reports wouldn’t open, wouldn’t run, and Central Admin SSRS service app wasn’t working either. I got various errors such as:
– this website requires you to log in
– COM error
– IncludeExceptionInDefault

Fix for me was the blog below:

https://adammcewen.wordpress.com/2012/12/10/reporting-services-is-sleepingand-wont-wake-up/

Setting SSRS Column Headings for CSV Output

October 8, 2014

Got stuck on this today and found this post:
http://www.sqlchick.com/entries/2011/8/12/updating-the-ssrs-name-property-to-control-csv-column-headin.html
Not sure why, but I had to edit the textbox properties of the detail row text box, not the text box in the header row. Works now though!

SQL Server Reporting Services (SSRS) 2012 Service not appearing in SharePoint 2013 Central Administration

October 7, 2013

This caught me out when I was installing SSRS in SharePoint mode on my SharePoint 2013 lab.

I have three servers in my sharepoint farm, SP-MAIN with web and central admin, SP-APP for app and search, and SP-DB for my database components. SharePoint 2013 was installed and working on all servers and I wanted to install SSRS 2012 on my SP-APP application server.

I installed the software on SP-APP, everything looked OK, but I couldn’t see the services in Central Admin. I used the Powershell commands:

Install-SPRSService
Install-SPRSServiceProxy
get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance

but it said the SharePoint reporting service was already running. Couldn’t see it in the services for SharePoint, and couldn’t create an application for SSRS.

Solution: Central Admin is only running on my SP-MAIN server, and you need to install the Reporting Services Add-In for it to show up in Central Administration. After running the SQL 2012 install and selecting the Reporting Services Add-In component (and after a required reboot because of some other updates), when I checked the SharePoint services running on my SP-APP server, sure enough there was the “SQL Server Reporting Services Service” started and running.

When I went to the Manage Service Applications page, I now had the option of creating a SQL Server Reporting Services Application.

 

HOWTO Move Forefront Identity Manager 2010 Databases to a Different SQL Server

October 3, 2011

I had installed FIM 2010 using an SQL Server alias, which worked fine – I use the same process when installing SharePoint and it has helped before, so I thought “why not with FIM?”. Famous last words.

I tried to deploy FIM 2010 Cumulate Update 1 and it failed, citing “Cannot connect to SQL Server”. I had the nasty suspicion that while FIM 2010 seemed to support SQL Server aliases, the Cumulate Update process did not.

FIM 2010 maintains information on SQL Server connections in the Registry under the hives:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\FIMService
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\FIMSynchronizationService\Parameters

The FIM services use these keys to establish database connections. Likewise, the Cumulative Update process accesses these locations to perform database updates.

A final note – after changing to a SQL Server instance instead of an alias, I got some errors when trying to upgrade the database via the Cumulative Update 1:

25070 Invalid class string

Installing the latest SQL Server Native Client from the Microsoft SQL Server 2008 R2 SP1 Feature Pack fixed these errors.

SharePoint 2010 and SQL Server Reporting Services – The request failed with HTTP status 401: Unauthorized

August 15, 2011

This error occurred due to browser issues and Kerberos authentication.

We haveĀ a SharePoint 2010 implementation (with Service Pack 1) and SQL Server 2008 R2 Reporting Services (SSRS) environment, with SSRS configured in integrated mode with SharePoint. We have enabled Kerberos authentication for single sign-on to SSRS via SharePoint 2010.

While running some training for the system our users could not open a report using their specially created test accounts. The report was accessing a SharePoint list to access the data. The user account could view the contents of the list without any issues, and had Contributor permission level in the SharePoint site collection. There were a couple of reasons why the reports failed:

  1. The accounts were created in a separate OU and due to a different Internet Explorer configuration via group policy, did not recognise our SharePoint 2010 website as an Intranet location/zone. Internet Explorer will not pass through credentials automatically when not in the Intranet zone, so Kerberos authentication failed.
  2. While fixing this issue, I happened to be using Firefox to test the different training user credentials. As per (1) above, this also failed because Kerberos authentication was not configured. You can configure Firefox to use Kerberos authentication by following these instructions
  3. You will probably not get any log entries in SSRS to try and track this particular error down, but you can use Kerberos logging to see any entries in the event log by following these instructions. Note that a reboot/restart of IIS is *not* required, so it is a good test to check kerberos issues

Hope this helps!

HOWTO Create a SQL Server Alias with the SQL Server Client Network Utility

February 24, 2011

The SQL Server Client Network Utility configuration tool allows you to manage protocols, aliases, database library options, and network libraries on a computer. In particular it allows you to creates an alias to a SQL Server instance, equivalent to adding a shortcut to SQL Server on a computer. Installing the SQL Server Client Network Utility is a lot quicker that installing the SQL Server Management Studio and tools.

The SQL Server Client Network Utility can be downloaded as part of the SQL Server Feature Pack. The SQL Server 2008 SP2 feature pack includes this utility, and a number of others.

To create an alias:

  1. Start the utility (typically installs in %SYSTEMROOT%\system32\cliconfig.exe)
  2. Select the “Alias” tab
  3. Click “Add”
  4. Enter the alias name
  5. Enter the details for the SQL Server instance you want to connect to
  6. Click OK

This will create an alias on that particular computer. If you have other computers that need an alias, this procedure needs to be run on each one.

SQL Server Version Number Information

January 22, 2010

SQL Server version numbers can provide information on what functionality, as well as resolved issues, are available in the build of SQL Server that has been installed. The following table provides a reference for the SQL Server version number, as well as the version label, for each addition of SQL Server. TechNet guidance on major version numbers is also available at the support site How to determine the version and edition of SQL Server and its components.

In SQL Server 2012 and SQL Server 2012 R2, use the following command:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

In SQL Server 2008 and SQL Server 2008 R2, use the following command:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

In SQL Server 2005, use the following command:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

In SQL Server 2000, use the following command:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The latest versions of SQL Server patches and updates can be found on the Update Center for Microsoft SQL Server.

SQL Version Build # DateĀ Released
7.0 RTM 7.00.623
7.0 SP1 7.00.699
7.0 SP2 7.00.842
7.0 SP3 7.00.961
7.0 SP4 7.00.1063
2000 8.00.194
2000 SP1 8.00.384
2000 SP2 8.00.532
2000 SP3 or 3a 8.00.760
2000 SP4 8.00.2039
2005 RTM 9.00.1399
2005 SP1 9.00.2047
2005 Rollup for SP1 9.00.2153
2005 SP2 9.00.3042
2005 GDR update to SP2 9.00.3150
2005 Cumulative Update 1 for SP2 9.00.3152
2005 Cumulative Update 2 for SP2 9.00.3175
2005 Cumulative Update 3 for SP2 9.00.3186
2005 Cumulative Update 4 for SP2 9.00.3200
2005 Cumulative Update 5 for SP2 9.00.3215
2005 SP3 9.00.4035
2005 SP4 9.00.5000
2008 RTM 10.00.1600
2008 Cumulative Update 10 10.00.1835
2008 SP1 10.00.2531
2008 Cumulative Update 1 for SP1 10.00.2710
2008 Cumulative Update 2 for SP1 10.00.2714
2008 Cumulative Update 3 for SP1 10.00.2723
2008 Cumulative Update 4 for SP1 10.00.2734
2008 Cumulative Update 5 for SP1 10.00.2746
2008 Cumulative Update 6 for SP1 10.00.2757
2008 Cumulative Update 7 for SP1 10.00.2766
2008 Cumulative Update 8 for SP1 10.00.2775
2008 Cumulative Update 9 for SP1 10.00.2789
2008 Cumulative Update 10 for SP1 10.00.2799
2008 Cumulative Update 11 for SP1 10.00.2804
2008 Cumulative Update 12 for SP1 10.00.2808
2008 SP2 10.00.4000
2008 Cumulative Update 1 for SP2 10.00.4266
2008 Cumulative Update 2 for SP2 10.00.4272
2008 Cumulative Update 3 for SP2 10.00.4285
2008 Cumulative Update 4 for SP2 10.00.4279 16-May-2011
2008 Cumulative Update 5 for SP2 10.00.4316 18-Jul-2011
2008 Cumulative Update 6 for SP2 10.00.4321 20-Sep-2011
2008 Cumulative Update 7 for SP2 10.00.4323 21-Nov-2011
2008 R2 RTM 10.50.1600
2008 R2 Cumulative Update 1 10.50.1702
2008 R2 Cumulative Update 2 10.50.1720
2008 R2 Cumulative Update 3 10.50.1734
2008 R2 Cumulative Update 4 10.50.1746
2008 R2 Cumulative Update 5 10.50.1753
2008 R2 Cumulative Update 6 10.50.1765
2008 R2 Cumulative Update 7 10.50.1777
2008 R2 SP1 10.50.2500 11-Jul-2011
2008 R2 SP1 Cumulative Update 1 10.50.2769 18-Jul-2011
2008 R2 SP1 Cumulative Update 2 10.50.2772 16-Aug-2011
2008 R2 SP1 Cumulative Update 3 10.50.2789 17-Oct-2011
2008 R2 SP1 Cumulative Update 4 10.50.2796 9-Jan-2012
2008 R2 SP1 Cumulative Update 5 10.50.2806 22-Feb-2012
2008 R2 SP1 Cumulative Update 6 10.50.2811 16-Apr-2012
2008 R2 SP2 10.50.4000 26-Jul-2012
2008 R2 SP2 Cumulative Update 1 10.50.4260 1-Aug-2012
2008 R2 SP2 Cumulative Update 3 10.50.4266 1-Aug-2012