Skip to content
/

In a previous post I have written about Using the people picker over a one-way trust. In this post I use STSADM commands as there are no other ways to configure this. A downside of the STSADM command is your domain password being visible on the command prompt in clear text for everybody to read, or to retrieve from the command line history.

SharePoint 2010 introduces several cmdlets to replace the “old” STSADM commands. Microsoft has posted an overview of the STSADM to Windows PowerShell mapping. However the commands for configuring the people picker are not available.

In a previous post I have written about . In that post I use STSADM commands as there are no other ways to configure this. A downside of the STSADM command is your domain password being visible on the command prompt in plain text for everybody to read.

With SharePoint 2010 Microsoft introduces several cmdlets to replace the “old” STSADM commands. But looking at the you will see the commands for configuring the people picker are not present.

Creating my own script

PowerShell contains the which uses a dialog to request credentials from the user and stores the password in a . This triggered me to write a PowerShell script which will work the same as STSADM -o setproperty -pn peoplepicker-searchadforests, but instead of typing the credentials on the command line it will use the credential dialog for every trusted domain.

As written in my previous post the configuration is done in two steps.

SetAppPassword

First you need to create a secure store for the credentials. This is done by executing the SetAppPassword command on every server in your SharePoint Farm with the same password.

STSADM

stsadm -o setapppassword -password <password>

PowerShell

Set-AppPassword "<password>"
function Set-AppPassword([String]$password) {
  $type = [Microsoft.SharePoint.Utilities.SPPropertyBag].Assembly
                           .GetType("Microsoft.SharePoint.Utilities.SPSecureString")
  $method = $type.GetMethod("FromString", "Static, NonPublic", $null,
                                                                 @([String]), $null)
  $secureString = $method.Invoke($null, @($password))
  [Microsoft.SharePoint.SPSecurity]::SetApplicationCredentialKey($secureString)
}

PeoplePickerSearchADForests

The second step is to register the (trusted) domains to be visible in the people picker. Remember this setting is per web application and zone.

STSADM

stsadm -o setproperty -url <url> -pn "peoplepicker-searchadforests" -pv
  "forest:<source forest>;domain:<trusted domain>,<trusted domain>\<account>,<password>"

PowerShell

Set-PeoplePickerSearchADForests "<url>" "forest:<source forest>;domain:<trusted domain>"
function Set-PeoplePickerSearchADForests([String]$webApplicationUrl,
                                         [String]$value) {
  $webApplication = Get-SPWebApplication $webApplicationUrl

  $searchActiveDirectoryDomains = $webApplication.PeoplePickerSettings
                                                      .SearchActiveDirectoryDomains
  $searchActiveDirectoryDomains.Clear()

  $currentDomain = (Get-WmiObject -Class Win32_ComputerSystem).Domain

  if (![String]::IsNullOrEmpty($value)) {
    $value.Split(@(';'), "RemoveEmptyEntries") | ForEach {
        $strArray = $_.Split(@(';'))

        $item = New-Object Microsoft.SharePoint.Administration
                                         .SPPeoplePickerSearchActiveDirectoryDomain

        [String]$value = $strArray[0]

        $index = $value.IndexOf(':');
        if ($index -ge 0) {
            $item.DomainName = $value.Substring($index + 1);
        } else {
            $item.DomainName = $value;
        }

        if ([System.Globalization.CultureInfo]::InvariantCulture.CompareInfo
                                       .IsPrefix($value, "domain:","IgnoreCase")) {
            $item.IsForest = $false;
        } else {
            $item.IsForest = $true;
        }

        if ($item.DomainName -ne $currentDomain) {
            $credentials = $host.ui.PromptForCredential("Foreign domain trust"
              + " credentials", "Please enter the trust credentials to connect to"
              + " the " + $item.DomainName + " domain", "", "")

            $item.LoginName = $credentials.UserName;
            $item.SetPassword($credentials.Password);
        }

        $searchActiveDirectoryDomains.Add($item);
    }

    $webApplication.Update()
  }
}

Using the script

I have attached the script so you can use it in any way you want. You can put the commands in you own .ps1 file, or load the script in your current session using the following syntax:

. .\<path to file>PeoplePickerSearchADForests.ps1

(yes, that’s a dot, then a space, then the path to the script)

PeoplePickerSearchADForests.zip

/

With the introduction of LINQ the difference between writing code for accessing a lists of objects in memory and accessing a list of data in an external data source like SQL is vanishing. Combining a in memory with a external list in a single query was not yet possible. With the introduction of .NET Framework 4.0 this has changed.

With the introduction of LINQ the difference between writing code for accessing a lists of objects in memory and accessing a list of data in an external data source like SQL Server is vanishing. Combining an “in memory” list with an external list in a single query was not yet possible. With the introduction of .NET Framework 4.0 this has changed.
In this post I want to filter my SQL data using a list of integers I have stored in memory.

Data model

I have created a small data model as illustration. This will be used for the examples. Follow the link on the image below for a larger version. The model is part of the sample code.

Visual representation of the datamodel as described above.
Data model

IQueryable.Join

The first option you can use is to use the .

var entities = new DemoModelContainer();
var countryIds = Enumerable.Range(1, 3);

var query = from i in entities.Items
            from c in i.Countries
            join cid in countryIds on c.Id equals cid
            select i;

var items = query.ToList();

When you look at the generated SQL Statement (I love IntelliTrace!) you can see a temporary table is created by using the .

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name]
FROM
    [dbo].[Items] AS [Extent1]
    INNER JOIN [dbo].[CountryItem] AS [Extent2]
                               ON [Extent1].[Id] = [Extent2].[Items_Id]
    INNER JOIN (
        SELECT
            [UnionAll1].[C1] AS [C1]
        FROM (
            SELECT 1 AS [C1] FROM (SELECT 1 AS X) AS [SingleRowTable1]
            UNION ALL
            SELECT 2 AS [C1] FROM (SELECT 1 AS X) AS [SingleRowTable2]
        ) AS [UnionAll1]
        UNION ALL
        SELECT 3 AS [C1] FROM (SELECT 1 AS X) AS [SingleRowTable3]
    ) AS [UnionAll2] ON [Extent2].[Countries_Id] = [UnionAll2].[C1]

What happens is that 2 values are combined in a set, this set is extended with the next value, this set is extended with another value, and so on, and so on, until all values are present.

This works nicely, but as you can imagine, the SQL Query will grow rapidly when the IEnumerable list gets larger.

When you change the following:

-var countryIds = Enumerable.Range(1, 3);
+var countryIds = Enumerable.Range(1, 50);

you will run into a SqlException:
Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.
SQL Server has a hard-coded limit of nesting queries, it doesn’t matter which version or edition of SQL Server you use.

IEnumerable Contains

Joining tables is one way of filtering data, an alternative is to use the . This can be done by using the .

var entities = new DemoModelContainer();
var countryIds = Enumerable.Range(1, 3);

var query = from i in entities.Items
            from c in i.Countries
            where countryIds.Contains(c.Id)
            select i;

var items = query.ToList();

When you look at the generated SQL Statement (Still loving IntelliTrace!) you can see now the is used.

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name]
FROM
    [dbo].[Items] AS [Extent1]
    INNER JOIN [dbo].[CountryItem] AS [Extent2]
                               ON [Extent1].[Id] = [Extent2].[Items_Id]
WHERE
    [Extent2].[Countries_Id] IN (1,2,3)

This SQL Query is much smaller and filtering against a larger set will not give you the exception.

This is how the WHERE statement looks when filtering on 50 values:

WHERE [Extent2].[Countries_Id] IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,
                                   16,17,18,19,20,21,22,23,24,25,26,27,
                                   28,29,30,31,32,33,34,35,36,37,38,39,
                                   40,41,42,43,44,45,46,47,48,49,50)

Conclusion

You can use both methods to filter SQL data with a list in memory. But when choosing for the join method you should be really sure your query will not reach the maximum level of nesting.

I prefer to use Contains method.

Although I used a many-to-many relation in this example, the same applies for a one-to-many scenario.

Sample code

I’ve added a small Visual Studio 2010 project so you can see the SQL statements for yourself.

You will need SQL Server Express 2008 R2 installed on your machine to run the code.

IQueryableIEnumerableDemo.zip

Filed under C#, SQL
Last update:
/

With SharePoint 2010 the amount of databases on your SQL server has grown quite a bit. By default most of these databases have their recovery model set to 'FULL'. After some time you will discover you're running out of space.

With SharePoint 2010 the amount of databases on your SQL Server has grown quite a bit. By default most of these databases have their recovery model set to FULL. After some time you will discover you’re running out of space.

The problem

Most likely the problem lies with the transaction logs of your databases. With the recovery model set to FULL they will keep storing every transaction until you make a backup. Chances are you don’t configure a backup plan for you development environment as most development databases don’t need a backup as your sources will be stored in a source control system.

The (manual) solution

To solve this problem you can change the recovery model of each database by hand. For this you can open SMSS (SQL Server Management Studio), open the properties screen for a database and navigate to the options tab. There you will find the recovery model option.

Database Properties screen with the Recovery model set to 'Simple'
Database Properties screen with the Recovery model set to Simple.

Saving this change will empty your transaction log. But it will not shrink the physical file on disk. To shrink this file you can look at the Shrink task.

The context menu's to shrink the size of the log files
The context menu's to shrink the size of the log files.

The (automated) solution

Executing this step for every database manually is quite some work. So you want the easy solution.

The following TSQL script will change the recovery model for every database to Simple and shrinks the database.

USE [master]
GO

DECLARE @dbname SYSNAME
DECLARE @altercmd NVARCHAR(1000)
DECLARE @shrinkcmd NVARCHAR(1000)

DECLARE [dbcursor] CURSOR FOR SELECT [name] FROM sysdatabases

OPEN [dbcursor]
FETCH NEXT FROM [dbcursor] INTO @dbname

WHILE
  @@FETCH_STATUS = 0
BEGIN
  IF
    (SELECT DATABASEPROPERTYEX(@dbname, 'RECOVERY')) != 'SIMPLE'
    AND
    @dbname != 'tempdb'
  BEGIN
    SET @altercmd = 'ALTER DATABASE "' + @dbname + '" SET RECOVERY SIMPLE'
      EXEC (@altercmd)

      SET @shrinkcmd = 'DBCC SHRINKDATABASE ("' + @dbname + '")'
      EXEC (@shrinkcmd)

      PRINT @dbname
  END

  FETCH NEXT FROM [dbcursor] INTO @dbname
END

CLOSE [dbcursor]
DEALLOCATE [dbcursor]