Change Ntext To Nvarchar(max) In A Live Database
Oct 19, 2006
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.
View 4 Replies
ADVERTISEMENT
Oct 2, 2007
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
View 3 Replies
View Related
Nov 28, 2007
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)
View 2 Replies
View Related
Sep 27, 2005
I am new to SQL Server and would like to hear opinions on pros and cons of using nText vs. nVarChar data type for following uses:
URLs (up to 260 bytes)
Addresses (50-300 bytes)
Descriptions and comments (50-2,000 bytes)
Memos (up to 8000 bytes)
TIA.
View 8 Replies
View Related
Feb 6, 2007
Hello,
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 am using SQL 2005.
Thanks,
Miguel
View 4 Replies
View Related
Feb 15, 2008
Dear All,
i'm trying to convert the datatype from ntext to nvarchar.i'm getting error. is it not possible?
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 3 Replies
View Related
May 24, 2006
I just move our SQL server to version 2005. In new version ntext field is deprecated and documentation says that ntext(max) should be used.
If I have table Table1 and ntext column Column1. When I execute following SQL statements:
alter table Table1 alter column
Column1 nvarchar(max)
go
1.) Are out of row data automatically move to in row?
2.) Or should I also execute something like this ?
update Table1 set Column1 = Column1+'' where Column1 is not null
3.) Is there way to check if data is stored out or in row?
Best regards
edvin
View 9 Replies
View Related
Feb 19, 2008
Hi,
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 ?
Thanks,
Steve
View 5 Replies
View Related
Jan 22, 2005
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).
Josh
View 5 Replies
View Related
Jan 28, 2004
Hi all
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.
Thanks
Matt
View 1 Replies
View Related
Apr 10, 2008
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.
Howard
View 11 Replies
View Related
Apr 4, 2007
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?
View 2 Replies
View Related
Jul 23, 2005
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.
View 7 Replies
View Related
Dec 14, 2004
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...
Thanks a lot!
View 4 Replies
View Related
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.
PLEASE HELP ME!!!
Thanks,
Firoz Ansari
View 2 Replies
View Related
Mar 27, 2004
/* 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'
EXECUTE sp_executesql <-LINE 11
@SQLString,
@ParmDefinition,
@DB = @X,
@Num_MembersOUT = @Num_Members 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
View 3 Replies
View Related
Jul 22, 2002
The title says it all. I've used ALTER DATABASE, and ALTER TABLE...ALTER COLUMN to change all my character fields from 'Compatibility_42_409_30003' to the default I want ('Latin1_General_CI_AI') on databases converted from SQL7. Now I just have these ntext fields to change...
Al
View 1 Replies
View Related
Jun 29, 2015
What would be the best process to change the datatype of ntext fields.
I assume just changing the datatype - is not the way to go?
View 5 Replies
View Related
Aug 6, 2015
I want to restore a database (from an encrypted .bak file) - but *not* over the live original if you take my meaning. Encryption is the standard AES-256 that comes with Sql Server 2014 btw. I don't want the original touched/altered in any way. I would like to capture a success message if possible.I can extract the physical device name of the database in question using the following code:
SELECT physical_device_name, *
FROM msdb.dbo.backupmediafamily
WHERE media_set_id =(SELECT TOP 1 media_set_id
FROM msdb.dbo.backupset
WHERE database_name='MyDatabase'
AND type='D'
ORDER BY backup_start_date DESC)
I would like if the newly restored database was rename to something different than 'MyDatabase' (as shown above) and has different logs than the original. If possible, and capture a success message when restored.
View 5 Replies
View Related
Feb 1, 2005
have 2 databases in sql server 2000
Live
Dev
I need to append all the data from LIVE into DEV environment. I have tried using MS Access (linking tables & importing tables and running APPEND query to update the rows from LIVE to DEV but PK & FK is causing problems as some data will have the same ID's....
Could I use DTS--Access wizard in SQL Server.
What is the best option to use?
Thanks all in advance
View 2 Replies
View Related
Dec 7, 2006
Hello,
I haven written a Windows program in Visual Studio 2005. This program stores data in and retrieves data from a SQL Express database. The customer is starting to use the program and filling the database with data. However the program is still under construction (new functionality will be added) and so is the database (new tables, views etc. will be added).
Of course I could send the customer the new database, but than all data already entered will be lost.....
I'm thinking about using the SQL Management Studio Express and VS2005 to connect remotely to the database (a VPN connection is availabe) but worry about performance issues.... Or should I use some kind of replication / synchronization and how, because this is new to me.
What is the best way to solve this 'problem' ?
View 5 Replies
View Related
Aug 2, 2007
hi.
I would wish to know whether it is possible for me to program the code from html or aspx ( from web hosting server ) to our office intranet database which is window server 2005?
I had tried to google for more info but there are too complicated thing for me to understand.
Actually i am doing the web form feedback form from the website for the public to fill in and submit. i am using the mysql from web server to store the records from the feedback form.
We have another database in our server, ms server 2005 to store records in the intranet office only. i need to get the records from mysql to intranet database. i don`t think there is a automated scripting which allow u to auto update the intranet database from mysql( web server )
So i thought of one thing . program html or aspx to insert records directly to our database intranet . but how ?
I appreciated that.
Regards
newbie on aspnet.
View 3 Replies
View Related
Feb 5, 2008
Hi,Ive been building a project on a local server and now need to transfer the files and database over to the live server. I have managed to transfer the files quite easily, however I am having a few problems transferring the database using Microsoft SQL Server Management Studio. When I try importing data to the live database, all the data and tables are brought across however some of the values for the fields are lost. For example all the fields which had a primary key no longer have one, and all the set default values for the fields are also lost. Ive been considering just going through every table in the database and re-entering the correct values for the fields, but this seems a bit time consuming and I'm also concerned about possible errors which could arise as there will be an inconsistency.Another method I tried was copying the SQL query across which created all the tables and the correct values for the fields. However when I tried to import the data an error was produced as you cannot import data into a table which has read only fields etc..Does anyone know a solution?Thanks for your time.
View 2 Replies
View Related
Mar 23, 2006
im using the the login controls with asp.net
when i test locally everything works great, its see the ASPNETDB.MDF file, checks the login info and passes the user on to the next page.However when i move the application to the live server it fails, it cant find the datasource that contains users/password etc. here is there error:An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) So im missing something here, im used to connecting directly to a remote SQL database and accessing the info from there.
Can someone point me in the right direction? Can the MDF file not be used off the server itself without SQL express installed? Do i need to migrate those tables into a real SQL database?Thanks,Sean
View 2 Replies
View Related
Jul 31, 2007
Hi
I'm migrating from access to sql server 2005.
With access, I did full backups every 6 hours, by simply copying the .mdb file to a folder on another hard disk on the server, and then ftp it to a distant server.
I guess i just can't simply do the same with the SQL server .mdf file.
What is the best practice to achieve the same goal ?
Thank you
View 9 Replies
View Related
Sep 11, 2007
I'm not sure this is the exact forum in which to ask this question, so if there is a more appropriate forum, please point me in that direction.
Basically I'm looking for advice and best practices for dealing with an upgrade plan for a hosted database. Here is the situation:
- Hosted software application (.Net)
- Uses SQL Server 2005
- When we upgrade the software application to a new version, there will be schema and data changes that need to be applied to the database
- For the software application, we have considered upgrading an offline machine and then switching old one instantaneously.
- For the database, I'm trying to figure out if we can keep everything live for upgrades, or if we should have a small amount of scheduled downtime to upgrade.
So we have identified 2 basic directions:
1) Keep the database live and apply the new script changes just after the application software is upgraded. This means zero downtime, but we'd have to design and test our application to be backward compatible, so that the new version of the application code could handle both the new schema design, and the old schema design. It seems that this option would add a huge amount of complexity because we would essentially be testing a 3rd app/schema combination.
2) Take a small amount of scheduled downtime and upgrade the application code and database all at once.
Obviously option #2 is the most desirable from a development process point of view, and #1 is the most desirable option from a business point of view (zero downtime).
So, I'm looking for some advice on this. Do most people take the whole system down? Are there 100% live systems that deal with this? Are there other approaches I'm not considering?
Any information would be greatly appreciated. Thanks.
View 4 Replies
View Related
Jan 31, 2015
I want to Replace The Big Log database with A new one ( A database with same structure).But current DB has many connection .
This is my plan :
1- Create a new database with same structure.
2- Rename current database to olddb with this code :
USE master
GO
EXEC sp_dboption CurDataBase, 'Single User', True
EXEC sp_renamedb 'CurDataBase', 'OldDataBase'
GO
3- Rename Newdb to current DB.
USE master
GO
EXEC sp_renamedb 'NewDataBase', 'CurDataBase'
is it true ? and Tsql code is ok ? (dont forget many of connection to curdatabase (that Is a log db) and loss some seconds data is not problems)
View 4 Replies
View Related
Oct 7, 2015
Client is running X- version of application and corresponding database size is huge. Now client's vendor is releasing Y-version of same application with many database schema changes (like new tables added, new columns added, renamed existing columns and etc) To upgrade to the Y-version, vendor is suggesting to my client that down the system and do the upgrade for application/database to Y-version. We are sure that this process will take days together to upgrade to the Y-version. My client is not ready to down the system for that long. So we are trying to find the solution with minimal down time.The approach we are thinking is,
1) Create the replicated database to another server (server2) from production server(server1) using golden gate with X-version
2) Create new tables/schema updated tables from Y-version database on same server1. Here for Updated schema tables we are planning to use the name <table_name_Y_version> as the same table name exists in X-version.
3)With above 2 steps, golden gate replicate the changes from production to server1 and server1 will have the new Y-version table schema (with different concatenate name ' _Y_version'). BTW , there is no affect for the production
4) At this stage we are planning to find best approach, to fill the '<table_name>_Y_version' from X-version tables. two challenges here a) all data needs to be moved to Y-version tables b) they have to sync data in real time.
we thought of going to
a) ssis package to pump the data to Y-version tables, but real time data will not sync.
b) trigger based technique, previous experience said, lot of load
c) thinking about sql replication.
View 5 Replies
View Related
Dec 27, 2006
Live Webcast tomorrow
Essential Team System for Database Developers
1 PM Central time 12/28
https://www.clicktoattend.com/invitation.aspx?code=112602
View 2 Replies
View Related
Feb 27, 2006
Can any one tell me what is the nText type in SQL 2005 mobile database please? How long is the text that I may put into this type?
Thanks in advance.
View 4 Replies
View Related
Jun 20, 2007
Hello,
I have around 7 ntext fields in my data base table and I am getting data from the data base table through executing stored procedure, But when I am displaying data using record set, few of the ntext fields in recored set are empty .Iam sure that these are having data in table.
I am not sure why recordset is lossing that ntext field data?Because of this I am unable to display that data in web form.
any ideas really appriciated.
Thanks
Ram
View 4 Replies
View Related
Jan 22, 2006
I had several databases under sqlserver ce 2.0 (in my Pocket PC) which contained ntext fields. The size of the databases varies from 50,000 to 700,000 records. The size of an ntext field is from 4 bytes to 2 megabytes.
When I recreated my databases under sql server 2005 mobile on my desktop using VS2005 (see my post just under this one), I saw a big difference between the old and new database sizes. This problem was mentioned in one of the posts in this forum and the reply was to replace ntext data with ncharvar type.
Since most of my data was longer than 4000 bytes (which is the limit for nvarchar type), I couldn't use this suggestion. Instead, I changed my ntext type to image type and used a GetByte conversion.
No change! The size of the new database is still 50 % larger than the original. Since the difference is around 300 MB, this is an unacceptable thing.
Now, I either wait from somebody to suggest a new solution (apart from keeping the ntext data in a separate binary file and keep index of the records of this file in the records of sql database) or, most preferably, have
Microsoft solve this problem as soon as possible.
Thanks for any help in advance
Talat
View 4 Replies
View Related
Nov 16, 2005
I have finished a change request from our client. I need to update clients' database with the one in developments.Here is the changes i made to database:Added/Changed some tablesAdded/Changed some stored proceduresAdded data to some dictionary tableThe data in clients' current database MUST be kept. So how can I merge the changed information to clients' database?
View 3 Replies
View Related
