Experience Sitecore ! | All posts tagged 'SQL'

Experience Sitecore !

More than 200 articles about the best DXP by Martin Miles

Few SQL tricks helping to work with Sitecore

1. Delete all the databases having a specific prefix, in the example below it will drop all the databases starting with habitat.dev.local_ - this may be helpful when Sitecore 9 installation broke half way down and you'd like to return everything to an initial point, also removing (partly) installed databases:

USE MASTER
GO

DECLARE @dbnames NVARCHAR(MAX)
DECLARE @statement NVARCHAR(MAX)
SET @dbnames = ''
SET @statement = ''
SELECT @dbnames = @dbnames + ',[' + name + ']' FROM sys.databases WHERE NAME LIKE 'habitat.dev.local_%'
IF LEN(@dbnames) = 0
    BEGIN
    PRINT 'no databases to drop'
    END
ELSE
    BEGIN
    SET @statement = 'drop database ' + SUBSTRING(@dbnames, 2, LEN(@dbnames))
    PRINT @statement
    EXEC sp_executesql @statement
    END


2. Backup all SQL database having some prefix (in this case, habitat.dev.local_) to a folder (like C:\DatabaseBackups\):

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @scPrefix VARCHAR (20) -- Sitecore database prefix

-- specify database backup directory
SET @path = 'C:\DatabaseBackups\' 

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

-- specify the Sitecore db prefix
SET @scPrefix = 'habitat.dev.local_'
DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE CharIndex(@scPrefix, name) = 1 OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor

3. Shrink all databases larger than a certain number of megabytes (stored in @megabytesMoreToShrink variable):

DECLARE @megabytesMoreToShrink INT SET @megabytesMoreToShrink = 100

DECLARE @name NVARCHAR(MAX) 
DECLARE @logical_name NVARCHAR(MAX) 

DECLARE db_cursor CURSOR FOR 
SELECT db.name AS DBName, mf.Name AS Logical_Name
FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id Where type_desc = 'LOG' AND (size*8)/1024 > @megabytesMoreToShrink

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name, @logical_name  

WHILE @@FETCH_STATUS = 0  
BEGIN  

              exec('USE ' + @name + ' ;ALTER DATABASE ' + @name + ' SET RECOVERY SIMPLE;DBCC SHRINKFILE ('''+@logical_name +''', 1);ALTER DATABASE '+@name +' SET RECOVERY FULL')

              FETCH NEXT FROM db_cursor INTO @name, @logical_name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

4. Change sa password for SQL instance, you do not need to know the previous password at all, but need to have an administrative access, for sure. Run osql tool, it's path should be added to the environment variable, but if it's not there, please find it here: "%ProgramFiles%\Microsoft SQL Server\130\Tools\Binn\OSQL.exe":

osql -S SITECOREDEV -E
1> sp_password NULL, 'SA_PASSWORD','sa'
2> GO

5. Some basic item's operations, such as retrieving by ID, by parent and retrieving fields and versions:

-- Get item by Sitecore ID
SELECT TOP 1000 *
  FROM [habitat_Master].[dbo].[Items]
  WHERE ID = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'

-- Get item by ID of parent in Sitecore
SELECT TOP 1000 *
  FROM [habitat_Master].[dbo].[Items]
  WHERE ID = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'

-- Enumerate all the versioned fieds for an item in Sitecore by item ID
SELECT TOP 1000 
  i.Name, v.[Id], [ItemId], [LANGUAGE], [FieldId], [Value], v.[Created], v.[Updated]
  FROM [habitat_Master].[dbo].[versionedFields] v
  JOIN [habitat_Master].[dbo].Items i 
    ON v.FieldId = i.ID
  WHERE ItemId = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'
  ORDER BY [Name]

5. Some basic item's operations, such as retrieving by ID, by parent and retrieving fields and versions:

-- Get item by Sitecore ID
SELECT TOP 1000 *
  FROM [habitat_Master].[dbo].[Items]
  WHERE ID = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'

-- Get item by ID of parent in Sitecore
SELECT TOP 1000 *
  FROM [habitat_Master].[dbo].[Items]
  WHERE ID = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'

-- Enumerate all the versioned fieds for an item in Sitecore by item ID
SELECT TOP 1000 
  i.Name, v.[Id], [ItemId], [LANGUAGE], [FieldId], [Value], v.[Created], v.[Updated]
  FROM [habitat_Master].[dbo].[versionedFields] v
  JOIN [habitat_Master].[dbo].Items i 
    ON v.FieldId = i.ID
  WHERE ItemId = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}'
  ORDER BY [Name]

6. One of the most popular snippets, to restore default admin / b password. Please keep in mind running that against core database:

-- use core database
UPDATE dbo.aspnet_Membership
SET
    Password='qOvF8m8F2IcWMvfOBjJYHmfLABc=',
    PasswordSalt='OM5gu45RQuJ76itRvkSPFw=='
WHERE
    UserId = (SELECT UserId FROM dbo.aspnet_Users WHERE UserName = 'sitecore\Admin')

7. Copy users and all their data including passwords from core database of one instance to another:

-- Copy all non-existing users and their data from OLD core database to NEW core database
INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Applications SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Applications 
    WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Applications WHERE [Sitecore.NEW.Core].dbo.aspnet_Applications .ApplicationId = [Sitecore.OLD.Core].dbo.aspnet_Applications.ApplicationId)

INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Membership SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Membership 
    WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Membership WHERE [Sitecore.NEW.Core].dbo.aspnet_Membership .UserId = [Sitecore.OLD.Core].dbo.aspnet_Membership.UserId)

INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Paths SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Paths 
    WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Paths WHERE [Sitecore.NEW.Core].dbo.aspnet_Paths .PathId = [Sitecore.OLD.Core].dbo.aspnet_Paths.PathId)

INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_PersonalizationAllUsers SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_PersonalizationAllUsers 
    WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_PersonalizationAllUsers WHERE [Sitecore.NEW.Core].dbo.aspnet_PersonalizationAllUsers.PathId = [Sitecore.OLD.Core].dbo.aspnet_PersonalizationAllUsers .PathId)

INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_PersonalizationPerUser SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_PersonalizationPerUser 
    WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_PersonalizationPerUser WHERE [Sitecore.NEW.Core].dbo.aspnet_PersonalizationPerUser.PathId = [Sitecore.OLD.Core].dbo.aspnet_PersonalizationPerUser .PathId)

INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Profile SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Profile 
    WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Profile WHERE [Sitecore.NEW.Core].dbo.aspnet_Profile.UserId = [Sitecore.OLD.Core].dbo.aspnet_Profile.UserId)

INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Roles SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Roles 
    WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Roles WHERE [Sitecore.NEW.Core].dbo.aspnet_Roles.RoleName = [Sitecore.OLD.Core].dbo.aspnet_Roles.RoleName)

INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_SchemaVersions SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_SchemaVersions 
    WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_SchemaVersions WHERE [Sitecore.NEW.Core].dbo.aspnet_SchemaVersions.Feature = [Sitecore.OLD.Core].dbo.aspnet_SchemaVersions.Feature)

INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_Users SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_Users 
WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_Users WHERE [Sitecore.NEW.Core].dbo.aspnet_Users.UserName = [Sitecore.OLD.Core].dbo.aspnet_Users.UserName)

INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_UsersInRoles SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_UsersInRoles 
    WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_UsersInRoles WHERE [Sitecore.NEW.Core].dbo.aspnet_UsersInRoles.UserId = [Sitecore.OLD.Core].dbo.aspnet_UsersInRoles.UserId)

INSERT INTO [Sitecore.NEW.Core].dbo.aspnet_WebEvent_Events SELECT * FROM [Sitecore.OLD.Core].dbo.aspnet_WebEvent_Events 
    WHERE NOT EXISTS (SELECT * FROM [Sitecore.NEW.Core].dbo.aspnet_WebEvent_Events WHERE [Sitecore.NEW.Core].dbo.aspnet_WebEvent_Events.EventId = [Sitecore.OLD.Core].dbo.aspnet_WebEvent_Events .EventId)

8. Delete children item of an item with a given ID directly from SQL (beware item cache). Please make sure you execute that in a context of a Sitecore database (ie. call use Sitecore_master before running this):

DECLARE @parentId AS UNIQUEIDENTIFIER; SET @parentId = '{29267838-AAB2-415F-B07A-D006719CD088}'
DECLARE @RowsToProcess  INT
DECLARE @CurrentRow     INT
DECLARE @SelectCol1     UNIQUEIDENTIFIER
DECLARE @Items2Delete TABLE (RowID INT NOT NULL PRIMARY KEY IDENTITY(1,1),ID UNIQUEIDENTIFIER)

SET @parentId = '{29267838-AAB2-415F-B07A-D006719CD088}'

INSERT INTO @Items2Delete (ID)
SELECT [Descendant] AS ID
  FROM [dbo].[Descendants]
  WHERE Ancestor = @parentId
SET @RowsToProcess=@@ROWCOUNT
PRINT  @RowsToProcess

INSERT INTO @Items2Delete (ID)
SELECT ID FROM [dbo].[Items] WHERE ParentID = @parentId
SET @RowsToProcess=@@ROWCOUNT
PRINT  @RowsToProcess

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
    SET @CurrentRow=@CurrentRow+1
    SELECT
        @SelectCol1=ID
        FROM @Items2Delete
        WHERE RowID=@CurrentRow

  DECLARE @id AS UNIQUEIDENTIFIER;

  SET @id = @SelectCol1
  PRINT @id

  EXEC sp_executesql N'DELETE FROM [Items]
        WHERE [ID] = @itemId

        DELETE FROM [SharedFields]
        WHERE [ItemId] = @itemId

        DELETE FROM [UnversionedFields]
        WHERE [ItemId] = @itemId

        DELETE FROM [VersionedFields]
        WHERE [ItemId] = @itemId',N'@itemId UNIQUEIDENTIFIER',@itemId=@id

  EXEC sp_executesql N'DELETE FROM [Descendants] WHERE [Descendant] = @itemId',N'@itemId uniqueidentifier',@itemId=@id
  EXEC sp_executesql N' DELETE FROM [Links] WHERE [SourceItemID] = @itemID AND [SourceDatabase] = @database',N'@itemID uniqueidentifier,@database nvarchar(6)',@itemID=@id,@database=N'master'
  EXEC sp_executesql N'DELETE FROM [Tasks] WHERE [ItemID] = @itemID AND [Database] = @database',N'@itemID uniqueidentifier,@database nvarchar(6)',@itemID=@id,@database=N'master'
END

Hope that helps you!

Web Forms for Marketers 8.0 - missing Save to Database action and making it work with SQL database again

As we know, Sitecore version 8.0 incorporated popular the module called Web Forms for Marketers (WFFM), and it became an integral part of Sitecore. WFFM is still shipped as a package, however now through Sitecore SDN portal, rather than via Marketplace as before.

So, let's assume you have a brand clean install of Sitecore (I have 8.0 Update 4 for this example). In order to download WFFM for 8.0 Update 4 please follow to https://dev.sitecore.net/Downloads/Sitecore_Experience_Platform/8_0/Sitecore_Experience_Platform_80_Update4.aspx and find download link below in the module section.

Remark 1: zip archive you download is not a package - it is normal archive containing 2 child packages - first is package for CM instance and additionally contains all dialogs etc. while another is just CD installation package. For our demonstration, unpack Web Forms for Marketers 8.0 rev. 150625.zip into /Data/Packages directory and use Development Tools - Installation Wizard, as you normally install packages.

Remark 2: ensure you have Mongo up and running, otherwise it will come to "package never installs" issue (more details about that bug here). it is required just for installation, after you have it installed you it is safe to turn off Mongo - WFFM will save data to database without any problems.

Remark 3: it may take up to 5 minutes to install the package, so please do not panic, as soon as you got Mongo running - you'll reach the point when it prompts you for setting Placeholder settings.


After WFFM installation is complete, you may configure a form. By default WFFM contains several pre-created forms as an example for people to play with it and get acknowledges with the module. For those who are new to WFFM here is a screenshot below displaying how to locate Form Designer:


Selecting Form Designer above loads a screen with the list of existing forms. Let's pick Get Our Newsletter form and click OK. Form Designer will load form configuration screen with fields and Subscribe button at the bottom. A click to that button opens Submit panel left hand side with several settings for pre-save (validation), save and post-save (success page / success message).


Remark 4: Those who used to work with previous versions of WFFM will be frustrated by missing Save to Database action from save actions menu. These are save actions that come out of box:


There is nothing to worry about, Save to Database action is still there but is not displayed only because as it always happens. However:

Remark 5: SQL database itself is not coming with the package, so you need to pick database backup from one of previous versions - its schema remains the same. To make you life easier, I have attached forms empty database backup so you may download it by this link. In WFFM 8.0 there is the setting that references connection string name (in you ConnectionStrings.config file) that is used by WFFM.

<!-- CONNECTION STRING - Sets the name of the connection string -->
<setting name="WFM.ConnectionString" value="wfm" />
<add name="wfm" connectionString="Data Source=.;Initial Catalog=test2_Forms;Integrated Security=False;User ID=sa;Password=your_password" />

To make Get Our Newsletter form work, we need to assign it to some page. Default landing page called Home will work well for our purpose. This is how you set a form to a placeholder:


Image above displays webform, because we use default layout that comes with webforms aspx page. For MVC there is rendering called Form MVC. With next step below (as clicking Edit button) you need to specify which exact form should be served by this sublayout (or rendering for MVC):



Now you may verify /Home page in Publish - Preview screen or publish web site and then load the page normally (http://your.site.name/) so you end up with something like on a screenshot below:


After you fill in valid email and click Subscribe button, you'll get "Thank you for filling in the form" message, that's in case database was references correctly. if not - there will be message abut unexpected error and corresponding exception details will appear in log file.

Let's get physically into database to verify the data has been stored correctly. In successful case you'll see one record in Form table and few records in Field table, one record per each form field.


That's all!

Following blog post will demonstrate how to make WFFM result screen display forms records from SQL database.