I've just gotten my data into SQL Server, and changed the connection string
on the program.
There was a memo field (Access 2000) and we put that in a text field (with a
default length of 16?).
Now, when displaying the data online (through asp pages), there are big
white-space gaps in the pages (that display the memo field data), images
have a "double" url
(http://www.mysite.com/thisfolder/ht...thelinksite.com)
there are lots of place with " ".
The field data has html tags in it, if that has anything to do with it.
It seems like I've heard that there are issues with text fields. Is this
what they meant? Is there anything I can do?
Hi,I have a problem to insert(update) a long text (more than 64K) intoSQL 2000 (datatype - 'text'). It cuts the data and insert only 64K.MSDN says: "When the ntext, text, and image data values get larger,however, they must be handled on a block-by-block basis. BothTransact-SQL and the database APIs contain functions that allow applications towork with ntext, text, and image data block by block." Could somebodygive me an example how to do this, please.Thank you
Hi all I have a table with one nText Field , some times i need to realize whether two records have the same value in that nText Field or not , so how can i compare these two records based on their ntext Fields.? in the other words i'm looking for some thing like the follows: declare @a nTextdeclare @b nText select @a=MynTextField from MyTable where PK=18select @b=MynTextField from MyTable where PK=21 if @a=@b print 'Records Are the same'else print 'Record are not the same'
I haven't had to deal with ntext fields before, I'm finding that they are a ton of fun
Can someone tell me what the impact would be of changing all of the ntext column in ReportServer, ReportServerTempDB, and RSExecutionLogs to varchar(MAX)?
I can only guess that ntext was left in for backwards compatibility with sql2k?
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
what am trying to do is adding an instead of trigger on update which concatenates the old value in the text column with the new value
so the problem is how i can concatenate both values since i cant declare a variable of type text.
A solution may be dividing the text column into many varchar(8000) variables and then add them but i dont know how it can be implemented or if there's an easier solution
the code am trying:
CREATE TRIGGER InsteadTrigger on tbl INSTEAD OF Update AS BEGIN
if exists(select null from deleted) begin --am considering updating only 1 row update tbl set theValue=((select theValue from deleted)+(select theValue from inserted)) end
i tried the writetext and updatetext functions and the problem is always related to both columns concatenation
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.
Can some one please explain how the storage of a huge text or ntext object ( say a string of characters 100 KB in size ) is carried out please?
With a data page maximum size of 8000 bytes, how does SQL handle the storage of such an string - it would obviously obverlap multiple rows etc. Does the application writing it to the database have to split the object over multiple rows and manually keep track of which bit is in which row so it can be re-created later in correct sequence by adding the bits back together, or is it done some how differently?
My Stored Proc runs through a loop and concats the contents of each field into one big nvarchar. Procedure works fine on a smaller scale but now it is being implemented on a very large table and the results of the sequel overflow the nvarchar limits. I looked into using text and ntext but both cannot be declared locally. Does anyone know how I can work aroudn this limitation?
Summary:The problem is that the temporary variable I am using (nvarchar) is too small to contain the robust size that the SQL is concating into it. The final field it winds up in is a text field and will be able to handle the amount of data, its just getting the data there is the issue..... Your thoughts please....
Hi there! I've read through several of these forums and am impressed by the talent out there. I'm hoping someone could share some insight on a little complication I've got.
I have a sproc that essentially is pulling all mail items from a profile, reading them in and storing them in a table. It is all working fine MINUS the fact that I cannot pass the message because it won't allow me to pass text or ntext.
If anyone has any ideas I would truly appreciate it. In the meantime I'll be searching through the forum looking for a way to make it happen. Thx, Camey
declare @post_id int declare@topic_id int declare@forum_id int declare@poster_id int declare@post_time int declare@poster_ip int declare@post_username char(25) declare@enable_bbcode smallint declare@enable_html smallint declare@enable_smilies smallint declare@enable_sig smallint declare@post_edit_time int declare@post_edit_count smallint declare@bbcode_uid char(10) declare@post_subject char(60) declare@post_text text
Set @msg_id = NULL while (1=1) begin exec master..xp_findnextmsg @msg_id = @msg_id output if @msg_id is null break
I have a table column type as nText, however there are some Chinese character stored in that field and it is a messed up as it is not readable.
In my vb.net code, I did Convert to unicode by getting the byte of each character and encode it with UTF8 e.g:
Public Shared Function ConvertToUnicode(ByVal s As String) As String
' Convert To Unicode
Dim MyBytes As Byte() = Encoding.Default.GetBytes(s)
Dim GBencoding As Encoding = System.Text.UTF8Encoding.UTF8
Return GBencoding.GetString(MyBytes)
End Function
This works well but ,the problem is that it slows down the process quite alot, and I wonder are there any text encoding method I can use in SQL that can run when i do the SELECT Statement?
SELECT Convert(MyNTEXTColumn) .... something like that?
Hi, i'm trying to do a full text search on my site to add a weighting score to my results. I have the following database structure: Documents: - DocumentID (int, PK) - Title (varchar) - Content (text) - CategoryID (int, FK) Categories: - CategoryID (int, PK) - CategoryName (varchar) I need to create a full text index which searches the Title, Content and CategoryName fields. I figured since i needed to search the CategoryName field i would create an indexed view. I tried to execute the following query: CREATE VIEW vw_DocumentsWITH SCHEMABINDING ASSELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryNameFROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryID GOCREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndexON vw_Documents(DocumentID) But this gave me the error: Cannot create index on view 'dbname.dbo.vw_Documents'. It contains text, ntext, image or xml columns. I tried converting the Content to a varchar(max) within my view but it still didn't like. Appreciate if someone can tell me how this can be done as surely what i'm trying to do is not ground breaking.
i have created asp.net page, one feild of this page text area. when i insert some text through this page in "text feild" of SQL server on few words of this feild cut and inserted to text feild of SQL server but all text that i have written in text area feild.
can u please help me how to handle so that i can all text in text feild of SQL server data type text/next.
I have two SQL 2005 SP2 Server and want to use the transaction replication with updateable subscriber. Now the problem is that i can do any changes on the master server. But if i want to change a record on the subscriber which contains a ntext, text, oder image column - then i geht the error that the field will be NULL on the master.
Is there any solution to fix this problem? I dont wan`t to change the datatype vom ntext to varchar(max) !!
I have to run a dynamic sql that i save in the database as a TEXT data type(due to a large size of the sql.) from a .NET app. Now i have to run this sql from the stored proc that returns the results back to .net app. I am running this dynamic sql with sp_executesql like this.. EXEC sp_executesql @Statement,N'@param1 varchar(3),@param2 varchar(1)',@param1,@param2,GO As i can't declare text,ntext etc variables in T-Sql(stored proc), so i am using this method in pulling the text type field "Statement". DECLARE @Statement varbinary(16)SELECT @Statement = TEXTPTR(Statement)FROM table1 READTEXT table1.statement @Statement 0 16566 So far so good, the issue is how to convert @Statment varbinary to nText to get it passed in sp_executesql. Note:- i can't use Exec to run the dynamic sql becuase i need to pass the params from the .net app and Exec proc doesn't take param from the stored proc from where it is called. I would appreciate if any body respond to this.
SQL ERROR - I need DISTINCT but can use it with image,ntext, text - How To work around??? ! more - How to Work around - MIN() I cant use it when having text in SELECT statement The text, ntext, or image data type cannot be selected as DISTINCT.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: The text, ntext, or image data type cannot be selected as DISTINCT.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
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.
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.
I do not understand the error message but I do know it is associated with a specific table and one of its indexes. If I try to drop the index that is the error message I get.
I have copied the data from the table to a new table and rebuilt the indexes on the new table and everything is working fine.
The bad table is now renamed to myTable_BAD. I now want to delete it but can't as everytime I enter drop table myTable_BAD
I get Page (1:404399), slot 5 for text, ntext, or image node does not exist.
I have a data table in my project that has a "text" (not varchar) field in it. I am trying to load this field with little paragraphs of text for showing on my pages. How do I get the free text loaded into the table? The database explorer and all the data grid controls cut the text off at one line. There doesn't seem to be any way to get multiline text into this table.
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)
using SQL 6.5 sp_makewebtask in a stored procedure I want to create a press-info file. My query calls four fields, a datum field, two varchar fields and a text field, it's the field "PrText" in my example:
EXEC sp_makewebtask @outputfile='F:EntwicklungRedaktionZeitwenden27.ht m', @query='SELECT Datum, Ort, Headline, PrText FROM news WHERE ID = 27', @templatefile='F:EntwicklungRedaktionZeitwendenTem plates.htm'
My question: Am I limited to 255 characters in this case? After 255 characters the text is cut off and I have only a truncated text fragment.
Is there a possibility to get the whole text without using @blobfmt (I don't want to use this parameter because I want to have the datum data, the varchar headline AND the text in ONE HTML-file.)
I have tried the same as dynamic query in ASP where I have no problem to get the whole content of the text field, but I want to create a static file because this information will be read often, but won't be updated frequently and I don't want to charge the server resources to much.
Has anybody an idea? Any help would be appreciated.
The Text datatype can hold several million characters The size of a record can be no more than 8 KB in normal data.
Created a table with one text field. For some reason can only store 1K of data into it. Even if the 8K limit was a factor (which it shouldn't for a text field) I cannot add more than 1K of data into my text field. 1023 characters to be exact.
Why is SQL Server 2000 doing this and more importantly how to resolve?
Hi, I'm sure that this is probably a really easy question to most of you, but I seem to be spending ages on it as I am new to SQL. how do I add the carraige return char between joining two text fields ie.
New to using MS SQL server! I have a requirements DB. Before I came on board, the DB was seeded with requirements text and a requirements ID in separate columns. I've been asked to append the ID in front of the text:
Text - The user shall be able.... ID - 1.01.01. Combined - 1.01.01.The user shall be able....
I've created the following query that displays the columns:
CREATE VIEW dbo.ReqID AS SELECT RQREQUIREMENTS.ID, RQREQUIREMENTS.REQUIREMENTNAME, RQUSERDEFINEDFIELDVALUES.FIELDVALUE
FROM RQREQUIREMENTS LEFT OUTER JOIN RQUSERDEFINEDFIELDVALUES ON RQREQUIREMENTS.ID = RQUSERDEFINEDFIELDVALUES.REQUIREMENTID
WHERE (RQUSERDEFINEDFIELDVALUES.FIELDID = 171)
The Text field is REQUIREMENTNAME The ReqId is FIELDVALUE
Should I create an UPDATE query based on this SELECT, updating the REQUIREMENTNAME column with FIELDVALUE + REQUIREMENTNAME?
I am trying to update text fields where the field only has the value"closed." So a field that has closed and anything else will not beupdated.update dbo.Histset dbo.Hist.Notes = ''FROM dbo.HistWHERE (Notes LIKE 'closed_')update dbo.Histset dbo.Hist.Notes = ''FROM dbo.HistWHERE (Notes LIKE 'closed%') and len(convert(varchar(7000),notes)) = 7Neither code works and i get this error: "Subquery returned more than1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression.The statement has been terminated."What's another way to make the update?
