scripts Archives - Flux Digital Sitecore Blog

Scripting Sitecore Packages with SPE

This week I needed to automate the creation of a package to include a number of items that we want to backup before syncing content down from other environments. I needed to conditionally only backup certain test pages and components based on name.

I figured I’d be able to do this with Sitecore PowerShell Extensions and it turns out you can.

Here is a script based on the one included in SPE (/sitecore/system/Modules/PowerShell/Script Library/SPE/Core/Platform/Development/PowerShell Extensions Maintenance/Prepare Console Distribution).

The script creates a package with two sources:

Test Pages – pages within a specific folder
Test Components – matches components in sub-folders that have a specific name (sub-folders that start with ‘Test’)

It then provides the package for you to download like so:

To use this script update the meta data section at the top and the paths to include your own pages and components. Check the –InstallMode param is correct for what you want to do and also the filters for component name matches what you need, update it if not. You could include additional sources too by duplicating the $source = ‘x’ and $package.Sources.Add($source); lines.

Hopefully this is useful for others that need to do this in future.

Fixing Standard Values without Affecting Existing Content

Unfortunately I wasn’t able to attend Sitecore Symposium this week so won’t be writing up an overview of what I’ve learned, there were some exciting things announced for Sitecore in 2020 though from what I’ve seen on Twitter so I’ll keep an eye out for those Symposium Blog posts for more info on what I missed.

Instead this Blog Post is about an slightly odd situation regarding fixing Standard Values when they have been set incorrectly for a number of templates.

Standard values provide initial values for all items based on a given data template.

We had around 30 page data templates that have an field value set on the standard values to an incorrect value. Unfortunately there are thousands of items based on these templates and I needed to maintain the existing values so I couldn’t just change the standard values to the right ones as it would also re-set all items still using the Standard Values.

Reading up on How Standard Values Work again the import part is this:

A field can contain the same value as its standard value without actually containing that standard value. For example, an item contains its standard value. The user updates that field; the field no longer contains its standard value.

The user updates the field again, setting the value to the same value as the standard value for the field, but without resetting the field to its standard value. The field now contains the same value as its standard value; however, it does not contain its standard value.

So I decided that SPE would be the perfect tool to solve this issue. I initially wrote an script to update the item field value to another value and then a 2nd script to set it back to the same value as the standard value. This means the item would no longer be inheriting the field value from the Standard Values.

However this wasn’t very efficient and there was a risk of my items loosing the correct value when being set back again. My colleague Mobeen suggested that perhaps if I updated the script to set the value to something and then immediately back to the same value as the standard value then this would work and only require one script. It did and here is the final script I came up with:

The script makes use of the .ContainsStandardValue property of a field which Sitecore uses to track if a field is using the Standard Value or not. Once the script has run the ContainsStandardValue for the field is false for all items and therefore the Page template can be updated without affecting them.

You will see it sets a field called ‘content type’ so to re-use it you should change the field name used. Also ensure you set the path to the items in the pages query.
You can run this with the value of $global:updateContentTypeField set to false to just report which items are using the standard values for this field. If you wish to update the field value for items then set this to true.

Once this had run I was safe to correct the standard values on all my page templates without affecting the existing content :-).

Hopefully this will help someone else who has this issue.

Auto-maintaining Sitecore Session State permissions in tempDB after SQL Server restarts

We use SQL Server to maintain our Sitecore sessions which mostly works fine. However as part of the performance boosts recommended by Sitecore the session-state is moved to store session in SQL Server tempDB.

The process is explained here: https://doc.sitecore.net/sitecore_experience_platform/setting_up_and_maintaining/session_state/session_state/walkthrough_configure_a_shared_session_state_database_using_the_sql_server_provider

However, every time that SQL Server is restarted, it recreates tempDB and resets the access rights for users and the session tables. This will result in your Sitecore site going down and an error such as:

System.Data.SqlClient.SqlException: The SELECT permission was denied on the object ‘SessionState’, database ‘tempdb’, schema ‘dbo’. The INSERT permission was denied on the object ‘SessionState’, database ‘tempdb’, schema ‘dbo’. The UPDATE permission was denied on the object ‘SessionState’, database ‘tempdb’, schema ‘dbo’.

And also an angry client and customers :-(.

How do I solve this?

You could re-run the script from the Performance boost download provided by Sitecore (Sessions db performance boost.sql ) but this could happen late at night when no one is monitoring your site and cause downtime.

The best way to solve this and still use tempDB for sessions is to offload this manual task into a stored procedure that runs on Start-up.

The following script will create a Stored Procedure in the Master database that will run each time SQL Server restarts and add your session tables and database user back to tempDB with the right permissions.

USE [master]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE type = ‘P’
and name = ‘AddSitecoreSessionTempDBOwner’)
DROP PROC AddSitecoreSessionTempDBOwner
GO
CREATE PROC AddSitecoreSessionTempDBOwner
AS DECLARE @sql
VARCHAR(500)SELECT @sql = ‘EXEC [master].[dbo].[Sitecore_InitializeSessionState]’ + char(13) +’USE [tempdb]’ + char(13) +’IF NOT EXISTS(SELECT name FROM sys.database_principals WHERE name = ”Sitecore_Sessions”)’ + char(13) +’BEGIN’ + char(13) +’CREATE USER [Sitecore_Sessions] FOR LOGIN [Sitecore_Sessions]’ + char(13) +’ALTER ROLE [db_datareader] ADD MEMBER [Sitecore_Sessions]’ + char(13) +’ALTER ROLE [db_datawriter] ADD MEMBER [Sitecore_Sessions]’ + char(13) +’END’EXEC (@sql)
GO
EXEC sp_procoption ‘AddSitecoreSessionTempDBOwner’, ‘startup’, ‘true’
GO

Just remember to update ‘Sitecore_Sessions’ in the script above to the name of the user in your Sessions Database connection string, then run the script in SQL Server Manager to create the stored procedures that will run on start up.

How do I test this?

It is important to test this to ensure that on a restart of the SQL Server the script kicks in and works it’s magic. First check if the Stored Proceedure is set to run on Startup correctly by running this query:

SELECT name,create_date,modify_date
FROM sys.procedures
WHERE OBJECTPROPERTY(OBJECT_ID, ‘ExecIsStartup’) = 1

You should see the ‘AddSitecoreSessionTempDBOwner’ and ‘Sitecore_InitializeSessionState’ stored procedures listed. If you don’t try running the script above again as something went wrong.

Once these are listed correctly you should carry out a full test by scheduling in a time out of hours when you can reboot the SQL Server machine and check your script runs correctly.

Gotchas

One thing to ensure is running is the SQL Server Agent Service. This needs to be running in order for and startup stored procedures to be executed.

Check this is set to ‘Auto Run’ under startup type. This caught me out on my first test as my Stored Procedure didn’t run correctly on reboot of the SQL Server.

Lastly check your session state tables are collecting session data correctly using a query such as:

select count (*) from [tempdb].[dbo].[SessionState]

That should be it, no more problems with tempDB when SQL Server restarts.

Bulk Move Items with Sitecore Powershell Extensions

Sitecore Powershell Extensions (or SPE) is a great Module for Sitecore that allows you to automate a lot of menial tasks, e.g bulk renaming, moving or deleting items and much more besides.

There is a great Git book here with more info on what you can do with it: https://sitecorepowershell.gitbooks.io/sitecore-powershell-extensions/

I needed to move about 3,000 child-items to a sub-folder in Sitecore and since there is no easy way to do this I decided SPE would be perfect for the job.

Before you do anything with SPE on a Production environment – a word of caution: please be very careful to test your scripts on a Development or Staging environment first and backup your live database before you run anything on Production.
Ok now on with the code. The script below has two variables defined at the top.

$rootOfitemsToMove – The folder/parent item where the child items you want to move currently exist.

$destinationItem – The folder/parent item where you want to move your child items to.

$templateNameToMatch – The name of the template you wish to match on. This allows you to filter out other child items you don’t want to move.

Give me the script already

$rootOfitemsToMove = Get-Item “/sitecore/content/My Site/My Items Folder”;
$destinationItem = Get-Item “/sitecore/content/My Site/My Items Folder/Sub Folder”;

$templateNameToMatch = “My Template”;

Write-Host “Moving items from: ” $rootOfitemsToMove.Paths.FullPath ” to: ” $destinationItem.Paths.FullPath ” …”;

Get-ChildItem | Where-Object { $_.TemplateName -match $templateNameToMatch } | ForEach-Object {
$name = $_.Name
if(![string]::IsNullOrEmpty($name))
{
Move-Item -Path $_.ItemPath -Destination $destinationItem.Paths.FullPath;
Write-Host “Item moved to: “$_.ItemPath;
}
else
{
Write-Host “Couldn’t move Item: ” $name;
}
}

Write-Host “Moving items complete.”;

Just open up Powershell ISE (Developer Tools > Powershell ISE from Sitecore Desktop), paste in the script, update the paths and template name and run it. One the script is complete you should see that all your child items that have the correct template have been moved to their new destination.

Note: the script above purposely doesn’t use the -Recurse argument on the Get-ChildItem method as this would keep looping through your destination sub-folder if it exists below the current paths of the item and get stuck in an infinite loop potentially. However this means it won’t loop round sub-folders, so you may want to change this if your $rootOfitemsToMove has sub-folders with items in you wish to move also. Just make sure your destination folder is elsewhere to avoid the above infinite loop issue.

Thats all there is to it. Hopefully this is useful and gives you some idea of how SPE can save you a whole lot of time and pain too!.