SQL

SharePoint and SSRS: The given key was not present in the dictionary

Consider this scenario. You have SharePoint with SQL Reporting Services (SSRS) running in SharePoint Integrated mode. You want to use a SharePoint list as a data source for a report.

In the SSRS Data Connection you enter the URL and credentials for the SharePoint site but when you test the connection the following error appears:

The given key was not present in the dictionary

After a bit of trial and error I discovered that this error occurs if the URL entered isn’t the default URL for the web application.

To resolve the issue, go into Central Admin and check the Alternative Access mappings on the SharePoint site used in the data source. Check that you are using the default URL.

Similar but unrelated issue

Note that there is a similar issue that occurs when added managed accounts in Central Admin. These issues are not related. Further details of that issue can be found here: https://support.microsoft.com/en-nz/help/2463865/sharepoint-2010-error-message-the-given-key-was-not-present-in-the-dic

 

 

Advertisements

Changing SSRS database names

I’ve been working on a project where multiple servers including two SharePoint farms running SQL Server Reporting Services (SSRS) in SharePoint Integrated mode need to be moved to a single SQL Server. Each farm has its own SQL Server to host the SharePoint and SSRS databases. To make the job a little more challenging the databases in each farm have the same name.

To complete this job I need to change the names of the databases to avoid duplicate database names on the new SQL Server. Here’s the approach I have taken and few tips to resolve issues caused by the renaming process.

Before attempting the following process, make sure you have a backup.

SQL Reporting Services has two databases:

  • ReportingServerDatabase
  • ReportingServerDatabaseTemp

Backup / Restore :

  1. Backup and restored the databases to the new server, renaming the databases in the process. In this case I added a prefix to distinguish between the two SharePoint farms.
  2. On the SharePoint Server run the SSRS Configuration Tool
  3. Change the database to the new database server name. Note that you don’t get the opportunity to change the Temp database name only the reporting server database.

Update the references to the Temp database:

  1. In SQL Management Studio, locate the ReportingServerDatabase. There are references to the Temp database in several views and many stored procedures (83 in my case).
  2. Generate a script for the Views in the database (Drop and Create)
  3. Generate a script for the Stored Procedures in the database (Drop and Create)
  4. In both scripts search and replace the old Temp database name with the new one
  5. Stop the SSRS Services on the SharePoint Server (in the SSRS Configuration tool)
  6. Run the updated View script
  7. Run the updated Stored Procedures script
  8. Restart the SSRS service

Testing:

  1. In SharePoint navigate to the SSRS Reporting library (in my case called ReportServer)
  2. Edit a Data Connection
  3. View a report

If you get an error in either of these functions, you should see the name of the Stored Procedure and database it is referencing. Go back into SQL Management Studio and check the references have been updated.

 

 

 

Data Channel : Managing SharePoint Databases

I was recently interviewed for the Data Channel about managing SharePoint databases on SQL Server.

In the interview with Nagaraj Venkatesan (SQL Server MVP from Singapore) I cover many of the common questions DBA’s have about SharePoint including:

  • Things that make SharePoint databases unique
  • Capacity Planning
  • Remote Blob Storage (RBS)
  • Backup / Recovery
  • SSRS Integration
  • Patching

You can download a copy of my presentation from #SQLsat614 which covers the basics for DBA’s and is also useful for SharePoint Admins.

Thanks to The SQL Saturday team for giving me the opportunity to present and Nagaraj for the interview.

 

 

SharePoint for DBA’s

Yesterday I presented at #SQLSat614 in Christchurch. SQL Saturday was a full day event with 20 experts talking on topics related to the Microsoft Data Platform. Since this wasn’t my normal audience, I thought I’d ask the twitter-sphere what questions DBA’s have when it comes to managing SharePoint and the responses helped formulate my talk.

I attended another session where the speaker raised culture as an important aspect of ‘getting things done’. He spoke about the disconnect between different groups within IT, Ops team vs Dev team vs Systems team etc and how communication is often lacking.

Over the years I’ve seen many instances where people using SharePoint do things that ‘upset’ either the Ops team, DBA or someone else in the team. The is often swiftly followed by finger pointing and limited understanding of each others needs only makes things worse.

The point I would make is that SharePoint is a platform. In many cases a group of people are responsible for different elements of the platform – server infrastructure, SQL servers, backups, administration of SharePoint, site builds, content migrations, governance etc. If we can clearly identify the needs of each team and get better understanding across the group, then things will run more smoothly, especially when a crisis occurs. We want the group to become a team.

This leads me to my presentation, Introduction to SharePoint for DBA’s. I made a deliberate decision not to dive in to deep or create a comprehensive guide covering every aspect of SharePoint that a DBA might need to understand, but just enough that they would see how different it is to other workloads and provide a few tips for doing things better.

I’ll leave you with a few simple rules to remember and a link below to download the presentation. Here are the rules:

  • Always use SQL Aliases when installing SharePoint
  • Pre-grow your databases for planned data migration or bulk loading
  • Create one Site Collections per Content Databases were possible
  • Understand your recovery options (see more here)
  • Have a regular catch up with your SharePoint admin
  • Never update SharePoint databases directly, use the SharePoint UI or API’s

Download Presentation

During the questions I was asked, what is the difference between a DBA and a SharePoint Admin? A difficult question for a guy who spends his days working with SharePoint to answer, when the room is full of DBA’s!

A big thank you to the Microsoft SQL community (#sqlfamily) for having me at their event.

SSIS, InfoPath and SharePoint Lists

Have you ever needed  a low cost solution for capturing data in a form on mobile device and then pull the data back into an in house SQL database. In this solution data is entered into an InfoPath form on a tablet (Windows, iOS or Android), submitted to a SharePoint Online form library and then pulled into a SQLserver database using SQL Integration Services (SSIS).

screen-shot-2017-01-21-at-10-17-01-am

This solution will also work with standard SharePoint lists and can be used with SharePoint Online, SharePoint 2013 or SharePoint 2016.

The things you need to know:

  • InfoPath forms can be hosted in a SharePoint Online forms library
  • InfoPath can ‘promote’ fields from the form into metadata fields in the SharePoint Online forms library e.g. each form will be saved and selected fields will appear as columns on the form library.
  • SharePoint Online form libraries can be queried using an oData connection
  • SQL Server Integration Services (SSIS) can use oData as a data source and push or pull data to and from the data source
  • SSIS can be scheduled

Note: SQL Server Standard Edition (or better) includes SSIS

InfoPath steps:

  1. Using InfoPath Designer, create a form and publish the form to a SharePoint Form library. The easiest option is to do this from InfoPath and let it create the Form library for you.
  2. In Form Options, select the fields that you want to ‘promote’ to SharePoint.
  3. Publish the form
  4. In SharePoint verify that the promoted fields are showing as columns in the Forms library

SQL Server and SSIS steps:

In this step we setup a database and SSIS package. The connection is initiated from the server running SSIS and so this server must be able to make an outbound connection to Office 365.

SSIS packages are created using SQL Server Data Tools

  1. Create a table in your database with fields to store the data from the form
  2. Using Visual Studio create an SSIS package
    • Create the  SQLserver data connection for SQL table created in step 1
    • Create the oData connection to the SharePoint Online list. You will need:
      • The URL for the Form library (the site URL)
      • Office 365 credentials with permission to read the list. These credentials will be saved in the SSIS package
      • See the authentication note below
    • Map the fields from the oData Connection to the SQLserver Connection fields
    • Save
  3. Once you have tested the process (see steps below), you can schedule the SSIS package to run on as frequently as you need.

Testing:

  1. Create a new InfoPath form and submit the form to the library
  2. In SharePoint, check to see that the promoted fields are populated
  3. In Visual Studio run the SSIS package (you may need to ‘Run as Administrator’ when you start Visual Studio for this to work
  4. Check the table in SQLserver to see if any new data has arrived

Authentication issue:

When creating the oData connection to Office 365, you must manually change the setting for ‘Microsoft Online Services Authentication’ to true. This is in the oData Connection Manager settings, under Connection in the Security section. More details here.

Resources:

How to promote fields from InfoPath to SharePoint

How to create an SSIS package with SQL Server Data Tools

MSDN: SSIS oData tutorials