17 Oct 2011

Database does not have a valid owner

 

Error Message:

“Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects. "

“Database diagram support objects cannot be installed because this database does not have a valid owner.”

Solution:

USE [<DatabaseName>]
GO
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
GO

jQuery disable and enable an element

 

To Disable:

$('#elementID').attr('disabled', 'disabled');

To Enable:

$('#elementID').removeAttr('disabled');

 

15 Oct 2011

SQL Server Dynamic SQL commands Execute

 

Using EXEC

With this approach you are building the SQL statement on the fly and can pretty much do whatever you need to in order to construct the statement.  Let's say we want to be able to pass in the column list along with the city. 

For this example we want to get columns CustomerID, ContactName and City where City = 'London'.

As you can see from this example handling the @city value is not at straight forward, because you also need to define the extra quotes in order to pass a character value into the query.  These extra quotes could also be done within the statement, but either way you need to specify the extra single quotes in order for the query to be built correctly and therefore run.

DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'CustomerID, ContactName, City'
SET @city = '''London'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = ' + @city
EXEC (@sqlCommand)

 

sp_exectesql

With this approach you have the ability to still dynamically build the query, but you are also able to still use parameters as you could in example 1. This saves the need to have to deal with the extra quotes to get the query to build correctly.  In addition, with using this approach you can ensure that the data values being passed into the query are the correct datatypes.

DECLARE @sqlCommand nvarchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'CustomerID, ContactName, City'
SET @city = 'London'
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = @city'
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city

2 Oct 2011

LINQ to SharePoint

 

  • LINQ stands for Language Integrated Query
  • SharePoint works on top of Microsoft .NET Framework 3.5, not .NET 4.0
  • LINQ provides a methodology that simplifies and unifies the implementation of any kind of data access.
  • It facilitates the implementation of several existing architectures for accessing data
    • RAD/prototype
    • Client/server
    • N-tier
    • Smart client
  • LINQ includes many providers suitable for accessing several different types of data sources, few are:
    • LINQ to Objects
    • LINQ to SQL
    • LINQ to Entities
    • LINQ to DataSet
    • LINQ to XML
  • With LINQ you can use the same query syntax over different models.
  • When using LINQ to SharePoint, the query provider generates a CAML query that will be executed against the target SPList using the standard Server Object Model querying syntax.
  • image

  • The key feature of LINQ to SharePoint is that it can query SharePoint data with a fully-typed approach
  • SPMetel.exe automatically generate entities for you
  • it is good practice to use LINQ to SharePoint only against sites that have a well-defined and stable structure.

 

Limitations of LINQ to SharePoint and CAML queries:

  • The LINQ to SharePoint query provider has some limitations, due to its use of CAML queries under the covers. For example, with CAML, you cannot query more than one list at a time, so you can’t use a group join.
  • LINQ to SharePoint does not support multi-fetch queries that query across multiple lists, or join clauses on fields other than Lookup fields.
  • You cannot define queries across multiple websites or that query different DataContext instances.
  • You cannot use mathematical functions, because CAML does not support them.

 

Complete list of unsupported syntax and commands, please refer to MSDN http://msdn.microsoft.com/en-us/library/ee536585.aspx

Server Object Model SharePoint

 

SharePoint Server Object Model hierarchy:

  • SPFarm
    • SPService
      • SPWebApplication
        • SPSite
          • SPWeb
            • SPList
            • SPWeb
              • SPList
          • SPWeb
            • SPList

 

SPFarm

  • Manage entire SharePoint Server Farm
  • Microsoft.SharePoint.Administration namespace
  • You can connect to an existing Farm, or create a new Farm
  • Important Methods:
    • Create
    • Open
      public static SPFarm Open(SqlConnectionStringBuilder connectionString, SecureString passphrase)
  • Important Properties
    • Servers : to enumerate all the physical servers that belong to the Farm as objects of type SPServer
    • Services : has the type SPServiceCollection and contains different kinds of services, all sharing a common base class of type SPService
      • SPWindowsService,
      • SPWebService.

 

SPSite

SPSite Constructor with max parameters:

public SPSite(Guid id, SPUrlZone zone, SPUserToken userToken);

List Views SharePoint

 

List Views

Set of six predefined formats:

  1. Standard View This is the classic view style. You can choose fields, sorting and filtering
    rules, grouping, paging, and so on. The result will be a webpage.
  2. Calendar View This view shows data in a calendar format (daily, weekly, or monthly).
  3. Access View This view launches Microsoft Access so users can create forms and reports based on the list’s data.
  4. Datasheet View This view renders data in an editable spreadsheet format (such as Excel),
  5. Gantt View :  renders data in a Gantt chart.
  6. Custom View In SharePoint Designer

Visual Studio Projects for SharePoint

 

Project Types:

These models simply prepare a pre-configured environment

Item Templates:

1 Oct 2011

SharePoint Version Comparison vs

 

SharePoint Foundation

  • It is free.
  • Accessibility, cross-browser support, basic search features, out-of-thebox
    Web Parts, Silverlight support, new UI features based on dialogs and ribbons, blogs and
    wikis, and the workflow engine
  • Basic infrastructure of Business Connectivity
  • SharePoint Health Analyzer
  • Custom development, including the Web Parts programming model, the Server Object Model, the Client Object Model, Event Receivers, Claims-Based security

 

SharePoint Server Standard

  • Useful for building business-level solutions
  • This edition provides legal compliance capabilities, including records management, legal holds, and
    document policies, Document sets, Document IDs
  • Content Management System solution that provides features, such as content publishing, content approval, page layouts, web standards (XHTML, WCAG 2.0, etc.) support,
  • Supports tags and metadata-driven search refinement, people search, and some other social features
  • Managing people, profiles, and personal sites

 

SharePoint Server Enterprise

  • Targets large business solutions and enterprise-level organizations.
  • Support for dashboards, key performance indicators (KPIs), and business intelligence features.
  • Improves search capabilities by offering contextual search, deep search query refinement, extreme scale-out search capabilities, rich web indexing,
  • Support for Excel Services, Visio Services, InfoPath Forms Services, and Access Services

 

SharePoint for Internet Sites

Targets web publishing sites, whether the users are anonymous or authenticated

Two Internet site editions:

  • Standard Edition for Internet Sites
  • Enterprise Edition for Internet Sites

Standard Edition for Internet Sites is licensed for publishing a single domain website

Enterprise Edition for Internet Sites can publish multiple domains

 

SharePoint Online

Microsoft SharePoint Online is the cloud-based SharePoint offering, based on the Software
as a Service (SaaS) paradigm included in Microsoft Office 365

 

View Version Comparison at:
http://sharepoint.microsoft.com/en-us/buy/Pages/Editions-Comparison.aspx

SharePoint Architecture

 

architecture of Microsoft SharePoint 2010

Software and hardware requirements of SharePoint 2010:
http://technet.microsoft.com/en-us/library/cc262485.aspx

SharePoint farm

SharePoint farm is a set of servers that have different roles and offer various services that together make up a server farm suitable for hosting a full SharePoint deployment.

Server roles:

Front-end web servers
These servers publish websites, often called web applications.

Application servers
These servers host back-end services, such as the search index service, the crawler service, and so forth.

Database servers
These servers store configuration and content data for the entire SharePoint farm.

 

Deployment Topology

Single tier deployment
One server performing all roles.

Two-tier deployment
For the sake of scalability and business continuity, you should deploy a minimum
of two front-end web servers and a back-end database server.

Three-tier farm deployment
It has dedicated application servers.

 

SQL Server Databases:

SharePoint_ Config_<UniqueId> : Main and fundamental farm configuration database

Content Database:  A Content Database is a database file that stores content for one or more Site Collections.

sample SharePoint farm with a three-tier topology
Sample SharePoint farm with a three-tier topology

Business Connectivity Services SharePoint

The Business Connectivity Services is a fundamental application service of Microsoft SharePoint 2010. It provides capabilities to read and write data from external systems, such as Line of Business applications (LOB), web services, databases, or any other external source that offers a suitable connector

architectural schema of the Business Connectivity Services in SharePoint 2010.

Architectural schema of the Business Connectivity Services in SharePoint 2010.

“Creating Custom Business Connectivity Services Connectors Using SharePoint Server 2010”
http://msdn.microsoft.com/en-us/library/ff953161.aspx

BDC Server Runtime will store configurations and shapes of data sources in a dedicated repository, which is called BDC Metadata Store and corresponds to a dedicated database file

The BDC Client Runtime, which is a client-side engine that can be automatically installed by SharePoint on any PC client hosting Office 2010

 

SharePoint Designer 2010 is the ideal solution for modeling SQL Server–based solutions and web/WCF service–based solutions.

Visual Studio 2010 works very well with custom .NET assemblies and custom connectors.

 

SharePoint Designer 2010 for a new ECT

ECT : External Content Type

Step 1:

image

Microsoft SharePoint Designer 2010, shown with External Content Types highlighted.

Step2:

image

Step 3:

Click the Click Here To Discover External Data Sources And Define Operations link adjacent to External System

Step 4:

Click on Add connection, and provide connection details

SharePoint Designer 2010 gives you the following three options:
.NET Type
SQL Server
WCF Service

Operations Designer window for an ECT.
Operations Designer window for an ECT.

 

Using the SharePoint Designer 2010 interface, you can define the following operations:
Read Item Corresponds to the method for reading a single row/item.
Read List Corresponds to the method for reading a list of rows/items.
Create Creates a new row/item.
Update Updates an already existing row/item.
Delete Deletes an already existing row/item.

New Association command with which you can create a relationship between two ECTs in a master/detail fashion.

 

image

SharePoint Central Administration page for managing Business Data Connectivity Services

SharePoint Central Administration page for managing Business Data Connectivity Services

 

You can create the external list from SharePoint Designer 2010 or from the web browser.

Choose an External List template and create it.

In the standard properties select the  target ECT, and you are done

User experience is exactly the same of browsing a native list of SharePoint.

 

You can export the ECT model by using SharePoint Designer 2010 or via the management page of the Business Data Connectivity Services service application.
If you try to export the Customer ECT definition, an XML file with extension .bdcm
(Business Data Connectivity Model)

Capabilities of SharePoint 2010

 

  1. Sites

    1. Ribbon UI
      SharePoint Workspace
      SharePoint Mobile
      Office Client and Office Web App Integration
      Standards Support

  2. Communities

    1. Tagging, Tag Cloud, Ratings
      Social Bookmarking
      Blogs and Wikis
      My Sites
      Activity Feeds
      Profiles and Expertise
      Org Browser

  3. Content

    1. Enterprise Content Types
      Metadata and Navigation
      Document Sets
      Multi-stage Disposition
      Audio and Video Content Types
      Remote Blob Storage
      List Enhancements

  4. Search

    1. Social Relevance
      Phonetic Search
      Navigation
      FAST Integration
      Enhanced Pipeline

  5. Insights

    1. PerformancePoint Services
      Excel Services
      Chart Web Part
      Visio Services
      Web Analytics
      SQL Server Integration
      PowerPivot

  6. Composites

    1. Business Connectivity Services
      InfoPath Form Services
      External Lists
      Workflow
      SharePoint Designer
      Visual Studio
      API Enhancements
      REST/ATOM/RSS

 

Web Applications:

 

Collaboration

Collaboration includes the following templates: Team Site, Blank Site, Document Workspace, Blog, Group Work Site, and Visio Process Repository.

Meetings

Templates are: Basic Meeting Workspace, Blank Meeting Workspace, Decision Meeting Workspace, Social Meeting Workspace, and Multipage Meeting Workspace.

Enterprise

These templates target enterprise-level needs in the areas of document management, policies, and so on. They include Document Center, Records Center, PowerPoint Broadcast Site, Business Intelligence Center, Enterprise Search Center, My Site Host, Basic Search Center, and FAST Search Center.

Publishing

Templates are Publishing Portal and Enterprise Wiki.

Custom

This is where you can develop your own site templates. Also in this group is a list of all the available custom templates, if any exist.

Use Search engine by Code SharePoint

 

Federated Search Object Model:  Can query multiple locations and merge all the results together.

Query Object Model: Query the index of SharePoint and FAST Search Server.

Search Engine Assemblies:
Microsoft.SharePoint.dll
Microsoft.Office.Server.dll
Microsoft.Office.Server.Search.dll

Federated Search Object Model

Important class:

QueryManager class

public sealed class QueryManager : List<LocationList> {
public QueryManager();
public string DefaultFASTSearchSort { get; set; }
public int Timeout { get; set; }
public bool TrimDuplicates { get; set; }
public string UserQuery { get; set; }
public XmlDocument GetResults(LocationList locationList);
public bool IsTriggered(LocationList locationList);
public void SendRequest(LocationList locationList, int count);
}



SearchServiceApplicationProxy class offers a lot of methods for managing federated locations by code


Query Object Model


Query Object Model allows querying only the SharePoint index


Kinds of search queries supported by SharePoint  are:


Keyword Query


SearchServiceApplicationProxy searchServiceProxy =
(SearchServiceApplicationProxy)
SearchServiceApplicationProxy.GetProxy(SPServiceContext.GetContext(site));
KeywordQuery keywordQuery = new KeywordQuery(searchServiceProxy);
// Define the query type
keywordQuery.ResultTypes = ResultType.RelevantResults;
keywordQuery.QueryText = searchTerms;
// Execute the query
ResultTableCollection result = keywordQuery.Execute();



 


 


Full Text SQL Query


SearchServiceApplicationProxy searchServiceProxy =
(SearchServiceApplicationProxy)
SearchServiceApplicationProxy.GetProxy(SPServiceContext.GetContext(site));
FullTextSqlQuery sqlQuery = new FullTextSqlQuery(searchServiceProxy);
// Define the query type
sqlQuery.ResultTypes = ResultType.RelevantResults;
sqlQuery.QueryText = String.Format(
"SELECT Title, Path FROM Scope() WHERE FREETEXT(‘{0}’)", searchTerms);
// Execute the query
ResultTableCollection result = sqlQuery.Execute();



 


 


Query Web Service


This is an ASMX service, which is available only in SharePoint Server 2010, and not in SharePoint Foundation 2010.


relative URL /_vti_bin/search.asmx


Search.asmx service is that internally it works with the Query Object Mode


Query queries the index and returns a generic structured XML response.


QueryEx queries the index and returns a DataSet of ADO.NET, serialized as XML.


QueryText element accepts a type attribute, which can assume a value of:
STRING To execute a keyword query.
MSSQLFT To execute a Full Text SQL query.
FQL ( FAST Query Language)