SQL Reporting Keep Header Rows Visible Repeat on New Page

This shouldn't be that difficult to figure out. But it is, so here is how to do it.

Select the first static member of the Tablix and set the property of RepeateOnNewPage to True and the KeepWithGroup property to After.

That's it.

SQL Reporting Services Split Expression

=(Replace(Fields!WebUrl.Value, Parameters!ParentUrl.Value, "")).Split("/")(0)

SQL Server 2005 versions

SQL Server 2005 SP2 - 9.0.3042

SQL Server 2005 SP2 with critical update - 9.0.3050

 http://www.microsoft.com/downloads/details.aspx?familyid=e2c358a1-ecc4-4c49-8f65-daa6b7800eec&displaylang=en

SQL Server 2005 SP3 - 9.0.4035

http://support.microsoft.com/kb/321185

Custom SSRS Web Parts for SharePoint

No code but sounds like it can be done. This is in regards to developing custom SQL Reporting Services SharePoint Web Parts.

http://blogs.msdn.com/sharepoint/archive/2008/11/03/how-we-did-it-allowing-connections-to-multiple-ssrs-servers-with-report-viewer-and-explorer-web-parts.aspx

Current configuration settings prohibit embedding username and password in the database connection string SharePoint InfoPath

You may get this error using an InfoPath form within SharePoint

Current configuration settings prohibit embedding username and password in the database connection string.)

SharePoint Central Admin -> Application Management -> Configure InfoPath Forms Services.

Check the box next to Allow embedded SQL authentication and click the OK button.

Outlook BCM running on SQL Server 2008

Will BCM 2007 using a shared database work on SQL Server 2008? The answer is not clear. Technically there is no reason why it shouldn't. However, it has not been tested with SQL 2008 and the increased security in SQL 2008 may cause issues.

Should you move to SQL 2008 if you are using a BCM 2007 shared database? Probably not.

Can you? Yes, but you're pretty much on your own to troubleshoot any issues that arise to make it work correctly.

If you have done this, shoot me a note and let me know the steps you took or issues you ran into, I'm sure others would love to get feedback on how it went.

SQL 2008 Open Table is gone

Another change that wasn't obvious at first is that the Open Table option is gone when a table is right clicked. I'm sure that was probably ambiguous so MS had to take it out.

The Open Table command has now been replaced with Edit Top 200 Rows.

Well what if there is a row at 201 that I want to edit. Is there no easy way to right click and just open all the rows.

SQL 2008 Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.

I've just started using SQL 2008 and have encountered a very annoying problem. Whenever I change a table I get an error:

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

WTF? I was able to change tables whenever I wanted to in SQL 2000 and 2005 and now I'm not allowed to in SQL 2008. Oh wait, I need to disable some stupid option that shouldn't have been enabled in the first place. To do this follow these instructions:

Within SQL Management Studio click on the Tools menu. Then Options, then the Designer node and uncheck the box that says: Prevent saving changes that require the table to be re-created.

Now we can actually work with tables like we were able to do in SQL 2005. Nice extra step MS, next time make it more difficult to do what we need to do.

Login failed for user 'username'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

This error:

Login failed for user 'username'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)


Most likely indicatest that your SQL Server is not enabled for Mixed Mode authentication, it is probably only allowing Windows logons to log onto it, make it mixed mode and then you'll be able to connect using your SQL server login account. Quick fix but unknown unless you have this information.

SQL Server Reporting Services SharePoint Set Server Defaults

Trying to configure Reporting Services 2005 with an existing SharePoint site is painful to say the least. One of the pains was the error below when clicking on "Set Server Defaults" under Application Management within Central Admin. After searching through some forums, I found a solution

I when to the Operations tab in Central Admin and clicked "Alternate access mappings" and then selected the Central Admin collection and removed all mappings except for the default one. Then went back to "Set Server Defaults" and the error went away. I could then go back and add my mappings back in and the error still did not show.

I can't explain and really don't want to know the internal workings of Reporting Services with SharePoint but the above solution worked for me, hope it works for you too.

w3wp!library!5!07/11/2008-13:03:58:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.ArgumentException: Value does not fall within the expected range. at Microsoft.ReportingServices.SharePoint.Server.Utility.GetSPItemMetaDataAndContent(String path, UserContext userContext, Boolean returnContent, Guid& id, ISecurableObject& secObj, SharePointImpersonatedWeb& wrapper, Byte[]& content, String& mimeType) at Microsoft.ReportingServices.SharePoint.Server.Utility.GetSPItemType(String objectName, UserContext userContext, Guid& id, ISecurableObject& secObj, SharePointImpersonatedWeb& wrapper) at Microsoft.ReportingServices.SharePoint.Server.SharePointAuthorizationExtension.InternalCheckAccess(UserContext userContext, String itemPath, SPBasePermissions requiredRights) at Microsoft.ReportingServices.SharePoint.Server.SharePointAuthorizationExtension.CheckAccess(UserContext userContext, String path, CatalogOperation requiredOperation) at Microsoft.ReportingServices.SharePoint.Server.SharePointSecurity.CheckAccess(Byte[] secDesc, CatalogOperation catalogOperation, String catalogPath) at Microsoft.ReportingServices.Library.GetSystemPropertiesAction.PerformActionNow() at Microsoft.ReportingServices.Library.RSSoapAction`1.Execute()

Continuing SQL Reporting Services Integration with SharePoint (SSRS)

Another issue that I ran into while trying to configure the integration is when trying to add the "SQL Reporting Services" web part I encountered an error along the lines that it wasn't added to the safe controls in the web.config. I looked at the safe control entries and the previous control was registered the version was something like 9.0.262.0 however the new dlls are all 10.0.0.0 so I added the following to the web.config and got rid of the old entries.

safecontrol assembly="Microsoft.ReportingServices.SharePoint.UI.ServerPages, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" namespace="Microsoft.ReportingServices.SharePoint.UI" typename="*" safe="True"

safecontrol assembly="Microsoft.ReportingServices.SharePoint.UI.WebParts, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" namespace="Microsoft.ReportingServices.SharePoint.UI.WebParts" typename="*" safe="True"

Trying to add the web part to the Report Center again throws another error about a verb not added to the web.config. This time the error message actually tells me what to add. So now I add the following:

add type="Microsoft.ReportingServices.SharePoint.UI.WebParts.WebPartHttpHandler, Microsoft.ReportingServices.SharePoint.UI.WebParts, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" path="Reserved.ReportViewerWebPart.axd" verb="*"

And finally try adding the web part again and finally the web part gets added without errors.

I will say that the integration with SharePoint is a lot easier than it was with SQL Server 2005 Reporting Services but it still has some issues.

Configuring and Installing SQL Server 2008 Reporting Services SharePoint Add-in

Installing the SQL Server 2008 Reporting Services SharePoint Add-in is as painful as ever. I've downloaded the installation package from Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies and followed the instructions provided on msdn How to: Install or Uninstall the Reporting Services Add-in but still encounter installation errors.

The error in the installation log was

Install error: A web configuration modification operation is already running.
Error details: System.InvalidOperationException: A web configuration modification operation is already running.
at Microsoft.SharePoint.Administration.SPWebService.ApplyWebConfigModifications()

I looked at the SharePoint web.config and it was not write protected and the Everyone group has access to it. I couldn't figure out why it was trying to be modified twice, so after searching on "the google" (nice Bushism) it appears that this occurs when SPFarm.Local.Services.GetValue().ApplyWebConfigModifications(); is called twice during the feature installation.

Well knowing that SharePoint would create a Timer job for this operation I went into Central Admin -> Operations -> TImer Job Definitions during the "Removing backup files" part of the rsSharePoint.msi installation process and sure enough there were two "SharePoint Update Web Configuration" jobs. This is a no no since they will lock trying to modify the web.config. So I deleted both jobs and waited...

This time the rsSharePoint.msi installation did not fail, it took a long time to install because it apparently extracts all the locale cabs until it finds the one for the current SharePoint locale and then during the Removing backup files part it actually has to uninstall all the locale cab packages except the one that is right for the SharePoint installation.

But deleting those two jobs and waiting worked.

Just in case you are waiting for it to "remove backup files" look in the Local Settings/Temp folder of whichever user you are installing the package as and you'll see a folder called MS.WSS.RS and within that folder you'll see another folder named 3082, 2070... so on as you can see below, this is exactly what it does. If you constantly refresh this folder you'll see new ones created and removed consistently and in the installation log you'll actually see that each package is being uninstalled. You'd think they'd be able to tell which locale is installed before extracting and installing all unnecessary files.

Calling HCInstal for lcid '3082'

Calling HCInstal for lcid '2070'

Calling HCInstal for lcid '2052'

Calling HCInstal for lcid '1055'

Calling HCInstal for lcid '1053'

Calling HCInstal for lcid '1049'

Calling HCInstal for lcid '1046'

Calling HCInstal for lcid '1045'

Calling HCInstal for lcid '1044'

Calling HCInstal for lcid '1043'

Calling HCInstal for lcid '1042'

Calling HCInstal for lcid '1041'

Calling HCInstal for lcid '1040'

Calling HCInstal for lcid '1038'

Calling HCInstal for lcid '1036'

Calling HCInstal for lcid '1035'

** You'll notice that my locale of '1033' is missing since it doesn't need to uninstall that one.

Calling HCInstal for lcid '1032'

Calling HCInstal for lcid '1031'

Calling HCInstal for lcid '1030'

Calling HCInstal for lcid '1029'

Calling HCInstal for lcid '1028'

Cab files uninstalled successfully.

One other note, calling the rsCustomActions.exe /i doesn't work, it will give you more verbose logging but it won't install the files it needs. For instance, it will through an error that Microsoft.ReportingServices.SharePoint.UI.ServerPages file is not found. So running that exe is useless.