I've got a table that I have to update in preparation for our environment move (2k to 2005 SP2). The developers that designed the application created a table called schemas, which holds the contents of an XML file inside of an ntext field named Data.
I need to parse through the field and do a find/replace to replace all instances of www.site.com with www7.site.com. It's all over the place in the file. The problem is, that the datalength() of each of the fields (there are 2 rows) are above 15000.
normally, I'd run something like this:
update schemas
set data=replace (cast(Data as varchar(max)),'www.site.com','www7.site.com')
where data like '%www.site.com%'
Smaller columns it works great - but it won't work on these because they're too big (the update will chop anything beyond the varchar(max) value). I could do it manually, but this DB will be refreshed from production on a weekly basis and I'd like to script as many of the environment changes to the DB as much as possible.
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
I am having a problem writing a large amount of ntext data to a field within an ADO recordset. I am using the append chunk method but it does not seem to work. The SQL 7 field will hold the data its only about 60K.
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.
Hi! I recently was confronted with a problem where a piece of text that was included in many NTEXT column values in a table needed to be replaced with another piece of text. You can't issue normal REPLACE statements against NTEXT columns, so this seemed to be a bit of a challenge €” issuing a REPLACE() against a TEXT or NTEXT column in SQL Server yields error
Server: Msg 8116, Level 16, State 1, Line 1Argument data type ntext is invalid for argument 1 of replace function.
For Example: I want to replace string <![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage]]> with <![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage, ArgParamReferralId]]> in NTEXT column values in a table.
A sql server 2000 table contains a ntext field that needs to be updated. The update is replacing a particular string in the ntext field. I heard it's hard to do it in sql server 2000 itself. Can it be done easily in an SSIS? Please kindly provide example. Thanks.
i define a clumn like this: clumn name: Body data type: ntext then i run a store procedure which is defined as : create insert_artcle as ( @body ntext ) insert into article{body} values{@body} but the data get into the database is truncated to 8 charactors what is the problem? Is it has something to do with the ntext size 16?
I need to find a way to update a notes field that is ntext in a table with an update statement. The code will run as a SQL job. I though something like the following, but doesn't like it: update dbo.myTable set notes = notes + ' addtional notes to append' Any ideas? thanks...
Loading thousands of records to the DB through bulk insert. There's one field NText which I have left NULL because it will be hard to gen dummy flat file to it.
I have another table which has the Ntext Value which i will want to copy and duplicate to the other table.
what is the way to do it?
simply said i want to update a record with NULL value from one table with NText field with the value from another table..
The data types ntext and nvarchar are incompatible in the equal to operator. 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 data types ntext and nvarchar are incompatible in the equal to operator.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. Thanks , John
Hi My SP to update a value type ntext don't work, ALTER PROCEDURE UpdateMultiContentFullDescriptionByID (@ContentID int, @FullDescription ntext) AS UPDATE MultifunctionalContent SET FullDescription = @FullDescription WHERE ContentID = @ContentID RETURNPublic Sub UpdateMultiContentFullDescriptionByID(ByVal ContentID As String, ByVal FullDescription As String) ' Create Instance of Connection and Command Object Dim myConnection As SqlConnection = New SqlConnection(Const.ConnectionString) Dim myCommand As SqlCommand = New SqlCommand("UpdateMultiContentFullDescriptionByID", myConnection) ' Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure
' Add an input parameter and supply a value for it myCommand.Parameters.Add("@ContentID", SqlDbType.Int, 4) myCommand.Parameters("@ContentID").Value = ContentID
' Add an input parameter and supply a value for it myCommand.Parameters.Add("@FullDescription", SqlDbType.NText, 8000) myCommand.Parameters("@FullDescription").Value = FullDescription
' Open the connection and execute the Command myConnection.Open() myCommand.ExecuteNonQuery() myConnection.Close() End Sub Then how to ...
SQL Server 2000 : I have a series of tables which all have the samestructure. When any of these tables are modified I need to syncrhoniseall of those modifications with one other table wich is a sort of mergeof the individual tables with one extra column.For most of these tables this is not a problem. The problem arriveswhen one of the tables has an ntext column which obviously can not beused in an update or insert trigger.Here's an example of one of them:CREATE TABLE tblImages(ID INT IDENTITY(1,1) PRIMARY KEY,Inventory nvarchar(8) NOT NULL,Coll nvarchar(8) NOT NULL,ImageFile nvarchar(128) NOT NULL,ImageNotes ntext NULL,TS timestamp NULLCONSTRAINT U_Images UNIQUE NONCLUSTERED (ItemCode, Inventory, Coll,ImageFile)I then had created an update trigger which looked like this:CREATE TRIGGER COLLNAME_UTRIGGER ON COLLNAME_ImagesFOR UPDATEASBEGINUPDATE tblImages SETInventory = inserted.Inventory,Coll = 'COLLNAME',ImageFile = inserted.ImageFileName,FROM inserted INNER JOIN tblImages ON inserted.ItemCode =tblImages.ItemCode ANDinserted.Invventory = tblImages.Invventory AND tblImages.Coll ='COLLNAME' ANDinserted.ImageFileName = tblImages.ImageFileUPDATE tblImagesSET ImageNotes=inserted.NotesFROM inserted INNER JOIN tblImages ON inserted.ItemCode =tblImages.ItemCode ANDinserted.Inventory= tblImages.Inventory AND tblImages.Coll ='COLLNAME' ANDinserted.ImageFileName = tblImages.ImageFileEND " & vbCrLf)The first update in my trigger, be it an update or insert trigger,works fine. It crashes with the "Cannot use text, ntext or imagecolumns in the 'inserted' or 'deleted' tables." error in the secondpart.I have read various messages through the Internet on this and severalof them reference using INSTEAD OF triggers and views. I have neverused those before as this is my first work with SQL 2000. None of theexamples of INSTEAD OF triggers I have seen yet use the actual insertedtables and I haven't quite understood how to use them correctly.Can someone help me with the basic syntax as this trigger is one ofseveral that I am going to have to get working.Thank you in advance for any help, assistance, suggestions or"direction pointing" you may provide.
Hi, I've been reading all sorts of info on the ntext field. I needthis to store xml documents in sql server via a stored proc.Because of its size, I apparently can not use SET (as in UPDATE)therefore I'm trying to do an INSERT of the row with this field (afterdeleting the old row).CREATE PROCEDURE dbo.UpdateXmlWF(@varWO varchar(50)@strWF ntext@varCust varchar(50)@varAssy varchar(50))ASINSERT INTO tblWorkOrders (WorkOrder, Customer, Assy, xmlWF) VALUES(@varWO, @varCust, @varAssy, @strWF)I'm using MSDE so I can't tell what's wrong...it just won't save theproc.PLEASE HELP!Thanks, Kathy
Hi, I've read conflicting articles on updating an ntext field in acolumn.My ntext field will exceed 8,000 characters (typically twice that size-- but just a text string).One article (I think from MicroSoft) said you could NOT use ntext inan UPDATE statement, but I've seen examples from other people usingit...but don't know if it's related to the size/characters issue.Is this true or not?Thanks very much...Kathy
My issue is this. I have a total of 23000 rows in my Table. Now I'm trying to Replace any rows under the codeabbreviation column that has the word Protocol with the Abbreviation Prtcl. The problem that I'm running into is that When I use the UPDATE/REPLACE statement below I get the message that "23000 rows were affected" even though there are only total of 10rows that has the word Protocol. Shouldn't the correct message be "10 rows were affected" since the statement only did update 10 rows?
Here is my Table structure: ID is my Primary key IDint CodeIdnvarchar(50) CodeCategoryIdnvarchar(50) CodeCategorynvarchar(50) CodeCategoryNamenvarchar(50) Codenvarchar(50) CodeNamenvarchar(250) CodeAbbreviationnvarchar(250) FullTextNamenvarchar(250) Suggestedabbreviationnvarchar(72) ModifiedDatedatetime
I need to do an update on a field which will update 3 characters within a string. Right now I have '000111000333'. I want to replace '000' starting from the 7th character space with '222'. The first set of zeroes will remain unchange. I want the final outcome to be '000111222333'. How can I do this?
I have tried searching this forum and could not find anything. the closest thing I can find is http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81890#360936.
Field Name Type Size Key-------------------------------------------------- attr_name char 25 PK attr_value nvarchar 250 PK comment nvarchar 100
the records in the table are
attr_name attr_value comment------------------------------------------------------------------ mail dasari.kanth@gmail.com nothing mail xyz@gmail.com nothing UID test123 nothing -------------------------------------------------------------------- now i am trying to update this table with the below UPDATE command
update attribute_definitions set attr_value=replace(attr_value,'gmail','yahoo') where attr_name='mail'
but the above update statement giving the error like "Violation of PRIMARY KEY constraint 'PK_attribute_definitions'. Cannot insert duplicate key in object 'attribute_definitions'."
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 need to do an update to several tables that would update just a few characters w/in a string. So if the String is 1122334455 I would need to update just '1122' and replace with '0000' - leaving '0000224455'.
I've tried the update/replace functions and they're not working. How would I do this or would I just create a trigger?
the application will add items into a "bag". That is, the items in one table will refer a record in another table. This will be done in timely manner -- with second or minute delays between adding a new item. There will be up to thouthand of items per bag. The option is to wait until a full bag accumulates and set up all the references at once by using
UPDATE items SET container_ref = bag WHERE id IN [...]
The disadvantage of such all-at-once I see is inability to encapsulate the functionality into a SP -- the problem is to pass a set of IDs. The advantage should be efficiency in terms of total SQL Server load. How mush would it be?
I can think of ways to resolve this issue, but I am wondering if there is a standard practice or some setting that is used to ensure that text containing a single quote, such as "Bob's house", is passed correctly to the database when using a SqlDataSource with all of the default behavior. For example, I have a FormView setup with some text fields and a SqlDataSource that is used to do the insert. There is no code in the form currently. It works fine unless I put in text with a single quote, which of course messes up the insert or update. What is the best way to deal with this? Thank you
I have a table which when certain columns are updated, need a trigger to fire to update a next schedule date in that same table for that record. I can write the trigger, but my question for performance and efficiency is which approach would be better. Separate triggers fo the 8 columns, or a large trigger with an If to check if these columns are updated. Thanks
I'm updating the name data in a large user database with the following UPDATE statement. The staging table was bulk loaded from a flat file and contains 10 million records. The production table (Recipients) contains 15 million records. This worked correctly but this single update statement took an entire ten hours to run which is way too long. While it was running the server was clearly 100% disk bound. CPU activity was near nothing. We've just upgraded RAM from 1GB to 2GB but we expect data sizes to grow significantly and we can't keep adding RAM. Absolutely nothing else is running on this server. Any ideas how I can optimize this?
UPDATE Recipients SET [First] = Stages.[First] , [Last] = Stages.[Last] FROM Stages INNER JOIN Recipients ON (Stages.UserName = Recipients.UserName AND Stages.DomainID = Recipients.DomainID) WHERE (CASE WHEN Stages.[First] IS NULL THEN 1 ELSE 0 END + CASE WHEN Stages.[Last] IS NULL THEN 1 ELSE 0 END) <= (CASE WHEN Recipients.[First] IS NULL THEN 1 ELSE 0 END + CASE WHEN Recipients.[Last] IS NULL THEN 1 ELSE 0 END)
Text execution plan. I've made small annotations with the % information from the graphical execution plan:
|--Clustered Index Update(OBJECT:([Recipients].[dbo].[Recipients].[PK_Recipients]), SET:([Recipients].[First]=[Stages].[First], [Recipients].[Last]=[Stages].[Last])) |--Top(ROWCOUNT est 0) |--Sort(DISTINCT ORDER BY:([Bmk1000] ASC)) 14% |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([Stages].[DomainID], [Stages].[UserName])=([Recipients].[DomainID], [Recipients].[UserName]), RESIDUAL:(([Recipients].[UserName]=[Stages].[UserName] AND [Recipients].[DomainID]=[Stages].[Domain 25% |--Clustered Index Scan(OBJECT:([Recipients].[dbo].[Stages].[IX_Stages]), ORDERED FORWARD) 61% |--Clustered Index Scan(OBJECT:([Recipients].[dbo].[Recipients].[PK_Recipients]), ORDERED FORWARD)
Everything I've heard on the subject suggests you change the index scans to index seeks. How do I do this?
Any other tuning advice is greatly appreciated.
Here are the exact statements I used to create the tables:
CREATE TABLE Recipients ( ID INT IDENTITY (1, 1) NOT NULL, UserName VARCHAR (50) NOT NULL, DomainID INT NOT NULL, First VARCHAR (24) NULL, Last VARCHAR (24) NULL, StreetAddress VARCHAR (32) NULL, City VARCHAR (24) NULL, State VARCHAR (16) NULL, Postal VARCHAR (10) NULL, SourceID INT NULL,
CREATE TABLE Stages ( ID INT NULL, UserName VARCHAR(50) NOT NULL, DomainID INT NULL, Domain VARCHAR(50) NOT NULL, First VARCHAR(24) NULL, Last VARCHAR(24) NULL, StreetAddress VARCHAR(32) NULL, City VARCHAR(24) NULL, State VARCHAR(24) NULL, Postal VARCHAR(10) NULL ) CREATE CLUSTERED INDEX IX_Stages ON Stages (DomainID, UserName)
We have a simple UPDATE query, joining two tables, that takes much longer than 10 hours to run, but if we break the table in six (10 million rows in each table), it takes only fifteen minutes to run each part.
Why? And how can we tell in advance whether a query will cross the threshold into l.o.n.g.r.u.n.n.i.n.g query? Or, how can we prevent it?
The system is Windows XP Pro with 4GB RAM (/3GB switch), and SQL Server Standard 2005. Log files, swap files, dbf files are on separate drives. The system is dedicated to SQL Server. No other queries are running at the same time. The database is in Simple logging mode. Each table is a few GB with 60 million rows.
An example problem query is: (updating fewer than 10 bytes) UPDATE bigtable SET bigtable.custage = scores.custage, bigtable.custscore = scores.custscore FROM bigtable JOIN t2 ON bigtable.custid = scores.custid
In this case, each table has 60 million rows. 'custid' is a sequential, unique integer. SCORES table is clustered on 'custid' and is 1.5GB in size. BIGTABLE has an index on 'custid', and is 6GB in size. There is a one-to-one match between the tables on 'custid', but not enforced. The SCORES table was created by exporting a few fields (but all 60 million records) from BIGTABLE, updating the values in a separate program, then importing back in SQL Server into the SCORES table.
The first time this query was run, we stopped it after it ran 16 hours. When we broke up the bigtable into 10 million record chunks (big1, big2, big3..., big6) each update only took 15 minutes, for 90 minutes total.
* How can in we tell in advance that the full chunk would take more than a few hours? * Why is it taking SO MUCH LONGER than in smaller chunks? * When a query is taking that long to run, is there any way to tell where in the plan it is? * What should we do differently?
Thanks for any help; this is a real head scratcher for us.
When I enter over 4000 chars in any ntext field in my SQL Server 2005 database (directly in the database and through the application) I get an error saying that the data could not be updated because string or binary data would be truncated.Has anyone ever seen this? I cannot figure out what is causing it, ntext should be able to hold a lot more data that this...
I have to modify the table structure where the table have a lot of data already. The log is getting full due to uncommitted transactions, there is a lot of data being updated in large bulks, not all of the transactions are committed, the update task cannot be completed. However, there is no more spare disk space for it to commit the transaction. Anyone can help?
I'm a newbie to Replication and recently setup the following.
Publisher and Distributor on the same SQL2005 server, then I've got 7 subscribers(SQL2000 servers) and I'm using push subscriptions. I'm replicating 5 SQl tables which don't have too many changes and these are scheduled to run every 3 hours. In a few days a large one off SQL update with add an additional 10,000 rows to one of the replicated tables. I was wondering what impact this would have on the above setup i.e are there any sort of limitations here. I'm assuming not but thought I would check. I'm thinking it will just cause additional overhead on the server, but the update is being applied when no users will be using the database.
Hi all,I am doing some large updates,that may update 10,000 plus rows.This works fine when I execute the SQL directlyin Query Analyzer.If I set the timeout on my VB connection to 0 (zero)the connection should not time out????But it does.If I set the time out to a high value, say 1200,I get the same problem well within 1200 seconds.Also, I am getting the problem that the log fills up,but it is set to auto grow????Any ideas would be appreciated.ThanksGreg
