--- title: "Hello World Ready Sample | Microsoft Docs" ms.custom: "" ms.date: "03/06/2017" ms.prod: "sql-server-2014" ms.reviewer: "" ms.technology: "database-engine" ms.topic: "reference" ms.assetid: 1cb94266-f702-4a57-a1ae-689a89c98757 author: mashamsft ms.author: mathoma manager: craigg --- # Hello World Ready Sample The Hello World Ready sample demonstrates the basic operations that are involved in creating, deploying, and testing a simple world ready common language runtime (CLR) integration-based stored procedure. A world-ready component can be easily localized into different languages for different markets around the world without changing the component's source code. This sample also demonstrates how to return data through an output parameter and through a record, which is dynamically constructed by the stored procedure and returned to the client.This sample is almost identical to the Hello World Sample except that it is much easier and safer to localize this application. To change localized text requires the following: 1. Changing an XML file (the .`resx` file) for the particular culture in the resources directory 2. Building the culture's resources file by using `resgen` 3. Building the updated satellite DLL for that culture 4. Dropping and adding that assembly in SQL Server The source code and assembly for the CLR stored procedure itself does not change. A `build.cmd` script is provided which demonstrates how to compile and link the resource assemblies.Although the source code for the application creates a resource manager based the currently executing assembly, you do not have to embed the culture neutral resources in the DLL that contains the stored procedure. The `System.Resources.NeutralResourcesLanguage attribute` permits the culture-neutral resources to exist in a satellite DLL. It is much better to use a separate DLL for this purpose so that when localized text needs to be added or changed, the primary DLL that contains the CLR stored procedure does not have to be changed. This is especially useful for CLR user-defined types that might have columns and other dependencies which would make it difficult to drop and re-add the type.Ordinarily, the satellite DLL versions must be identical to the main assembly version. However, you can use the `SatelliteContractVersion` attribute to allow the main assembly to be updated without updating the satellite assemblies too. For more information, see the `ResourceManager` class in the Microsoft .NET documentation. ## Prerequisites This sample works only with SQL Server 2005 and later versions. To create and run this project the following the following software must be installed: - [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] or [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Express. You can obtain [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Express free of charge from the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Express Documentation and Samples [Web site](https://www.microsoft.com/sql-server/sql-server-editions-express) - The AdventureWorks database that is available at the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Developer [Web site](https://go.microsoft.com/fwlink/?linkid=62796) - .NET Framework SDK 2.0 or later or Microsoft Visual Studio 2005 or later. You can obtain .NET Framework SDK free of charge. - In addition, the following conditions must be met: - The [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] instance you are using must have CLR integration enabled. - In order to enable CLR integration, perform the following steps: #### Enabling CLR Integration - Execute the following [!INCLUDE[tsql](../../includes/tsql-md.md)] commands: `sp_configure 'clr enabled', 1` `GO` `RECONFIGURE` `GO` > [!NOTE] > To enable CLR, you must have `ALTER SETTINGS` server level permission, which is implicitly held by members of the `sysadmin` and `serveradmin` fixed server roles. - The AdventureWorks database must be installed on the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] instance you are using. - If you are not an administrator for the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] instance you are using, you must have an administrator grant you **CreateAssembly** permission to complete the installation. ## Building the Sample #### Create and run the sample by using the following instructions: 1. Open a Visual Studio or .NET Framework command prompt. 2. If necessary, create a directory for your sample. For this example, we will use C:\MySample. 3. In c:\MySample, create `HelloWorld.vb` (for the Visual Basic sample) or `HelloWorld.cs` (for the C# sample) and copy the appropriate Visual Basic or C# sample code (below) into the file. 4. In c:\MySample, create the file `messages.resx` and copy the sample code into the file. 5. In c:\MySample, create the file `messages.de.resx` by saving the file `messages.resx` as `messages.de.resx` after changing the line - `Hello, World!` - To read - `Hallo Welt!` 6. In c:\MySample, create the file `messages.es.resx` by saving the file `messages.resx` as `messages.es.resx` after changing the line - `Hello, World!` - To read - `Hola a todos` 7. In c:\MySample, create the file `messages.fr.resx` by saving the file `messages.resx` as `messages.fr.resx` after changing the line - `Hello, World!` - To read - `Bonjour !` 8. In c:\MySample, create the file `messages.fr-FR.resx` by saving the file `messages.resx` as `messages.fr-FR.resx` after changing the line - `Hello, World!` - To read - `Bonjour de France!` 9. In c:\MySample, create the file `messages.it.resx` by saving the file `messages.resx` as `messages.it.resx` after changing the line - `Hello, World!` - To read - `Buongiorno` 10. In c:\MySample, create the file `messages.ja.resx` by saving the file `messages.resx` as `messages.ja.resx` after changing the line - `Hello, World!` - To read - `` `ã"ã‚"にちは` 11. In c:\MySample, create the file `build.com` and copy the sample code into the file 12. Build the satellite assembles by executing the file build at the command prompt. 13. Compile the sample code from the command line prompt by executing the one of the following: - `Vbc /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll /out:HelloWorldReady.dll /target:library HelloWorld.vb` - `Csc /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.XML.dll /out:HelloWorldReady.dll /target:library Hello.csCopy the tsql installation code into a file and save it as Install.sql in the sample directory.` 14. If the sample is installed in a directory other then `C:\MySample\`, edit the file `Install.sql` as indicated to point to that location. 15. Deploy the assembly and stored procedure by executing - `sqlcmd -E -I -i install.sql` 16. Copy [!INCLUDE[tsql](../../includes/tsql-md.md)] test command script into a file and save it as `test.sql` in the sample directory. 17. Execute the test script with the following command - `sqlcmd -E -I -i test.sql` 18. Copy the [!INCLUDE[tsql](../../includes/tsql-md.md)] cleanup script into a file and save it as `cleanup.sql` in the sample directory. 19. Execute the script with the following command - `sqlcmd -E -I -i cleanup.sql` ## Sample Code The following are the code listings for this sample. C# ``` using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Globalization; using System.Threading; using System.Resources; using System.Reflection; using System.Runtime.CompilerServices; [assembly: System.Resources.NeutralResourcesLanguage("", System.Resources.UltimateResourceFallbackLocation.Satellite)] [assembly: System.Security.Permissions.SecurityPermissionAttribute(System.Security.Permissions.SecurityAction.RequestMinimum)] [assembly: System.Runtime.ConstrainedExecution.ReliabilityContract(System.Runtime.ConstrainedExecution.Consistency.MayCorruptInstance, System.Runtime.ConstrainedExecution.Cer.None)] public sealed partial class StoredProcedures { private StoredProcedures() { } [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1021:AvoidOutParameters"), Microsoft.SqlServer.Server.SqlProcedure] public static void HelloWorldReady(string culture, out string greeting) { ResourceManager rm = new ResourceManager("Messages", Assembly.GetExecutingAssembly()); string message = rm.GetString("HelloWorld", CultureInfo.GetCultureInfo(culture)); Microsoft.SqlServer.Server.SqlMetaData columnInfo = new Microsoft.SqlServer.Server.SqlMetaData("Column1", SqlDbType.NVarChar, 24); SqlDataRecord greetingRecord = new SqlDataRecord(new Microsoft.SqlServer.Server.SqlMetaData[] { columnInfo }); greetingRecord.SetString(0, message); SqlContext.Pipe.Send(greetingRecord); greeting = message; } } ``` Visual Basic ``` Imports System Imports System.Data Imports System.Data.Sql Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.Globalization Imports System.Resources Imports System.Reflection Imports System.Runtime.InteropServices Partial Public NotInheritable Class StoredProcedures Private Sub New() End Sub _ Public Shared Sub HelloWorldReady(ByVal culture As String, ByRef greeting As String) Dim rm As New ResourceManager("Messages", Assembly.GetExecutingAssembly()) Dim message As String = rm.GetString("HelloWorld", CultureInfo.GetCultureInfo(culture)) Dim columnInfo As New Microsoft.SqlServer.Server.SqlMetaData("Column1", _ SqlDbType.NVarChar, 24) Dim greetingRecord As New SqlDataRecord(New _ Microsoft.SqlServer.Server.SqlMetaData() {columnInfo}) greetingRecord.SetString(0, message) SqlContext.Pipe.Send(greetingRecord) greeting = message End Sub End Class ``` This is `build.com`, which builds the satellite assemblies. ``` resgen Messages.resx resgen Messages.de.resx resgen Messages.es.resx resgen Messages.fr.resx resgen Messages.fr-Fr.resx resgen Messages.it.resx resgen Messages.ja.resx if not exist de/ mkdir de if not exist es/ mkdir es if not exist fr/ mkdir fr if not exist fr-FR/ mkdir fr-FR if not exist it/ mkdir it if not exist ja/ mkdir ja al /t:lib /culture:de /embed:Messages.de.resources /out:de\HelloWorldReady.resources.dll al /t:lib /culture:es /embed:Messages.es.resources /out:es\HelloWorldReady.resources.dll al /t:lib /culture:fr /embed:Messages.fr.resources /out:fr\HelloWorldReady.resources.dll al /t:lib /culture:fr-FR /embed:Messages.fr-FR.resources /out:fr-FR\HelloWorldReady.resources.dll al /t:lib /culture:it /embed:Messages.it.resources /out:it\HelloWorldReady.resources.dll al /t:lib /culture:ja /embed:Messages.ja.resources /out:ja\HelloWorldReady.resources.dll al /t:lib /culture:"" /embed:Messages.resources /out:HelloWorldReady.resources.dll ``` This is the [!INCLUDE[tsql](../../includes/tsql-md.md)] installation script (`Install.sql`), which deploys the assemblies and creates the stored procedure within the database. ``` USE AdventureWorks GO -- Drop existing sproc and assembly if any. IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = 'usp_HelloWorldReady') DROP PROCEDURE usp_HelloWorldReady; GO IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady') DROP ASSEMBLY HelloWorldReady; GO IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.neutral') DROP ASSEMBLY [HelloWorldReady.resources.neutral] GO IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.de') DROP ASSEMBLY [HelloWorldReady.resources.de] GO IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.es') DROP ASSEMBLY [HelloWorldReady.resources.es] GO IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.fr') DROP ASSEMBLY [HelloWorldReady.resources.fr] GO IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.fr-FR') DROP ASSEMBLY [HelloWorldReady.resources.fr-FR] GO IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.it') DROP ASSEMBLY [HelloWorldReady.resources.it] GO IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.ja') DROP ASSEMBLY [HelloWorldReady.resources.ja] GO DECLARE @SamplesPath nvarchar(1024) -- You may need to modify the value of this variable if you have installed the sample someplace other than the default location. Set @SamplesPath = N'C:\MySample\' -- Add the assembly and CLR integration based stored procedure CREATE ASSEMBLY HelloWorldReady FROM @SamplesPath + 'HelloWorldReady.dll' WITH permission_set = Safe; CREATE ASSEMBLY [HelloWorldReady.resources.neutral] FROM @SamplesPath + 'HelloWorldReady.resources.dll' WITH permission_set = Safe; CREATE ASSEMBLY [HelloWorldReady.resources.de] FROM @SamplesPath + '\de\HelloWorldReady.resources.dll' WITH permission_set = Safe; CREATE ASSEMBLY [HelloWorldReady.resources.es] FROM @SamplesPath + '\es\HelloWorldReady.resources.dll' WITH permission_set = Safe; CREATE ASSEMBLY [HelloWorldReady.resources.fr] FROM @SamplesPath + '\fr\HelloWorldReady.resources.dll' WITH permission_set = Safe; CREATE ASSEMBLY [HelloWorldReady.resources.fr-FR] FROM @SamplesPath + '\fr-FR\HelloWorldReady.resources.dll' WITH permission_set = Safe; CREATE ASSEMBLY [HelloWorldReady.resources.it] FROM @SamplesPath + '\it\HelloWorldReady.resources.dll' WITH permission_set = Safe; CREATE ASSEMBLY [HelloWorldReady.resources.ja] FROM @SamplesPath + '\ja\HelloWorldReady.resources.dll' WITH permission_set = Safe; GO CREATE PROCEDURE usp_HelloWorldReady ( @Culture NVarchar(12), @Greeting NVarchar(24) OUTPUT ) AS EXTERNAL NAME HelloWorldReady.StoredProcedures.HelloWorldReady; GO USE master; GO ``` This is `test.sql`, which tests the sample by executing the functions on each locale. ``` USE AdventureWorks GO DECLARE @GreetingDe nvarchar(24); DECLARE @GreetingDe_CH nvarchar(24); DECLARE @GreetingEn nvarchar(24); DECLARE @GreetingEs nvarchar(24); DECLARE @GreetingFr nvarchar(24); DECLARE @GreetingFr_FR nvarchar(24); DECLARE @GreetingIt nvarchar(24); DECLARE @GreetingJa nvarchar(24); --German as spoken anywhere in the world (the neutral German culture) EXEC usp_HelloWorldReady 'de', @GreetingDe OUTPUT; --German as spoken in Switzerland. Because we don't have a specific assembly --for this case, the .NET Framework will automatically fall back to the neutral German culture DLL. EXEC usp_HelloWorldReady 'de-CH', @GreetingDe_CH OUTPUT; EXEC usp_HelloWorldReady 'en', @GreetingEn OUTPUT; EXEC usp_HelloWorldReady 'es', @GreetingEs OUTPUT; --French as spoken anywhere in the world (the neutral French culture) EXEC usp_HelloWorldReady 'fr', @GreetingFr OUTPUT --French as spoken in France. Since we do have a specific assembly for this case, a specific --greeting is provided from that DLL. The neutral French culture DLL is not used in this case. EXEC usp_HelloWorldReady 'fr-FR', @GreetingFr_FR OUTPUT EXEC usp_HelloWorldReady 'it', @GreetingIt OUTPUT; EXEC usp_HelloWorldReady 'ja', @GreetingJa OUTPUT; SELECT @GreetingDe AS OUTPUT_PARAMETER_DE; SELECT @GreetingDe_CH AS OUTPUT_PARAMETER_De_CH; SELECT @GreetingEn AS OUTPUT_PARAMETER_EN; SELECT @GreetingEs AS OUTPUT_PARAMETER_ES; SELECT @GreetingFr AS OUTPUT_PARAMETER_FR; SELECT @GreetingFr_FR AS OUTPUT_PARAMETER_Fr_FR; SELECT @GreetingIt AS OUTPUT_PARAMETER_IT; SELECT @GreetingJa AS OUTPUT_PARAMETER_JA; GO ``` The following [!INCLUDE[tsql](../../includes/tsql-md.md)] removes the assemblies and stored procedure from the database. ``` USE AdventureWorks; GO -- Drop existing sproc and assembly if any. IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = 'usp_HelloWorldReady') DROP PROCEDURE usp_HelloWorldReady; GO IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady') DROP ASSEMBLY HelloWorldReady; GO IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.neutral') DROP ASSEMBLY [HelloWorldReady.resources.neutral] GO IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.de') DROP ASSEMBLY [HelloWorldReady.resources.de] GO IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.es') DROP ASSEMBLY [HelloWorldReady.resources.es] GO IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.fr') DROP ASSEMBLY [HelloWorldReady.resources.fr] GO IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.fr-FR') DROP ASSEMBLY [HelloWorldReady.resources.fr-FR] GO IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.it') DROP ASSEMBLY [HelloWorldReady.resources.it] GO IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'HelloWorldReady.resources.ja') DROP ASSEMBLY [HelloWorldReady.resources.ja] GO USE master; GO ``` ## See Also [Usage Scenarios and Examples for Common Language Runtime (CLR) Integration](../../../2014/database-engine/dev-guide/usage-scenarios-and-examples-for-common-language-runtime-clr-integration.md)