October 11, 2010 at 7:16 am
Hi, I'm trying to put together a utility that can execute BCP for me in a consistent fashion but I'm hitting file encoding problems when running BCP with the same parameters but via different processes.
To set up, there's a table with a british pound £ sign in some of the rows (along with lots of other currency symbols on other rows for real)
CREATE TABLE [MetaData].[Table](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL);
SET IDENTITY_INSERT [MetaData].[Table] ON;
INSERT [MetaData].[Table] ([Id], [Name]) VALUES (3016, N'AInet £');
SET IDENTITY_INSERT [MetaData].[Table] OFF;
When BCP is run from a command window (Windows 7) like so the output file is encoded perfectly
bcp.exe "[dbName].[MetaData].[Table]" out "C:\OutputFolder\MetaData.Table.dat" -c -C RAW -t"|^" -S "(local)" -T
But when the same command is run from C# using the System.Diagnostics.Process object the encoding of the £ sign is wrong. It substitues a black diamond with a questionmark in it � and the resulting file is unusable to BULK INSERT or OPENROWSET. This incorrect encoding only happens to special characters such as currency symbols the rest of the output is fine.
string execPath = @"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe";
string localFile = @"MetaData.Table";
string localPath = @"C:\OutputFolder";
System.Diagnostics.Process P = new System.Diagnostics.Process();
P.StartInfo.UseShellExecute = false;
P.StartInfo.RedirectStandardOutput = true;
P.StartInfo.RedirectStandardError = true;
P.StartInfo.CreateNoWindow = true;
P.StartInfo.FileName = execPath;
P.StartInfo.Arguments = string.Concat("\"", "MetaData.Table", "\" out \"", localPath, "\\", localFile, ".dat\" -c -C RAW -t\"", separator, "\" -S \"", "(local)", "\" -T");
P.Start();
P.WaitForExit();
P.Close();
P.Dispose();
I've been going around in circles for days on this to no avail, can anyone offer a reason I'm sure it's environmental and there's something I need to add to the C# to make the process equivilent to the command windows. SQL database is Latin1_General_CI_AS, any help is much appreciated.
October 11, 2010 at 8:05 am
try specifying a different value for the -C (codepage) option.
The probability of survival is inversely proportional to the angle of arrival.
October 11, 2010 at 8:09 am
thanks for reply but I've tried them all. if it was that parameter it wouldn't work from the command window but it does.
October 11, 2010 at 8:19 am
Read this: http://msdn.microsoft.com/en-us/library/ms190919.aspx
regarding ramification of using -c switch
What I am implying is that a different codepage is in effect when it is run from the command line (shell) versus when run from the .net CLR. The above link says to specify a codepage for this situation.
The probability of survival is inversely proportional to the angle of arrival.
October 11, 2010 at 8:30 am
thanks again, ramifications understood which is why i chose raw, i don't want any translation to occur. days gone by i had tried other codepages and widechar/unicode to no avail either. the resultant files need to be character and not binary based hence the -(lower)c and no codepage conversion with -(upper)C raw. what i don't understand is why it works perfectly well when running it from a command window but not from a spawned process.
October 12, 2010 at 3:12 am
it turned out to be another process I missed later in the code opening and re-writing the files changing the codepage as they went. The -c and -CRAW were the right options for BCP. I found that using 'c:\windows\system32\chcp.com' I could verify the code page the command window uses as 850, so I changed the other process to open and re-write the files in this encoding - problem solved. Thanks for your input sturner much appreciated. 😀
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply