title: sql local db unable to interpret £ €
body: <p>I have a script that creates a sql localdb (2014), creates a table and puts some data into it. If I run this script in SSMS against a full SQL server 2014 instance, it completes fine and all the data in the table looks as I'd expect it to. </p>
  
  <p>However, when I run this via SQLCMD or a Visual Studio app, the £ and € in the strings are interpreted and stored as ? in the database.</p>
  
  <p>I've checked the collation, and tried forcing both the database and/or the columns in question to use the same collation as my working server, but it's still not storing the £ and € correctly. </p>
  
  <p>Any Ideas anyone?</p>
  
  <p>This is a cutdown version of the code, but it hopefully gets the point across:</p>
  
  <pre class="lang-sql prettyprint-override"><code>IF (EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE ('[' + name + ']' = 'Hestia' OR name = 'Hestia'))) 
  BEGIN 
    ALTER DATABASE [Hestia] SET SINGLE_USER WITH ROLLBACK Immediate
    DROP DATABASE [Hestia]
  END
  GO
  CREATE DATABASE [Hestia] 
  --Collate Latin1_General_CI_AS
  GO
  ALTER DATABASE [Hestia] SET COMPATIBILITY_LEVEL = 100
  GO
  IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
  begin
  EXEC [Hestia].[dbo].[sp_fulltext_database] @action = 'enable'
  end
  GO
  ALTER DATABASE [Hestia] SET ANSI_NULL_DEFAULT ON 
  GO
  ALTER DATABASE [Hestia] SET ANSI_NULLS ON 
  GO
  ALTER DATABASE [Hestia] SET ANSI_PADDING ON 
  GO
  ALTER DATABASE [Hestia] SET ANSI_WARNINGS ON 
  GO
  ALTER DATABASE [Hestia] SET ARITHABORT ON 
  GO
  ALTER DATABASE [Hestia] SET AUTO_CLOSE OFF 
  GO
  ALTER DATABASE [Hestia] SET AUTO_CREATE_STATISTICS ON 
  GO
  ALTER DATABASE [Hestia] SET AUTO_SHRINK OFF 
  GO
  ALTER DATABASE [Hestia] SET AUTO_UPDATE_STATISTICS ON 
  GO
  ALTER DATABASE [Hestia] SET CURSOR_CLOSE_ON_COMMIT OFF 
  GO
  ALTER DATABASE [Hestia] SET CURSOR_DEFAULT  LOCAL 
  GO
  ALTER DATABASE [Hestia] SET CONCAT_NULL_YIELDS_NULL ON 
  GO
  ALTER DATABASE [Hestia] SET NUMERIC_ROUNDABORT OFF 
  GO
  ALTER DATABASE [Hestia] SET QUOTED_IDENTIFIER ON 
  GO
  ALTER DATABASE [Hestia] SET RECURSIVE_TRIGGERS OFF 
  GO
  ALTER DATABASE [Hestia] SET  DISABLE_BROKER 
  GO
  ALTER DATABASE [Hestia] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
  GO
  ALTER DATABASE [Hestia] SET DATE_CORRELATION_OPTIMIZATION OFF 
  GO
  ALTER DATABASE [Hestia] SET TRUSTWORTHY OFF 
  GO
  ALTER DATABASE [Hestia] SET ALLOW_SNAPSHOT_ISOLATION OFF 
  GO
  ALTER DATABASE [Hestia] SET PARAMETERIZATION SIMPLE 
  GO
  ALTER DATABASE [Hestia] SET READ_COMMITTED_SNAPSHOT OFF 
  GO
  ALTER DATABASE [Hestia] SET HONOR_BROKER_PRIORITY OFF 
  GO
  ALTER DATABASE [Hestia] SET RECOVERY FULL 
  GO
  ALTER DATABASE [Hestia] SET  MULTI_USER 
  GO
  ALTER DATABASE [Hestia] SET PAGE_VERIFY NONE  
  GO
  ALTER DATABASE [Hestia] SET DB_CHAINING OFF 
  GO
  CREATE TABLE [dbo].[Denomination](
      [Id] [int] NOT NULL,
      [Name] [nvarchar](50) NOT NULL,
      [Currency] [nvarchar](50) NOT NULL,
      [Value] [decimal](18, 4) NOT NULL
   CONSTRAINT [PK_Denominations] PRIMARY KEY CLUSTERED 
  (
      [DenominationId] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  GO
  INSERT INTO [dbo].[Denomination] ([Id], [Name], [Currency], [Value]) VALUES (7, N'£1', N'GBP', 1.0000)
  INSERT INTO [dbo].[Denomination] ([Id], [Name], [Currency], [Value]) VALUES (8, N'£2', N'GBP', 2.0000)
  INSERT INTO [dbo].[Denomination] ([Id], [Name], [Currency], [Value]) VALUES (9, N'£5', N'GBP', 5.0000)
  INSERT INTO [dbo].[Denomination] ([Id], [Name], [Currency], [Value]) VALUES (25, N'€1', N'EUR', 1.0000)
  INSERT INTO [dbo].[Denomination] ([Id], [Name], [Currency], [Value]) VALUES (26, N'€2', N'EUR', 2.0000)
  INSERT INTO [dbo].[Denomination] ([Id], [Name], [Currency], [Value]) VALUES (27, N'€5', N'EUR', 5.0000)
  </code></pre>
  
  <p>and app code:</p>
  
  <pre><code>public static ActionResult CreateDatabase(Session session)
          {
            ActionResult result = ActionResult.NotExecuted;
            session.Log("Begin Create Hestia Database");
            try
            {
              //System.Diagnostics.Debugger.Launch();
  
              ISqlLocalDbApi localDb = new SqlLocalDbApiWrapper();
              if (localDb.IsLocalDBInstalled())
              {
                ISqlLocalDbProvider provider = new SqlLocalDbProvider();
                ISqlLocalDbInstance instance = provider.GetOrCreateInstance(@"MyLocalDB");
  
                // Check it's running
                if (!instance.GetInstanceInfo().IsRunning)
                {
                  session.Log("Starting MyLocalDB instance");
                  instance.Start();
                }
  
  
                using (SqlConnection connection = instance.CreateConnection())
                {
                  connection.Open();
  
                  try
                  {
                    // These two scripts are linked as content in our project
                    // So WiX bundles them into the temp folder we run in
                    string createScript = File.ReadAllText(@"1. Create.sql");
                    string dataScript = File.ReadAllText(@"2. StaticData.sql");
  
                    // Use a SMO server connection because it's happy
                    // with scripts containing GOs etc.
                    Server server = new Server(new ServerConnection(connection));
  
                    // Execute both scripts
                    session.Log("Executing create script");
                    server.ConnectionContext.ExecuteNonQuery(createScript);
                    session.Log("Executed create script");
                    session.Log("Executing static data script");
                    server.ConnectionContext.ExecuteNonQuery(dataScript);
                    session.Log("Executed static data script");
                  }
                  finally
                  {
                    connection.Close();
                  }
                }
  
              }
              else
              {
                session.Log("LocalDB Not Installed");
              }
  
              result = ActionResult.Success;
            }
            catch (Exception e)
            {
              System.Diagnostics.Debugger.Launch();
              var innerex = e.InnerException;
              var innerex2 = e.InnerException.InnerException == null ? String.Empty : e.InnerException.InnerException.ToString();
              session.Log(string.Format("Error: {0}", e.Message + innerex + innerex2));
              result = ActionResult.Failure;
            }
  
            session.Log("End Create MyLocalDB Database");
            return result;
          }
  </code></pre>