Language Translation within SQL Server using Microsoft Translator public APIs and CLR Stored Procedures / Functions
Does SQL Server have a built-in way to translate my text data from English to Spanish, German to French, Italian to Russian? The answer is no, SQL Server does not have this functionality built-in, but it is indeed possible to build by leveraging CLR and the public Microsoft Translator APIs. All C# and SQL Server code has been uploaded as a TechNet Gallery here to make it easier to implement.
I’ve heard this question a surprising number of times over the past few years and finally had the business need to build it myself. I’m working with a database that is hosted in another country and at one point allowed free text entry into a product description column. While the majority of the descriptions are in English, a somewhat substantial amount were entered in the native language. It is time to clean up this database to arrive at the definitive list of product descriptions, but in order to do so, we need to first translate the native language entries into English. Using the code samples from the TechNet Gallery found here, we can perform language translation as shown below.
Sample call within SQL Server to translate nvarchar text data within a table using the supplied user-defined function:
Sample call within SQL Server showing the flexibility of the stored procedure translating between several different languages:
Since Microsoft Translator provides a free public API that can be leveraged by developers, the process to build this application is fairly straightforward. As of April 2013, the process to access these APIs has changed so that you must register for a public access token to obtain your personal “client secret”. This is used to obtain a token that is then passed along with the API call to validate the user and supply the requested translation.
The three steps required to obtain the access token are:
- Sign up for Microsoft Translator API: – https://datamarket.azure.com/dataset/bing/microsofttranslator (Scroll to the bottom to find the free registration that allows 2 million characters translated per month)
- Register the application: – https://datamarket.azure.com/developer/applications/
- Obtaining an Access Token: – http://msdn.microsoft.com/en-us/library/hh454950.aspx
Once you obtain the clientID and clientSecret, you will replace the sample values in the C# application and are ready to compile.
Detailed steps to use the project:
- Download and extract the .zipped C# source code and SQL Server found here
- Open the C# application, replace ClientID and ClientSecret (lines 69-70) with the values obtained in the steps above, and compile the C# application
- From the extracted SQL Server scripts folder, open CreateAssemblyCode.sql within your instance of SQL Server, update the script with the path to your compiled DLL from step #2 and run the script to create the assembly
- From the extracted SQL Server scripts folder, open the spCallTranslator and udfCallTranslator for the source code to create the SP and UDF. The SP script also contains a few samples.
Sam Lester (MSFT)