Language Translation in SQL Server using Bing Translator APIs & SQL CLR


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:

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:

  1. Download and extract the .zipped C# source code and SQL Server found here
  2. Open the C# application, replace ClientID and ClientSecret (lines 69-70) with the values obtained in the steps above, and compile the C# application
  3. 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
  4. 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.

Enjoy!
Sam Lester (MSFT)

 

 

 

 

Comments (14)

  1. Natalia Sargu says:

    Hello ,

    We would like to place a text link within an article from your blog/website.

    I would appreciate if you could suggest the page yourself and I hope we can agree on a reasonable yearly charge.

    Our website is http://www.polilingua.com/. The keyword text would be translation or transcription services related.

    If interested please suggest the page and I shall get back to you with our link info and budget.

    Best Regards,

    Natalia Sargu

  2. Sakil Ahmmed says:

    Hello,

    You use TranslatorAccessURI  "datamarket.accesscontrol.windows.net/…/OAuth2-13" in your given sample project code. But this link did not work cause throw an exception. Can you please look in to this issues?

    Regards,

    Sakil Ahmmed

  3. Hi Sakil, this specific code is actually from the Microsoft Bing Translator Developer sample code and I'm not sure the best way to debug it.  Would you mind asking a question in the MSDN forum?  I'm curious to see how they troubleshoot it since it has been working for several other users.

    Microsoft Translator User Forum (Including Bing Translator):

    social.msdn.microsoft.com/…/threads

    Thanks,

    Sam

  4. Ritz Lim says:

    Sorry i'm not a developers , but i use for some thing els can you teach step by stap installation ? i know notting about SQL but i use SQL 2008 r2 pls help or can email me the tut thanks you anyone if can help thaks you

    i'm very noob on this .

  5. Pradnya K says:

    Hey Sam

    I am having trouble creating the assembly in SQL Server 2012. I updated the project with my client Id and client secret and created the DLL. Now,

    CREATE ASSEMBLY CLRTest01 from 'C:…….CLRTest01.dll' with permission_set = unsafe

    gives me an error:

    Assembly 'CLRTest01' references assembly 'system.web, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.

    What am I doing wrong?

  6. sherief says:

    i am stopped at the create assemply step

    sql gives me this erorr:

    CREATE ASSEMBLY failed because it could not open the physical file "C:…CLRTest01.dll": 3(The system cannot find the path specified.).

  7. Hi Sherief, did you compile the C# project and confirm that CLRTest01.dll was created?  Also, double check your file path since the error message is telling you that it cannot find the path specified.

    1. shawn says:

      hi lester,

      I ran into same error like shrif, I debuged the c# code and the dll was generated with 2 errors below…any clue? thanks…..

      #1Error 1 SQL72014: .Net SqlClient Data Provider: Msg 6503, Level 16, State 12, Line 1 Assembly ‘system.web, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.’ was not found in the SQL catalog. C:\Users\Admin\Downloads\LanguageTranslationWithinSQLServer\LanguageTranslationWithinSQLServer\VS2012\CLRTest01\CLRTest01\bin\Debug\CLRTest01.sql 47 1 CLRTest01

      #2Error 2 SQL72045: Script execution error. The executed script:
      CREATE ASSEMBLY [CLRTest01]
      AUTHORIZATION [dbo]
      FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030006BEFC560000000000000000E00002210B010B00001400000006000000000000DE320000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000843200005700000000400000A802000000000000000000000000000000000000006000000C0000004C3100001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000E4120000002000000014000000020000000000000000000000000000200000602E72737263000000A8020000004000000004000000160000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001A000 C:\Users\Admin\Downloads\LanguageTranslationWithinSQLServer\LanguageTranslationWithinSQLServer\VS2012\CLRTest01\CLRTest01\bin\Debug\CLRTest01.sql 47 0 CLRTest01

  8. Jurica says:

    Hi.

    When I execute CREATE ASSEMBLY… in SQL Server 2012, sql gives me this erorr:

    Warning: The Microsoft .NET Framework assembly ‘system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=amd64.’ you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.
    Assembly ‘System.Web’ could not be installed because existing policy would keep it from being used.

    Any help?

    Regards,
    Jurica

  9. my sql query is being translated in to different language which i do not have a clue
    egtˆÚ(l@hMˆÑöü6¿‘ä¾#RAï]N<܏ú†Íå}oæAØž}˜m؁oÌ
    can some one tell me which language is this and how to translated it in to english

  10. Jack says:

    I received an error when running the CreateAssemblyCode.sql:

    –Assembly ‘microsoft.build.framework, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.’ was not found in the SQL catalog.

    There were several solutions for SQL Server 2008, but not 2012 x64. I was able to solve it by running the modified code:

    CREATE ASSEMBLY SystemWeb
    from ‘C:\WINDOWS\Microsoft.NET\Framework64\v4.0.30319\System.Web.dll’
    with permission_set = unsafe

    I’m recording it here because the solutions that I found initially did not help and I had to do some digging, so hopefully this helps someone in the future.

    Cheers.

    1. Hi Jack, thanks for providing the solution you found!
      Sam

    2. RAJESHKUMAR EDIGA says:

      THANK YOU JACK along with that code we need to write “””ALTER DATABASE Databasename SET TRUSTWORTHY ON;””” after that write this code this will work for sure

  11. Mujahid says:

    by above it gives this error in Creating Assembly
    Msg 10301, Level 16, State 1, Line 1
    Assembly ‘CLRTest01’ references assembly ‘system.web, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.’, which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.

    by applying
    CREATE ASSEMBLY SystemWeb
    from ‘C:\WINDOWS\Microsoft.NET\Framework64\v4.0.30319\System.Web.dll’
    with permission_set = unsafe

    giving this error

    Assembly ‘CLRTest01’ references assembly ‘system.web, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.’, which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.

Skip to main content