Converting CHAR/VARCHAR/TEXT Into NCHAR/NVARCHAR/NTEXT!!!
Jan 28, 2005
Hi,
We are in process of converting all of the data type of the fields from CHAR/VARCHAR/TEXT into NCHAR/NVARCHAR/NTEXT (DBCS). Having more than 900 store procedure its look like real pain to make modification in all of the SPs.
After failed to find any help from GOOGLE, I am posting this request. I am basically looking for any automated tool which are convert data type in SP based on the field of the table used in the SP. Or at least which can provide me some sort of list which can helpful for doing manual reactoring.
can anybody please explain me why microsoft using nvarchar/nchar instead of varchar/char in northwind database and pubs database. I know if a column holds unicode data you should use nvarchar or nchar but for me all those tables in northwind/pubs are not holding unicode data. but still why microsoft settled for nchar/nvarchar.
Could someone please help me by explaining which one is best to use and when? For example, storing the word "Corona Del Mar" - which Data Type would be suggested? Thanks.
Hi All: I am new to Sql 2000 database,Now I'm planing to create a table in my databse,my table included below fields like this : PoNo(the length is 15 characters) ,Supplier Name(the length is 50 characters).etc but I don't how to select the datatype for them. should I select Char or VarChar ? which one is the best slection ? thans in advanced!
When I tried to insert armenian by doing the following insert into tablex (field1) values (N'testdata') it does not display in query analyzer or in the database as armenian. When I copy this to word it does not convert it.
What else am I supposed to do to get that information to redisplay the correct way and I would appreciate any tutorials or samples you can show or direct me to.
/* INFO USED HERE WAS TAKEN FROM http://support.microsoft.com/default.aspx?scid=kb;en-us;262499 */ DECLARE @X VARCHAR(10) DECLARE @ParmDefinition NVARCHAR(500) DECLARE @Num_Members SMALLINT SELECT @X = 'x.dbo.v_NumberofMembers' DECLARE @SQLString AS VARCHAR(500)
SET @SQLString = 'SELECT @Num_MembersOUT=Num_Members FROM @DB' SET @ParmDefinition = '@Num_MembersOUT SMALLINT OUTPUT'
Just Need Help On This Error Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 11 Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
I dont know why im getting a errrror b/c I followed http://support.microsoft.com/default.aspx?scid=kb;en-us;262499 exactly
Is it possible to convert from ntext to varchar or text?? This is the syntax: ALTER TABLE ssu ALTER COLUMN analysis varchar NULL. This is the error message Server: Msg 4928, Level 16, State 1, Line 1 Cannot alter column 'ANALYSIS' because it is 'ntext'. Thanks in advance!
I have followed many tutorials on selecting and replacing text in text fields, varchar fields and char fields, but I have yet to find a single script that will to all 3 based on field type. Let's assume for a moment that I don't know where all in my database a certain value that I need changed resides ... i.e., the data's tablename and fieldname. How would I go about doing the following ... or more importantly, is this even possible in a SQL only procedure?1) Loop over entire database and get all user tables2) Loop over all user tables and get all fields3) Loop over all fields and determine the field type4) switch between field types and change a string of text from 'a' to 'b'Please be gentle, I'm a procedure newb.
My organization have a web-based application and needs it to support multilingual so we will be adapting our app to use unicode. However, one of our problems is to convert existing data from text to ntext. I couldn't find anything that document this. What is the best way to do that? I would like to be able to migrate the data from an existing text column to another ntext column in the table.
If I can't do that, what are the options? If it's possible, I would like to be able to do this in sql script.
I am running this query to an sql server 2000 database from my aspcode:"select * from MyTable whereMySqlServerRemoveStressFunction(MyNtextColumn) = '" &MyAdoRemoveStressFunction(MyString) & "'"The problem is that the replace function doesn't work with the ntextdatatype (so as to replace the stresses with an empty string). I hadto implement the MySqlServerRemoveStressFunction, i.e. a function thattakes a column name as a parameter and returns the text contained inthis column having replaced some letters of the text (the letters withstress). Unfortunately, I could not do that because user-definedfunctions cannot return a value of ntext.So I have the following idea:"select * from MyTable whereCheckIfTheyAreEqualIngoringTheStesses(MyNtextColum n, '" & MyString &"')"How can I implement the CheckIfTheyAreEqualIngoringTheStessesfunction? (I don't know how to combine these functions to do what Iwant: TEXTPTR, UPDATETEXT, WRITETEXT, READTEXT)
I am trying to convert a single code page MS Server database into a unicode database, using the unicode data types,NCHAR, NVARCHAR, NTEXT. The problem is that in the original database, indexes and constraints have been defined on the tables whose configurations need to be changed. As a result, the ALTER TABLE command fails. Are there any other alternative solutions? Also, data from the old database needs to be preserved. The objective is to create a unicode database which keeps the old data intact as well as accepts the new data in unicode. It would be great if you could help! Thanks, Sheetal.
Hi I have a table, which contains Char and NChar NOT NULL columns Now I need to change it to NULL, when I use the following command, it fails for the following error,
The command I used, ALTER TABLE <TableName> ALTER COLUMN <ColName> CHAR NULL ALTER TABLE <TableName> ALTER COLUMN <ColName> NCHAR NULL
Msg 8152, Level 16, State 13, Line 1 String or binary data would be truncated. The statement has been terminated.
But for the same table, the below command executes fine, ALTER TABLE <TableName> ALTER COLUMN <ColName> SMALLINT NULL
Also I can change the NULLABILITY from NOTNULL to NULL using Enterprise Manger, editing the table using Table Design and selecting Allow Nulls option.
#1 This stored procedure can be used to search and replace substring in the char, nchar, varchar and nvarchar columns in all tables in the current database. You should pass the text value to search and the text value to replace. So, to replace all char, nchar, varchar and nvarchar columns which contain the substring 'John' with the substring 'Bill', you can use the following (in comparison with the SetTbColValues stored procedure, this stored procedure replace only substring, not the entire column's value):
My organization have a web-based application and needs it to support multilingual so we will be adapting our app to use unicode. However, one of our problems is to convert existing data from text to ntext. I couldn't find anything that document this. What is the best way to do that? I would like to be able to migrate the data from an existing text column to another ntext column in the table.
I brief you about my system, I used List manager system to store the messages and distribute to all members. Right now,by design the Lyris system keep the message in the text field which mean it 's not support multilanguage directly because of unicode field. We needs to create new Db which has the data structure as same as Lyris but just one difference is keep the message in unicode format (ntext) which we need the sql script to automatically update the new record get from Lyris to new DB.
If I can't do that, what are the options? If it's possible, I would like to be able to do this in sql script.
I have a column in my extract table as nchar(3) and in the destination (the same column with diff name ) it is decimal(3,0) .....i tried to use dataconversion transformation.....i even tried to use cast/convert fn's in the SQL Command (which i use in the "Source Transformation" to get the columns from the extract table).
I tried all the ways i can and still i get the same error..:
[OLE DB Source [1]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E07 Description: "Error converting data type nvarchar to numeric.".
Can we actually do it...?? any help would be appreciated.
I need to save some news text in an SQL table. The text can be long. 1. Should I use nvarchar(MAX) or nText? 2. And what is the difference between nText and Text?
I have a table that contains a lot of demographic information. The data is usually small (<20 chars) but ocassionally needs to handle large values (250 chars). Right now its set up for varchar(max) and I don't think I want to do this.
How does varchar(max) store info differently from varchar(250)? Either way doesn't it have to hold the container information? So the word "Crackers" have 8 characters to it and information sayings its 8 characters long in both cases. This meaning its taking up same amount of space?
Also my concern will be running queries off of it, does a varchar(max) choke up queries because the fields cannot be properly analyzed? Is varchar(250) any better?
Should I just go with char(250) and watch my db size explode?
Usually the data that is 250 characters contain a lot of blank space that is removed using a SPROC so its not usually 250 characters for long.
Here is the example data <qMultipleChoice><qText>The%20AE%20understands%20what%20conditions%20the%2 0Account%20Manager%20is%20allowed%20to%20sign-off 20on.</qText><qChoice>Strongly20Disagree</qChoice><qChoice>Disagree </qChoice><qChoice>Agree</qChoice><qChoice>Strongly%20Agree</qCh
I want result look like this First Column:The AE Understands what conditions the Account Manager is allowed to sign-off. Second Column: Strongly Disagree Disagree Agree Strongly Agree
This is what i had so far Select (SUBSTRING(QuestionText, (PATINDEX(N'%<qText>%', QuestionText) + 7),(PATINDEX(N'%</qText>%', QuestionText) - (PATINDEX(N'%<qText>%', QuestionText) + 7)))) From tblQuestion
my result: The%20AE%20understands%20what%20conditions%20the%2 0Account%20Manager%20is%20allowed%20to%20sign-off%20on.
I have problem with replace '%20' and how to make the second column. Any Help? Thanks Shan
my question concerns both desktop and device apps.
I'm using sql compact to store some data. I often have to store strings (descriptions, url, etc.) but I don't know when to use nvarchar or ntext.
Nvarchar needs to have a size limit, but I often set it to 8092 when I don't know the actual limit (urls can be very long !). I fear Ntext because I suppose there is performances impact.
Is there any "rules" to help to choose which data type I'd use ?
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).
I have a table that contains an ntext column for storing values up to a couple of Mb in size.
However, I estimate that 95% of the values stored in this ntext field will fit into an nvarchar(4000) field.
Is it worth me having both fields in the table?
i.e. For rows where the values < 4000 characters I would store the value in the nvarchar column. Otherwise I would use the ntext column.
Can anyone confirm whether this technique would increase performance given that ntext values are sort of stored separately to the rest of the table data?
A colleague of mine is an Oracle DBA and he mentioned this technique is fairly caommonly adopted in the Oracle world.
I have a live SQL 2005 database that has ntext fields, when the ntext fields go over 4000 chars the record can no longer be edited. It throws a string or binary data would be truncated error. I tried turning text in row OFF, but it did not work. Can anyone forsee any problems with changing the ntext fields to nvarchar(max) in the live database? Also, I came across sp_tableoption N'MyTable', 'large value types out of row', 'ON', does this work for ntext also? sp_tableoption N'MyTable', 'text in row', 'OFF' did not do anything.Any help would be appreciated.
Like in the subject: What are the cons and pros of using nvarchar(max) versus ntext? Does it have something to do with having to enable full text search perhaps in the latter case?
Hi all,I need to store data into about 104 columns. This is problematic with MSSQL, since it doesn't support rows over 8kb in total size.Most of the columns are of type NVARCHAR(255), which means we can't havemore than 8092/(255*2) = 15 columns of this type.With a row length of more than 8kb, SQL gives a warning that any rows overthat amount will be truncated.So far I'm seeing two possible solutions to this problem:1. Split data into multiple tables with the same ID column accross alltables, and then join them on SELECT statements.2. Use NTEXT instead of NVARCHAR. NTEXT's length is 16 bytes because itcontains a pointer to the actual value stored somewhere else. However, NTEXTdoesn't support regular indexing, only through a Full-Text Index catalog. Inthis case I'll need to user "WHERE CONTAINS(columnName, 'sometext')" toperform searches, which is bearable.I'm inclined toward #2. However I haven't used Full-Text indices before anddon't know their limitations. Will I run into problems with NTEXT? Is therea better solution?Thanks.-Oleg.
I hope this is the right forum for this question, my apologies in advance if it isn't....
We have a web based CGI product (written in C++ VS 6) that uses ODBC and takes text from a submitted web page and stores it in a SQL Server table in a field of type "ntext". The user in question is copying and pasting this text from an MS Word 2003 document. After the initial save our app errors out trying to access the table it just wrote to, and when we look in the table we see that up to **200 carriage returns** have been mysteriously inserted into the ntext field!! (Our product has been out in the field with no such problem for several years, so we are thinking it's related to something specific the customer is doing - perhaps with using MS Word for the source text.) We have tried but cannot duplicate the problem, but the customer sees it with each attempt to modify the table in question. The only thing that I see out of the ordinary is that the field in question is of type "ntext" - which supports unicode, instead of nvarchar. Does any of this ring a bell for anybody? I'm thinking of changing the field type to nvarchar to see if that solves the problem. Thanks, Steve Bradbery
I have used a query statement with the following WHERE string to 'Fill' a dataset. "AND (A.ApptsDate > '" & strApptPreDate & "' OR (A.ApptsDate = '" & strApptPreDate & "' AND A.ApptsTime >= '" & strApptPreTime & "' ))" & _ and strApptPreTime is defined as:Dim strApptPreTime As String = SomaShared.strPadTime(CStr(dApptCalcNewDate.Hour) + ":" + CStr(dApptCalcNewDate.Minute))
Somehow, the dataset showed only the ApptsTime after 10 am. After more than 2 weeks of debugging, I still can see a dataset watch for > 10 amAppsTime only. Now I am guessting, the problem is 9 is different from 10 - 16, it all because 9 is single digit. Then I check the data type settings for these variables. Here are what I found: In SQL Server Agent job, the ApptsTime data was 'inserted' by @NewApptTimes, which is declared as char(5).In SQL Server database, the ApptsTime was defined as nvarchar(15). My question are: 1. The reason why there were no 9 am data for the dataset, is becasue 9 am of nvarchar(15) is not > 8:30 of strApptPreTime? 2. If the answer to the quation 1 is yes, how do I define AppsTime and/or strApptPreTime? TIA,Jeffrey
I want to retrieve data from SQL containing non English character but fail, can anyone shed me some light?
What I use currently:
Dim strSQL As String strSQL = "SELECT ArticleID, " strSQL &= "ISNULL(Body, '') AS Body, " strSQL &= "ISNULL(Subject, '') AS Subject " strSQL &= "FROM Articles " strSQL &= "WHERE (Subject LIKE N'%' + @Keyword + '%' OR [Body] LIKE N'%' + @Keyword + '%') " Dim con As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim cmd As New SqlDataAdapter(strSQL, con) cmd.SelectCommand.Parameters.Add("@Keyword", SqlDbType.NVarChar).Value = keyword ...
I'm not so sure where should I place the letter "N", I use :
SELECT ArticleID, ISNULL(Body, '') AS Body, ISNULL(Subject, '') AS Subject, FROM Articles WHERE (Subject LIKE N'%SomeNonEnglishString%' OR [Body] LIKE N'%SomeNonEnglishString%')
in Query Analzyer, it works! But it failed in my program... oh my god...
