Aravind

How to filter the content type which has Information Management Policy Settings in SharePoint online

2016-05-30T23:28:00.000-07:00

All the Information management policy settings information can be obtained from the SchemaXML of the content type.

Below XML is an extract from the schema XML which contains details about the policies. Here I do have a retention policy attached to my content type.

<XmlDocument NamespaceURI="office.server.policy">

- <p:Policy xmlns:p="office.server.policy" id="" local="true">

<p:Name>DemoContentType</p:Name>

<p:Description />

<p:Statement />

- <p:PolicyItems>

- <p:PolicyItem featureId="Microsoft.Office.RecordsManagement.PolicyFeatures.Expiration" staticId="0x0101007F2D345A54104E038DF20E887A9D9E57|-225302813" UniqueId="a4a001ae-0512-40f4-b6dc-36a3861a6726">

<p:Name>Retention</p:Name>

<p:Description>Automatic scheduling of content for processing, and performing a retention action on content that has reached its due date.</p:Description>

- <p:CustomData>

- <Schedules nextStageId="2">

- <Schedule type="Default">

- <stages>

- <data stageId="1">

- <formula id="Microsoft.Office.RecordsManagement.PolicyFeatures.Expiration.Formula.BuiltIn">

<number>60</number>

<property>DemoContentType</property>

<propertyId>c8f6e1c4-183f-4bf6-8756-8428c39aab33</propertyId>

<period>days</period>

</formula>

<action type="action" id="Microsoft.Office.RecordsManagement.PolicyFeatures.Expiration.Action.MoveToRecycleBin" />

</data>

</stages>

</Schedule>

</Schedules>

</p:CustomData>

</p:PolicyItem>

</p:PolicyItems>

</p:Policy>

</XmlDocument>

Simple code snippet to traverse all the content types and get the content types which has a policy attached to it in SharePoint online using client object model.

string url = "siteURL";

ClientContext ctx = new ClientContext(url);

ctx.Credentials = new SharePointOnlineCredentials("email id", new NetworkCredential("email id", "XXXX").SecurePassword);

ContentTypeCollection contentTypeCollection = ctx.Web.ContentTypes;

ctx.Load(contentTypeCollection, ct => ct.Include(val => val.Name, val => val.Hidden, val => val.Id, val => val.SchemaXml));

ctx.ExecuteQuery();

foreach (ContentType contentType in contentTypeCollection)

{

if (!contentType.Hidden)

{

XDocument xdoc = XDocument.Parse(contentType.SchemaXml);

IEnumerable<XElement> elments = xdoc.Descendants().Where(elm => elm.Name.NamespaceName == "office.server.policy");

if (elments.Count() > 0)

{

Console.WriteLine("\"" + contentType.Name + "\"," + contentType.Id);

}

Add user to Yammer group and approve the added user in private group

2015-10-16T22:08:00.001-07:00

Pre-requisite

1. Service account with Verified admin credentials
2. Create a app and follow the steps in this link - https://developer.yammer.com/docs/test-token
3. Client id and token (oAuth token) of verified admin is mandatory.
4. Service account has to be group admin to approve the members if only those approved by an admin is selected.

Please find the link to download the working code. client id, oAuth token of verified admin of your network needs to be updated along with the user email id and the group id to which the user has to be added.

Step 1: https://www.yammer.com/api/v1/users/by_email.json?email={0} - returns user id from the user id. If you have user id handy you can ignore this step.

Step 2: https://www.yammer.com/api/v1/oauth/tokens.json?consumer_key={0}&user_id={1} - Impersonate the user and get the oAuth token after impersonation. Below diagram explains the request flow.

Note: Above diagram is taken from the book - Develop on Yammer: Social Integration for Modern Business Applications - By Pathik Rawal, Pryank Rohilla

Step 3: https://www.yammer.com/api/v1/group_memberships.json?group_id={0} - Make a post call to add the user to the group with the impersonated token as Authentication - Bearer token.

Step 4: https://www.yammer.com/api/v1/group_memberships.json?group_id={0}&user_id={1}&approve=1 - Make a post call with the group admin - oAuth token to approve the request.

Step 4 is not documented in Yammer API but it works. At any point of time, it can go unsupported.

This link contains end to end project - Completed demo

Increase KQL query length limit in SharePoint 2013

2014-03-06T20:41:00.000-08:00

The length limit of a KQL query varies depending on how you create it. If you create the KQL query by using the default SharePoint search front end, the length limit is 2,048 characters. However, KQL queries you create programmatically by using the Query object model have a default length limit of 4,096 characters. You can increase this limit up to 20,480 characters by using the MaxKeywordQueryTextLength property or the DiscoveryMaxKeywordQueryTextLength property (for eDiscovery).

Source - MSDN - Link

Using PowerShell the length can increased.

Code snippet:

$SearchApplicationName = "Search Service Application";

$ServiceApplication = Get-SPEnterpriseSearchServiceApplication -Identity "Search Service Application";

$ServiceApplication.MaxKeywordQueryTextLength = 5000;

This will increase the KQL length to 5000.

Circular linkedlist in C#

2012-05-25T00:13:00.003-07:00

class CustomCircularLinkedList<T>
    {
        public CircularLinkedListItem<T> RootNode;
        public int Count { set; get; }

        public void Add(T value)
        {
            CircularLinkedListItem<T> newNode = new CircularLinkedListItem<T>(value);

            if (RootNode == null)
            {
                this.RootNode = newNode;
                Count++;
                return;
            }

            CircularLinkedListItem<T> node = this.RootNode;

            while (node.NextNode != null)
            {
                node = node.NextNode;
            }

            node.NextNode = newNode;
            newNode.PreviousNode = node;
//This distinguishes double and circular linkedlist
            this.RootNode.PreviousNode = newNode;
        }
    }

    class CircularLinkedListItem<T>
    {
        public CircularLinkedListItem<T> PreviousNode { set; get; }
        public CircularLinkedListItem<T> NextNode { set; get; }
        public T NodeItem { set; get; }

        public CircularLinkedListItem(T value)
        {
            this.NodeItem = value;
        }
    }

Double Linkedlist in C#

2012-05-25T00:10:00.002-07:00

class CustomDoubleLinkedList<T>
    {
        private CustomDoubleLinkedListItem<T> RootNode;

        public int Count { set; get; }

        public void AddValue(T value)
        {
            CustomDoubleLinkedListItem<T> CustomDoubleLinkedListItemReference = new CustomDoubleLinkedListItem<T>(value);

            if (RootNode == null)
            {
                RootNode = CustomDoubleLinkedListItemReference;
                RootNode.PreviousNode = null;
                Count++;
                return;
            }

            CustomDoubleLinkedListItem<T> nextNodeReference= this.RootNode;

            while (nextNodeReference.NextNode != null)
            {
                nextNodeReference = nextNodeReference.NextNode;
            }

            nextNodeReference.NextNode = CustomDoubleLinkedListItemReference;
            CustomDoubleLinkedListItemReference.PreviousNode = nextNodeReference;
        }

        public void InsertAt(T value, uint position)
        {

            if (position > Count)
            {
                throw new InvalidOperationException("Position cannot be greater than link nodes");
            }

            int tempCount = 0;
            CustomDoubleLinkedListItem<T> CustomDoubleLinkedListItemReference = new CustomDoubleLinkedListItem<T>(value);
            CustomDoubleLinkedListItem<T> node = this.RootNode;

            while (tempCount != position && node.NextNode != null)
            {
                node = node.NextNode;
            }

            if (node == this.RootNode)
            {
                CustomDoubleLinkedListItemReference.PreviousNode = null;
            }
            else
            {
                CustomDoubleLinkedListItemReference.PreviousNode = node.PreviousNode;
            }

            CustomDoubleLinkedListItemReference.NextNode = node;
        }

        public void ReverseList()
        {
            CustomDoubleLinkedListItem<T> node = this.RootNode.NextNode;
            CustomDoubleLinkedListItem<T> previousNode;
            CustomDoubleLinkedListItem<T> nextNode;
            while (node != null)
            {
                nextNode = node.PreviousNode;
                previousNode = node.NextNode;
                node.PreviousNode = previousNode;
                node.NextNode = nextNode;

                //Handle the old root
                if (previousNode == null)
                {
                    previousNode = this.RootNode.NextNode;
                    this.RootNode.NextNode = null;
                    //Set the previous node
                    this.RootNode.PreviousNode = previousNode;
                    this.RootNode = node;
                    previousNode = null;
                }

                if (previousNode == this.RootNode)
                {
                    node.PreviousNode = null;
                }

                node = previousNode;
            }
        }

        public string DrawNode()
        {
            string output = String.Empty;

            if (RootNode.NextNode == null)
            {
                return RootNode.Value.ToString();
            }

            output = RootNode.Value.ToString();
            CustomDoubleLinkedListItem<T> nextNodeReference = this.RootNode.NextNode;

            while (nextNodeReference.NextNode != null)
            {
                output = String.Concat(output,",", nextNodeReference.PreviousNode.Value.ToString(), "->", nextNodeReference.Value);
                nextNodeReference = nextNodeReference.NextNode;
            }

            return output;
        }
    }

    class CustomDoubleLinkedListItem<T>
    {
        public CustomDoubleLinkedListItem<T> PreviousNode { set; get; }
        public CustomDoubleLinkedListItem<T> NextNode { set; get; }
        public T Value { set; get; }

        public CustomDoubleLinkedListItem(T customDoubleLinkedListItemValue)
        {
            this.Value = customDoubleLinkedListItemValue;
        }
    }

LinkedList

2012-05-25T00:06:00.000-07:00

    class CustomSingleLinkedList<T>
    {
        private CustomLinkedListItem<T> rootNode;
        public int Count = 0;

        public void Add(T value)
        {
            CustomLinkedListItem<T> linkedListItem = new CustomLinkedListItem<T>(value);

            if (rootNode == null)
            {
                rootNode = linkedListItem;
                ++Count;
                return;
            }

            CustomLinkedListItem<T> previousNode = rootNode;

            while (previousNode.Reference != null)
            {
                previousNode = previousNode.Reference;
            }

            previousNode.Reference = linkedListItem;
            ++Count;
        }

        public void InsertAt(T value, int position)
        {
            if (position > Count)
            {
                throw new IndexOutOfRangeException();
            }

            int tempCount = 0;
            CustomLinkedListItem<T> linkedListItem = new CustomLinkedListItem<T>(value);
            CustomLinkedListItem<T> node = rootNode;
            CustomLinkedListItem<T> tempnode = null;

            while (tempCount != position && node.Reference != null)
            {
                tempnode = node;
                node = node.Reference;
                tempCount++;
            }

            if (tempnode != null)
            {
                tempnode.Reference = linkedListItem;
            }
            else
            {
                this.rootNode = linkedListItem;
            }

            linkedListItem.Reference = node;
        }

        public void ChangeValueAt(int position, T value)
        {
            int tempCount = 1;
            CustomLinkedListItem<T> node = rootNode;

            while (tempCount != position && node.Reference != null)
            {
                node = node.Reference;
                tempCount++;
            }

            node.Item = value;
        }

        public T ValueAt(int position)
        {
            T value = default(T);

            int tempCount = 1;
            CustomLinkedListItem<T> node = rootNode;

            while (tempCount != position && node.Reference != null)
            {
                node = node.Reference;
                tempCount++;
            }

            value = node.Item;
            return value;
        }

        public void ReverseLinkedList()
        {
            int tempCount = Count;

            for (int i = 1, j = tempCount; i < j; i++, j--)
            {
                T Temp = ValueAt(i);
                ChangeValueAt(i, ValueAt(j));
                ChangeValueAt(j, Temp);
            }
        }

        public T ReturnLastItem()
        {
            if (rootNode == null)
            {
                throw new InvalidOperationException("Root node is null");
            }

            if (rootNode.Reference == null)
            {
                return rootNode.Item;
            }

            CustomLinkedListItem<T> node = rootNode;

            while (node.Reference != null)
            {
                node = node.Reference;
            }

            return node.Item;
        }

        public void RemoveLastItem()
        {
            if (rootNode == null)
            {
                throw new InvalidOperationException("Root node is null");
            }

            if (rootNode.Reference == null)
            {
                rootNode = null;
                return;
            }

            CustomLinkedListItem<T> node = this.rootNode;
            CustomLinkedListItem<T> tempnode = null;

            while (node.Reference != null)
            {
                tempnode = node;
                node = node.Reference;
            }

            tempnode.Reference = null;
        }

        public void RemoveFirstItem()
        {
            if (rootNode == null)
            {
                throw new InvalidOperationException("Root node is null");
            }

            if (rootNode.Reference != null)
            {
                rootNode = rootNode.Reference;
            }
            else
            {
                rootNode = null;
            }
        }
    }

    class CustomLinkedListItem<T>
    {
        public T Item { set; get; }
        public CustomLinkedListItem<T> Reference { set; get; }

        public CustomLinkedListItem(T item)
        {
            this.Item = item;
        }
    }

Implement stack from two que

2012-05-24T23:57:00.001-07:00

class StackFromQue
    {
        private Queue<int> a = new Queue<int>();
        private Queue<int> b = new Queue<int>();

        //Optimize for Push operation
        public void Push(int value)
        {
            a.Enqueue(value);
        }

        public int Pop()
        {
            if (a.Count.Equals(0) && b.Count.Equals(0))
            {
                throw new InvalidOperationException("Empty stack");
            }

            if (a.Count > 0)
            {
                return PopOperation(a, b);
            }
            else
            {
                return PopOperation(b, a);
            }
        }

        private int PopOperation(Queue<int> first, Queue<int> second)
        {
            while (first.Count > 1)
            {
                second.Enqueue(first.Dequeue());
            }

            return first.Dequeue();
        }
    }

Explanation:

1. For Push operation, Enque all the values in Que 1.

2. For Pop opeartion, deque all the values from the current que into the next que until there is only one value in the current que. Return the last value and deque them. For the next value swap the ques.

Note: This is optimized for Push operation. It can be optimized for Pop operation in the reverse manner.

Merge Sort in C#

2012-05-10T17:22:00.000-07:00

Merge Sort in C#

public int[] MergeSortAction(int[] input)
        {
            int inputArrayLength = input.Length;

            if (inputArrayLength.Equals(1))
            {
                return input;
            }

            int middle = inputArrayLength / 2;
            int[] left = new int[middle];
            int[] right = new int[inputArrayLength - middle];

            //for (int i = 0; i < middle; i++)
            //{
            //    left[i] = input[i];
            //}

            //for (int i = 0; i < inputArrayLength - middle; i++)
            //{
            //    right[i] = input[i + middle];
            //}

            Array.Copy(input, 0, left, 0, middle);
            Array.Copy(input, middle, right, 0, inputArrayLength - middle);

            left = MergeSortAction(left);
            right = MergeSortAction(right);

            int[] sortedArray = new int[inputArrayLength];
            int leftp = 0;
            int rightp = 0;

            for (int count = 0; count < inputArrayLength; count++)
            {
                if (rightp == right.Length || (leftp < left.Length && left[leftp] <= right[rightp]))
                {
                    sortedArray[count] = left[leftp];
                    leftp++;
                }
                else if (leftp == left.Length || (rightp < right.Length && right[rightp] <= left[leftp]))
                {
                    sortedArray[count] = right[rightp];
                    rightp++;
                }
            }

            return sortedArray;
        }

Quick Sort in C#

2012-05-09T20:43:00.000-07:00

Quick Sort in C#

public List<int> QuickSortAction(List<int> input, int left, int right)
        {
            int i = left;
            int j = right;
            int pivotValue = input[(left + right) / 2];
            int temp = 0;

            while (i <= j)
            {
                while (input[i] < pivotValue)
                {
                    i++;
                }
                while (pivotValue < input[j])
                {
                    j--;
                }
                if (i <= j)
                {
                    temp = input[i];
                    input[i++] = input[j];
                    input[j--] = temp;
                }
            }

            if (left < j)
            {
                QuickSortAction(input, left, j);
            }

            if (i < right)
            {
                QuickSortAction(input, i, right);
            }

            return input;
        }

Facebook OAuth authentication for WPF application in C#

2011-09-13T13:33:00.000-07:00

Facebook uses OAuth 2.0 for authentication and authorization.This authentication is used to access Facebook data using their Graph API. To access data from Facebook, 3 steps - user authentication, app authorization and app authentication are involved. At the end of the third step, user access token is issued. This is explained in detail here.

The prerequisite from Facebook, for fetching the data using Facebook graph API is, getting the client id. Go to https://developers.facebook.com/apps and click on Create New App button. Just walk through the next button clicks and complete it. Once it is completed, make a note of the App ID.

Next step is to decide what set of permissions are required for the app. h ttp://developers.facebook.com/docs/reference/api/permissions/ link explains in detail about the set of available permissions. They accept comma delimited permissions.

For desktop applications "http://www.facebook.com/connect/login_success.html" is the redirect uri. For web apps, redirect uri has to be mentioned in the Facebook app.

Navigate the browser control to this url - https://graph.facebook.com/oauth/authorize?client_id=<<App Id>>&redirect_uri=<<redirect uri>>&scope=<<comma delimited permissions>>&type=user_agent&display=popup.

The browser control gets navigated to http://www.facebook.com/connect/login_success.html#access_token=<<Access token>>&expires_in=<<Expires in>>&code=<<code>>.

In WPF, there is problem with the System.Windows.Controls.WebBrowser. In the url, the characters after # is ignored. System.Windows.Forms.WebBrowser can capture it. So host it under System.Windows.Forms.Integration.WindowsFormsHost. This comes as handy workaround for it. The complete code looks like this

xmlns:my="clr-namespace:System.Windows.Forms.Integration;assembly=WindowsFormsIntegration"
xmlns:win="clr-namespace:System.Windows.Forms;assembly=System.Windows.Forms"

    <my:WindowsFormsHost>
            <win:WebBrowser x:Name="browser" Navigated="browser_Navigated"></win:WebBrowser>
        </my:WindowsFormsHost>

I have created a sample WPF application which takes the App Id & comma delimited scope as the input and returns Access token & Expiration time in seconds.The Sample project can be downloaded from the link - http://aravindks.codeplex.com/

Detaching and Attaching a SQL Database

2011-03-27T21:05:00.000-07:00

In this blog I will cover about detaching and attaching the SQL database.

When to go for detaching and attaching a Database?
If you can afford the database to be taken offline and want it to be reused then this is the right option rather than going for backup & restore.
When you want to move the database from one instance to another instance of SQL Server in the same machine this has to be used. Practically during the development time the same database data may be used for development as well as testing. The DB can be taken offline and the mdf & ldf sql files can be shared with testers so they attach the DB. This will help both developers and testers to proceed separately with same data.

Detaching the SQL Database

Takes the SQL DB offline.

SQL Query:

Syntax:

 EXEC sp_detach_db @DBNname=<<DB Name>>, @skipchecks = <<TRUE or FALSE for skipchecks>>, @keepfulltextindexfile = <<TRUE or FALSE for keepfulltextindexfile>>

Here @DBName is a mandatory parameter.

Usage: sp_detach_db <dbname>, [TRUE|FALSE], [TRUE|FALSE]

Example: EXEC sp_detach_db @DBName = 'DemoDB';

Practically, it can also be used like this.

db_id(<<DB Name>>) is used to check whether the DB exists or not. If DB doesn't exist and if its tried to be detached then the below error is thrown.

Database '<<DB name>>' does not exist. Make sure that the name is entered correctly.

Sometimes the DB may be used and at that time if its tried to be detached then the below error is thrown. Kill the process which is currently attached to our Database and then take the database offline.

Cannot detach the database '<<DB Name>>' because it is currently in use.

SQL



DECLARE @DatabaseName nvarchar(15) = N'DemoDB'



If db_id(@DatabaseName) is not null

    BEGIN

        

        DECLARE @SQL varchar(max)

            

         SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' +

         Convert(varchar, SPId) + ';'

         FROM MASTER..SysProcesses

         WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId    

        

         EXEC(@SQL)



         EXEC sp_detach_db @DatabaseName

        

         If db_id(@DatabaseName) is null

         BEGIN        

            PRINT @DatabaseName + ' is deattached successfully'

         END

    END

Else

    PRINT 'DB does not exist'

SQL PowerShell Script:

This uses the SQL server provider for PowerShell. If its not installed then .Net framework dlls can be consumed and then the query can be executed.

Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $ServerInstance -Database $DBName -verbose

The above code executes the sql query, most of the parameters passed to Inovke-sqlCmd here can be guessed just by their name but still let me brief about each and every parameter that are passed to Invoke-Sqlcmd command.

$SQLQuery is the sql query that has to be executed.

Invoke-Sqlcmd -InputFile "C:\SQLCmd.sql"

Here in our case the query is very small, if in case if the SQL Query is large then the Sql query can be stored as script file and it can be invoked in PowerShell.

$ServerInstance is the SQL server instance that has to be used, if its not passed then the default instance is used.

$DBName is the initial database that has to be used for executing the query, this can also be achieved by having USE <<DB Name>> as the first line in the SQL query.

-Verbose - This displays the SQL messages. In our SQL query we are having Print statement, to show the SQL messages -verbose is employed.

PowerShell



#

# Add the SQL Server Provider.

#



$ErrorActionPreference = "Stop"



#Check whether SQL PowerShell snapin is loaded or not

if ( (Get-PSSnapin -Name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue) -eq $null -and 

     (Get-PSSnapin -Name SqlServerProviderSnapin100 -ErrorAction SilentlyContinue) -eq $null)

{

    #Load the SQL Powershell snapin

    write-host "Loading SQL snapin ..."

    

    $sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"



    if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")

    {

        throw "SQL Server Provider for Windows PowerShell is not installed."

    }

    else

    {

        $item = get-itemproperty -path $sqlpsreg

        $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)

    }





    #

    # Set mandatory variables for the SQL Server provider

    #

    Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0

    Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30

    Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false

    Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000



    #

    # Load the snapins, type data, format data

    #

    Push-Location

    cd $sqlpsPath

    Add-PSSnapin SqlServerCmdletSnapin100

    Add-PSSnapin SqlServerProviderSnapin100

    Update-TypeData -PrependPath SQLProvider.Types.ps1xml 

    update-FormatData -prependpath SQLProvider.Format.ps1xml 

    Pop-Location

}



#SQL Query to be executed

$SQLQuery = "  DECLARE @DatabaseName nvarchar(15) = N'DEMODB'



If db_id(@DatabaseName) is not null

    BEGIN

        

        DECLARE @SQL varchar(max)

            

         SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'

         FROM MASTER..SysProcesses

         WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId    

        

         EXEC(@SQL)



         EXEC sp_detach_db @DatabaseName

        

         If db_id(@DatabaseName) is null

         BEGIN        

            PRINT @DatabaseName + ' is deattached successfully'

         END

    END

Else

    Print 'DB does not exist'"



#SQL Instance

$ServerInstance = "SQLSERVER"                    



#SQL DB Name

$DBName = "Master" 



#Execution

Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $ServerInstance -Database $DBName -verbose

Attaching the SQL Database

This brings the database online.

SQL Query:
sp_attach_db can be used for attaching the database but use of this stored procedure is not advised. 'Create Database' should be used for attaching the database.

If only mdf file is available, then use the below query.

SQL



  CREATE DATABASE SMS_POC

        ON 

        ( NAME = SMS_POCDAT,    

            FILENAME = N'D:\Aravind\SQL\SMS_POC.mdf')

            FOR ATTACH ;

If both mdf & ldf files are available, then the below query can be used.

SQL



DECLARE @DatabaseName nvarchar(15) = N'DemoDB'



If db_id(@DatabaseName) is null

    BEGIN

        CREATE DATABASE DemoDB

        ON 

        ( NAME = DEMODAT,    

            FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\DemoDB.mdf')

            LOG ON

        ( NAME = Demo_log,

            FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\DemoDBlog.ldf')

        FOR ATTACH ;

        

        If db_id(@DatabaseName) is not null

         BEGIN        

            PRINT @DatabaseName + ' is attached successfully'

         END

    END

ELSE

Print 'DB ' +@DatabaseName+ ' already exists'

PowerShell Script

The below script executes the SQL query to attach the database from PowerShell.

PowerShell



#

# Add the SQL Server Provider.

#



$ErrorActionPreference = "Stop"



#Check whether SQL PowerShell snapin is loaded or not

if ( (Get-PSSnapin -Name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue) -eq $null -and 

     (Get-PSSnapin -Name SqlServerProviderSnapin100 -ErrorAction SilentlyContinue) -eq $null)

{

    #Load the SQL Powershell snapin

    write-host "Loading SQL snapin ..."

    

    $sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"



    if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")

    {

        throw "SQL Server Provider for Windows PowerShell is not installed."

    }

    else

    {

        $item = get-itemproperty -path $sqlpsreg

        $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)

    }





    #

    # Set mandatory variables for the SQL Server provider

    #

    Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0

    Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30

    Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false

    Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000



    #

    # Load the snapins, type data, format data

    #

    Push-Location

    cd $sqlpsPath

    Add-PSSnapin SqlServerCmdletSnapin100

    Add-PSSnapin SqlServerProviderSnapin100

    Update-TypeData -PrependPath SQLProvider.Types.ps1xml 

    update-FormatData -prependpath SQLProvider.Format.ps1xml 

    Pop-Location

}



#SQL Query to be executed

$SQLQuery = "  DECLARE @DatabaseName nvarchar(15) = N'DemoDB'



If db_id(@DatabaseName) is null

    BEGIN

        CREATE DATABASE DemoDB

        ON 

        ( NAME = DEMODAT,    

            FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\DemoDB.mdf')

            LOG ON

        ( NAME = Demo_log,

            FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\DemoDBlog.ldf')

        FOR ATTACH ;

        

        If db_id(@DatabaseName) is not null

         BEGIN        

            PRINT @DatabaseName + ' is attached successfully'

         END

    END

ELSE

Print 'DB ' +@DatabaseName+ ' already exists'"



#SQL Instance

$ServerInstance = "SQLSERVER"                    



#SQL DB Name

$DBName = "Master" 



#Execution

Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $ServerInstance -Database $DBName -verbose

Aravind: Run time error - Office 2010 Add-Ins

2011-03-16T00:12:00.000-07:00

Aravind: Run time error - Office 2010 Add-Ins: "One among the common problem faced by office 2010 Add-Ins developer is 'Not loaded. A runtime error occurred during loading of COM Add-in'. ..."

Run time error - Office 2010 Add-Ins

2011-03-16T00:09:00.000-07:00

One among the common problem faced by office 2010 Add-Ins developer is "Not loaded. A runtime error occurred during loading of COM Add-in". Here I'm providing some steps for troubleshooting it.

First I will walk you through the steps if the Add-Ins is not visible/enabled by default. At the end I will cover the trouble shooting.

Go to File --> Options --> Add-Ins. Check whether the Add-In is listed under Disabled Application Add-ins or Inactive Application Add-ins.

Excel Add-Ins

Disabled Add-Ins

We can enable the disabled Add-Ins. Under the Manage dropdown select Disabled Items. By default COM Add-Ins is selected, select Disabled Items from it and then click on Go button next to it. Select the disabled Add-In and click Enable button. Close the Office application and re-open it. Most probably this issue should have been fixed by now. You can verify it by going to File --> Options --> Add-Ins and now the Add-In should be listed under Active Application Add-Ins.

Enabling the Disabled Add-Ins

Inactive Application Add-ins

From Office 2010 UI

Go to File --> Options --> Add-Ins. Under the Manage dropdown select COM Add-Ins and this is only selected by default. Click on manage button next to it. It will open a dialog with the list of Add-ins. If the Add-In is not checked, check it and click on Ok.


Enabling the inactive Add-ins

Try closing and opening the application. This should fix the problem.
There are many ways to get it working. Lets go step by step

Registry key

Here I have taken office Excel as example. It applies to all Microsoft Office application.

Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\Excel\Addins\<<Add-in name>>
Under this registry you can find two string values - Description and FriendlyName. These explain the name and the description of the Add-in. There is not of something which is more important of our discussion. There is a DWORD registry value - LoadBehaviour. Check the value of it. It infers the status of our Add-In.

This link explains the value, their Add-In status, Add-in behaviour along with their description.

Value (in decimal)	Add-in status	Add-in load behavior	Description
0	Unloaded	Do not load automatically	The application never tries to load the add-in automatically. The user can try to manually load the add-in, or the add-in can be loaded programmatically. If the add-in is successfully loaded, the LoadBehavior value remains 0, but the status of the add-in in the COM Add-ins dialog box is updated to indicate that the add-in is loaded.
1	Loaded	Do not load automatically	The application never tries to load the add-in automatically. The user can try to manually load the add-in, or the add-in can be loaded programmatically. Although the COM Add-ins dialog box indicates that the add-in is loaded after the application starts, the add-in isn't actually loaded until it is loaded manually or programmatically. If the application successfully loads the add-in, the LoadBehavior value changes to 0, and remains at 0 after the application closes.
2	Unloaded	Load at startup	The application does not try to load the add-in automatically. The user can try to manually load the add-in, or the add-in can be loaded programmatically. If the application successfully loads the add-in, the LoadBehavior value changes to 3, and remains at 3 after the application closes.
3	Loaded	Load at startup	The application tries to load the add-in when the application starts. This is the default value when you build or publish an add-in in Visual Studio. If the application successfully loads the add-in, the LoadBehavior value remains 3. If an error occurs when loading the add-in, the LoadBehavior value changes to 2, and remains at 2 after the application closes.
8	Unloaded	Load on demand	The application does not try to load the add-in automatically. The user can try to manually load the add-in, or the add-in can be loaded programmatically. If the application successfully loads the add-in, the LoadBehavior value changes to 9.
9	Loaded	Load on demand	The add-in will be loaded only when the application requires it, such as when a user clicks a UI element that uses functionality in the add-in (for example, a custom button in the Ribbon). If the application successfully loads the add-in, the LoadBehavior value remains 9, but the status of the add-in in the COM Add-ins dialog box is updated to indicate that the add-in is currently loaded. If an error occurs when loading the add-in, the LoadBehavior value changes to 8.
16	Loaded	Load first time, then load on demand	Set this value if you want your add-in to be loaded on demand. The application loads the add-in when the user runs the application for the first time. The next time the user runs the application, the application loads any UI elements that are defined by the add-in, but the add-in is not loaded until the user clicks a UI element that is associated with the add-in. When the application successfully loads the add-in for the first time, the LoadBehavior value remains 16 while the add-in is loaded. After the application closes, the LoadBehavior value changes to 9.

Most probably the Add-in will be designed to load at the startup and its value will be 3. If any error occurs the value changes to 2. Try changing this value to 3 and reopen the office application. This can solve the problem. Once again if it is changing to 2 then go for the next step.

Trouble shooting

Set the environment variable VSTO_SUPPRESSDISPLAYALERTS to 0.

steps to create/modify environment variables.
Go to Control Panel\System and Security\System [This is in Windows 7]
Click on advanced system settings.
Click on Environment variables button.
Check VSTO_SUPPRESSDISPLAYALERTS environment variable is present. It can be user specific environment variable or at system level. If its present edit it and set the value to 0. Sometimes it will be 1. Most probably it will not be present. To create it click on New button and then create it with the value as 0.

Now Open the application it will throw the exception dialog box. This can help the developer to fix the bug.

To write the errors to a log file, set the VSTO_LOGALERTS environment variable to 1 (one). This will create a log file where the manifest file for the Add-in is present.
This link explains it in more.

.Net framework version error
If you are getting any .net framework error check that this KB article is published or not - http://support.microsoft.com/kb/982305