Metadata Management - Crawler Query - To get data bases info from a list of servers

To fetch list of databases in a server usually we use EXEC sp_helpdb, but if your IT infrastructure is scaled to multiple servers we need to collect this information from all the serves in given IT landscape. 

Usually in metadata management you capture this information in to a table to do your data analysis and reporting, you either create "Central Management Servers" to run your query at a single go in all these servers or create an SSIS data flow task using dynamic source connection manager to connect all these serves and pull information into your metadata tables.

In both cases a plain EXEC sp_helpdb might not help, you get the results in different windows for each server.

Use below piece of code to get the results in a single plain window or place this in your SSIS OLEDB Source - SQL Command option to pull and push this information straight into your underlying tables:

SET NOCOUNT ON

DECLARE @AllTables TABLE (

Name sysname,
db_size sysname,
owner sysname,
dbid sysname,
created sysname,
status nvarchar(max),
compatibility_level sysname)     

INSERT INTO @AllTables (Name,

db_size,
owner,
dbid,
created,
status,
compatibility_level)

EXEC sp_helpdb

SET NOCOUNT OFF

SELECT @@servername as ServerName, Name AS DataBaseName, 'UserDefined' AS DataBaseType, db_size AS DataBaseSize FROM @AllTables
WHERE dbid > 4

I am filtering out the results (skipping master tables and few database attributes) based on my need you can play around with them accordingly.

Happy Learning.. :)

Sign In as different user is missing in SharePoint 2013

Solution:
I don't have proper solution for this..
but i will provide workaround for this..
Start Internet Explorer by using the Run as different user option, and then go to the SharePoint site.
Note: The Run as different user option is visible if you hold the Shift key when you right-click a program icon.
Access the SharePoint site by using the the URL as http://siteurl/_layouts/closeConnection.aspx?loginasanotheruser=true

Web Part life cycle in SharePoint

OnInit:
This method handles initialization of the control.
OnLoad:
This event handles the Load event. This is also used for initialize the control but is not intended for loading data or other processing functionality.
CreateChildControls:
This is the most popular event in web part life cycle. This creates any child controls. So if you are adding any control to display then you have to write in this method.
EnsureChildControls:
This method ensures that CreateChildControls has executed. EnsureChildControls method must be called to prevent null reference exceptions.
SaveViewState:
View state of the web part saved.
OnPreRender:
This method handles or initiates tasks such as data loading that must complete before the control can render.
Page.PreRenderComplete:
The page fires the PreRenderComplete event after all controls have completed their OnPreRender methods.
Render:
This method is used to render everything.
RenderContents:
Renders the contents of the control only, inside of the outer tags and style properties.
OnUnload:
Performs the final cleanup.

Using SPSecurity.RunWithElevatedPrivileges

SPWeb oWeb = SPContext.Current.Web;
SPWeb oWeb = oSite.OpenWeb();
Diff b/w above two is:
when using SPSecurity.RunWithElevatedPrivileges:
1.in the delegate function, you must build a new SPSite/SPWeb object (like SPSite siteColl = new SPSite(App.SITE_COLLECTION_URL)) and you can't use the SPContext.Current.Web, because the SPContext runs with the current context (with current user).
2.Also set the AllowUnsafeUpdates property of the site/web where you will be updating/accessing stuff to true, to be sure you don't get an error like "The security validation for this page is invalid" (see more below). If you don't do it, you code will work, but when returning from the delegate function, the error will arise..

Limitations of Using the SharePoint List Object Model and Client Object Model

Benefits of Using the SharePoint List Object Model and Client Object Model
The following are the benefits of using the SharePoint List and client object models.
· Simple and familiar object model. The SharePoint List object model (SPList class) is simple to work with and is familiar to SharePoint developers.
· Availability. The BDC object model is available on a client only with Office Professional Plus 2010 installed. However, the SharePoint object model is available on both the server and the client.
· Runs in partial trust environment. The SharePoint object model can run in a partially trusted environment. BDC object models cannot.
Limitations of Using the SharePoint List Object Model and Client Object Model
The following are the limitations of using the SharePoint List and Client object models.
· Requires external lists. The SharePoint List and Client object models perform CRUD operations on an external list from the server. Therefore, they require the creation of an external list on the server. The BDC object model does not have this requirement as it performs CRUD operations directly through an external content type.
· Only works for supported simple types. The SharePoint List and Client object models work only if the external list has simple fields. By design, external lists are flat, row-based representations of data. Complex types and simple types that are not supported by the SharePoint List type system are omitted. In particular, you cannot have fields of the following types:
1. Complex types For example, a "CustomerAddress" field as part of the "Customer" structure, which in turn has child elements defining "Street", "City", "State", and "PostalCode".
2. Custom types Example, a "Resume field as part of the "Applicant" structure, which might not have child elements but cannot be represented by using one of the supported SharePoint List field types.
3. Unsupported .NET types The following .NET Framework types are not supported by external lists: System.GUID, System.Object, System.URI, System.UInt64, and System.Int64. Therefore, if one of the fields of the external list are of the .NET Framework types listed here, these fields are omitted.
The BDC object model does not have this requirement and can work with all types of complex fields mentioned here.
· No support for advanced features of BDC. The SharePoint List and Client object models do not have support for the following advanced features supported by the BDC:
1. Paging External lists do not provide paging or chunking of data.
2. Bulk operations External lists cannot perform bulk operations, for example, reading 25 instances simultaneously.
3. Streaming There is no support for streaming data such as binary large objects (BLOBs) from external systems.
4. Complex associations External lists do not support associations.
Things to know when using the List Object Model with External Lists
· BdcIdentity is a field of the SharePoint List object that represents the identity of a specific list item. This is the only deterministic way to uniquely identify a particular list item. You can get it by using the Fields collection of the SPList object. See Code Snippets: Using the SharePoint List Object Model with External Lists on the Server and Code Snippets: Using the SharePoint Client Object Model with External Lists for some examples.
· The ItemID for external list items is always zero. You should use BdcIdentity instead.
· When you use the SharePoint client object model with external lists, you need to remember a few additional things. See Code Snippet: Get Item Data from an External List on the Client for more information.

AllowUnsafeUpdates RunWithElevatedPrivileges

What does AllowUnsafeUpdates do ?
If your code modifies Windows SharePoint Services data in some way, you may need to allow unsafe updates on the Web site, without requiring a security validation. You can do by setting the AllowUnsafeUpdates property.
Ex:
using(SPSite mySite = new SPSite("yourserver"))
{ using(SPWeb myWeb = mySite.OpenWeb())
{
myWeb.AllowUnsafeUpdates = true;
SPList interviewList = myWeb.Lists["listtoinsert"];
SPListItem newItem = interviewList.Items.Add();
newItem["interview"] = "interview";
newItem.Update();
}
}
What does RunWithElevatedPrivileges do?
Assume that you have a Web Part in which you want to display information obtained through the Windows SharePoint Services object model, such as the name of the current site collection owner, usage statistics, or auditing information. These are examples of calls into the object model that require site-administration privileges. Your Web Part experiences an access-denied error if it attempts to obtain this information when the current user is not a site administrator. The request is initiated by a nonprivileged user. you can still successfully make these calls into the object model by calling the RunWithElevatedPrivileges method provided by the SPSecurity class.
Ex:
SPSite siteColl = SPContext.Current.Site;
SPWeb site = SPContext.Current.Web;
SPSecurity.RunWithElevatedPrivileges(delegate()
{
using (SPSite ElevatedsiteColl = new SPSite(siteColl.ID))
{
using (SPWeb ElevatedSite = ElevatedsiteColl.OpenWeb(site.ID))
{
string SiteCollectionOwner = ElevatedsiteColl.Owner.Name;
string Visits = ElevatedsiteColl.Usage.Visits.ToString();
string RootAuditEntries = ElevatedSite.RootFolder.Audit.GetEntries().Count.ToString();
}
}
});

Excel Pivot Table MOSS 2007

Add an Excel Pivot Table and Pivot Chart to your SharePoint Site
Creating Pivot Tables and Pivot Charts are a powerful way to display line of business data on your site.
Prerequisites:
MOSS 2007 Enterprise Edition
Microsoft Office 2007 Enterprise Edition
Directions:
Create the Data Connection Library
1. From your Site, on the Site Action bar, select Create.
2. Within the Libraries section, select Data Connection Library.
3. From the New Data Connection page, provide a Name and Description and leaving the defaults as remaining settings.
Configure the Excel Services Trusted Data Connection Library
4. Proceed to Shared Services, and select Trusted Data Connection Libraries (in the Excel Services Settings section).
5. Select to Add Trusted Data Connection Library
6. Add the URL path to the Data Connection Library created earlier
Create a Document Library
7. From your Site, on the Site Action bar, select Create.
8. Within the Libraries section, select Document Library.
9. From the New Document Library page, provide a Name and Description and leaving the defaults as remaining settings.
Configure the Excel Services Trusted file location
10. Proceed to Shared Services, and select Trusted file location (in the Excel Services Settings section).
11. Select to Add Trusted File Location
12. From the Edit Trusted File Location page, update the following fields from their default settings:
Address Add the URL Path of the Document Library created in the previous section
Trust Children Set to Checked
Allow External Data Select Trusted Data Connection Libraries only option
Warn on Refresh Set Refresh Warning Enabled to Unchecked
Allow User-Defined Functions Set User-Defined Functions Allowed setting to Checked
Add Office Data Connection (ODC ) File to Data Connection Library
13. From the Data Connection Library created on your Site, select to Upload Document from the Action bar
14. Select to browse or enter the path to the desired Office Data Connection file which will define the data source to populate the Pivot Table and Pivot Chart
Create Excel Services Spreadsheet
15. Launch Microsoft Office Excel 2007 and create a new spreadsheet.
16. From the Data Tab, select Existing Connections from the Get External Data section of the Office Ribbon.
17. From the Existing Connections dialog, select the Browse for more… button.
18. Locate and supply the path to the Data Connection Library and the appropriate Office Data Connection file.
19. From the Import Data dialog, select the PivotTable Report option.
20. From the PivotTable Field List, select the desired fields and required for the PivotTable.
21. While the PivotTable is selected within Microsoft Excel, note the PivotTable Name within the PivotTable section on the Options Tab within the Office Ribbon, this is be required later within SharePoint.
22. While the PivotTable is selected within Microsoft Excel, select the PivotChart from the Tools section within the Office Ribbon
23. From the Insert Chart dialog, select the desired Chart Type to be added.
24. While the PivotChart is selected within Microsoft Excel, note the PivotChart Name within the Properties section on the Layout Tab within the Office Ribbon, this is be required later within SharePoint.
Publish Excel Services Spreadsheet
25. Select from the main menu, select the Office Button, then the Publish selection.
26. Locate or enter the path of the Document Library created earlier and provide a meaningful name for the Excel document.
Add the Excel Web Access Web Part to the page for use with a Pivot Table
27. From the Site Action bar, select Edit Page.
28. From any Web Part Zone, select to Add a Web Part.
29. From the Add Web Parts dialog, select to add the Excel Web Access web part (in the Business Data section).
30. From the web part’s Edit menu, select the Modify Shared Web Part selection.
31. From the Web Part settings pane, modify the following settings for the Excel Web Access Web Part:
Workbook Select the path of the Workbook created earlier
Named Item Enter the name of the Named Item noted in Step 21
Add the Excel Web Access Web Part to the page for use with a Pivot Chart
32. From the Site Action bar, select Edit Page.
33. From any Web Part Zone, select to Add a Web Part.
34. From the Add Web Parts dialog, select to add the Excel Web Access web part (in the Business Data section).
35. From the web part’s Edit menu, select the Modify Shared Web Part selection.
36. From the Web Part settings pane, modify the following settings for the Excel Web Access Web Part
Workbook Select the path of the Workbook created earlier
Named Item Enter the name of the Named Item noted in Step 24
37. From here, it is typical to make changes to the either the Excel Web Access Web Part or the Excel spreadsheet for the best aesthetic results depending on needs and requirements.