Archive for the ‘SQL’ Category

Okay, so you try to search and your getting dreaded “Internal Server error exception” message seen below. This is not very helpful at all. However, the chances are, your search databases have somehow became corrupt


To resolve this, I carried out the following steps.

Note: All of the below assumes that you have called your Search Service Application the default name of Search Service Application

Firstly, you need to delete the SharePoint Search Service Application. You can try doing this from Central Admin, but I found that this just hangs and does nothing. So, in remedy, fire up a SharePoint PowerShell – remembering to run as Administrator (seen below)


Once you have PowerShell open you need to find out the ID of Application and delete it.

You can do this in two seperate commands:

Get-SPServiceApplication |?{$ -eq "Search Service Application"}

Remove-SPServiceApplication –Identity <ID of Above> –RemoveData

or you can assign it to a variable and do it as one command

$search = Get-SPServiceApplication |?{$ -eq "Search Service Application"}
Remove-SPServiceApplication -Identity $search.Id –RemoveData

You should now see the following confirmation.


Choose Y to delete it.

Now, just to check that all search references are gone, run the following command

Get-SPDatabase | Where-Object {$ -like "Search_Service_Application*"} | select id, Name

You should not have anything returned here, if you have run the command below.

ForEach($db in Get-SPDatabase |  Where-Object  {$ -like "Search_Service_Application*"})

Before going any further you may want to go into SQL Server Management Studio and delete the three orphaned databases created for the search:


However, I’ll leave that decision up to you.

After all of the above has been done and you are satisfied that all the references have gone you can now re-configure the Search Service Application as described in the MSDN documentation

Another resource that i have found very helpful is An A-Z Index of Windows PowerShell 2.0 commands

Okay, here’s the scenario:
You’ve set up the User Profile Synchronisation using the Microsoft Documentation, and your now at the situation where have the Active Directory Accounts imported into SharePoint. Now, you want to do a data load and match up people picker fields to the actual profiles in the SharePoint format of ID;#FirstName LastName. However, to do this you need to find out what the Id of each user is.
So, here how I went about getting them.
Firstly, the profile import throws all the users into the Profile DB you have set up in the previous step. So, open SQL Server Management Studio –> Profile DB  and return all the user profile records by running the following query
Insert List Item Method
  1. SELECT [PreferredName] ,[Email]
  2. FROM [Profile DB].[dbo].[UserProfile_Full]
Next step is to copy all the returned email addresses into a file and save it somewhere, were going to use the data in a minute.
Now create an console application, remembering to set the framework to 3.5 and the target processor to x64 as I’ve described in a previous blog.
Now, add the text file to the root of the project as an embedded resource.
and the copy in the following code into your program.cs . You will need to change the constant values to suit your environment and add
using Microsoft.Office.Server;
using Microsoft.SharePoint;
Insert List Item Method
  1. class Program
  2. {
  3. private const string siteUrl = http://sharepoint/&#8221;;
  4. private const string subSite = “hr”;
  5. private const string outputFileLocation = @”C:\Temp\SharePointIds.txt”;
  6. static void Main(string[] args)
  7. {
  8. Dictionary<string, string> userIdEmailMapping =  GetUserProfiles();
  9. using (StreamWriter writer = new StreamWriter(this.outputFileLocation))
  10. {
  11. foreach (var item in userIdEmailMapping)
  12. {
  13. StringBuilder sb = new StringBuilder();
  14. sb.AppendFormat(“{0},{1}”, item.Key, item.Value);
  15. writer.WriteLine(sb.ToString());
  16. }
  17. writer.Flush();
  18. }
  19. }
  20. private static Dictionary<string, string> GetUserProfiles()
  21. {
  22. Dictionary<string, string> userIdEmailMapping = new Dictionary<string, string>();
  23. using (SPSite site = new SPSite(this.siteUrl))
  24. {
  25. using (SPWeb web = site.OpenWeb(this.subSite))
  26. {
  27. using (StreamReader reader = new StreamReader(Assembly.GetExecutingAssembly().GetManifestResourceStream(Assembly.GetExecutingAssembly().GetName().Name + “.UserEmails.txt”)))
  28. {
  29. while (!reader.EndOfStream)
  30. {
  31. var email = reader.ReadLine();
  32. try
  33. {
  34. SPUser user = web.AllUsers.GetByEmail(email);
  35. userIdEmailMapping.Add(user.Email, user.ID.ToString());
  36. }
  37. catch (Exception ex)
  38. {
  39. userIdEmailMapping.Add(email, ex.Message);
  40. }
  41. }
  42. }
  43. }
  44. }
  45. return userIdEmailMapping;
  46. }
  47. }
Compile the exe, copy it your server and run it as administrator and it should populate the output file in a comma separated list with the ids which are in use.
However, there is one snag.
SPWeb.AllUsers  only returns the user who have actual been parsed in the site. So, to get round this, create a new column on list as a people picker and allow it to hold multiple values. Then, take your list of AD users, who don’t have Id yet and copy them into the people picker and save the list item. This should now create id for those users. I’m not sure if there is a limit for the people picker fields but I successfully added 30. Massive domains may be a bit harder though.
Oh well that’s about it. Cheers Winking smile

When trying to change a table using design view SQL 2008 R2 I received the error shown in the screen shot below.

The changes you have made require the following tables to be dropped and re-created

I can understand the reason for this, but sometimes when you are in development, its just a pain.   So, how do you switch it off?

Go to tools –> Options and uncheck Prevent  saving changes the require table re-creation.


Note: Don’t do this if you have millions of rows, it will take ages.


When using SQL Server Reporting Service SSRS in integration mode with MOSS you come across the following error when trying to deploy reports using BIDS to a Document Library:  “The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel”

This problem is cause by the fact that on a default install of SSRS 2008 R2 in SharePoint integration mode the SecureConnectionLevel element of the RSReportDesigner Configuration file is set to a value of 2.



For the current configuration to work you need a SSL certificate. However,  if your in an intranet environment, as we were, you can get your reports deployed by setting the value to “0” and try again

<Add Key="SecureConnectionLevel" Value="0" />

Okay, I can’t claim full credit for this find, but I’m sure my colleague won’t mind me blogging this to save trawling Google again!

The correct collation for SharePoint in the English speaking world is  Latin1_General_CI_AS_KS_WS. However, if your collation is not set to this is can be changed by running the command below.

Note: Don’t make the mistake I did! The Instance name is not the name of the server, but the actual instance. This is MSSQLSERVER by default.


Exporting SQL 2008 to earlier version

Posted: 25th January 2010 in SQL

To allow an earlier version of SQL Server to recognise the data from 2008 it is necessary set the database compatibility level to the correct version.

Transact-SQL Method
ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

eg. to use in 2005 run


The versions are as follows:

80 : 2000
90 : 2005
100: 2008

Ctrl + Shift + M on SQL 2008

Posted: 26th November 2009 in SQL


When creating a new stored procedure in SQL 2008 Management Studio the following text displayed in the header of the stored procedure:

— Use the Specify Values for Template Parameters
— command (Ctrl-Shift-M) to fill in the parameter
— values below.
However, when you try this nothing happens!
In Management Studio Go to the menu options: Tools -> Options, then in the select Environment -> Keyboard and select SQL Server 2000 from the dropdown as seen in the screen shot below:
SQL 2008 Management Studio - Keyboard Options
Now, when you press CTRL-Shift+M you should see the form shown below allowing you to specify the values for the stored procedure.
Specify Store Procedure Values for Template Parameters