“Key” columns values are used for sorting the index and they exist at the node level and the leaf level of the balanced tree structure. “Included” columns are not used for sorting the index and they only exist at the leaf level.

--set the table name below to search for a single table
--leave blank to return indexes for all tables
declare @table_name as varchar(255) = ''

select 
	schema_name(t.schema_id) + '.' + t.name as [table],
    i.name as [index],
    c.name as [column],
	ic.is_included_column
from 
    sys.indexes as i
	join sys.index_columns as ic 
		on ic.object_id = i.object_id 
		and ic.index_id = i.index_id
	join sys.columns as c 
		on c.object_id = ic.object_id 
		and c.column_id = ic.column_id
	join sys.tables as t 
		on t.object_id = c.object_id
where
	--if @table_name is null or empty then do not filter
	coalesce(ltrim(@table_name), '') = '' 
	or lower(t.name) = lower(@table_name)
order by 
	i.index_id,
	ic.index_column_id

Advertisements

In this example, I create a SQL user defined aggregate function that returns a Comma Separated Value (CSV) string from a table column.

In SQL, user defined aggregate functions use CLR, so you cannot create them with SQL scripts alone, you must use C# in Visual Studio.

  1. Create a new project in Visual Studio. I used the name “MyNamespace”.
  2. Create a new class within the project. I used the name “CSV.cs”. Here is the code for the class:
    • using System.Data.SqlTypes;
      using Microsoft.SqlServer.Server;
      using System.Collections.ObjectModel;
      namespace MyNamespace{
      [System.Serializable]
      [Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
      Microsoft.SqlServer.Server.Format.UserDefined,
      IsInvariantToDuplicates = false,
      IsInvariantToNulls = false,
      IsInvariantToOrder = true,
      IsNullIfEmpty = true,
      MaxByteSize = -1,
      Name = “CSV”
      )]
      public struct CSV : IBinarySerialize
      {
      private Collection<string> list;
      private string delimiter;
      public void Init()
      {
      list = new Collection<string>();
      delimiter = string.Empty;
      }
      //this seems to handle the SQL function parameters
      public void Accumulate(string value, string delimiter)
      {
      if (value != null)
      {
      if (this.delimiter != delimiter) { this.delimiter = delimiter; }
      list.Add(value.ToString().Replace(this.delimiter, “”)); //remove delimeter character from value
      }
      }
      //not sure when this gets used
      public void Merge(CSV group)
      {
      if (null != group.list && group.list.Count > 0)
      {
      foreach (string entry in group.list)
      {
      list.Add(entry);
      }
      }
      }
      //this seems to return the result
      public SqlString Terminate()
      {
      return new SqlString(string.Join<string>(“,”, list));
      }
      #region IBinarySerialize Members
      //not sure when this gets used, but it matches the function below
      public void Write(System.IO.BinaryWriter writer)
      {
      writer.Write(this.delimiter);
      foreach (string entry in list)
      {
      writer.Write(entry);
      }
      }
      //not sure when this gets used, but it matches the function above
      public void Read(System.IO.BinaryReader reader)
      {
      this.delimiter = reader.ReadString();
      list = new Collection<string>();
      while(reader.BaseStream.Position != reader.BaseStream.Length) //not end of file
      {
      this.list.Add(reader.ReadString());
      }
      }
      #endregion
      }
      }
  3. Build -> Build Solution
  4. Next we will deploy the assembly (.dll file) generated in the previous step. It should be somewhere within the “bin” folder of you solution. Open the SQL command prompt (Start -> All Programs -> Microsoft SQL Server 2012 -> Visual Studio Tools -> Developer Command Prompt for VS2012) and enter the following command in order to deploy the assembly to Global Assembly Cache (GAC). (be sure to replace the .dll path with your own file path):
    • gacutil.exe /if c:\projects\Web\MyNamespace\MyNamespace\bin\release\MyNamespace.dll
  5. Open SQL Management Studio. Register the assembly in SQL by running the following script:
    • EXEC sp_configure ‘clr enabled’, 1
      GO
      RECONFIGURE
      GOCREATE ASSEMBLY MyNamespace
      AUTHORIZATION dbo
      FROM ‘C:\projects\Web\MyNamespace\MyNamespace\bin\release\MyNamespace.dll’
      WITH PERMISSION_SET = SAFE
      GO
  6. Create the user defined aggregate function:
    • CREATE AGGREGATE dbo.CSV (
      @value nvarchar(MAX),
      @delimiter nvarchar(50)
      )
      RETURNS nvarchar(MAX)
      EXTERNAL NAME MyNamespace.[MyNamespace.CSV]
      –EXTERNAL NAME SQLAssemblyName.[C#NameSpace”.C#ClassName].C#MethodName
  7. Your user defined aggregate function should be ready for use now: run the following script: (the function works correctly with GROUP BY, but does provide any ORDER BY capability within the CSV)
    • SELECT dbo.CSV(MyColumn, ‘,’)
      FROM MyTable
  8. If you need to remove the SQL assembly reference and the user defined aggregate function, run the following script:
    • DROP AGGREGATE dbo.CSV
      DROP ASSEMBLY MyNamespace

I recently created a custom error pages feature that was scoped to “WebApplication”. I ran the STSADM “addsolution” command then went to Central Administration -> System Settings -> Manage farm solutions -> mySolution.wsp -> Deploy Solution. I noticed that the “Deploy To?” value was set to “This solution deploys globally.” and no configurable options were given. In order to get a list of web application options in the “Deploy To?” setting, I had to add a SafeControl to the package manifest of the solution, in Visual Studio. It was a really simple fix. Here is what I did:

  1. Open your solution in Visual Studio
  2. Solution Explorer -> Package -> (double click) Package.package
  3. Click the “Manifest” button near the bottom of the Package.package window
  4. Click the “+” button next to “Edit Options” to expand the manifest template area
  5. Click the “(Open in XML Editor)” link
  6. Here is what my Package.Template.xml contained before modifications:
    <?xml version=”1.0″ encoding=”utf-8″?>
    <Solution xmlns=”http://schemas.microsoft.com/sharepoint/”>
    </Solution>
  7. Here is what my Package.Template.xml contained after modifying and saving (be sure to change “mySolution.dll“):
    <?xml version=”1.0″ encoding=”utf-8″?>
    <Solution xmlns=”http://schemas.microsoft.com/sharepoint/”>
    <Assemblies>
    <Assembly Location=”mySolution.dll” DeploymentTarget=”GlobalAssemblyCache”>
    <SafeControls>
    <SafeControl Assembly=”$SharePoint.Project.AssemblyFullName$”
    Namespace=”$SharePoint.Project.FileNameWithoutExtension$” TypeName=”*” Safe=”True”></SafeControl>
    </SafeControls>
    </Assembly>
    </Assemblies>
    </Solution>
  8. Then I did a rebuilt the solution (not sure that it was necessary) and I repackaged the solution. This updates the .wsp file.
  9. Make sure the existing “mySolution.wsp” is removed from Central Administration’s farm solutions gallery.
  10. Run the STSADM “addsolution” command for the updated .wsp file
  11. Central Administration -> System Settings -> Manage farm solutions -> mySolution.wsp -> Deploy Solution
  12. In the “Deploy To?” setting, you should now see “Choose a Web application to deploy this solution:” along with a drop down list of web applications to choose from

 

This is the syntax that I use for insert/update:

MERGE Table2 AS TARGET
USING (
    SELECT Column1, Column2, Column3
    FROM Table1
) AS SOURCE ([Table1].Column1, [Table1].Column2, [Table1].Column3)
ON TARGET.Column1 = SOURCE.Column1 AND TARGET.Column2 = SOURCE.Column2
WHEN MATCHED THEN
    UPDATE
    SET [Table2].Column1 = SOURCE.Column1,
        [Table2].Column2 = SOURCE.Column2,
        [Table2].Column3 = SOURCE.Column3
WHEN NOT MATCHED THEN
    INSERT
    (
        [Table2].Column1,
        [Table2].Column2,
        [Table2].Column3
    )
    VALUES
    (
        SOURCE.Column1,
        SOURCE.Column2,
        SOURCE.Column3
    );

I’m using SharePoint 2010. Open the “SharePoint 2010 Management Shell” application. Start -> All Programs -> Microsoft SharePoint 2010 Products -> SharePoint 2010 Management Shell. Then enter the following commands:

$web = Get-SPWeb http://MySiteUrl/MySubsiteUrlIfNecessary

$list = $web.lists[“My List Title”]

$list.AllowDeletion = $true

$list.Update()

$list.Delete()

$list.Dispose()

$list.Dispose() does not work for me, but I saw it in an example, so I do it just for good measure.

I was getting an “Property ‘AllowDeletion’ cannot be found on this object; make sure it exists and is settable.” error when I tried $list.AllowDeletion = $true. That was because the list title I used did not exist in the $web that I specified.

In this post, I will show you how to deploy a Web Part Page with content added to the page, specifically a custom Visual Web Part. This method should work for other web parts, including out of the box SharePoint Web Parts. This post focuses on how to provision the Web Part on a page, it does not explain how to create the Web Part.

Here is a view of the Object Explorer window:

ProvisionWp01

Here is the code for MyWebPartPage.aspx:

<%@ Assembly Name=”$SharePoint.Project.AssemblyFullName$” %>
<%@ Import Namespace=”Microsoft.SharePoint.ApplicationPages” %>
<%@ Register Tagprefix=”SharePoint” Namespace=”Microsoft.SharePoint.WebControls” Assembly=”Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %>
<%@ Register Tagprefix=”Utilities” Namespace=”Microsoft.SharePoint.Utilities” Assembly=”Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %>
<%@ Register Tagprefix=”asp” Namespace=”System.Web.UI” Assembly=”System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35″ %>
<%@ Import Namespace=”Microsoft.SharePoint” %>
<%@ Assembly Name=”Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %>
<%@ Register Tagprefix=”WebPartPages” Namespace=”Microsoft.SharePoint.WebPartPages” Assembly=”Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %>
<%@ Page Language=”C#” AutoEventWireup=”true” CodeBehind=”MyWebPartPage.aspx.cs” Inherits=”DeployWebPartOnPage.MyWebPartPage.MyWebPartPage” MasterPageFile=”~masterurl/default.master” %>

<asp:Content ID=”Main” ContentPlaceHolderID=”PlaceHolderMain” runat=”server”>
<WebPartPages:WebPartZone runat=”server” ID=”wpzMyZone”>
<ZoneTemplate></ZoneTemplate>
</WebPartPages:WebPartZone>
</asp:Content>

Here is the code for MyWebPartPage.aspx.cs:

using System;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;

namespace DeployWebPartOnPage.MyWebPartPage
{
public partial class MyWebPartPage : WebPartPage
{
protected void Page_Load(object sender, EventArgs e)
{
}
}
}

Here is the code for Elements.xml:

    <?xml version=”1.0″ encoding=”utf-8″?>
<Elements xmlns=”http://schemas.microsoft.com/sharepoint/&#8221; >
<Module Name=”MyWebPartPage” Url=”SitePages”>
<File Path=”MyWebPartPage\MyWebPartPage.aspx” Url=”MyWebPartPage.aspx”
Type=”GhostableInLibrary” IgnoreIfAlreadyExists=”FALSE”>
<AllUsersWebPart WebPartOrder =”0″ WebPartZoneID =”wpzMyZone”>
<![CDATA[
<?xml version=”1.0″ encoding=”utf-8″?>
<webParts>
<webPart xmlns=”http://schemas.microsoft.com/WebPart/v3″&gt;
<metaData>
<type name=”DeployWebPartOnPage.HelloWorldWP.HelloWorldWP, DeployWebPartOnPage,
Version=1.0.0.0, Culture=neutral, PublicKeyToken=00a467d3ecefd6d1″ />
<importErrorMessage>Cannot import this Web Part.</importErrorMessage>
</metaData>
<data>
<properties>
<property name=”Title” type=”string”>HelloWorldWP</property>
<property name=”Description” type=”string”>HelloWorldWP Description</property>
</properties>
</data>
</webPart>
</webParts>
]]>
</AllUsersWebPart>
</File>
</Module>
</Elements>

The only issues that i ran into while deploying this are that i forgot to modify the feature to include the Web Part Page and i did not have the correct assembly info in Elements.xml. I was unable to add the .aspx file directly to the MyWebPartPage module so i had to drag and drop it where i wanted within the Object Explorer window. Then when i deployed i was getting a “cannot find/deploy web part” sort of a message. That is because i had not corrected the namespace value of the Web Part Page after the drag and drop.

Chrome behaves as expected: A “position:absolute” element’s “top”, “bottom”, “left”, and “right” styles are relative to the immediate parent/enclosing element. Firefox does not behave as expected: A “position:absolute” element’s “top”, “bottom”, “left”, and “right” styles are NOT necessarily relative to the immediate parent/enclosing element. Instead, Firefox goes up the chain of parent elements and sets the positioning relative to the first element that has “display:block” style.

My solution was to add “display:block” style to the “position:absolute” element’s immediate parent/enclosing element. I was using Chrome v.27 and Firefox v.21 in this scenario.