Category Archives: SQL Server

SSIS Script Task – The Script Task is corrupted

Having moved an SSIS package to a new server it was failing to run with the errors below.

%Script Task Name%:Error: There were errors during task validation.

%Script Task Name%:Error: The Script Task is corrupted.

%Script Task Name%:Error: There was an exception while loading Script Task from XML: System.Exception: The Script Task “ST_…” uses version 15.0 script that is not supported in this release of Integration Services. To run the package, use the Script Task to create a new VSTA script. In most cases, scripts are converted automatically to use a supported version, when you open a SQL Server Integration Services package in %SQL_PRODUCT_SHORT_NAME% Integration Services. at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.LoadFromXML(XmlElement elemProj, IDTSInfoEvents events)

The resolution is to change the SSIS project TargetServerVersion property.  Right-click on the project an choose Properties.

ssis error 1

On the screen that loads change the TargetServerVersion value to the version of SQL Server you’re using.  Save and build your project, then re-deploy.

SSIS Error 2.png

A significant part of sql server process memory has been paged out.

Looking through the SQL Server log shortly after the server started I saw:

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 128084, committed (KB): 423936, memory utilization: 30%.

This is on a Windows Server 2012 R2 VM with SQL Server 2008 R2, 8GB RAM and a maximum server memory configuration of 6144MB.  Lock pages in memory is enabled for the SQL Server service account.  There are many reports of this error from the Windows Server 2003 x64 and SQL Server 2005 era, but little information from more recent times. 

The message is saying that a large amount of the memory allocated to the SQL Server process, sqlservr.exe, has been moved from RAM to the page file as the process’ working set has been trimmed.  In the case of  the message above the SQL Server process has a working set of 128084KB of memory allocated in RAM and a total memory allocation which could be in RAM or on disk of 423936KB.  The memory utilisation value of 30% is showing that ~30% of the SQL Server process’ total memory allocation is in RAM, which is the working set value.  This warning message is raised when the memory allocation in RAM (working set) is 50% or below the committed memory value.

In the case of this server the working set and committed values are low compared to the maximum server memory setting and Task Manager was showing ~2.5GB of the server’s 8GB RAM in use.  Following research I found this MSDN blog article which discusses working set trim warning messages early in the SQL Server startup phase, or shortly after the server is ready for user connections.  Based on this article it appears that in my case I can ignore the warning as the message values are low compared to the server max memory setting and there is little activity on the server.

 

 

SSIS Package Incompatible in SSDT and Visual Studio 2017

Having installed Visual Studio 2017 Professional with the SQL Data Tools component I tried to open a SSIS package.  This failed to load and was listed as incompatible and that the application wasn’t installed.

I ran the SQL Server Data Tools (SSDT) install and added the SQL Server Integration Services component to my existing Visual Studio installation.  Unfortunately, it was still showing as incompatible and that the application wasn’t installed.

The resolution was to right click on the project and click reload.  This reloaded the project files from disk and I was able to edit the package.

Mirrored SQL Databases Stuck In Recovery Mode

Following a server restart, two of three SQL Server databases in a SQL Server Database Mirroring configuration were stuck in recovery mode on the primary server.

Running sys.dm_tran_locks showed a session with a resource_database_id for each of the databases stuck in recovery.

It appeared that SQL Server Database Mirroring was preventing the recovery process completing on the two databases following the server restart.

The resolution was to stop and start the database mirroring endpoints on the primary and secondary SQL Servers.  To return a list of endpoints run select * from sys.endpoints.

To stop and restart the endpoints run

ALTER ENDPOINT %ENDPOINT NAME% STATE=STOPPED

ALTER ENDPOINT %ENDPOINT NAME% STATE=STARTED

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.