Recent Updates Toggle Comment Threads | Keyboard Shortcuts

  • vdz8 15:26 on 23.09.2018 Permalink | Reply
    Tags: jquery,   

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

     
  • vdz8 15:07 on 23.09.2018 Permalink | Reply  

    win cmd hibernate with timer 

    99 seconds timer before hibername:

    ping -n 99 localhost > NUL 2>&1 && shutdown /h /f
     
  • vdz8 17:29 on 07.12.2015 Permalink | Reply  

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

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

     
  • vdz8 16:21 on 22.11.2015 Permalink | Reply  

    drop all tables except one 

    EXEC sys.sp_MSforeachtable 
           N'IF OBJECT_ID(''?'') <> OBJECT_ID(''dbo.YourTableToKeep'')
                 DROP TABLE ?
           '
    
     
  • vdz8 23:00 on 29.10.2015 Permalink | Reply  

    SharePoint 2013 update instructions 

    technet

     
  • vdz8 15:45 on 29.10.2015 Permalink | Reply  

    powershell add metadata termstore administrator 

    $taxonomySession = Get-SPTaxonomySession -Site "http://localhost"
    $termStore = $taxonomySession.TermStores["Managed Metadata Service"]
    $termStore.AddTermStoreAdministrator("domain\user")
    $termStore.CommitAll()
    
     
  • vdz8 15:34 on 15.10.2015 Permalink | Reply  

    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 08:34 on 07.10.2015 Permalink | Reply  

    sharepoint delete all audit entries 

    asnp *sh*
    $x = get-date; (get-spsite http://sp/sites/site).Audit.DeleteEntries($x)
    
     
  • vdz8 12:40 on 29.09.2015 Permalink | Reply  

    taxonomy updater 

    force taxonomy list update (C#/powershell)

     
  • vdz8 12:33 on 25.09.2015 Permalink | Reply  

    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 11:46 on 21.09.2015 Permalink | Reply  

    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 10:28 on 16.09.2015 Permalink | Reply  

    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 10:03 on 12.08.2015 Permalink | Reply  

    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 17:11 on 11.08.2015 Permalink | Reply  

    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 07:30 on 08.07.2015 Permalink | Reply  

    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 15:00 on 07.07.2015 Permalink | Reply  

    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 13:10 on 30.06.2015 Permalink | Reply  

    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 21:03 on 18.06.2015 Permalink | Reply  

    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 14:43 on 11.06.2015 Permalink | Reply  

    tsql get all members of specific server role 

    EXEC sp_helpsrvrolemember 'sysadmin'
    
     
  • vdz8 12:34 on 10.06.2015 Permalink | Reply  

    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 14:00 on 02.06.2015 Permalink | Reply  

    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 16:39 on 29.05.2015 Permalink | Reply  

    Enable-PSRemoting 

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

     
  • vdz8 10:43 on 12.05.2015 Permalink | Reply  

    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 10:36 on 12.05.2015 Permalink | Reply  

    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 08:39 on 06.05.2015 Permalink | Reply  

    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 17:49 on 27.03.2015 Permalink | Reply  

    wifi virtual private network miniport 

    netsh wlan set hostednetwork mode=allow ssid="V" key="passssssss" keyUsage=persistent
    netsh wlan start hostednetwork
    
     
  • vdz8 17:16 on 26.03.2015 Permalink | Reply  

    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 15:25 on 24.03.2015 Permalink | Reply  

    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 16:11 on 03.03.2015 Permalink | Reply  

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

    update [table] set [column] =
    FLOOR(RAND(convert(varbinary, newid()))*(@from-@to)+@from)
    
     
  • vdz8 13:17 on 03.03.2015 Permalink | Reply  

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

    use undocumented sp_MSforeachtable and sp_MSforeachdb
    example

     
  • vdz8 12:19 on 03.03.2015 Permalink | Reply  

    some usefull excel vb settings 

    With ActiveWindow
        .DisplayHorizontalScrollBar = False
        .DisplayHeadings = False
        .DisplayGridlines = False
    End With
        
    With Application
          .ExecuteExcel4Macro &amp;quot;Show.ToolBar(&amp;quot;&amp;quot;Ribbon&amp;quot;&amp;quot;,False)&amp;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 11:52 on 03.03.2015 Permalink | Reply  

    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 09:56 on 02.03.2015 Permalink | Reply  

    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 09:52 on 02.03.2015 Permalink | Reply  

    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 10:41 on 20.02.2015 Permalink | Reply  

    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 13:09 on 31.12.2014 Permalink | Reply  

    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 09:13 on 22.12.2014 Permalink | Reply  

    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 16:41 on 19.12.2014 Permalink | Reply  

    RESTORE FILELISTONLY — get sql backup info 

    RESTORE FILELISTONLY 
    FROM DISK = 'C:\DB\Backup\Milestones.bak'
    
     
  • vdz8 13:04 on 12.12.2014 Permalink | Reply  

    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 17:49 on 10.12.2014 Permalink | Reply  

    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 16:20 on 10.12.2014 Permalink | Reply  

    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 11:33 on 08.12.2014 Permalink | Reply  

    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 13:48 on 04.12.2014 Permalink | Reply  

    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 16:55 on 20.11.2014 Permalink | Reply  

    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 16:57 on 17.11.2014 Permalink | Reply  

    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 16:54 on 17.11.2014 Permalink | Reply  

    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 15:09 on 10.11.2014 Permalink | Reply  

    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 16:21 on 06.11.2014 Permalink | Reply  

    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 20:10 on 05.11.2014 Permalink | Reply  

    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 09:42 on 04.11.2014 Permalink | Reply  

    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())
    
     
c
Compose new post
j
Next post/Next comment
k
Previous post/Previous comment
r
Reply
e
Edit
o
Show/Hide comments
t
Go to top
l
Go to login
h
Show/Hide help
shift + esc
Cancel