Code Page Question (convert Squiggles To Korean And Chinese, Store In Nvarchar)
Jan 17, 2008
Hi all,
I spent about an hour searching the forums and the web but could not find a solution. Bob Bojanic, if you are around can you answer?
I have a DB that stores WW data for company names in a varchar
I don't have control over this DB, other than to pull data from it
I have an SSIS package that grabs WW data in a single pull using a system account
I have an Execute SQL task that runs a sproc to stage the data
I have a Data Flow Task that then copies the data to another server (where I need it)
The destination columns are nvarchar. The source columns are varchar.
Some countries (such as Korea and China) end up with ASCII gibberish (because of code page issues).
I have a solution that involves pulling the data, BCP the pulled data to a text file, then re-import with the correct code page, and delete the gibberish. BUT, I'd like to do this as part of the pull if possible (without any data duplication).
I've tried modifying the CodePage properties for both the Staging step (Execute SQL task running a sproc) and the Source & Dest columns for the Data Flow Task with no luck. Can anyone assist? Thanks much!
My application supports multiple languages/locales in a single database. Some of our new customers want to support Chinese, Japanese, Korean, Italian, Spanish, and German in addition to English. Supporting the Latin based languages is not a problem. But I am having trouble finding a collation sequence that allows me to store the other double byte languages in the same database correctly.
I have found changing the data types from text, char, varchar to ntext, nchar, nvarchar and adding an N in front of the various strings that getting inserted seems to work:
insert into CONTENTDATA (recordid, xml) values (newid(), N'<CHANNEL1><FILE1/><TEXT1><![CDATA[和红é”拉拉队的动感精神 ]]></TEXT1><TEXT3><![CDATA[和红é”拉拉队的动感精神]]></TEXT3></CHANNEL1>');
But this is not going to be a practical solution for us. Is there a collation sequence that would allow us to store multiple locales like we do in Oracle (AL32UTF8)?
Hi,Can anyone advice me which is commonly used collation name for adatabase that store Korean characters as there are a few options like"Korean_Wansung_Bin, etc.."?
Does anyone know how to call a SQL store procedure that return a value to the page? I've a simple data entry aspx page with several textboxes and a save button. When user fill out the form and click save/submit, it calls a store procedure to insert a row into a SQL table and automatically generate an ID that need to return the the page to display for the user. Are there a similar article somewhere?
I am using Windows 2003 Server English Version. I wanna store the big-5data so I install the sql server 2000 as if i install it in the Windows2000 with Server Collation of the Chinese_Taiwan_Stroke_CL_AS.However, the data are stored into the database server in unicodeinstead of big-5 in that of windows 2000 OS.I would like to ask how i can set so that the Sql Server 2000 can storethe big-5 data
I have a table like this below and it doesn't only contain English Names but it also contain Chinese Name. CREATE TABLE Names (FirstName NVARCHAR (50), LastName NVARCHAR (50)); I tried to view the column using SQL Query Analyzer, It didn't display Chinese Character. I know that SQL Server 2005 is using UCS-2 Encoding and Chinese Character uses Double Byte Character Set (DBCS) Encoding. I want to read the FirstName and LastName columns and display in Window Form Data Grid and ASP.NET Grid View. I tried to use this code below and it didn't work. It convert some of the English Name to Chinese Character and it display the chinese character and some still in the original unreadable characters. Does anybody know how to read those character from SQL Table and display the correct Chinese Character without converting the English Name into Chinese also? Thanks
int codePage = 950; StringBuilder message = new StringBuilder(); Encoding targetEncoding = Encoding.GetEncoding(codePage); byte[] encodedChars= targetEncoding.GetBytes(str); . message.AppendLine("Byte representation of '" + str + "' in Code Page '" + codePage + "':"); for (int i = 0; i < encodedChars.Length; i++) { message.Append("Byte " + i + ": " + encodedChars); }
message.AppendLine(" RESULT : " + System.Text.Encoding.Unicode.GetString(encodedChars)); Console.Writeline(message.ToString());
Hi, How I can convert text '07012003' into datetime ?. If I am using below format and getting the error 'the conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value'
I have a data type float with a value of 10000487930 that I'm trying to insert into a data type nvarchar and am getting the result of '1.00005e+010'. I've tried cast(field as nvarchar) however this is not working. What might fix this? I cannot change the insert table data type.
I need to sort by an ntext field, but it won't let me do it.
However, if I cast the field as nvarchar(100), I can use ORDER BY on that.
Is there any reason that this is a bad idea? In my testing, ordering by a converted ntext field was actually *faster* than ordering by an nvarchar (same data in the fields).
So as the subject says, I have a few fields that are nvarchar but hold date information. Most of these fields I have been able to move to datetime easiliy enough, simply by going into edit mode for the table and converting the fields to datetime. But 1 field is giving me problems I keep getting this error. quote:- Unable to modify table. Arithmetic overflow error converting expression to data type datetime. The statement has been terminated.
I really dont know why I'm getting this error, but I"m assuming it may have to be something like one of the records may not be in date format. But I don't know if this is the case and I don't know how to locate where my problem is coming from. Any guidance is greatly appreciated.
problem is that the data I am trying to insert is of the type nvarchar. eg: 06.50 I need it to be converted to type decimal (or float) before it is inserted in the new table.
Is there a way to do this within the SELECT TOP expression?
Account Number Balance Percent 45678935 4596.00 0.3500 78965215 56.43 1.0000
This seems like it should be ridiculously easy, but I keep running into road blocks. Like I said, this is just a sample from my table. My real table has 90 columns in it and about half of them need to be changed to either a dollar representation or a percent.
problem is that the data I am trying to insert is of the type nvarchar. eg: '06.50' I need it to be converted to type decimal (or float) before it is inserted in the new table.
Is there a way to do this within the SELECT TOP expression?
I have imported a text file with various data into sql table. all these values have been imported as nvarchar. I need to convert these into Integer. the format of the values is 10length i.e. 0000000.00. example of data: 0001028.99 - needs to be shown as 1028.99 222.00 - needs to be shown as 222.00 0000190.89 - needs to be shown as 190.89 2708.99 - needs to be shown as 2708.99 00000-50.99 - needs to be shown as -50.99 -109.79 - needs to be shown as -109.70
as you can see some of the values have leading zeros and some don't. i have tried converting from nvarchar to int and i get the error cannot convert nvarchar to int, i believe it may be because the data contains negative values as well as positive values.
Is there a split function or position function which i can use to extract the data? or any other methods which i can use would be really helpful.
I have a stored procedure that returns XML using FOR XML Explicit. I need to use the output of this procedure in another procedure, and modify the xml output before it is saved somewhere.
Say StoredProc1 is the one returning xml output and StoredProc2 needs to consume the output of StoredProc1
I declared a nvarchar(max) variable and trying to saved the result of StoredProc1
HI All, 1)I have a question. I have a column in nvarchar format which is called close_date and is in the following format: 29.02.2008 ( example). I need to convert it to date format.I also add that data conversion not working as column apper blank on table. Do you have any idea?
2)Also the second problem is how to unzip the file stored on the shared drive in the package before uploading.
I'm working with a horrible database! The field I'm having issues with is a negative number stored as a nvarchar, but it's not stored in a consistent format. I need to convert the field to a decimal (9,2) but I can't because of the negative sign.
I need to always have a formatting of 999,999,999.00 in a column called PropertyMap9.
PropertyMap9 is always a nvarchar(max)
How do I SET a value using a CONVERT or CAST to accomplish this ?
Why does this give me a syntax error ? The PropertyMap7 set actually works and performs the calculation and sets the new value for that row, however the next line gives an error. I need all 3 columns PropertyMap9, 8, and 7 to always have the above formatting, while still maintaining the nvarchar(max) datatype in the column. HELP!
update PropertyMapValues
set PropertyMap7 = CONVERT(NVARCHAR(MAX),CAST(PropertyMap8 AS money) - (CAST(PropertyMap9 AS money)),1)
set PropertyMap9 = CONVERT(NVARCHAR(MAX),CAST(PropertyMap9 AS money))
END
Msg 102, Level 15, State 1, Procedure UpdatePropertyMap7, Line 59
I have this issue and I can not figure out the problem. I have 4 other forms from the same database using practly the same code, slight variations based on datavalidation requirements. IIS6 SQL Express 2005.
I have tried to defint eh colum for CODE as a bound filed and as a templated field. I get the same error.ASPX Page <%@ Page Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="MaintainBSP.aspx.cs" Inherits="MaintainBSP" Title="Maintain BSP Codes" %> <%@ MasterType VirtualPath="~/Site.master" %> <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"><br /> <table class="mainTable" cellspacing="0" cellpadding="3" align="center"> <tr><td class="mainTableTitle">BSP Codes</td></tr> <tr><td> <table align="center"> <tr> <td><asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"BorderColor="Silver" BorderStyle="Solid" BorderWidth="1px" HorizontalAlign="Center" CellPadding="3"DataKeyNames="CODE" DataMember="DefaultView" DataSourceID="SqlDataSource1" OnRowEditing="GridView1_OnRowEditing" OnRowCancelingEdit="GridView1_EndEdit" OnRowUpdated="GridView1_EndEdit"> <Columns> <asp:CommandField ShowEditButton="True" EditText="Edit" CancelText="Cancel" UpdateText="Update" HeaderStyle-CssClass="rptTblTitle" > <HeaderStyle CssClass="rptTblTitle"></HeaderStyle> </asp:CommandField> <asp:BoundField DataField="CODE" HeaderText="Code" ReadOnly="true" HeaderStyle-CssClass="rptTblTitle" /> <asp:TemplateField HeaderText="Bottle Size" SortExpression="Btl Sz"> <EditItemTemplate> <asp:TextBox ID="TextBox1" MaxLength="10" Columns="10" runat="server" Text='<%# Bind("[BOTTLE$SIZE]") %>'></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="Bottle Size is a required field." Text="*" ControlToValidate="TextBox1"></asp:RequiredFieldValidator><asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ErrorMessage="Bottle size must be a number followed by 'ML' or 'L'" Text="*" ControlToValidate="TextBox1" ValidationExpression="[0-9.]+(ML|L)"></asp:RegularExpressionValidator> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("[BOTTLE$SIZE]") %>'></asp:Label> </ItemTemplate> <HeaderStyle CssClass="rptTblTitle" /> </asp:TemplateField> <asp:TemplateField HeaderText="Labeled" SortExpression="Labeled"> <EditItemTemplate> <asp:TextBox ID="TextBox2" MaxLength="1" Columns="2" runat="server" Text='<%# Bind("LABELED") %>'></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ErrorMessage="Labeled is a required field" Text="*" ControlToValidate="TextBox2"></asp:RequiredFieldValidator> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label2" runat="server" Text='<%# Bind("LABELED") %>'></asp:Label> </ItemTemplate> <HeaderStyle CssClass="rptTblTitle" /> </asp:TemplateField> <asp:TemplateField HeaderText="Bottles Per Case" SortExpression="Btls Per Case"> <EditItemTemplate> <asp:TextBox ID="TextBox3" Columns="4" runat="server" Text='<%# Bind("[BOTTLES$PER$CASE]") %>'></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ErrorMessage="Bottles per case must be a whole number." Text="*" ControlToValidate="TextBox3"></asp:RequiredFieldValidator> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label3" runat="server" Text='<%# Bind("[BOTTLES$PER$CASE]") %>'></asp:Label> </ItemTemplate> <HeaderStyle CssClass="rptTblTitle" /> </asp:TemplateField> <asp:TemplateField HeaderText="Liters Per Case" SortExpression="Ltrs Per Case"> <EditItemTemplate> <asp:TextBox ID="TextBox4" MaxLength="8" Columns="8" runat="server" Text='<%# Bind("[LITERS$PER$CASE]") %>'></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ErrorMessage="Liters per case must be a number." ControlToValidate="TextBox4" Text="*"></asp:RequiredFieldValidator> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label4" runat="server" Text='<%# Bind("[LITERS$PER$CASE]") %>'></asp:Label> </ItemTemplate> <HeaderStyle CssClass="rptTblTitle" /> </asp:TemplateField> <asp:CommandField ShowDeleteButton="True" DeleteText="Delete" HeaderStyle-CssClass="rptTblTitle" > <HeaderStyle CssClass="rptTblTitle"></HeaderStyle> </asp:CommandField> </Columns>
Just getting started with SQL Server CLR integration. Any suggestions on how to organize code? Should I keep my CLR Stored Procedures in a separate project than the code that will call them? Should each DB have its own project containing all the CLR code?
Maybe there is no right way...I'm just looking for a good way.
I am almost done with a simple page to have a user select a file (using the FileUpload object), give it a short description and associate it with an item from a listbox. Thensave these pieces of data to a new row in a SQL DB. My issue... The SQLSmd.ExecuteNonQuery FAILS... I have got all the fields able to connect and almost save... but for the actual "document" field errors out. The user can select any type of file (image, word, excel, Powerpoint, etc) for storage into the DB. I am not storing a referance to a file or the path to a file but the actual file in SQL.In the DB I have the field (Document) set to a type of VarBinary(8000) in the table. Here is thethe corrisponding code in my app... Dim bArray As Byte...bArray = FileUpload1.PostedFile.InputStream.ReadByte()... SQLCmd.Parameters.Add("@Document", Data.SqlDbType.VarBinary, 8000).Value = bArray...iReturn = SQLCmd.ExecuteNonQuery() ERRORThe first section of the stact trace... [InvalidCastException: Invalid cast from 'System.Byte' to 'System.Byte[]'.] System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider) +867 System.Byte.System.IConvertible.ToType(Type type, IFormatProvider provider) +37 System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) +408 System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) +896
Error: 0xC02020F4 at Data Flow Task, OLE DB Source [1]: The column "dbname" cannot be processed because more than one code page (936 and 1252) are specified for it.
Error: 0xC02020F4 at Data Flow Task, OLE DB Source [1]: The column "dbsize" cannot be processed because more than one code page (936 and 1252) are specified for it.
Error: 0xC02020F4 at Data Flow Task, OLE DB Source [1]: The column "Owner" cannot be processed because more than one code page (936 and 1252) are specified for it.
Error: 0xC02020F4 at Data Flow Task, OLE DB Source [1]: The column "Status" cannot be processed because more than one code page (936 and 1252) are specified for it.
Error: 0xC02020F4 at Data Flow Task, OLE DB Source [1]: The column "CompLevel" cannot be processed because more than one code page (936 and 1252) are specified for it.
When I run the package itself it is working fine and when I am calling the package from another package getting this error. From Parent package I sending servername for olde db source to this package.
Hello Frdz, I have doubt regarding the datatypes fields used in SQL SERVER 2005.I want to use the field to store the mobile number of the user .Which datatype should i used ?? The value for bigint Int64 is 18The value of int Int32 is 9/10Now,if in int i write : 1234567890 (accepted)This gives error : 9874565656 (not accepted..........why is it so ??? )Why is it so ??I want to know the perfect size of all the datatypes used in SQLSERVER 2005.There are also smallint,tinyint.....What's the main difference with all of them ??Can anyone provide me the nice links which can explain me what m i asking in this post...Please help me....I want to know all the datatypes used differences...
Hi, I have the following Sql Data Source that I wish to bring back on my page: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:FrogConnectionString %>" SelectCommand="ProposalsCheckNewCustomerData" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:FormParameter FormField="MaritalStatusID" Name="MartialStatusID" Type="Int32" /> <asp:FormParameter FormField="LivingStatusID" Name="LivingStatusID" Type="Int32" /> <asp:FormParameter FormField="DealerID" Name="DealerID" Type="Int32" /> <asp:FormParameter FormField="VehicleTypeID" Name="VehicleTypeID" Type="Int32" /> <asp:FormParameter FormField="FinanceTypeID" Name="FinanceTypeID" Type="Int32" /> <asp:FormParameter FormField="MediaSourceID" Name="MediaSourceID" Type="Int32" /> <asp:FormParameter FormField="DealerContactID" Name="DealerContactID" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> The recordset brings back 7 values that I wish to populate into 7 Labels. 1. Should the SqlDataSource be on the page (between the Head tags) or within the Page_Load command of the Page Behind ?- If its on the page behind do any of you have any sample code to show me how to get the recordset up and running ? 2. How would I bind the results to a label ?- Within my old asp pages it was simple (I'm not saying its not simple here, I am still getting my head around vb.net)- eg <%=rsX("MaritalStatus")%> I guess its something very similar Thanks for any pointersFizzystutter
