Category Archives: SQL Server

Extend SharePoint 2013 People Search Using Business Connectivity Services Part 1

In this four part series I will describe the process of extending the SharePoint 2013 user profile from Active Directory with additional data from a SQL Server table.  The additional data will then be displayed in users’ My Sites and SharePoint people search.

Part 1 will show the process to create the SQL Server table.  Part 2 will show the process to make the data in the SQL Server table accessible to SharePoint.  Part 3 will show the process to extend user profiles and My Sites to include the additional user data.  Part 4 will show the process to make the new user profile properties accessible in SharePoint Search.

The SharePoint environment is SharePoint 2013 Standard, with SQL Server 2008 R2 and SharePoint Designer 2013.  The steps are based on an environment where the User Profile Service has already been setup, users have been imported from Active Directory and My Sites have been configured.  It is assumed that SharePoint Search has been configured and a search site using the Enterprise Search Center template has been created.

Firstly, create a SQL table to hold the user data.  Below is some example code that creates the table and adds an index on the email address column.

CREATE TABLE [dbo].[SPPeopleData](
[NameID] [int] NULL,
[FirmInitials] [varchar](6) NULL,
[Jurisdiction] [nvarchar](15) NULL,
[Email] [varchar](40) NULL
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [_TW_Email] ON [dbo].[SPPeopleData]
(
[Email] ASC
) ON [PRIMARY]
GO

Next, fill the table with some test data.

BCS - Data

Now create a stored procedure to return user data for a given email address.  I’m using email address as it’s a property that’s imported for each user from Active Directory.

CREATE PROCEDURE [dbo].[twsp_GetSPPeopleDataForEmail] @Email VARCHAR(40) AS

SELECT [NameID]
,[FirmInitials]
,[Jurisdiction]
,[Email]
FROM [SPPeopleData]
WHERE [Email] = @Email

In part two I’ll show how to connect SharePoint to the data in the SQL Server table.

SQL Server Reporting Services Service Fails To Start After Windows Updates

Following an update of a SQL 2005 SSRS server to SP4 + all Windows Updates, the SSRS service would not start.  “Report Server (MSSQLSERVER) cannot connect to the report server database.” was logged in the Application Event Log.  “Timeout (30000 milliseconds) waiting for the SQL Server Reporting Services (MSSQLSERVER) service to connect.” was logged in the System Event Log.

The resolution was to update the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\ServicesPipeTimeout registry key to 60000.  This increases the time allowed for a service to start from 30 seconds, to 60 seconds.  In my case the registry key didn’t exist so I had to create it.  Once the registry has been updated/created reboot the server.

SSRS

SQL Server 2008 R2 Management Tools Installation Fails

When installing the management tools for SQL Server 2008 R2 I received “SQL Server Setup has encountered the following error: “Unknown property.”

Looking on the web indicated that SQL Server Compact 3.5 could be causing the issue.  Programs and Features showed SQL Server Compact 3.5 was installed, but I couldn’t uninstall, or repair the installation.

I downloaded the installation files here http://www.microsoft.com/en-us/download/details.aspx?id=5783  The installation instructions state that on a 64-bit computer you have to install the 32-bit and 64-bit versions of the files.  Having installed both SSCERuntime_x86-ENU.msi and SSCERuntime_x64-ENU.msi I successfully re-ran the SQL Server 2008 R2 management tools installation.  Both the 64-bit and 32-bit versions of SQL Server Compact are now listed in Programs and Features.

 

UPDATE 08/08/13

I’ve found that in some cases the Microsoft SQL Server Compact 3.5 SP2 x86 (SSCERuntime_x86-ENU.msi) installation can fail.  In this case, open Registry Editor, navigate to HKEY_CLASSES_ROOT\Installer and search for the key relating to Microsoft SQL Server Compact 3.5 SP2 x86.  Backup the key and delete it from the registry.  The installation should now complete successfully.

Excel Source Output. The column status returned was: “Text was truncated or one or more characters had no match in the target code page.”

I came across this error when I was attempting to import data from an Excel spreadsheet into a SQL database using SQL Server Integration Services (SSIS) 2005.  The error occurs because by default the Excel driver only reads the first eight rows of the spreadsheet when it determines the column data type and data length.

If you have a spreadsheet where the first eight rows aren’t representative of the data in the spreadsheet, you can increase the number of rows that are sampled by editing the TypeGuessRows value under the following registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

See here for more details http://technet.microsoft.com/en-us/library/ms141683(v=sql.90).aspx.

The case of the failed SQL backup

I enabled a maintenance plan on a SQL server which creates a full backup of all databases.  When checking the job history I noticed that the job had failed.  Upon further investigation I could see that although the job had failed, all the databases had been backed up.  Opening the job log revealled the following error:

Failed:(-1073548784) Executing the query “BACKUP DATABASE [master] TO  DISK = N’I:\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Backup\\master\\master_backup_201010260000.bak’ WITH NOFORMAT, NOINIT,  NAME = N’master_backup_20101026000019′, SKIP, REWIND, NOUNLOAD,  STATS = 10
” failed with the following error: “Transaction (Process ID 580) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.

I checked the SQL job schedule and noticed that another “Cleanup History” maintenance plan was set to run at the same time.  In order for this maintenance plan to cleanup the job history is needs to access MSDB, which caused the deadlock.  Re-scheduling the Full Backup plan to run 5 minutes later cured the problem