Recent Updates Toggle Comment Threads | Keyboard Shortcuts
-
vdz8
-
vdz8
win cmd hibernate with timer
99 seconds timer before hibername:
ping -n 99 localhost > NUL 2>&1 && shutdown /h /f
-
vdz8
Error – Unable to Modify View: /_vti_bin/owssvr.dll?CS=65001
Solution is to deactivate “Minimal Download Strategy” site feature.
-
vdz8
drop all tables except one
EXEC sys.sp_MSforeachtable N'IF OBJECT_ID(''?'') <> OBJECT_ID(''dbo.YourTableToKeep'') DROP TABLE ? '
-
vdz8
-
vdz8
powershell add metadata termstore administrator
$taxonomySession = Get-SPTaxonomySession -Site "http://localhost" $termStore = $taxonomySession.TermStores["Managed Metadata Service"] $termStore.AddTermStoreAdministrator("domain\user") $termStore.CommitAll()
-
vdz8
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'
-
vdz8
sharepoint delete all audit entries
asnp *sh* $x = get-date; (get-spsite http://sp/sites/site).Audit.DeleteEntries($x)
-
vdz8
-
vdz8
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
-
vdz8
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 "--------------" }
-
vdz8
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() } } #> }
-
vdz8
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).
-
vdz8
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()
-
vdz8
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
-
vdz8
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} }
-
vdz8
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')
-
vdz8
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."}
-
vdz8
tsql get all members of specific server role
EXEC sp_helpsrvrolemember 'sysadmin'
-
vdz8
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
-
vdz8
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]
-
vdz8
Enable-PSRemoting
Enable-PSRemoting -Force
Set-Item wsman:\localhost\client\trustedhosts *
Restart-Service WinRM -
vdz8
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() }
-
vdz8
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 }
-
vdz8
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
-
vdz8
wifi virtual private network miniport
netsh wlan set hostednetwork mode=allow ssid="V" key="passssssss" keyUsage=persistent netsh wlan start hostednetwork
-
vdz8
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 $_)
-
vdz8
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
-
vdz8
t-sql generate random int value between @from @to
update [table] set [column] = FLOOR(RAND(convert(varbinary, newid()))*(@from-@to)+@from)
-
vdz8
Process through all the tables in a database or through all databases
use undocumented sp_MSforeachtable and sp_MSforeachdb
example -
vdz8
some usefull excel vb settings
With ActiveWindow .DisplayHorizontalScrollBar = False .DisplayHeadings = False .DisplayGridlines = False End With With Application .ExecuteExcel4Macro &quot;Show.ToolBar(&quot;&quot;Ribbon&quot;&quot;,False)&quot; .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
-
vdz8
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';
-
vdz8
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 } } } }
-
vdz8
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
-
vdz8
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
-
vdz8
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
-
vdz8
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
-
vdz8
RESTORE FILELISTONLY — get sql backup info
RESTORE FILELISTONLY FROM DISK = 'C:\DB\Backup\Milestones.bak'
-
vdz8
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. -
vdz8
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()
-
vdz8
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)}}
-
vdz8
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
-
vdz8
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
-
vdz8
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))
-
vdz8
stsadm forcedeletelist
"C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\BIN\stsadm.exe" -o forcedeletelist -url "http://sp/lists/History attachments"
-
vdz8
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}` }
-
vdz8
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' -----------------------------
-
vdz8
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!
-
vdz8
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.
-
vdz8
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())
Reply