If you’ve ever worked with multiple language packs for SharePoint, you’ll know that after you add a language pack to a farm, you’ll have the option of selecting a base language for any new site that is created.
The default language will be that of the site collection, but all installed language packs will be available. All of the system generated text in that site will be presented in the language of the site. This has been true since SharePoint 2007. SharePoint 2010 introduced the MUI (Multilingual User Interface), which, if configured, allowed the user to switch the language of the system generated text. SharePoint 2013 retains the MUI, but the way it is used has changed. However, all versions of SharePoint share a common limitation.
Once a site is created, its language cannot be changed. No way, no how. Well, at least not in any supported way.
I recently encountered a situation where a customer wanted to move their Internet facing site to SharePoint 2013. It was a multilingual site that used variations. However, when it was originally set up, no language packs had been installed. Both variation sites (English and French) were based on English. Although the content in the French variation site was in French, all of the system text was in English. This obviously needed to be corrected as any system text would pop up in English. A significant investment had been made into the content, so re-creation wasn’t our first choice.
An attempt was made to use the export function (using stsadm –o export – I’m old school). While the content exported just fine, it couldn’t be imported into a newly created French site, because the source site was in English. A little bit of web searching found Mirjam’s Van Olst’s article from 2008 on how to change a site’s language. This article was written for SharePoint 2007, and described how the content database could be directly updated to change the language for one or many sites.
Unfortunately, as Mirjam correctly points out, monkeying with the content database voids your warranty, and leaves SharePoint in an unsupported state. She also points out that this approach doesn’t work well for publishing sites, which is what we were dealing with. Our goal was to wind up with a clean system, so this wasn’t going to work for us, at least not as a complete solution.
The beauty of this approach however is that if you’re willing to compromise your content database temporarily, you can literally change the language of the site. Using this approach, we were able to set the language to 1036 (French) for all sites, export the French variation, and then change it back. Now technically, we’ve edited the content database, and rendered it unsupported. However, this doesn’t matter, as we wanted to import the content into new (French) variation site in a new, untouched content database,
This approach works, and unless I’m mistaken, should be totally supported. To be clear the steps taken are:
Back up the source content database (always a good idea)
Open SQL Server Management Studio, Connect to the content database in question , and create a new query. Any of Mirjam’s update statements would work, but this one is easiest
UPDATE dbo.Webs SETLanguage = 1036
Immediately export the site and all subsites. In my case I used stsadm, but of course PowerShell can be used, as can Central Administration.
The source content is successfully migrated into the destination site. Now, technically, because the source database has been directly modified, it’s in an unsupported state, and should be discarded. However, I have yet to see any ill effects. The good news is that the destination content database is pristine, and therefore this approach should be supported.
While we technically haven’t changed the language for an existing site, we have achieved the goal of getting the French language content into a proper French language SharePoint site.
I recently completed a multi-farm consolidation and upgrade from SharePoint 2007 to 2010 for a customer, collapsing three farms into one. The approach was to create a new farm, and to individually do dbattach upgrades of each of the 2007 databases into separate SharePoint applications. Everything went well, including Reporting Services subscriptions, but we ran into a problem with incoming email.
The problem is that while all of the incoming email settings for a library are properly migrated when doing a dbattach upgrade, all of the aliases are stored in the Farm Database (in the EmailEnabledLists table, if you’re interested in looking), and it remains empty. The resultant effect is that you inspect the library properties, and all looks good, and email is properly being delivered to the drop folder. Unfortunately, the incoming email service timer job is looking for emails that correspond to the entries in the EMailEnabledLists table, and there are none.
The solution is to simply turn off incoming email for the affected lists, and then turn it back on. A bit cumbersome maybe, but it does work. However, in our case, our customer had over 100 libraries and wasn’t sure where they all were. A bit of hunting around found a couple of ways to get a list of email enabled libraries through code (here and here).
However, I don’t like writing code if I don’t have to, and decided to have a look in the content databases. Don’t forget the first rule of playing around in the SharePoint content databases, which is don’t. However, reading from them isn’t so bad, and ultimately solves our problem. Enough information is in fact in there to construct a simple query:
Webs.FullUrl As LibraryURL,
AllLists.tp_Title As LibraryTitle,
AllLists.tp_EmailAlias As emailAlias
webs on AllLists.tp_WebID = Webs.Id
AllLists.tp_EmailAlias ISNOT NULL
Opening up SQL Server Management Studio, and running this query on each content database will give you a comprehensive list of all e-mail enabled document libraries.
There is a plethora of instructions out there on upgrading from SharePoint 2007 to SharePoint 2010, but relatively little on doing the upgrade where Reporting Services has been set up in SharePoint integrated mode. Given that there are a few gotchas that you can run into when doing this, I decided to put together this step-by-step, complete with the gotchas.
The most common scenario that will be encountered, given the vintages of the products will be an RS upgrade from SQL Server 2005 to 2008 R2. In addition, the in place upgrade is relatively painless (in the short term….) so I’ll be walking through a DB attach upgrade, which is just as applicable to RS as it is to SharePoint. Finally as I’ve written about previously, in a small farm, it’s likely a better idea to add the Reporting Services bits to a SharePoint front end server, than to add the SQL server to the SharePoint farm, and that will also be a part of our scenario.
The first question to answer is “why bother”? One of the advantages to using RS in SharePoint Integrated mode is that unlike Native mode, the reports, data connections and models are stored directly in SharePoint. It is therefore possible to just create a new RS database, and move forward. However, since subscriptions, schedules, and cache profiles are still in the database, it’s likely worth it to do the upgrade.
Step 1 – Back Up The Asymmetric Key
Reporting Services itself uses 2 SQL databases. One of the databases is for temporary operations, but the other database stores a number of important, and sensitive items for this reason, all sensitive items in the database are encrypted with a key. If we want to get access to these items, we need the key. To do so, we need to back it up from the source server before we move ahead.
Run the Reporting Services Configuration Manager on the source RS server, and select “Encryption Keys”. Click the Backup button, select (and remember) a password, and then save the key to the file system.
Once saved, copy the key file to the destination RS server (likely your SharePoint 2010 front end server).
Step 2 – Back Up the Reporting Services Databases
Run SQL Server Management Studio on the Server where the Reporting Services databases are located. Run full backups of the two RS databases. When complete, copy the backups to the destination SQL server (likely the server that will host the SharePoint 2010 databases).
Step 3 – Restore the Reporting Services Databases
Using SSMS, restore the two databases to the host SQL server. Once restored, it’s likely a good idea to set the recovery model to Simple, and the Compatibility level to SQL Server 2008. These steps aren’t required, but are recommended, unless you have a reason for not doing so.
Step 4 – Run the RS Configuration
If Reporting Services hasn’t yet been installed on the SharePoint server, do so, otherwise, proceed to configuration by running the Reporting Services Configuration Wizard on the destination RS Server. Configure the basic steps, and then when it comes to Database configuration, select the option to choose an existing database.
Select the server where you restored the files in step 3, and select the primary RS database (the one without the word “Temp” in it”).
Complete the configuration wizard.
Step 5 – Connect SharePoint to Reporting Services
From SharePoint Central Administration, select General Application Settings, and then Reporting Services Integration.
Complete the integration configuration, and then select OK
So far so good – now we’re ready for some gotchas. If you now click on the “Set Server Defaults” link in the Reporting Services section, you likely get a rather nasty looking error. You’ll also experience this error if you access ewither of the two RS URLs defined in the RS configuration wizard. The error is:
The report server installation is not initialized. (rsReportServerNotActivated)
This error happens when the server can’t access configuration information, and the most common cause of that is that it can’t decrypt the content. In our case, it can’t because we haven’t yet restored our key.
Step 6 – Restore the Asymmetric Key
On our new RS server, we need to run the RS Configuration Manager, Select Encryption Keys, and then click the Restore button. You will be prompted for the file that you created and copied in Step 1, and this is where remembering the password comes in very handy.
Once this is done, we can close the configuration manager and return to Central Administration. However, now when we try to access access any aspect of RS we get a new error:
The feature: “Scale-out deployment” is not supported in this edition of Reporting Services. (rsOperationNotSupported)
The reason for this error is that when we restored the key, it added an entry in the Keys table in the Reporting Services database, causing RS to think that we’re using multiple Reporting Services servers. This is what’s known as a scale-out deployment, and is only supported in the Enterprise version of Reporting Services. Obviously this isn’t a problem for anyone running Enterprise, but if not, it’s a showstopper.
The way to fix this is to remove the old server entry in the Keys table. Using SQL Server Management Studio, connect to the Reporting Services database, and open the dbo.Keys table. The old entry should be easy to spot as it will have the old server name. Simple delete the row.
Once the offending entry is deleted, RS should be good to go.
7. Fix up the content type names
I have posted about this already, but often, an upgrade will break the Content Type names for the Reporting Services content types. Just follow the steps in this post to clean them up.
8. Reconnect Reports with Data Sources And/Or Republish
In addition, moving connection files and reports around in SharePoint can cause them to be disconnected from each other, or for the connection files to be disabled. It’s a good idea to navigate to all of your reports to make sure that they are connected, or better yet, to republish from the source if you had previously used BIDS to publish reports.
If you’ve worked at all with SharePoint declarative workflows (the ones that you use InfoPath to create), or others, such as Nintex workflows that are based on them, you are undoubtedly aware of their ability to log items to the history list. These items are those that appear in the Workflow History section of the workflow status page.
What may be less commonly known is how this works. This history list is really just a view of items that are contained in a hidden list on the site, and whenever an item is logged to the list, it gets created in the history list. For regular SharePoint workflows, this list is located at http://yoursiteurl/Lists/Workflow History, and for For Nintex workflows, you can find it at http://yoursiteurl/Lists/NintexWorkflowHistory. So, why does this matter? Well, if you need to audit what is has happened with your workflows, this is where the information is contained.
There is, however a catch. By default, SharePoint will run a Timer job named “Workflow Auto Cleanup” daily that will remove all of the the tasks associated with a workflow, and all of the history links for workflows that are over 60 days old. This is done for performance reasons. Well, unless your audit requirements only go back two months, this isn’t going to work for you.
Try doing a search for “Workflow History” and you’ll see that this has caused a number of issues (especially for those that have found it out after the fact. The good news for those people is that the workflow history list isn’t actually purged (which is also bad news, as we’ll see shortly), and those links can be recreated through reporting. However, the most common guidance found on this topic is to simply disable the automatic cleanup job, as outlined in this very poorly named Technet article.
The problem with disabling the job is that performance will suffer, potentially badly. Assume that we have an approval workflow that runs on a list that will receive 2500 approvals annually. This is a reasonably sized list (for SharePoint 2010). Now lets also assume that during the life of the workflow, 10 items get logged to the history list. This means that in a given year, 25,000 items are being logged to the history list, which is beyond the default list view threshold of administrators, and would be considered a very large list.
What is needed is a way to balance the auditing requirements with the list size constraints of SharePoint. 25,000 items may be a large SharePoint list, but it’s trivial to a relational database like SQL. What the remainder of this article will do is to discuss how to use Microsoft’s Business Intelligence tools to extract workflow history data into a data warehouse, and then safely purge it from the workflow history list. This will be a lengthy one.
Step 1 – Extract And Load
In my opinion, one of the most underutilized tools in Microsoft’s arsenal is SQL Server Integration Services (SSIS). Almost every SharePoint site has it, and very few know about it. It is Microsoft’s ETL (Extract, Transform, and Load) tool, and it is used for taking data from source systems, performing operations on it, and loading it into a destination system, which is typically a data warehouse in the form of one or more SQL databases. This is precisely what we need to do with our Workflow History data. You can read more about SSIS here.
The problem however is that SSIS does not support SharePoint list data as a data source. Yes, ultimately all SharePoint data is stored in SQL content databases, but we all know that we’re supposed to stay out of there. SharePoint data should only be accessed via UI constructs, the SharePoint APIs, or the SharePoint web services.
Happily, a Codeplex project was created several years ago that adds both source and destination SSIS adapters for SharePoint list data, and yes, it works well with SharePoint 2007 data. What this project does is to encapsulate calls to the SharePoint web services into SSIS data adapters. Because it uses the SharePoint web services (not the API), there is no requirement for the SharePoint bits to be installed where it is being run.
What we want to be able to do is to maintain a complete log of workflow history. We also want to be able to keep the history in SharePoint for a period of time (60 days by default), and then be able to purge it, knowing that it’s secure in the data warehouse. Therefore, we need to take an initial dump of the data, and then be able to add only new items to it. The design of the data warehouse will also support multiple site history lists.
The solution will consist of 2 tables in a SQL data warehouse (a staging table and the actual archive table). The SSIS package will perform the following steps:
Empty the staging table
Extract the entire Workflow History List (SP) into the staging table (SQL)
Query the archive table for the most recent entry
Extract all items from the staging table more recent than the entry in step 3 into the archive table, and add in a site identifier.
First, open up Business Intelligence Development Studio (BIDS). BIDS is really just Visual Studio with all of the SQL BI project types added, and is normally installed when SQL is installed. If not, you can install it from the SQL media. You do not need SQL server installed to use it, but it does have some advantages.
From the Business Intelligence Projects section, select “Integration Services Project”, and give it a solution and project name. You’ll then be presented with the SSIS design canvas. The first thing that you’ll want to do is to create two connection managers – one for SharePoint, and one for SQL. In the Connection Managers pane right click anywhere in the window and select “New Connection”
Scroll down, and select SPCRED – Connection manager for SharePoint connections, give it a name, and select the credentials. If you use the credentials of the executing process, it will use your credentials when you test it, but the credentials of the SQL Server Agent process if you schedule it to run automatically. Alternatively, you can enter the credentials of a proxy account, which is what I typically do. Repeat this process, only this time select OLEDB and configure the connection to your SQL Data Warehouse database (if you haven’t already done so, you’ll need to create a SQL database to house the archive).
Next, from the Toolbox, drag a Data Flow Task onto the Design surface. Your surface should look something like below:
Double click on the Data Flow task, and the Data Flow window will open (you can also click on the Data Flow Tab). Here, from the toolbox, drag a SharePoint List Source, and an OLE DB Destination task onto the surface. Double click on the SharePoint List source, then click in the area to the right of the SharePoint Credential Connection, and set the Connection manager to the manager that you created above.
Next, click on the Component properties tab, and enter valid values for the SharePoint source site URL, and the list name. The List name will either be WorkflowHistory for standard SharePoint workflows, or NintexWorkflowHistory, for Nintex workflows.
Click OK. Next, grab the green arrow at the bottom of the SharePoint List source, and connect it to the OLE DB Destination. Double click on the OLE DB Destination, and select the New button beside the Name of the table field. What this allows us to do is to create our Temp Table in the Data Warehouse with the appropriate schema for our Workflow History List. Once the create table widow is open, simple change the name of the table to what you want (in this case wfhStaging).
As soon as you click OK, the table is created in SQL. Next, click the Mappings tab on the left, and confirm that all of the fields are mapped correctly from the SharePoint list, to the SQL table. No changes should be required. When complete, click OK, and the data flow is ready for testing. From the BIDS debug menu, select Start debugging. After a pause, the process will run, and the boxes will turn yellow and green as the process executes. If all works properly, you will see something like the screen below:
Both boxes green indicate that the process completed successfully, and there will be an indicator showing the number of rows that were transferred. You can confirm this by opening up SQL Server Management Studio, selecting your Data Warehouse database and running the following query:
SELECTCount(id) From wfhStaging
At this point, we need to stop our debugging process and switch back to the Control Flow tab. Given that we want to repopulate the staging table whenever we run this package, we need to first clear it at the beginning of the run. Drag an “Execute SQL Task” from the toolbox onto the design surface above or to the left of the data flow task. Us its arrow to connect it to the Data Flow task, and then double click on it. Select your OLE DB connection as its connection property and enter the following SQL (substituting your table name) as its SQL Statement:
Next, we will need to create and populate our actual archive table. To do this, drag another Data Flow task onto the design surface. Connect the output from the first data flow task to it and then double click on it. Drag an OLEDB Source, a derived column, and an OLEDB destination onto the design surface.
We want to be able to store the workflow history for multiple sites in the same data warehouse table. To do this, we need to add another identifier column to the schema of the workflow history list that will uniquely identify the source site. In our case we will use the relative site URL. The derived column action will add this column to each row as it is processed.
Configure the OLEDB Source to read from the Staging table. Then, connect the OLEDB Source to the Derived Column action with the green arrow. Double click on the derived column action. Under the Derived Column Name, enter the name of the new column. Leave the Derived Column action as “add as new column”, and for the expression we will simply use a literal string for the site relative site URL. When complete, the action should appear as below.
Click OK to close the dialog, and then connect the derived column action to the OLEDB Destination action with the green arrow. Double click the OLEDB Destination action and repeat the steps taken above to create the staging table, only this time, you’ll create the actual archive table. This time, when you click on the Mappings tab, note that the SiteURL column has been added at the bottom. Don’t run debug at this point, as it will run the entire package. Click back on the Control Flow tab, right click on the new Data Flow action, and select Execute Task. Just that task will run, and if you move back to the Data Flow tab, you should see that the same number of rows have been added to the archive table.
Now we need to ensure that only the new columns from the staging table are moved into the archive table. To do this, we will change the select statement in the OLEDB source of the second data flow task. Firstly, we’ll need to know what the date/time latest record in the archive table for this site. The SQL statement for this looks like
SELECTMAX(Modified) From wfhArchive
So therefore, we can embed that statement into the select statement for our staging table. However, we still need to accommodate the case where there are no records in the archive table, where the above statement returns a NULL value. To deal with this, we can use the ISNULL TSQL function, and our complete staging table select statement will be
SELECT * FROM wfhStaging
Where Modified >
(SELECTMAX(Modified) From wfhArchive
Translated into English, this basically says “Find the value for modified of the most recent record of any items with SiteURL set to /SalesPersonChangeRequest. If you don’t find any, set it to 1900-01-01. Then, get me everything from the staging table with a modified date more recent.”
Now that we have our SQL, we need to modify our OLEDB Source action. Double click on it, and then change the Data access mode from “Table or view” to “SQL command”. Then, add the select statement to the SQL command text window. At completion, the window should appear as follows:
Once done click back to the Control Flow tab, and then start Debugging (you can also just press the F5 key to start debug). The first Data Flow task should write all of the source records to the table, and the second should write none (assuming nothing has happened to the source since you did the initial extract. You can try deleting some of the records from the archive table, and rerunning the package – they should get replaced. That was step 1.
Step 2 – Schedule the package
Now that we have our package, we want it to run periodically (usually nightly). We do this by deploying the package to the server, and then scheduling to run with the SQL job agent.
To deploy the package, we need to first create a deployment utility for it. To do this, we must first select the Project in the Solution Explorer pane, and then select Project-Properties from the menu. The Configuration Properties window is then opened. In the left pane, select the Deployment Utility tab, and ensure that the CreateDeploymentUtility is set to True.
Also – take note of the DeploymentOutputPath value.
run the deployment utility for it. The deployment utility is stored in a subfolder of the package project. You can find the folder of the project by selecting the project in the Solution Explorer pane, and then examining its FullPath property in the Properties pane. Open the project path in Windows Explorer, and then navigate to the DeploymentOutputPath as noted above. In that folder, you’ll find a file named yourprojectname.SSISDeploymentManifest. When ready to deploy, double click on it, and the Deployment wizard will start.
The deployment wizard is straightforward and self explanatory. You’ll want to select “SQL Server deployment” on the first screen, then the SQL server that you wish to deploy to (usually (local) ), and select a location for the Package Path (the root is likely fine). Once the wizard is complete, you are ready to schedule the package.
Open Up SQL Server Management Studio, and connect the destination server. If the SQL Server Agent service is not running, start it (you’ll want to make sure that it is started automatically). Expand the Agent node, and then expand the Jobs node. Right click on Jobs, and select New Job.
Give the Job a name, then click on the Steps tab. Click the New button to create a new step, and give it a name. In the Type dropdown, select “SQL Server Integration Services Package”. In the General section, select the SQL server that holds the package, and then use the ellipsis in the Package field to select the package you deployed above.
Next, click the Schedules tab, click the new button, give the schedule a name, and select when you want the job to run. Save the schedule, and then save the job (click OK). Your job should now appear in the Jobs folder. To test it, right click on it and select “Start Job at step”. The job will run and you will see its progress in a dialog.
There are many options for scheduling SSIS jobs, and for error handling, and I would strongly recommend investigating them.
Step 3 – Purge the Workflow History Data
As mentioned above, the workflow cleanup job removes workflow history associations, but does not actually delete the items from the list, allowing that list to grow large. If you use Nintex, there’s a Nintex command that will take care of this for you:
This command is run on a front end server. To keep things up to date it would need to be scheduled. However, if you’re using out of the box workflows, there is no equivalent command. You could just access the history list and remove old data, but since SharePoint has built in tools for this, I recommend using them. These features are contained in the Information management policy settings of any list.
Open the Workflow history list (your site url + Lists/WorkflowHistory or NintexWorkflowHistory). Open the List settings page, and select “Information management policy settings” in the Permissions and Management section. If you don’t see this option, you may need to enable the relevant features.In the Content type policies, select the Workflow History content type, and then select “Enable Retention”. Once enabled, you will be able to select “Add a retention stage”.
The retention stage is what we will use to delete the workflow history items (which, given the name, is somewhat counter-intuitive, don’t you think?). Date occurred is when the event occurred, so it is likely our best time indicator, and I would suggest a period of time at least double to the automatic cleanup task, which is 60 days. Finally, we want the item to be deleted at this point, so we select “Permanently Delete” from the Action dropdown. When complete, the stage will appear as follows:
Once we save our policy, the expired items will be deleted the next time the timer jobs run.
And that’s all there is to it!
Now that we’ve taken the data out of SharePoint, it’s no longer obviously available to end users. If this is important, we will need to build some Reporting Services reports, and integrate them back into the appropriate locations in SharePoint. This will (hopefully) be the subject of an upcoming post.
I have been doing quite a few 2007-2010 upgrades lately, and suffering the appropriate slings and arrows. A recent upgrade resulted in a few issues, the strangest one was that Microsoft Access could no longer open a SharePoint list.
For quite some time now, Microsoft has been able to read and write data from SharePoint lists as if they were active Access tables. This is distinct from Access Services, which ships with the Enterprise version of SharePoint Server. Access Services lets you “convert” your entire Access application to a SharePoint site at which point the Access client is no longer required (for a user).
Our situation was much simpler. We were dealing with a Power user that was good with Access, and had leveraged the list read/write capability quite heavily with 2007. However, after the upgrade, Access 2007 couldn’t open some of the lists that it could previously. Compounding this problem was that Access 2010 didn’t have this problem on the lists in question. The browser could also open these lists just fine.
The answer to this one came from what appeared to be a different problem. Some of the other lists in the site couldn’t be opened by the browser. Instead, the user received the message “The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator”:
SharePoint has a bad reputation for “Unknown Error” messages, but this one is really quite good. This one pointed squarely at the list throttling features available in SharePoint 2010 that of course weren’t there in 2007. Basically, 2010 allows an administrator to throttle, or prevent poorly performing functions from slowing down the system for everyone. One such expensive operation is performing lookups, and the default limit is set to 8.
Dina Ayoub has a good post here on the throttling features if you would like to learn more, but the important thing to note here is that this setting affects not just lookup fields, but Person/Group and Workflow Status fields as well, so if you have 8 or more of them, the list will simply stop working.
This setting is scoped to the application level, so if it is changed, you affect all site collections in that application. (It also means that you can’t change it at all in Office 365.) You set it through the Resource Throttling settings in Central Administration. Once in CA, click on Application Management, highlight the application to be changed, and in the General Settings dropdown, pick Resource Throttling.
Scroll down to the section titled “List View Lookup Threshold”:
Here, you can simply increase its value to where you need it.
Changing the values should be done with considerable care. These throttling features were implemented for very good reasons, and changing them risks overloading your SQL server. A much better approach would be to go back and rethink the design of your list, if that’s an option. If it isn’t then this is a decent plan B. You can always buy more hardware…….
So this fixed our post upgrade list issue in the browser, how does this relate to our Access problem? Well, it turns out that they were one and the same, just manifesting differently. It seems that Access does something when it opens a list that adds a few more lookup type items to the Query, or at least it behaves that way. It also appears that Access 2010 and Access 2007 behave differently in this regard. In the end, increasing this value sufficiently solved the Access problems.
I haven’t found anything definitive out there, but anecdotally at least, you should be aware that when you use Access to open up a SharePoint list, you pay a “”List View Lookup Threshold” penalty.