Skip to content

Instantly share code, notes, and snippets.

@MartinMiles
Last active April 29, 2025 09:31
Show Gist options
  • Save MartinMiles/401773c36f8c1f4d58f53799e8d0ff9a to your computer and use it in GitHub Desktop.
Save MartinMiles/401773c36f8c1f4d58f53799e8d0ff9a to your computer and use it in GitHub Desktop.
SPE snipped to return items from MASTER database of my XM Cloud instance
$sql = @"
USE [Sitecore.Master];
-- Variables
DECLARE @NewItemId UNIQUEIDENTIFIER = NEWID();
DECLARE @ParentId UNIQUEIDENTIFIER = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}';
DECLARE @TemplateId UNIQUEIDENTIFIER = '{76036F5E-CBCE-46D1-AF0A-4143F9B557AA}'; -- Sample Item template
DECLARE @TextFieldId UNIQUEIDENTIFIER = '{A60ACD61-A6DB-4182-8329-C957982CEC74}'; -- YOUR CORRECT Text field ID
DECLARE @Now DATETIME = GETUTCDATE();
DECLARE @ItemName NVARCHAR(255) = 'FFF';
DECLARE @Language NVARCHAR(10) = 'en';
DECLARE @Version INT = 1;
DECLARE @TextValue NVARCHAR(MAX) = 'The value of the Text field';
-- 1. Insert into Items
INSERT INTO [dbo].[Items]
([ID], [Name], [TemplateID], [ParentID], [MasterID], [Created], [Updated])
VALUES
(@NewItemId, @ItemName, @TemplateId, @ParentId, '00000000-0000-0000-0000-000000000000', @Now, @Now);
-- 2. Insert into VersionedFields (CORRECT field ID now)
INSERT INTO [dbo].[VersionedFields]
([ItemId], [FieldId], [Language], [Version], [Value], [Created], [Updated])
VALUES
(@NewItemId, @TextFieldId, @Language, @Version, @TextValue, @Now, @Now);
"@
Import-Function Invoke-SqlCommand
Write-Verbose "Cleaning up the History, EventQueue, and PublishQueue tables in the $($db.Name) database."
$connection = [Sitecore.Configuration.Settings]::GetConnectionString("master")
$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder $connection
$dbName = $builder.InitialCatalog
#$query = [string]::Format($sql, $dbName)
$query = $sql
Invoke-SqlCommand -Connection $connection -Query $query
$sql = @"
USE [{0}]
SELECT ID, [Name], [TemplateID], Created from [dbo].[Items]
"@
Import-Function Invoke-SqlCommand
Write-Verbose "Cleaning up the History, EventQueue, and PublishQueue tables in the $($db.Name) database."
$connection = [Sitecore.Configuration.Settings]::GetConnectionString("master")
$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder $connection
$dbName = $builder.InitialCatalog
$query = [string]::Format($sql, $dbName)
Invoke-SqlCommand -Connection $connection -Query $query
@MartinMiles
Copy link
Author

Execute these scripts from SPE IDE

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment