sp2013 add custom text to organize disp/new/edit forms

win cmd hibernate with timer

99 seconds timer before hibername:

ping -n 99 localhost > NUL 2>&1 && shutdown /h /f

Error – Unable to Modify View: /_vti_bin/owssvr.dll?CS=65001

Solution is to deactivate “Minimal Download Strategy” site feature.

drop all tables except one

EXEC sys.sp_MSforeachtable 
       N'IF OBJECT_ID(''?'') <> OBJECT_ID(''dbo.YourTableToKeep'')
             DROP TABLE ?
       '

SharePoint 2013 update instructions

technet

powershell add metadata termstore administrator

$taxonomySession = Get-SPTaxonomySession -Site "http://localhost"
$termStore = $taxonomySession.TermStores["Managed Metadata Service"]
$termStore.AddTermStoreAdministrator("domain\user")
$termStore.CommitAll()

set recovery simple for all user databases

select 'alter database ['+name+'] set recovery simple' from master.sys.databases where database_id > 4 and state_desc = 'online'

sharepoint delete all audit entries

asnp *sh*
$x = get-date; (get-spsite http://sp/sites/site).Audit.DeleteEntries($x)

taxonomy updater

force taxonomy list update (C#/powershell)

kill all connections to database

USE master
go

DECLARE @dbname sysname
SET @dbname = 'yourdbname'

DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE ('KILL ' + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
END

sharepoint reset role inheritance for all items in multiple lists

$lists = @(
'http://sp/sites/A/Lists/xxx/'
'http://sp/sites/A/Lists/yyy/'
)

foreach ($l in $lists)
{
write-host $l
$it = (get-spweb http://sp/sites/a).getlist($l).items | ? {$_.hasuniqueroleassignments -eq $true}
$it.count
$it | % {$_.ResetRoleInheritance()}
$it = (get-spweb http://sp/sites/a).getlist($l).items | ? {$_.hasuniqueroleassignments -eq $true}
$it.count
write-host "--------------"
}

delete all second level terms in termset

[GUID]$termset_to_check = '1c655d21-ffb5-465e-99f2-b724c8d143f2'

asnp *sh*

Function Get-TaxonomySession80port(){
  if ([string]$WebAppOn80portURL = (Get-SPWebApplication | ? {$_.url.LastIndexOf(':') -le 5})[0].url) # 80`s port web app
    { if ($session = Get-SPTaxonomySession -Site $WebAppOn80portURL)
        {return $session}
    }
 Write-host "Cannot open taxonomy session. Ensure that Managed Metadata Service Application is online and accessible and there is at least one web application with :80 port"
}

Function Get-TermStore(){
  $session = Get-TaxonomySession80port
  if ($termStore = $session.DefaultSiteCollectionTermStore)
    { return $termStore }
  return $null
}

$ts = Get-TermStore
$t = $ts.GetTermset($termset_to_check)

foreach ($oneTerm  in $t.Terms | ? {$_.TermsCount -gt 0} )
{
$i = 0
    foreach ($te in $oneTerm.Terms)
        {
        write-host $te.name
        $te.delete()
        $i++
        }
write-host $("Commiting {0} deletions" -f $i) -for Cyan
$ts.CommitAll()

<# First level terms deletion
foreach ($oneTerm  in $t.Terms)
{
write-host $oneTerm.name
$oneTerm.delete()
$ts.CommitAll()
}
#>

<# delete non-primary lables
foreach ($oneTerm in $t.Terms)
{
$i = 0
    foreach ($te in $oneTerm.labels | ? {$_.isdefaultforlanguage -eq $false})
        {
        write-host $te.Value
		$te.delete()
        $i++
        }
if ($i -gt 0)
{
    write-host $("Commiting {0} deletions" -f $i) -for Cyan
    $ts.CommitAll()
}
}
#>
}

SP metadata field mapping attributes

SspId     : 54c4065f-9a95-4b70-841f-73834041f9de # termstore ID
TermSetId : 30187a09-9c91-40e0-a215-636d3e809e7b # termset ID
AnchorId  : 00000000-0000-0000-0000-000000000000 # specific term ID (inside of the termstore abowe).

move sharepoint metadata terms to termset level (1 level up) and remap SP fields

Function Get-TaxonomySession80port(){
  if ([string]$WebAppOn80portURL = (Get-SPWebApplication | ? {$_.url.LastIndexOf(':') -le 5})[0].url) # 80`s port web app
    { if ($session = Get-SPTaxonomySession -Site $WebAppOn80portURL)
        {return $session}
    }
 LogErrorAndExitDQ "Cannot open taxonomy session. Ensure that Managed Metadata Service Application is online and accessible and there is at least one web application with :80 port"
}
Function Get-TermStore($ReturnAsObject = $true){
  $session = Get-TaxonomySession80port
  if ($termStore = $session.DefaultSiteCollectionTermStore)
    {
        if ($ReturnAsObject) {return $termStore}
        return [guid]$termStore.id;
    }
  return $null
}

# define term with childs
# this term has to be deleted, child terms will be moved up to the termset

$term_for_removing = '3c3c8fda-fdcd-4395-8b1f-c8a4d50637b3'

# find all involved sp fileds on the farm and remap them to the termstore
asnp *sh*
$TargetFieldSet = get-spsite -lim all | ? {$_.url -notlike "http://sp/personal/*" } | % {$_ | get-spweb -lim all} | % {$_.lists} | % {$_.fields } | ? {$_.TypeAsString -eq 'TaxonomyFieldType' -and $_.AnchorId -eq $term_for_removing }
$TargetFieldSet.count
$TargetFieldSet | % {$x = $_; $x.AnchorId = $([guid]::Empty); $x.update(); write-host * -nonew}

# move terms termset and delete old container (term)
$ts = Get-TermStore
$t = $ts.GetTerm($term_for_removing)
foreach ($oneTerm  in $t.Terms) {$oneTerm.move($t.TermSet);write-host *}
$t.delete()
$ts.CommitAll()

gac add/remove dll via powershell

[System.Reflection.Assembly]::Load("System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")
$publish = New-Object System.EnterpriseServices.Internal.Publish
$publish.GacInstall("c:\temp\MyDLL.dll")
$publish.GacRemove("c:\temp\MyDLL.dll")
iisreset

SP impersonate list items deletion

$web = 'http://sp/sites/AA'
$l = @(
'http://sp/sites/AA/Lists/Audit%20records%202015/'
'http://sp/sites/AA/Lists/Audit%20Participants%202015/'
)

asnp *sh*
$sys_user = (New-Object Microsoft.SharePoint.SPSite($web)).SystemAccount.UserToken
$Site = New-Object Microsoft.SharePoint.SPSite($Web, $sys_user)
$w = $Site.openweb()

foreach ($i in $l)
{
$list = $w.getlist($i)
write-host `n $list.title -NoNewline
write-host :: $list.items.count
$ids = $list.items | select id
foreach ($item in $ids)
    {$list.getitembyid($item.id).delete(); write-host * -nonew}
}

t-sql get info about restoring or backuping db

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time 
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

how to update sp list column with metadata value quickly

$list = 'http:/sp/sites/hr/lists/staff'
$MD_field = 'Specialization Metadata'
$MD_value = '-- not defined -- |0abaac1d-58c1-4c83-8a6c-85d6a5df5fce'

#-----------

asnp *sh*
Function GetList ($list_url){
    try {
            $spSite = new-object Microsoft.SharePoint.SPSite($list_url)
            $l = $spSite.RootWeb.GetList($list_url)
        }
    catch {$l = $false}
    return $l
}

if ($l = getlist $list)
{
    $it = $l.items.where({$_['Specialization'] -eq $null}) | select ID
    $it.count 

    foreach ($i in $it)
    {
        $item = $l.getitembyid($i.id)
        if (!($item[$MD_field]))
            {
                $item[$MD_field] = $MD_value
                $item.systemupdate()
                write-host $item.id
            }
    }
}
else {Write-host "List [$list] not found."}

tsql get all members of specific server role

EXEC sp_helpsrvrolemember 'sysadmin'

tsql single / multi user, take offline

ALTER DATABASE DWStagingAndConfig SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE DWStagingAndConfig SET MULTI_USER
-----
ALTER DATABASE DWStagingAndConfig 
SET OFFLINE WITH ROLLBACK IMMEDIATE

ADSI 901 rows limitation fix

-- Create a database view from unions of smaller selects.
DECLARE @queryFormat VARCHAR(MAX) = '
SELECT samAccountName, mail, givenName, sn, displayName, whenChanged, logonCount FROM OPENQUERY(ADSI, ''
    SELECT samAccountName, mail, givenName, sn, displayName, whenChanged,logonCount, jpegPhoto
    FROM ''''LDAP://OU=Staff,DC=DomainName,DC=local''''
    WHERE objectClass = ''''User'''' AND objectCategory = ''''Person'''' AND mail = ''''#p0'''''')';

DECLARE @sql VARCHAR(MAX) = 'CREATE VIEW [AdView] AS ';
DECLARE @asciiValue INT = ASCII('A');
DECLARE @asciiEnd INT = ASCII('Z');
WHILE @asciiValue <= @asciiEnd BEGIN 
    SET @sql = @sql + replace(@queryFormat, '#p0', CHAR(@asciiValue) + '*');
    IF @asciiValue < @asciiEnd  SET @sql = @sql + ' UNION ALL ';
    SET @asciiValue = @asciiValue + 1;
END
PRINT @sql;

-- the 'live' view of the active directory data.
IF OBJECT_ID('[AdView]') IS NOT NULL DROP VIEW [AdView]
EXEC(@sql);

-- a 'snapshot' of the active directory data, for faster selects. you could execute this on a schedule to keep up to date.
IF OBJECT_ID('[AdTable]', 'U') IS NOT NULL DROP TABLE [AdTable]
SELECT * INTO [AdTable] FROM [AdView]

Enable-PSRemoting

Enable-PSRemoting -Force
Set-Item wsman:\localhost\client\trustedhosts *
Restart-Service WinRM

sharepoint powershell restore previous version of list items

asnp *sh*

Function GetList ($list_url){
    $spSite = new-object Microsoft.SharePoint.SPSite($list_url);
    return $spSite.RootWeb.GetList($list_url)
}

$l = getlist 'http://sp/sites/ConfigSite/Lists/listname/'
$vi = $l.views['v']
$vi.RowLimit = 0
$vi.Update()
$items = $l.getitems($vi) | select id
$items.Count

foreach ($i in $items)
{
    # restore previous 
    $item = $l.getitembyid($i.id)
    $item.id
    $item.versions.RestoreByLabel($item.versions[1].versionlabel)

    # delete wrong version (previous to restored)
    $item = $l.getitembyid($i.id)
    $item.versions[1].Recycle()
}

sharepoint powrshell work with hyperlinks (link field)

Function GetList ($list_url){
    $spSite = new-object Microsoft.SharePoint.SPSite($list_url);
    return $spSite.RootWeb.GetList($list_url)
}
asnp *sh*

$l = getlist 'http://sp/sites/ConfigSite/Lists/APO/'
$vi = $l.views['v']
[array]$it = $l.items.where({$_['URL'] -eq $null}) | select id
foreach ($i in $it)
{
    $x = $l.getitembyid($i.id)
    $x['URL'] = $x['Link'].split(',')[0].replace('http://shp','')
    $x.update()
    write-host * -non
    $x = $null
}

restart sql server service remotelly

$pr = Get-Service -ComputerName sql-tst-vm
$pr.where({$_.status -eq 'Running' -and $_.DisplayName -like 'SQL Server (*'}) | Restart-Service -force

wifi virtual private network miniport

netsh wlan set hostednetwork mode=allow ssid="V" key="passssssss" keyUsage=persistent
netsh wlan start hostednetwork

disable some site features

$s = @(
'LocalSiteDirectorySettingsLink'
'V2VPublishedLinks'
'6c09612b-46af-4b2f-8dfc-59185c962a29'
'BaseSite'
'ReviewWorkflowsSPD'
'SignaturesWorkflowSPD'
'TranslationWorkflow'
'Workflows'
'PPSWorkspaceCtype'
'WAWhatsPopularWebPart'
'PPSMonDatasourceCtype'
'PPSWebParts'
'PPSSiteCollectionMaster'
'PPSWebParts'
'MobileExcelWebAccess'
'ReviewWorkflows'
'LegacyWorkflows'
'TaxonomyFieldAdded'
'ReviewPublishingSPD'
'Reporting')
 
 $s | % {Disable-SPFeature -url http://shp1-tst-vm/sites/2010 -id $_)

sql server – get active users (connections)

SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame

t-sql generate random int value between @from @to

update [table] set [column] =
FLOOR(RAND(convert(varbinary, newid()))*(@from-@to)+@from)

Process through all the tables in a database or through all databases

use undocumented sp_MSforeachtable and sp_MSforeachdb
example

some usefull excel vb settings

With ActiveWindow
    .DisplayHorizontalScrollBar = False
    .DisplayHeadings = False
    .DisplayGridlines = False
End With
    
With Application
      .ExecuteExcel4Macro "Show.ToolBar(""Ribbon"",False)"
      .DisplayFormulaBar = False
.ScreenUpdating = True
End With

ActiveWorkbook.ShowPivotTableFieldList = True
        
Rows("4:4").EntireRow.AutoFit

=SUBSTITUTE(TRIM(settings!J5&spacer&settings!J6&spacer);" ";CHAR(10))

ActiveSheet.ChartObjects(1).Chart.ProtectSelection = True

GET ALL TABLES of SPECIFIC SCHEMA

SELECT t.name 
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE s.name = N'dbo';

sp delete item versions

Add-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction silentlycontinue
$sites = @(
'http://amc-autotest-sp/sites/Acc'
'http://amc-autotest-sp/sites/HR'
'http://amc-autotest-sp/sites/PO'
)
$system_lists = @(
'Content type publishing error log'
'Converted Forms'
'Customized Reports'
'Form Templates'
'fpdatasources'
'Master Page Gallery'
'MSysASO'
'Reporting Metadata'
'Reporting Templates'
'Site Assets'
'Site Pages'
'Site Template Gallery'
'Solution Gallery'
'Style Library'
'TaxonomyHiddenList'
'Theme Gallery'
'Tool Documents'
'User Information List'
'USysApplicationLog'
'Web Part Gallery'
'wfpub'
'Workflow History'
'Журнал рабочего процесса'
'Рабочие процессы'
'Composed Looks'
'appdata'
'List Template Gallery'
)

$WSC_lists = @(
'Sport'
'Calendar'
'Person'
)
FOREACH ($s in $sites)
{
	FOREACH ($web in $(get-spweb -Site $s -Limit ALL))
	{
		FOREACH ($l in $($web.lists | ? {$system_lists -cnotcontains $_.title -and $WSC_lists -contains $_.title -and $_.title -notlike '*Time Tracking*' }))
		{
          write-host $l.title , $l.EnableVersioning, "`n"
                  foreach($item in $l.Items) {
                    $item.versions.DeleteAll()
                    write-host * -nonew
                  }
            }
	}
}

sp powershell content cleanup

Add-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction silentlycontinue
function SpPerformDelete ([Microsoft.SharePoint.SPWeb]$SpWeb, [string]$ListTitle = $(throw "List title required"), [int]$BatchSize = 1000)
{
    if ($BatchSize -lt 1 -or $BatchSize -gt 2000) {throw "BatchSize out of range"}
    $l = $SpWeb.lists[$ListTitle]
    if (!$l) {throw "$ListTitle - splist not found"}
    $isDocLib = ($l.basetemplate -eq 'DocumentLibrary' -or 'WebPageLibrary')
    $listID = $l.id.tostring()
 
    $query = New-Object Microsoft.SharePoint.SPQuery;
    $query.ViewAttributes = "Scope='Recursive'";
    $query.RowLimit = $BatchSize;
    $query.Query = '<Where><IsNotNull><FieldRef Name="ID"/></IsNotNull></Where>'
 
    [int]$item_counter = 0;
    do
    {
        [array]$ids=@();
        $items = $l.GetItems($query) 
        $query.ListItemCollectionPosition = $items.ListItemCollectionPosition
             
        $items | % {$ids+=$_.id}
         
        [System.Text.StringBuilder]$batchXml = New-Object 'System.Text.StringBuilder'
        $batchXml.Append('<?xml version="1.0" encoding="UTF-8"?>') | out-null
        $batchXml.Append('<Batch>') | out-null
 
        foreach ($id in $ids)
            {
                 $batchXml.Append('<Method>') | out-null
                 $batchXml.Append('<SetList Scope="Request">' + $listID + '</SetList>') | out-null
                 $batchXml.Append('<SetVar Name="ID">' + $id + '</SetVar>') | out-null
                 if ($isDocLib){
                 $batchXml.Append('<SetVar Name="owsfileref">' + $l.getitembyid($id).File.ServerRelativeUrl + '</SetVar>') | out-null
                 }
                 $batchXml.Append('<SetVar Name="Cmd">Delete</SetVar>') | out-null
                 $batchXml.Append('</Method>') | out-null
                 ++$item_counter
            }
        $batchXml.Append('</Batch>') | out-null
        #write-host $batchXML.ToString()
        $web.ProcessBatchData($batchXml.ToString()) | Out-Null;
        write-host $("$ListTitle :: $item_counter :: {0}" -f $(get-date).tostring())
    }
    while ( $query.ListItemCollectionPosition -ne $null )
}

#Log
	$logDate = $(get-date -UFormat %d%m%y).tostring()
	$logFile= ".\Log_$logDate.txt"
	start-transcript -path $logFile

$sites = @(
'http://amc-autotest-sp/sites/PO'
)

$system_lists = @(
'Content type publishing error log'
'Converted Forms'
'Customized Reports'
'Form Templates'
'fpdatasources'
'Master Page Gallery'
'MSysASO'
'Reporting Metadata'
'Reporting Templates'
'Site Assets'
'Site Pages'
'Site Template Gallery'
'Solution Gallery'
'Style Library'
'TaxonomyHiddenList'
'Theme Gallery'
'Tool Documents'
'User Information List'
'USysApplicationLog'
'Web Part Gallery'
'wfpub'
'Workflow History'
'Журнал рабочего процесса'
'Рабочие процессы'
'Composed Looks'
'appdata'
'List Template Gallery'
)

$WSC_lists = @(
'Role assignments'
'Projects and Activities'
)

FOREACH ($s in $sites)
{
	FOREACH ($web in $(get-spweb -Site $s -Limit ALL))
	{
		[array]$lists=@()

		FOREACH ($l in $($web.lists | ? {$_.Hidden -eq $False -and $system_lists -cnotcontains $_.title -and $WSC_lists -notcontains $_.title -and $_.title -notlike '*Time Tracking*' }))
		{
			$lists+=$l.title
		}
		FOREACH ($target in $lists)
		{
			SpPerformDelete $web $target
		}
	}
}
stop-transcript

sql server get all currently running queries

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
DB_NAME(req.database_id) as databaseName,
req.blocking_session_id
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

sharepoint excel services (rest) usage example

/_vti_bin/ExcelRest.aspx/DOC/Book.xlsx/model/PivotTables('PivotTable1')?$format=image
/_vti_bin/ExcelRest.aspx/DOC/Book.xlsx/Model/Ranges('Sheet1!A1')?Ranges('Sheet1!A1')=sometext

modify search service app topology

#clone and add

$ssa = Get-SPEnterpriseSearchServiceApplication
$active = Get-SPEnterpriseSearchTopology -SearchApplication $ssa -Active
$clone = New-SPEnterpriseSearchTopology -SearchApplication $ssa -Clone -SearchTopology $active
$wfe = Get-SPEnterpriseSearchServiceInstance -Identity INSITE1-VM

New-SPEnterpriseSearchAdminComponent -SearchTopology $clone -SearchServiceInstance $wfe
New-SPEnterpriseSearchIndexComponent –SearchTopology $clone -SearchServiceInstance $wfe -IndexPartition 0 -RootDirectory "C:\SpIndexPartition"
New-SPEnterpriseSearchQueryProcessingComponent -SearchTopology $clone -SearchServiceInstance $wfe

New-SPEnterpriseSearchAnalyticsProcessingComponent -SearchTopology $clone -SearchServiceInstance $wfe
New-SPEnterpriseSearchContentProcessingComponent -SearchTopology $clone -SearchServiceInstance $wfe
New-SPEnterpriseSearchCrawlComponent -SearchTopology $clone -SearchServiceInstance $wfe

Start-SPEnterpriseSearchServiceInstance -Identity $wfe
Set-SPEnterpriseSearchTopology -Identity $clone

# clone and remove 

$ssa = Get-SPEnterpriseSearchServiceApplication
$active = Get-SPEnterpriseSearchTopology -SearchApplication $ssa -Active
$clone = New-SPEnterpriseSearchTopology -SearchApplication $ssa -Clone -SearchTopology $active

Get-SPEnterpriseSearchComponent -SearchTopology $clone | ? {$_.servername -eq 'INSITE-VM'}

$crawl = 'e9b7a141-ec8e-47fb-bd13-26ac51832bfe'
$ContentProcessingComponent1 = 'fcb4d878-6b1a-485c-b40e-945cf563ad5a'
$AnalyticsProcessingComponent1 = 'd5408faf-e5b8-4b6d-8dee-50f08a20b83c'

Remove-SPEnterpriseSearchComponent -Identity $crawl -SearchTopology $clone -confirm:$false
Remove-SPEnterpriseSearchComponent -Identity $ContentProcessingComponent1 -SearchTopology $clone -confirm:$false
Remove-SPEnterpriseSearchComponent -Identity $AnalyticsProcessingComponent1 -SearchTopology $clone -confirm:$false

Set-SPEnterpriseSearchTopology -Identity $clone
Get-SPEnterpriseSearchStatus -SearchApplication $ssa

# start/stop sp search instance
Get-SPEnterpriseSearchServiceInstance
Stop-SPEnterpriseSearchServiceInstance b95c2ad3-235d-4c1f-8626-b8c4182fccdf

RESTORE FILELISTONLY — get sql backup info

RESTORE FILELISTONLY 
FROM DISK = 'C:\DB\Backup\Milestones.bak'

sharepoint audit events

SELECT [SiteId]
      ,[ItemId]
      ,[ItemType]
      ,[UserId]
      ,[MachineName]
      ,[MachineIp]
      ,[DocLocation]
      ,[LocationType]
      ,[Occurred]
      ,[Event]
      ,[EventName]
      ,[EventSource]
      ,[SourceName]
      ,[EventData]
      ,[AppPrincipalId]
FROM [HR].[dbo].[AuditData] WITH (nolock)
WHERE itemid  ='6680f7dc-c604-490d-9f0c-e8cc866c6931'
AND event = 8
ORDER BY occurred desc

1 document was checked out.
2 document was checked in.
3 an object was viewed.
4 an object was deleted.
5 an object was updated.
6 content type was updated.
7 child object was deleted.
8 list schemchanged.
10 an object was undeleted.
11 The audit event was generated by workflow.
12 an object was copied.
13 an object was moved.
14 the audit flags, as described in [MS-WSSFO], of an object were updated.
15 search operation was performed.
16 child object was moved.
30 security group was created.
31 security group was deleted.
32 security principal was added to security group.
33 security principal was removed from security group.
34 security role was created.
35 security role was deleted.
36 security role was updated.
37 security role breaks inheritance.
38 security scope was updated.
39 security scope restores inheritance.
40 security scope breaks inheritance.
50 audit events was deleted.
100 event was generated by custom operation.

Remove Server Reference from Farm (CacheHostInfo is null)

$SPFarm = Get-SPFarm
$cacheClusterName = "SPDistributedCacheCluster_" + $SPFarm.Id.ToString()
$cacheClusterManager = [Microsoft.SharePoint.DistributedCaching.Utilities.SPDistributedCacheClusterInfoManager]::Local
$cacheClusterInfo = $cacheClusterManager.GetSPDistributedCacheClusterInfo($cacheClusterName)
$instanceName ="SPDistributedCacheService Name=AppFabricCachingService"
$serviceInstance = Get-SPServiceInstance | ? {($_.Service.Tostring()) -eq $instanceName -and ($_.Server.Name) -eq "shp1-tst-vm"}
$serviceInstance.Delete()

Get each SpSite or SpContent DB size in MB

get-spsite | select url,@{label="size(MB)";Expression={[Math]::Round($_.usage.storage/1MB,2)}}
Get-SPDatabase | select Name,@{label="Size(MB)";Expression={[Math]::Round($_.disksizerequired/1MB,2)}}

Extracting SSRS Report RDL (XML) from the ReportServer database

--The first CTE gets the content as a varbinary(max)
--as well as the other important columns for all reports,
--data sources and shared datasets.
WITH ItemContentBinaries AS
(
  SELECT
     ItemID,Name,[Type]
    ,CASE Type
       WHEN 2 THEN 'Report'
       WHEN 5 THEN 'Data Source'
       WHEN 7 THEN 'Report Part'
       WHEN 8 THEN 'Shared Dataset'
       ELSE 'Other'
     END AS TypeDescription
    ,CONVERT(varbinary(max),Content) AS Content
  FROM ReportServer.dbo.Catalog
  WHERE Type IN (2,5,7,8)
),
--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
  SELECT
     ItemID,Name,[Type],TypeDescription
    ,CASE
       WHEN LEFT(Content,3) = 0xEFBBBF
         THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
       ELSE
         Content
     END AS Content
  FROM ItemContentBinaries
)
--The old outer query is now a CTE to get the content in its xml form only...
,ItemContentXML AS
(
  SELECT
     ItemID,Name,[Type],TypeDescription
    ,CONVERT(xml,Content) AS ContentXML
 FROM ItemContentNoBOM
)
--now use the XML data type to extract the queries, and their command types and text....
SELECT
     ItemID,Name,[Type],TypeDescription,ContentXML
    ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
    ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
FROM ItemContentXML
--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)

from here

t-sql view patcher

Patcher Code:

CREATE PROCEDURE dbo.SPViewsPatcher(@ViewName varchar(255), @ReplaceWhat1 VARCHAR(max), @ReplaceWith1 VARCHAR(max), @return TINYINT out)
AS BEGIN

/***********************************[ SPViewsPatcher v0.1 ]*********************************
ШО ДЕЛАЕТ:	Альтерит вьюху, заменяя при этом ее "бажную" часть на кошерную.
КАК ЮЗАТЬ:	Патчер ставится перед вызовом дигеста. Всегда.
ДЕФОЛТЫ:	1) Деплоится на CorporateDWH как хранимка, может быть вызван с любой БД.
			2) Работает только с вьюхами [CorporateDWH].[dbo].*
			3) Патч не ставится дважды на одну вьюху (определяется по ключу '!SPViewsPatcher' в дефинишине вьюхи)
РЕТУРНЫ:	0 -- view found, patch not needed (keyword found)
			1 -- view found, patched successfully.
ЕРРОРЫ:		1) Senseless operation: @ReplaceWhat = @ReplaceWith
			2) Replacement string not found.					
			3) Replacement string should be unique.				// шоб не запороть вьюху
			4) Patch failed.									// зафейлился финальный "альтер вью" 
********************************************************************************************/

IF (
	LEN(isnull(@ReplaceWhat1,0)) = 0
	OR	LEN(isnull(@ReplaceWith1,0)) = 0
	OR  LEN(isnull(@ViewName,0)) = 0
	OR	@ReplaceWhat1 = @ReplaceWith1
	)
	RAISERROR ('Senseless operation or one of parameters is NULL', 16, 1) WITH LOG
----- Msg -------------------------------------------------------------------------------
DECLARE @msg varchar (max) = '/* !SPViewsPatcher v0.1 :: THIS is NOT native SPViewsCreator view! '
SET @msg += 'String "'+@ReplaceWhat1+'" was replaced with string "'+ @ReplaceWith1 + '"'
SET @msg += '*/'
----- Code ------------------------------------------------------------------------------
IF NOT EXISTS(SELECT * FROM sys.objects WHERE [type] = 'V' AND [name] = @ViewName AND [schema_id] = 1)
	RAISERROR ('View not found', 20, 1) WITH LOG
ELSE
		BEGIN
		   SET @return = 0
		   DECLARE @def VARCHAR(MAX) = OBJECT_DEFINITION(OBJECT_ID(@ViewName))
		   IF (CHARINDEX('!SPViewsPatcher',@def) != 0 )				
				RETURN;
		   ELSE IF (CHARINDEX(@ReplaceWhat1,@def) = 0) 
				RAISERROR ('Replacement string not found.', 20, 1) WITH LOG
		   ELSE IF (CHARINDEX(@ReplaceWhat1,@def,(CHARINDEX(@ReplaceWhat1,@def)+LEN(@ReplaceWhat1))) !=0)
				RAISERROR ('Replacement string should be unique.', 20, 1) WITH LOG
	       ELSE BEGIN
				   DECLARE @defNew VARCHAR(MAX) = REPLACE(@def,'create view ','');
				   SET @defNew = REPLACE(@defNew,@ReplaceWhat1,@ReplaceWith1);
				   IF @defNew != @def
						EXEC (@msg + ' Alter View ' + @defNew)
				   IF @@error = 0
						SET @return = 1 -- patched successfully
					ELSE BEGIN
						SET @ReplaceWhat1 ='Patch failed. SQL error code: ' + CAST(@@error as varchar(5)) + 'ALTER STATEMENT: ' + @msg + ' Alter View ' + @defNew
						RAISERROR (@ReplaceWhat1, 20, 1) WITH LOG
					END
				END
		END
----- End -------------------------------------------------------------------------------
END

Usage example

-- view patcher
DECLARE @SPViewsPatcherReturn TINYINT
exec CorporateDWH.dbo.SPViewsPatcher 'AppServices__PEX_View','UD.int4 AS [Stakeholders],','dbo.Departments_DoStuff(UD.tp_UIVersion,''9d48d3e2-9416-4049-9634-4c3ae53c237b'','';'',UD.tp_ParentId,UD.tp_DocId) AS [Stakeholders],', @SPViewsPatcherReturn OUT

-- query
select * from AppServices__PEX_View

split string in sqlserver (t-sql)

declare @x [nvarchar] (10) = 'sdf|eee'
select SUBSTRING ( @x, 0, CHARINDEX ('|',@x, 0 ))
select SUBSTRING ( @x, CHARINDEX('|',@x, 0 )+1, len(@x))

stsadm forcedeletelist

"C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\BIN\stsadm.exe" -o forcedeletelist -url "http://sp/lists/History attachments"

find broken lists/fields

get-spsite -lim all | ? {$_.url -notlike "http://sp/personal/*" } |`
 % {$_ | get-spweb -lim all} |`
 % {$_.lists } |`
 % {$x = $($_.parentweb.url +'/'+$_.rootfolder);`
		try { $_.fields | out-null}`
		catch { $error[0].Exception; write-host $x -for yellow}`
	  }

ways to check sqlserver table or view existance

IF OBJECT_ID('*objectName*', 'U') IS NOT NULL 

--------------------------

if OBJECT_ID('tempdb..#test') is not null

---------------------------

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable'))
BEGIN
    --Do Stuff
END

---------------------------

IF EXISTS(
          select * FROM sys.views where name = 'nView'
         )
   DROP VIEW nView;

----------------------------

IF OBJECT_ID('nView', 'V') IS NOT NULL
    DROP VIEW nView;

-----------------------------

select
    count(*)
from
    INFORMATION_SCHEMA.VIEWS
where
    table_name = 'nView'
    and table_schema = 'MySchema'

-----------------------------

prevent sharepoint list item to be modified after it was checked by manager

Settings:
1. Open list settings and create datetime field “LockDate” with params:

   Require that this column contains information = no
   Enforce unique values = no
   Date and Time Format = "Date and Time"
   Default value = no (none)
   Add to content type = yes
   Show in default view = no

2. Hide this field (“LockDate”) from new/disp/edit forms.
3. Goto list validation setting and set:

   formula = "OR(ISBLANK(LockDate);LockDate > NOW())"
   message = "Item Locked. In case of urgency please contact your manager." (or similar)

4. Create personal datasheet list view with “LockDate” field.

use newly created view for bulk item locking/unlocking by setting value of “LockDate” field:
1) Set datetime since when item should be locked (this date must be grater than your current system date). After that date users will not be able to modify item.
2) To unlock item just remove value from “LockDate” field.

njoy!

sql server – using of EXECUTE AS

When impersonating a principal by using the EXECUTE AS USER statement, or within a database-scoped module by using the EXECUTE AS clause, the scope of impersonation is restricted to the database by default. This means that references to objects outside the scope of the database will return an error.

pass SSRS reports names to SP choice field values

Param(
 [string]$ReportServerUri = 'http://sql-server/ReportServer/ReportService2005.asmx'
,[string]$ReportFolder = '/ReportFolderName'
,[string]$DestinationList = 'http://sp/sites/accounting/Lists/ListName/'
,[string]$DestinationField = 'ChoiceField'
,	[int]$DaysOffset = $(Throw "Specify 'DeysOffset' parameter plz...") # ex: 1,2,3 -- если найдем репорты, измененные за последние N дней, it means, чойсы нужно поменять.
)

function LFU ($list_url){
	$spSite = new-object Microsoft.SharePoint.SPSite($list_url);
	return $spSite.RootWeb.GetList($list_url)
	}

# init vars
	$choices = @();
	$maxdate = (get-date).Addyears(-2)
	
# get ssrs reports names and max modified date
	$proxy = New-WebServiceProxy -Uri $ReportServerUri -Namespace SSRS.ReportingService2005 -UseDefaultCredential -EA Stop
	$proxy.ListChildren($ReportFolder, $true) | ? {$_.type -eq 'Report'} | % { if ([datetime]$_.ModifiedDate -gt $maxdate) {$maxdate = [datetime]$_.ModifiedDate}; $choices += $_.name }
		
# if nothing was modified -> exit
	if ((get-date).AddDays(-1*$DaysOffset) -gt $maxdate) {Write-host $("DONE. SSRS folder '{0}' is empty OR reports within it were not modified within last {1} days." -f $ReportFolder,$DaysOffset); break}
	
# get sp field
	$field = (LFU $DestinationList).fields[$DestinationField]
	if (-not $field) {Throw "TERMINATED. Field '{0}' in list '{1}' NOT FOUND..." -f $DestinationField, $DestinationList}
	
# save new values
	$field.Choices.Clear()
	$field.Choices.AddRange($choices)
	$field.Update()
	
# done
	write-host $("DONE. Field '{0}' updated with {1} choices." -f $DestinationField, $choices.count.tostring())