Igor Feldman Sharepoint WIKIpedia

Please login or register.

Login with username, password and session length
Advanced search  

News:

SMF - Just Installed!

Show Posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.

Messages - Administrator

Pages: [1]
1
Sharepoint Usefull stuff / SQL Queries for SharePoint Content Database
« on: March 09, 2018, 08:31:12 PM »
https://demantprasad.wordpress.com/category/useful-sql-queries-for-sharepoint-content-database/


Some of the common queries that can be used against the content database:

–Returns Total Number of Site Collections in WebApplication

select  count(*) as ‘Total Site Collection’ from sites

–Returns Root Site Title for each Site Collection available in WebApplication

 select Title as ‘Root Web Title’, Sites.RootWebId, Sites.Id as ‘Site Collection ID’ from webs

 inner join Sites on Webs.Id = Sites.RootWebId

 –Returns Total Web Sites in WebApplication

select count(*) from Webs

–Returns WebSite Title and Site Id

select Title as ‘Site title’,FullUrl, SiteId as ‘Site Collection Id’ from Webs order by SiteId

–Returns Total number of Web Sites under each SiteCollection

select SiteId, count(*) as ‘Total Sub Sites’ from Webs inner join Sites on Sites.Id = Webs.SiteId group by SiteId

–Returns Total number of Web Sites under each SiteCollection for ‘Doc Lib’ and ‘Form Lib’

SELECT

“Template Type” = CASE

WHEN [Lists].[tp_ServerTemplate] = 101 THEN ‘Doc Lib’

WHEN [Lists].[tp_ServerTemplate] = 115 THEN ‘Form Lib’

ELSE ‘Unknown’

END,

“List URL” = ‘http://mlaw/’ + CASE

WHEN [Webs].[FullUrl]=”

THEN [Webs].[FullUrl] + [Lists].[tp_Title]

ELSE [Webs].[FullUrl] + ‘/’ + [Lists].[tp_Title]

END,

“Template URL” = ‘http://mlaw/’ +

[Docs].[DirName] + ‘/’ + [Docs].[LeafName]

FROM [Lists] LEFT OUTER JOIN [Docs] ON [Lists].[tp_Template]=[Docs].[Id], [Webs]

WHERE ([Lists].[tp_ServerTemplate] = 101 OR [Lists].[tp_ServerTemplate] = 115)

AND [Lists].[tp_WebId]=[Webs].[Id]

order by “List URL”

–Returns Total number of Web Sites under each SiteCollection for ‘Doc Lib’ Only

SELECT

“Template Type” = CASE

WHEN [Lists].[tp_ServerTemplate] = 101 THEN ‘Doc Lib’

WHEN [Lists].[tp_ServerTemplate] = 115 THEN ‘Form Lib’

ELSE ‘Unknown’

END,

“List URL” = ‘http://mlaw/’ + CASE

WHEN [Webs].[FullUrl]=”

THEN [Webs].[FullUrl] + [Lists].[tp_Title]

ELSE [Webs].[FullUrl] + ‘/’ + [Lists].[tp_Title]

END,

“Template URL” = ‘http://mlaw/’ +

[Docs].[DirName] + ‘/’ + [Docs].[LeafName]

FROM [Lists] LEFT OUTER JOIN [Docs] ON [Lists].[tp_Template]=[Docs].[Id], [Webs]

WHERE ([Lists].[tp_ServerTemplate] = 101)

AND [Lists].[tp_WebId]=[Webs].[Id]

order by “List URL”

–query to get count of documents from site collection for 2010

select

SUM(itemcount) As [Total Item]

from lists inner join webs ON lists.tp_webid = webs.Id

inner join AllListsAux ON Lists.tp_ID = AllListsAux.ListID

Where tp_servertemplate = 101

select

case when webs.fullurl = ”

then ‘Portal Site’

else webs.fullurl

end as [Site Relative Url],

webs.Title As [Site Title],

lists.tp_title As Title,

tp_description As Description,

itemcount As [Total Item]

from lists inner join webs ON lists.tp_webid = webs.Id

inner join AllListsAux ON Lists.tp_ID = AllListsAux.ListID

Wheretp_servertemplate = 101

Order By [Site Relative Url]

for 2007

SELECT CASE WHEN webs.fullurl = ”

THEN ‘Portal Site’

ELSE webs.fullurl

END AS [Site Relative Url],

Webs.Title AS [Site Title],

CASEtp_servertemplate

WHEN 104 THEN ‘Announcement’

WHEN 105 THEN ‘Contacts’

WHEN 108 THEN ‘Discussion Boards’

WHEN 101 THEN ‘Docuemnt Library’

WHEN 106 THEN ‘Events’

WHEN 100 THEN ‘Generic List’

WHEN 1100 THEN ‘Issue List’

WHEN 103 THEN ‘Links List’

WHEN 109 THEN ‘Image Library’

WHEN 115 THEN ‘InfoPath Form Library’

WHEN 102 THEN ‘Survey’

WHEN 107 THEN ‘Task List’

ELSE ‘Other’ END AS TYPE,

tp_title ‘Title’,

tp_description AS [Description],

tp_itemcount AS [Total Item]

FROM lists inner join webs ON lists.tp_webid = webs.Id

WHEREtp_servertemplate IN (104,105,108,101,

      106,100,1100,103,109,115,102,107,120)

ORDER BY tp_itemcount DESC

— Query to get all the top level site collections
SELECT SiteId AS SiteGuid, Id AS WebGuid, FullUrl AS Url, Title, Author, TimeCreated
FROM dbo.Webs
WHERE (ParentWebId IS NULL)

— Query to get all the child sites in a site collection
SELECT SiteId AS SiteGuid, Id AS WebGuid, FullUrl AS Url, Title, Author, TimeCreated
FROM dbo.Webs
WHERE (NOT (ParentWebId IS NULL))

— Query to get all the SharePoint groups in a site collection
SELECT dbo.Webs.SiteId, dbo.Webs.Id, dbo.Webs.FullUrl, dbo.Webs.Title, dbo.Groups.ID AS Expr1,
dbo.Groups.Title AS Expr2, dbo.Groups.Description
FROM dbo.Groups INNER JOIN
dbo.Webs ON dbo.Groups.SiteId = dbo.Webs.SiteId

— Query to get all the users in a site collection
SELECT dbo.Webs.SiteId, dbo.Webs.Id, dbo.Webs.FullUrl, dbo.Webs.Title, dbo.UserInfo.tp_ID,
dbo.UserInfo.tp_DomainGroup, dbo.UserInfo.tp_SiteAdmin, dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Email
FROM dbo.UserInfo INNER JOIN
dbo.Webs ON dbo.UserInfo.tp_SiteID = dbo.Webs.SiteId

— Query to get all the members of the SharePoint Groups
SELECT dbo.Groups.ID, dbo.Groups.Title, dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Login
FROM dbo.GroupMembership INNER JOIN
dbo.Groups ON dbo.GroupMembership.SiteId = dbo.Groups.SiteId INNER JOIN
dbo.UserInfo ON dbo.GroupMembership.MemberId = dbo.UserInfo.tp_ID

— Query to get all the sites where a specific feature is activated
SELECT dbo.Webs.Id AS WebGuid, dbo.Webs.Title AS WebTitle, dbo.Webs.FullUrl AS WebUrl, dbo.Features.FeatureId,
dbo.Features.TimeActivated
FROM dbo.Features INNER JOIN
dbo.Webs ON dbo.Features.SiteId = dbo.Webs.SiteId AND dbo.Features.WebId = dbo.Webs.Id
WHERE (dbo.Features.FeatureId = ’00BFEA71-D1CE-42de-9C63-A44004CE0104′)

— Query to get all the users assigned to roles
SELECT dbo.Webs.Id, dbo.Webs.Title, dbo.Webs.FullUrl, dbo.Roles.RoleId, dbo.Roles.Title AS RoleTitle,
dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Login
FROM dbo.RoleAssignment INNER JOIN
dbo.Roles ON dbo.RoleAssignment.SiteId = dbo.Roles.SiteId AND
dbo.RoleAssignment.RoleId = dbo.Roles.RoleId INNER JOIN
dbo.Webs ON dbo.Roles.SiteId = dbo.Webs.SiteId AND dbo.Roles.WebId = dbo.Webs.Id INNER JOIN
dbo.UserInfo ON dbo.RoleAssignment.PrincipalId = dbo.UserInfo.tp_ID

— Query to get all the SharePoint groups assigned to roles
SELECT dbo.Webs.Id, dbo.Webs.Title, dbo.Webs.FullUrl, dbo.Roles.RoleId, dbo.Roles.Title AS RoleTitle,
dbo.Groups.Title AS GroupName
FROM dbo.RoleAssignment INNER JOIN
dbo.Roles ON dbo.RoleAssignment.SiteId = dbo.Roles.SiteId AND
dbo.RoleAssignment.RoleId = dbo.Roles.RoleId INNER JOIN
dbo.Webs ON dbo.Roles.SiteId = dbo.Webs.SiteId AND dbo.Roles.WebId = dbo.Webs.Id INNER JOIN
dbo.Groups ON dbo.RoleAssignment.SiteId = dbo.Groups.SiteId AND
dbo.RoleAssignment.PrincipalId = dbo.Groups.ID

====================================================================

— Query to get all the users assigned to roles

SELECT DISTINCT

CASE WHEN PATINDEX(‘%\%’, FullUrl) > 0 THEN LEFT(FullUrl, PATINDEX(‘%\%’, FullUrl) – 1) ELSE FullUrl END AS [Site],

Webs.Title,

Webs.FullUrl,

Perms.ScopeUrl,

UserInfo.tp_Login As Account,

CASE WHEN UserInfo.tp_DomainGroup>0 THEN NULL ELSE UserInfo.tp_Title END AS Username,

CASE WHEN UserInfo.tp_DomainGroup>0 THEN UserInfo.tp_Login ELSE NULL END AS [AD Group],

NULL AS [SharePoint Group],

Roles.Title AS RoleTitle,

Roles.PermMask

FROM

dbo.RoleAssignment

INNER JOIN dbo.UserInfo ON RoleAssignment.SiteId = UserInfo.tp_SiteID AND UserInfo.tp_ID = RoleAssignment.PrincipalId

INNER JOIN dbo.Perms ON Perms.SiteId = RoleAssignment.SiteId AND Perms.ScopeId = RoleAssignment.ScopeId

INNER JOIN dbo.Roles ON RoleAssignment.SiteId = Roles.SiteId AND RoleAssignment.RoleId = Roles.RoleId

INNER JOIN dbo.Webs ON Roles.SiteId = Webs.SiteId AND Roles.WebId = Webs.Id

WHERE

Roles.Type1 ANDtp_Deleted=0

UNION

=============================================================================

— Query to get all the SharePoint groups assigned to roles

SELECT DISTINCT

CASE WHEN PATINDEX(‘%\%’, FullUrl) > 0 THEN LEFT(FullUrl, PATINDEX(‘%\%’, FullUrl) – 1) ELSE FullUrl END AS [Site],

Webs.Title,

Webs.FullUrl,

Perms.ScopeUrl,

UserInfo.tp_Login As Account,

CASE WHEN UserInfo.tp_DomainGroup>0 THEN NULL ELSE UserInfo.tp_Title END AS Username,

CASE WHEN UserInfo.tp_DomainGroup>0 THEN UserInfo.tp_Login ELSE NULL END AS [AD Group],

Groups.Title AS [SharePoint Group],

Roles.Title AS RoleTitle,

Roles.PermMask

FROM

dbo.RoleAssignment

INNER JOIN dbo.Roles ON RoleAssignment.SiteId = Roles.SiteId AND RoleAssignment.RoleId = Roles.RoleId

INNER JOIN dbo.Perms ON Perms.SiteId = RoleAssignment.SiteId AND Perms.ScopeId = RoleAssignment.ScopeId

INNER JOIN dbo.Webs ON Roles.SiteId = Webs.SiteId AND Roles.WebId = Webs.Id

INNER JOIN dbo.Groups ON RoleAssignment.SiteId = Groups.SiteId AND RoleAssignment.PrincipalId = Groups.ID

INNER JOIN dbo.GroupMembership ON GroupMembership.SiteId = Groups.SiteId AND GroupMembership.GroupId = Groups.ID

INNER JOIN dbo.UserInfo ON GroupMembership.SiteId = UserInfo.tp_SiteID AND GroupMembership.MemberId = UserInfo.tp_ID

WHERE

Roles.Type1 AND tp_Deleted=0

===============================================================

— Top 100 Documents that is versioned based on doc size

SELECT TOP 100

Webs.FullUrl As SiteUrl,

Webs.Title ‘Document/List Library Title’,

DirName + ‘/’ + LeafName AS ‘Document Name’,

COUNT(Docversions.UIVersion)AS ‘Total Version’,

SUM(CAST((CAST(CAST(Docversions.Size as decimal(10,2))/1024 As

   decimal(10,2))/1024) AS Decimal(10,2)) )  AS  ‘Total Document Size (MB)’,

CAST((CAST(CAST(AVG(Docversions.Size) as decimal(10,2))/1024 As

   decimal(10,2))/1024) AS Decimal(10,2))   AS  ‘Avg Document Size (MB)’

FROM Docs INNER JOINDocVersions ON Docs.Id = DocVersions.Id

   INNER JOIN Webs On Docs.WebId = Webs.Id

INNER JOIN Sites ON Webs.SiteId = SItes.Id

WHERE

Docs.Type  1

AND(LeafName NOT LIKE ‘%.stp’) 

AND(LeafName NOT LIKE ‘%.aspx’) 

AND(LeafName NOT LIKE ‘%.xfp’)

AND(LeafName NOT LIKE ‘%.dwp’)

AND(LeafName NOT LIKE ‘%template%’)

AND(LeafName NOT LIKE ‘%.inf’)

AND(LeafName NOT LIKE ‘%.css’)

GROUP BY Webs.FullUrl, Webs.Title, DirName + ‘/’ + LeafName

ORDER BY ‘Total Version’ desc, ‘Total Document Size (MB)’ desc

Note: When the document is in “Draft” state, if you use the above query you will be getting the count of versions in double. Ex:if total version count is 3, when the doc is in Draft state if you use the above query the count will return as 6.


============================================================================

Retrieve the file details from AllDocs Database. Which has the information about the files stored in SharePoint List or Library.
– Returns all document from all lists availabe in WebApplication
SELECT AllDocs.Leafname AS FileName’,
                 AllDOcs.Dirname AS ‘Folder Path’,
                 AllLists.tp_Title AS ‘List Title’,
                 Webs.Title AS ‘Web Title’
FROM AllDocs
JOIN AllLists
ON
AllLists.tp_Id=AllDocs.ListId
JOIN Webs
ON
Webs.Id=AllLists.tp_WebId
ORDER BY webs.title

If you need the file informations about particular document type. Use the Extension column to check the document type.
For Ex., The following Query returns only master pages on all WebSites,
– Returns master pages in WebApplication for all WebSites
SELECT AllDocs.Leafname AS FileName’,
                 AllDocs.Dirname AS ‘Folder Path’,
                 AllLists.tp_Title AS ‘List Title’,
                 Webs.Title AS ‘Web Title’
FROM AllDocs
JOIN AllLists
ON
AllLists.tp_Id=AllDocs.ListId
JOIN Webs
ON
Webs.Id=AllLists.tp_WebId
WHERE Extension=’master’
ORDER BY Webs.Title

=========================================================================

simple SQL Query to retrieve the Document List Name,File Name, URL, and the Content (Binary Format)

 SELECT AllLists.tp_Title AS ‘List Name’,
AllDocs.LeafName AS ‘File Name’,
AllDocs.DirName AS ‘URL’,
AllDocStreams.Content AS ‘Document Contnt (Binary)’
FROM AllDocs
JOIN AllDocStreams
ON
AllDocs.Id=AllDocStreams.Id
JOIN AllLists
ON
AllLists.tp_id = AllDocs.ListId

==================================================================================

Get Documents By Age
SELECT Webs.FullUrl AS SiteUrl, Webs.Title AS [Title], DirName + ‘/’ + LeafName AS [Document Name], Docs.TimeCreated
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = Sites.Id
WHERE Docs.Type  1
AND (LeafName IS NOT NULL)
AND (LeafName  ”)
AND (LeafName NOT LIKE ‘%.stp’)
AND (LeafName NOT LIKE ‘%.aspx’)
AND (LeafName NOT LIKE ‘%.xfp’)
AND (LeafName NOT LIKE ‘%.dwp’)
AND (LeafName NOT LIKE ‘%template%’)
AND (LeafName NOT LIKE ‘%.inf’)
AND (LeafName NOT LIKE ‘%.css’)

ORDER BY Docs.TimeCreated DESC

==============================================================
Total # of Documents
SELECT COUNT(*)
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = Sites.Id
WHERE Docs.Type  1
AND (LeafName IS NOT NULL)
AND (LeafName  ”)
AND (LeafName NOT LIKE ‘%.stp’)
AND (LeafName NOT LIKE ‘%.aspx’)
AND (LeafName NOT LIKE ‘%.xfp’)
AND (LeafName NOT LIKE ‘%.dwp’)
AND (LeafName NOT LIKE ‘%template%’)
AND (LeafName NOT LIKE ‘%.inf’)
AND (LeafName NOT LIKE ‘%.css’)

 

================================================================
Total Size of All Content
SELECT SUM(CAST((CAST(CAST(Size AS DECIMAL(10,2))/1024 AS DECIMAL(10,2))/1024) AS DECIMAL(10,2))) AS ‘Total Size in MB’
FROM Docs INNER JOIN Webs ON Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = Sites.Id
WHERE Docs.Type  1
AND (LeafName IS NOT NULL)
AND (LeafName NOT LIKE ‘%.stp’)
AND (LeafName NOT LIKE ‘%.aspx’)
AND (LeafName NOT LIKE ‘%.xfp’)
AND (LeafName NOT LIKE ‘%.dwp’)
AND (LeafName NOT LIKE ‘%template%’)
AND (LeafName NOT LIKE ‘%.inf’)

AND (LeafName NOT LIKE ‘%.css’)

====================================================================
Get Documents by Size
SELECT TOP 100 Webs.FullUrl AS SiteUrl, Webs.Title AS [Title], DirName + ‘/’ + LeafName AS [Document Name], CAST((CAST(CAST(Size AS DECIMAL(10,2))/1024 AS DECIMAL(10,2))/1024) AS DECIMAL(10,2)) AS ‘Size in MB’
FROM Docs INNER JOIN Webs ON Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = Sites.Id
WHERE Docs.Type  1
AND (LeafName IS NOT NULL)
AND (LeafName  ”)
AND (LeafName NOT LIKE ‘%.stp’)
AND (LeafName NOT LIKE ‘%.aspx’)
AND (LeafName NOT LIKE ‘%.xfp’)
AND (LeafName NOT LIKE ‘%.dwp’)
AND (LeafName NOT LIKE ‘%template%’)
AND (LeafName NOT LIKE ‘%.inf’)
AND (LeafName NOT LIKE ‘%.css’)

ORDER BY ‘Size in MB’ DESC

===========================================================
Totals for each type of Document (DOCX/XLSX/PPTX/HTML)
If you want to check for others just reuse a select statement below with the additional file extension you want to track.

SELECT COUNT(*) AS “# of .DOCX”
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = Sites.Id
WHERE Docs.Type  1
AND (LeafName LIKE ‘%.docx’)
AND (LeafName NOT LIKE ‘%template%’)

 

SELECT COUNT(*) AS “# of .PPTX”
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = Sites.Id
WHERE Docs.Type  1
AND (LeafName LIKE ‘%.pptx’)
AND (LeafName NOT LIKE ‘%template%’)

 

SELECT COUNT(*) AS “# of .XLSX”
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = Sites.Id
WHERE Docs.Type  1
AND (LeafName LIKE ‘%.xlsx’)
AND (LeafName NOT LIKE ‘%template%’)

 

SELECT COUNT(*) AS “# of .HTML”
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = Sites.Id
WHERE Docs.Type  1
AND (LeafName LIKE ‘%.html’)

AND (LeafName NOT LIKE ‘%template%’) -r

2
SharePoint 2016 Usefull stuff / People Picker not searching email address
« on: December 08, 2017, 09:34:13 PM »
DC ROOT CASE

https://blogs.technet.microsoft.com/arudell/2013/08/19/sharepoint-2013-people-picker-changes/

There is a way to set a custom people picker AD query using (believe it or not) stsadm.  Yes, it still exists, and there is no powershell equivalent for setting the people picker properties.
See http://technet.microsoft.com/en-us/library/gg602075.aspx for the ways to customize the People Picker, and http://technet.microsoft.com/en-us/library/911b5f5a-bd1d-42fd-b816-8cd5a770b4b9(office.12).aspx on setting the customadquery property specifically.  Make sure you specify the web application URL.  Here is an example I used to get the AD "mail" property to return results (as well as the last name).
stsadm -o setproperty -pn peoplepicker-searchadcustomquery -pv "(sn={0}*)(mail={0}*)" -url http://servername
I had to do an iisreset.  And I noticed that searching on the first name still worked.

3
SharePoint 2016 Usefull stuff / How to get SID from User name
« on: October 26, 2017, 09:16:10 PM »
In Windows environment, each user is assigned a unique identifier called Security ID or SID, which is used to control access to various resources like Files, Registry keys, network shares etc. We can obtain SID of a user through WMIC USERACCOUNT command. Below you can find syntax and examples for the same.

Get SID of a local user

wmic useraccount where name='username' get sid
For example, to get the SID for a local user with the login name  ‘John’, the command would be as below
wmic useraccount where name='John' get sid
Get SID for current logged in user

To retrieve the SID for current logged in user we can run the below command. This does not require you to specify the user name in the command. This can be used in batch files which may be executed from different user accounts.

wmic useraccount where name='%username%' get sid
Get SID for current logged in domain user

Run the command ‘whoami /user’ from command line to get the SID for the logged in user.
Example:

c:\>whoami /user
USER INFORMATION
----------------
User Name      SID
============== ==============================================
mydomain\wincmd S-1-5-21-7375663-6890924511-1272660413-2944159
c:\>

Get SID for the local administrator of the computer

wmic useraccount where (name='administrator' and domain='%computername%') get name,sid
Get SID for the domain administrator

wmic useraccount where (name='administrator' and domain='%userdomain%') get name,sid
Find username from a SID
Now this is tip is to find the user account when you have a SID. One of the readers of this post had this usecase and he figured out the command himself with the help of the commands given above. Adding the same here.

wmic useraccount where sid='S-1-3-12-1234525106-3567804255-30012867-1437' get name

4
SharePoint 2016 Usefull stuff / How to query list at SP2016
« on: October 26, 2017, 02:22:50 PM »
……./_api/web/lists/getbytitle(‘…’)/items

5
SharePoint 2016 Usefull stuff / Install-SPSolution
« on: October 26, 2017, 02:18:58 PM »
Detailed Description
This cmdlet contains more than one parameter set. You may only use parameters from one parameter set, and you may not combine parameters from different parameter sets. For more information about how to use parameter sets, see Cmdlet Parameter Sets.
The Install-SPSolution cmdlet deploys an installed SharePoint solution in the farm. Use the Add-SPSolution cmdlet to install a SharePoint solution package in the farm.
SharePoint Management Shell
The following table lists the valid values for the CompatabilityLevel parameter:
 
Value
Result
14
Installs solution to 14 directories only
15
Installs solution to 15 directories only
“14,15”
Installs solution to both 14 and 15 directories
“AllVersions” or “All”
Installs solution to both 14 and 15 directories
“OldVersions” or “Old”
Installs solution to 14 directories only
“NewVersion” or “New”
Installs solution to 15 directories only
Parameters
 
Parameter   Required   Type   Description
Identity
Required
Microsoft.SharePoint.PowerShell.SPSolutionPipeBind
Specifies the SharePoint solution to deploy.
The value must be an authentic GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; an authentic name of a SharePoint solution (for example, SPSolution1); or an instance of an authentic SPSolution object.
AllWebApplications
Optional
System.Management.Automation.SwitchParameter
Specifies that the new SharePoint solution is deployed for all SharePoint web applications in the farm.
AssignmentCollection
Optional
Microsoft.SharePoint.PowerShell.SPAssignmentCollection
Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.
NoteNote:
When the Global parameter is used, all objects are contained in the global store. If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.
CASPolicies
Optional
System.Management.Automation.SwitchParameter
Specifies that code access security (CAS) policies can be deployed for the new SharePoint solution.
CompatibilityLevel
Optional
System.String
Specifies whether to install into the solution, into a specific version directory based on CompatibilityLevel. The default behavior if this parameter is not specified is to install the solution only to the version directory based on the version tracked in the manifest of the solution's cab file. For the list of values, see the table in the Detailed Description section.
Confirm
Optional
System.Management.Automation.SwitchParameter
Prompts you for confirmation before executing the command. For more information, type the following command: get-help about_commonparameters
Force
Optional
System.Management.Automation.SwitchParameter
Forces the deployment of the new SharePoint solution.
FullTrustBinDeployment
Optional
System.Management.Automation.SwitchParameter
Specifies that full trust Bin deployment is permitted. This parameter is to be used if the solution is fully trusted.
Bin assembly is an assembly installed into the bin directory of the virtual server. The assembly in the package will have DeploymentTarget=WebApplication attribute set. For additional information about bin assembly, see Assembly Element
GACDeployment
Optional
System.Management.Automation.SwitchParameter
Specifies that global assembly cache (GAC) can be deployed for the new SharePoint solution.
Language
Optional
System.UInt32
Specifies a language for the solution when a solution language package is deployed. If this parameter is not specified, zero ("0") is assumed. Use zero for solutions that are valid for all languages.
Local
Optional
System.Management.Automation.SwitchParameter
Deploys the solution on the active server.
Synchronize
Optional
System.Management.Automation.SwitchParameter
Synchronizes all solutions or the specified solution in the local farm.
Time
Optional
System.String
Specifies when the solution will be deployed. The default value is immediate deployment.
The type must be a valid DateTime value, in the form 2010, 5, 1.
WebApplication
Optional
Microsoft.SharePoint.PowerShell.SPWebApplicationPipeBind
Deploys the SharePoint solution for the specified SharePoint web application.
The value must be in one of the following forms:
An authentic GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh
An authentic name of a SharePoint web application (for example, MyOfficeApp1)
An instance of an authentic SPWebApplication object.
WhatIf
Optional
System.Management.Automation.SwitchParameter
Displays a message that describes the effect of the command instead of executing the command. For more information, type the following command: get-help about_commonparameters
Input Types
Return Types
Example
------------------EXAMPLE 1------------------
Install-SPSolution -Identity contoso_solution.wsp -GACDeployment
This example deploys the installed SharePoint solution contoso_solution.wsp in the farm and specifies that GAC can be deployed for the new SharePoint solution.
------------------EXAMPLE 2------------------
Install-SPSolution -Identity contoso_solution.wsp -GACDeployment -CompatibilityLevel 15
This example deploys the installed SharePoint solution contoso_solution.wsp in the farm within the latest version directories and specifies that global assembly cache (GAC) can be deployed for the new SharePoint solution.
------------------EXAMPLE 3------------------
Install-SPSolution -Identity contoso_solution.wsp -GACDeployment -CompatibilityLevel {14,15}
This example deploys the installed SharePoint solution installs a previously added solution so it can be used correctly in both 14 and 15 mode site collections.

6
Sharepoint Usefull stuff / SQL Alias - Creating and Testing the connection
« on: November 05, 2015, 11:29:44 PM »

Testing the alias and database connection - an awesome little trick!

Many folks use some 3rd party tools or some custom scripts to test the connection but many don’t even test the connection assuming that everything is ok. And when the time for the implementation come… bad things may happen. :)

So it is always good to test and double check the configuration before proceed any further!

This method for testing is very helpful because it takes less than a half minute to understand that something is not working and the good thing is that this is an out of the box tool in Windows.

Just navigate to any folder you like and create an empty text file. You can name it "TestSqlConnection" for example. Rename the file extension from txt to UDL and voila…

7
In order to update the SharePoint databases, you must manually run the PSconfig utility. To run the utility:

1. Open an Administrative command prompt.
2. Change directory to C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN
3. Run PSConfig.exe -cmd upgrade -inplace b2b -force -cmd applicationcontent -install -cmd installfeatures

Note: The Companyweb site will be inaccessible while the command is running. It is best to run the command after business hours. The amount of time the command takes to run will vary on the size of the database and the speed of the machine. On a reference machine with 8 logical processors, 32GB of RAM and a 2GB content database, the command took approximately 5 minutes to execute.

8
The Magic of Get-SPProduct -Local
Many of us have run into that situation where you’ve installed an update, started the Config Wizard, and you get a message that such-and-such server doesn’t have these updates. But they’re installed, because you just installed them, and SharePoint patches often won’t let you reapply them as they “do not apply” to your system. So what do you do? Run Get-SPProduct -Local on the server “missing” the updates! And then you’re able to run the Config Wizard. But why would a cmdlet with a Get verb fix something? Let’s take a look…

First, Get-SPProduct enumerates the installed products on a SharePoint server. It does this by examining the registry key  HKLM\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\1x.0\WSS\InstalledProducts. It looks at the DisplayName key for each product (product being represented by the key in the form of a GUID), and from there looks at the RequiredOnAllServers key, marking it as required if the key value is “1”. Next, it takes the DisplayName value or registry key values from HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall  and passes it through the Windows Installer service, calling MsiOpenProduct and MsiGetProperty to get the property of the ProductName, ProductVersion, and RequiredOnAllServers property.

Next, SharePoint continues looking at the MSI data, attempting to detect MSI patches using MsiGetPatchInfoEx. From here, if it finds patches, it uses MsiOpenDatabase to open the MSI database (this is why the Product Versions timer job fails if the Farm Administrator does not have Local Administrator rights) and runs a couple of queries. It also makes version comparisons between patches at this point. Finally, it places all detected products into a collection for use later.

The next step is to detect the upgrade status of the server, returning the status UpgradeRequired, UpgradeAvailabile, UpgradeInProgress, InstallRequired, UpgradeBlocked, or NoActionRequired.

Now we get into the part where the Get verb no longer makes sense. A T-SQL query is built using the proc_RegisterProductVersion stored procedure into one large SQL statement containing all detected products (named “BuiltProductsString” for reference below). For each product, this string contains the product name, GUID, Display Name, and if applicable, the patch GUID, KB article link, and the patch friendly display name. Once the string has been built, the process executes a stored procedure within the SharePoint Configuration database:


1
exec proc_ClearProductVersions '" + [Server_Guid] + "'\n" + [BuiltProductsString])
When the stored procedure completes, the result of the installed products is written out to the SharePoint Management Shell.

Another thing to note is that the process is the same for Get-SPProduct, the Product Version job timer job, joining a farm via Config Wizard or psconfig, and upgrading a SharePoint server via Config Wizard or psconfig.

Pages: [1]