March 7, 2016 at 7:41 am
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.
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.
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.
Any Ideas anyone?
This is a cutdown version of the code, but it gets the point accross
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]
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)
March 7, 2016 at 2:31 pm
are you calling you script (from sqlcmd) using -i?
I'm wondering if your .sql file that your calling is saving the unicode correctly...
(try saving the sql file with another text editor.)
March 10, 2016 at 7:22 pm
When saving the script from SSMS make sure you choose UNICODE encoding.
_____________
Code for TallyGenerator
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply