Project Server 2013: Problems upgrading if your SQL language is not English

This post has been republished via RSS; it originally appeared at: Project Support Blog articles.

Thanks to Diana Balan for all her work on this case of a failure in the PowerShell upgrade scripts when migrating from Project Server 2010 to Project Server 2013.  We do have a fix request in to overcome this issue (I’ll try to remember to update when it is released!) – and there is a workaround too.  The problem occurs when running the ConvertTo-SPProjectDatabase command – and it will fail after a short while with a message like the following:

ConvertTo-SPProjectDatabase : Action 15.0.10.0 of Microsoft.Office.Project.Server.Upgrade.ReportingDatabaseSequence failed.
At line:1 char:1
+ ConvertTo-SPProjectDatabase -WebApplication http://<servername> -Dbserver bris ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (Microsoft.Offic...baseImplementor:ConvertToProjec...baseImplementor) [ConvertTo-SPProjectDatabase], SPU
pgradeException
+ FullyQualifiedErrorId : Microsoft.Office.Project.Server.Cmdlet.PSCmdletConvertToProjectServiceDatabase

This will occur if you have edited the SQL language of the user running the PowerShell command in the following dialog in SQL Management Studio (or you may have set it in SQL):

In my case to reproduce the issue I have set the language to German.  This results in a German language error in the ULS logs too – the start of which says:

System.Data.SqlClient.SqlException (0x80131904): Bei der Konvertierung eines varchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb des gültigen Bereichs.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

which even with my poor schoolboy German tells me that something about the date/time format is causing the problem.  The actual problem is that using the German language (and others) will then set the ISO date format which mandates time as well as date. If you run a profiler trace then you see we have statement WHILE @CurrentTimeByDay <= '2149-12-31' and it is this which throws the exception.

Workaround - Setting the language to English for the user running the command will avoid the error.

For the search engines – the error you will get in the ULS logs in various languages (In no particular order):

• German - Bei der Konvertierung eines varchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb des gültigen Bereichs.
• French - La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.
• Spanish - La conversión del tipo de datos varchar en datetime produjo un valor fuera de intervalo.
• Portuguese - A conversão de um tipo de dados varchar num tipo de dados datetime resultou num valor fora do âmbito.
• Russian - Преобразование типа данных varchar в тип данных datetime привело к выходу значения за пределы диапазона.
• Italian - La conversione di un tipo di dati varchar in datetime ha generato un valore non compreso nell'intervallo dei valori consentiti.
• Danish - Konverteringen af en varchar-datatype til en datetime-datatype resulterede i en værdi, der ligger uden for intervallet.
• Dutch - De conversie van varchar-gegevenstype naar een datetime-gegevenstype heeft geresulteerd in een waarde buiten het bereik.
• Greek - Η μετατροπή ενός τύπου δεδομένων varchar σε τύπο δεδομένων datetime είχε ως αποτέλεσμα μια τιμή εκτός περιοχής.
• Polish - Konwersja typu danych varchar na typ danych datetime spowodowała utworzenie wartości leżącej poza zakresem.
• Finnish - Tietotyypin varchar muuntaminen tietotyypiksi datetime antoi tulokseksi arvon, joka ei ole alueella.
• Czech - Převod datového typu varchar na datový typ datetime vrátil hodnotu mimo rozsah.
• Turkish - varchar veri türünden bir datetime veri türüne dönüştürme aralık dışı bir değerle sonuçlandı.
• Some languages failed but gave the error in English (might just be my server config) Norwegian and Arabic - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
• Swedish, Japanese, Korean, Chinese and Hungarian all worked ok.

These are just the Project supported languages – you could of course be set in SQL for a language that Project does not support – so the error may not be listed here…