SP2010 and Managed Metadata and &

April 16th, 2013 in SharePoint. No Comments ».

I’ve been doing some work with dynamically populating a Managed Metadata term store using the API and data stored in a list.

The code was quite simple – check to see if a term existed, and if not, add it. The first pass of this routine would work fine, but then the second pass would crap out with an issue:

There is already a term with the same default label and parent term.

After a bit of debugging, I figured out it was falling over on a term that contained an & (ampersand) in the name. After a further bit of debugging (converting the character to HEX) it became apparent that it’s storing it as the wide (*pretty) rendition of the ampersand. And after some further research, it would appear that this is by design!

The name value will be normailized to trim consecutive spaces into one and replace the & character with the wide character version of the character (\uFF06). The leading and trailing spaces will be trimmed. It must be non-empty and cannot exceed 255 characters, and cannot contain any of the following characters ; “<>|&tab.

There are two approaches to resolving this, depending on your needs. For simple comparisons, you can use the TaxonomyItem.NormalizeName method to show you what your name will look like in the DB. You can also do some character replacements to convert the chars back. Check the links below for some code if you need it.

Thanks to Mike and Nick for the info.

SharePoint 2010 + DocumentSets + OfficialFile.asmx max file size

March 7th, 2013 in SharePoint. No Comments ».

Tripped over this issue today. This post explains exactly what’s going on – namely that when using the OfficialFile web service (for e.g., records management), the 50mb upload file size is in place, irrespective of any setting the Web Application General Settings.

Fix:

  1. Make sure that you have set the max file size on the web application
    Central Admin –> Manage Web Applications –> Select the Web Application –> General Settings –> Maximum Upload Size
  2. Open the following file and edit on each SharePoint server:
    C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI\web.config
    Add the following section, then save and close

Didn’t even need to recycle the web services website/app pool. Perfect!

Current user is not a farm administrator – redux

February 5th, 2013 in SharePoint. No Comments ».

A while ago, I posted about an issue with deploying a custom SharePoint retention policy where activating a feature was failing due to a permissions issue.

I fell over this issue again recently, but despite following all the advice in that post, I still couldn’t get things to activate and I didn’t really want to start giving all known service accounts local admin and DBO on everything in sight. So instead, I used a quick Powershell workaround. This works because PS always runs in the context of the Farm Admin, so I assumed it must have the relevant permissions. This script will find the feature you want to activate, and then activate it. Success is no message returned!

Get-SPFeature | where {$_.DisplayName -like ‘Yourfeat*} | Enable-SPFeature -url http://yoururl

Just ensure Get-SPFeature only returns a single feature.

The OLE DB provider Microsoft.Ace.OLEDB.12.0 for linked server (null) reported an error. Access denied.

November 30th, 2012 in Databases. No Comments ».

I’m noting this here primarily because I’m almost certain to trip over this again in the future. If you want to use the ACE or JET providers to read an e.g., Excel spreadsheet to an e.g., SQL Server table, then something like this is possible. Assuming you had a simple Excel workbook (Book1.xlsx) and in Sheet1 you had

Col1 Col2
Test Test2

You could use this to load it:

SELECT * INTO #tmpTable FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′,’Excel 12.0 Xml;HDR=YES;Database=C:\Temp\Book1.xlsx’,'SELECT * FROM [Sheet1$]‘)
GO
SELECT * from #tmpTable

However, you will potentially hit some errors.

1. If you’re on a 64-bit SQL Server, you will need this installed: http://www.microsoft.com/en-us/download/details.aspx?id=13255
2. You’ll need to enable some bits:

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1;
GO
RECONFIGURE;
GO
EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.4.0′, N’AllowInProcess’, 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.4.0′, N’DynamicParameters’, 1
GO

3. And here’s the kicker. You’ll need read to grant read access to everyone on C:\Users\{SQL Service account name}\AppData\Local\Temp.

Edit: note – Read only access on the above folder didn’t seem to cut it. We granted Full Control which did. I wouldn’t advocate giving full control to Everyone, but…

Auto generate SQL audit tables and database triggers

November 28th, 2012 in Databases. No Comments ».

One approach to monitoring version history of items in SQL tables is to use an _Audit table version with database triggers. However, the creation and maintenance of the tables and triggers can be time consuming. This post provided a nice script to automate the creation but unfortunately it suffered a few issues. In the comments, someone posted a sproc version of the script – but unfortunately this was also broken.

So I have tidied it up and fixed the issues. My host doesn’t let me post SQL in these posts, so you can view the file here.

You can then execute the sproc with the name of your table, and it will do the rest. Enjoy.