Hello, I have a very simple trigger that only copies the data from one table to a historial table, when a row is deleted. It works fine, but now I need to change it becouse I am inserting a new column to the table, which type is ntext, and when I try to declare a ntext variable (inside the trigger), it does not allow me. Does anyone know how to go round this? Here is the code of the trigger (maybe it is not efficient, as I am a newbie in this :D, please tell if so as well)
CREATE TRIGGER OnDelete ON dbo.Tracker
FOR DELETE
AS
declare @IDregistro as integer;
declare @IDTIT as integer;
declare @negocio as nvarchar(50);
declare @fechaOr as datetime;
declare @asuntoOr as nvarchar(50);
declare @estadoOr as nvarchar(50);
declare @descripcionOr as nvarchar(255);
declare @usuarioOr as nvarchar(50);
declare @contactoOr as nvarchar(50);
declare @companiaOr as nvarchar(40);
--declare @desc as ntext; --DOES NOT WORK!!
declare @fechaSus as datetime
select @fechaSus = getdate()
select @IDregistro = IDregistro from deleted;
select @IDTIT = IDTitular from deleted;
select @negocio = negocio from deleted;
select @fechaOr = fecha from deleted;
select @asuntoOr = asunto from deleted;
select @estadoOr =estado from deleted;
select @descripcionOr = descripcion from deleted;
select @usuarioOr = usuario from deleted;
select @contactoOr = contacto from deleted;
select @companiaOr = compania from deleted;
Hi all in .net I've created an application that allows creation of triggers, i also want to allow the deletion of triggers. The trigger name is kept in a table, and apon deleting the record i want to use the field name to delete the trigger
I have the following Trigger
the error is at
DROP TRIGGER @DeleteTrigger
I'm guessing it dosen't like the trigger name being a variable instead of a static name how do i get around this?
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 writing an audit trail trigger that needs to query columns, but the specific column names are not known until the trigger runs and determines what columns have changed. Once I know the changed column(s) I fetch the column name. No sweat. The problem is using a variable in place of a column name I can not get the data to return, only the column name.
-- Here is how I get the column name. It works great, @CName -- reflects the column name as I use it elsewhere fine. Select @CName = col_name(OBJECT_ID('TABContract'), @CNum)
-- This simple syntax usually works, it does not using a -- variable as the column name. @Prev is populated with -- @CName literally. I have tried various and asundry ()'s and []'s Select @Prev = @CName From Deleted
-- Here I have hard coded the Column name and ran a test modifying that column "Comment" and it works! @Prev is populated with the columns data not the Column name. Select @Prev = Comment From Deleted
-- Tried these syntaxes, no luck Select @Prev = (Select @CName From Deleted) Select @Prev = (Select col_name(OBJECT_ID('TABContract'), @CNum) From Deleted)
I need to pass a application userid on every call to my database for triggers on the db to use.
I am connecting to my database using integrated security from my application, all on one windows domain account. However I need to get an application user id over on the connection for triggers to use to stamp in changelogs.
Currently everytime we open a connection we create a #table with the user id in it, but that is expensive, especially in our new SOA where connections are frequently created and dropped on requests.
Is there any way I can get a connection level variable over to the database server so triggers could read it?
__________________________________________________ _ CREATE TRIGGER dbo.Fochini_Insert ON dbo.FochiniTable AFTER INSERT AS BEGIN DECLARE @v_object_key VARCHAR(80) DECLARE @v_object_name VARCHAR(40) DECLARE @v_object_verb VARCHAR(40) DECLARE @v_datetime DATETIME
SELECT ins.Cust_Id INTO @v_object_key FROM inserted ins <--- my problem area!! SET @v_object_name = 'FochiniTable' SET @v_object_verb = 'Create' SET @v_datetime = GETDATE()
IF ( USER <> 'webuser' ) INSERT INTO dbo.xworlds_events (connector_id, object_key, object_name, object_verb, event_priority, event_time, event_status, event_comment) VALUES ('Fochini', @v_object_key, @v_object_name, @v_object_verb, '1', @v_datetime,'0', 'Triggered by Customer CREATE')
END ________________________________________________
i'm trying to get the INSERTED variable from table FochiniTable on colomn Cust_Id
and the statement: SELECT ins.Cust_Id INTO @v_object_key FROM inserted ins - is failing [still a newbie on mssql server 2000]
hi all, I am new to sql server. I need some help regarding ntext value.I am using an insert stored proc to insert the value into ntext field, till now i have been using a varchar variable to pass the value. but i think this doesn't work when the size exceeds the limit of varchar and also what if i want to perform group insert? and the major issue i am concerned about is, i don't want to update this field quite often.I need to check whether the existing ntext value and the new value which i want to insert are different.I want to do it in a better way than processing both values entirely.I want to make sure that the update statement is executed only when both values differ.
HI all, I Need some help over ntext datatype.one of my table columns is of ntext datatype. how to insert or update this thru storedprocs. any special notes regarding ntext column.
Hi, I'm using a column (ntext) to store some long strings. An example of the string that I need to store is the following:
436;Implementing A Gang - Awareness Program/And A Middle School Gang Prevention Curriculum. Doctoral Th;Samuels;Donald J;Miami IV;Administrators; At Risk; Child And Youth Studies; Community; Community Members; Drop Out Prevention; Educational Leadership; Law/Criminal Justice; Peer Counseling; Principals; Secondary Education
I wrote a store procedure that does it, but it seems that the column space is not enough for the above string. The string in fact, is truncated and only the following portion is stored in my table:
436;Implementing A Gang - Awareness Program/And A Middle School Gang Prevention Curriculum. Doctoral Th;Samuels;Donald J;Miami IV;Administrators; At Risk; Child And Youth Studies; Community; Community Members; Drop Out Prevention; Educational Leadership
Hi, I have the following problem. I cannot alter column which is ntext under sql server 2000. I get the following error:25-Jul-2007 09:27:11 ERROR [sol.cm.dbsetup.StatementReader] Error occured while executing the statement: Cannot alter column 'asz' because it is 'ntext'. SQLState = S0001 ErrorCode = 492825-Jul-2007 09:27:11 ERROR [sol.cm.dbsetup.StatementReader] SQL statement was: ALTER TABLE a ALTER COLUMN asz NTEXT NOT NULL I cannot use varchar because it is to short. Please tell me if there is a magic way to do this. I've already read in documentation that I cannot alter column which is ntext. Is there really no way to do this. Please help :)
hi i wants to order by ntext data type. like 1.1.1.3.1.11.1.1.3.1.11.1.1.3.1.21.1.1.3.1.21.1.1.3.1.31.1.1.3.1.31.1.1.3.1.41.1.1.3.1.41.1.1.3.1.4.11.1.1.3.1.4.11.1.1.3.1.4.101.1.1.3.1.4.101.1.1.3.1.4.111.1.1.3.1.4.111.1.1.3.1.4.121.1.1.3.1.4.121.1.1.3.1.4.131.1.1.3.1.4.131.1.1.3.1.4.141.1.1.3.1.4.141.1.1.3.1.4.151.1.1.3.1.4.151.1.1.3.1.4.161.1.1.3.1.4.161.1.1.3.1.4.21.1.1.3.1.4.21.1.1.3.1.4.31.1.1.3.1.4.31.1.1.3.1.4.41.1.1.3.1.4.41.1.1.3.1.4.51.1.1.3.1.4.51.1.1.3.1.4.61.1.1.3.1.4.61.1.1.3.1.4.71.1.1.3.1.4.7 Thanks Parth
I am working in asp and using sql server 7. I have a section where the input is to long to use a char field so i had to set the field as ntext. Now i am trying to do a section where i wanna look and see if the field is null and if not then it is to perform an operation. If it is null it is to skip and go to the next field in the record. However it is not performing this operation. It sees the first field and does pass the second one because it is null. If i put in a character into the field it will go on until it gets to a null again and then stops. Any suggestions?
Hallo. I need help, how pull out some inquiry string from type "ntext" in MS SQL(it is xml document). Sring has invariable length, in note is always on other position and includes variable text (e.g .:<actionId>xx</actionId>) . Position I can find out by the help of "patindex" but I don't know what then. I tryed to write procedures, but I had trouble with declaration variables (data type). Thanks and sorry for my horrible English.
Maybe a stupid question but I'm new to the db admin work so please bear with me.
I've imported an Access db into SQL, in the Access db the field type was 'memo' to accomodate the large amount of text (on avg ruffly 4100 chars. with spaces). Now in SQL the field in the table I have set up as an ntext field, which I understood to be equivalent to a memo field in Access.
My problem is when saving data to the field the first time it saves all the data correctly with the exception of the field in question. The data in the field is '<LongText>', now when I try to update the data in the table I get a 'Data Truncated' error message and no update takes place throughout the table.
After testing this and trying different things, I've found that if I shorten this one field and try to save to the db I still get the 'Data Truncated' error message. If I shorten the data in the field AND delete the record from the SQL table then it will save just fine from there on out (which won't work for the reports).
I'm not sure what I'm missing here to get this to work the way it did in Access.
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?
Hai, i need ur suggestion to use ntext in my table. basically i want to store the xml data(will be in same xml format only) in sql2000 database. the max size of the data will be 5mb. so can i use ntext to store this data.basically, only once i insert the data to the database. no update after that. only i throw the query to retrieve that xml data from the database. is it advisable to use,ntext for this requirment. is there any other suggestion ?? help pls
Hi all,I have an asp page that writes to an ntext field in SQL Server 2000.All was going well until I had to put in a section of text greater than 8000bytes.Then I got a timeout error and the update wouldn't go through.After reading that 2000 only accepts chunks of 8000 bytes or under at atime, I attempted to use the AppendChunk method.The code I wrote seems to work first time I enter text in the page, nomatter how big. But when I try to update the ntext file,if it is more than 8K, it just sits there and nothing happens. Now I donteven get a timeout error.From what I read, I was assured this would work.For small text files under 8K it works beautifully, I can add and removetext and it all works fast.But as soon as the ntext field gets beyond that size its totally unworkable,and seems to be just the same as using the standard UPDATE method. I'veattached the code.If anyone has any suggestions I would be eternally grateful, this is drivingme nuts. I've attached the offending code.ThanksBill' ***********************************************' * PageStatus: SAVE Action: EDIT *' ***********************************************IF PageStatus = "SAVE" AND Action = "EDIT" THENsqlc = "SELECT * from " & tblNameDim FldValDim rsSet rs = Server.CreateObject("ADODB.Recordset")rs.cursortype = 1rs.cursorlocation = 3rs.locktype = 3rs.Open sqlc, oConniChunk = 254cTxtDescription = SQLReady(FileUp.Form(lang))For iNo = 1 to len(cTxtDescription) step iChunkiStartAt = iNocWorkString = mid(cTxtDescription, iStartAt , iChunk )Response.Write "At byte " & iNo & vbCRLF & "<br/>"rs.Fields("SimpleChinese").AppendChunk(cWorkString)NextIF Page_Err = "OK" THEN' Perform the Queryrs.Updaters.CloseSet rs = NothingSet oConn = NothingEND IFEND IF
Dear GroupI admit, this might be a very ignorant question.BOL says about the ntext data type:Variable-length Unicode data with a maximum length of 2^30 - 1(1,073,741,823) characters.I've stored a string consisting of around 45.000 characters in a ntextfield - done by copying the string in my txt document and pasting inEnterprise Manager.When I determine the datalength of the field in SQL Analyzer I get2046.Is this realistic? Is it bytes or the actual number of characters? Itseems incrrect to me..Although, when I read the string with a DataReader based on a storedprocedure and save it in a string variable in my VB.NET application,the string is truncated, although the variable can hold up toapproximately 2 billion Unicode characters according to MSDN.I just wonder whether I've done something wrong or misunderstoodsomething on the SQL server side that causes this problem.Thanks very much for your help!Martin
I'm trying to move from MSDE to SQL Everywhere in my project, which requires using of ADO classic and i have some problems storing ntext data in SQLEv database using parametrized command.
Could it be that the problem is because of ADO classic? Is it ok to use old ADO with SQLEv OLEDB provider and will compatibility with old ADO be maintained in SQLEv?
Below is JScript code illustrating the situation: ------------------------------- var oConn = new ActiveXObject("ADODB.Connection"); oConn.Open(sConnStr); var oCmd = new ActiveXObject("ADODB.Command"); oCmd.ActiveConnection = oConn; var sText = "test text"; oConn.Execute("create table tblTest(sText ntext)"); oCmd.CommandText = "insert into tblTest(sText) values(?)"; oCmd.Parameters(0).Value = sText; oCmd.Parameters(0).Size = sText.length; oCmd.Execute(); ------------------------------- It gives me 'Microsoft SQL Server 2005 Everywhere Edition OLE DB Provider: The given type name was unrecognized. [,,,,,]'.
If i remove 'oCmd.Parameters(0).Size = sText.length;' error changes to 'Insufficient memory to complete the operation.'
Is there any way to datamine using ntext? I'm trying to run some BI on some email messages -- seeing if it can accurately classify email into the proper folder. Currently, I get complains that ntext isn't comparable.
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?
In one of my tables, the data type is ntext and if it contains a null value, I get the following error:String[6]: the Size property has an invalid size of 0I'm using vb.net 2005 and sql server 2000.Here's a code snippet of my code behind: Dim pDescription As SqlParameter = Command.Parameters.Add("@description", SqlDbType.NText) pDescription.Direction = ParameterDirection.Output And my stored procedure: CREATE PROCEDURE dbo.retrieveEquipmentDetails
FROM tblEquipments a, tblManufacturers b, tblLocation c, tblStatus d, tblEquipment_Type e, tblLabs f
WHERE a.ID = @id AND a.manufacturer = b.manufacturerID AND a.location = c.locationID AND a.Status = d.statusID AND a.EquipmentType = e.ID AND f.ID = a.calLab
FOR READ ONLY
OPEN c_equipment
FETCH NEXT FROM c_equipment INTO @assignedID, @manufacturer, @modelNumber, @serialNumber, @equipmentType, @description, @location, @status, @modifiedDate, @modifiedBy, @notes, @calibrationLabId, @calibrationRequired, @calibrationDate, @calibrationDueDate, @assetNumber--, @picture
IF( @@FETCH_STATUS <> 0 ) BEGIN SET @errMessage = 'Equipment not found.' GOTO HANDLE_APPERR END CLOSE c_equipment DEALLOCATE c_equipment
SET @errCode = 0 RETURN @errCode
HANDLE_APPERR: IF( CURSOR_STATUS( 'local', 'c_equipment' ) >= 0 ) BEGIN CLOSE c_equipment DEALLOCATE c_equipment
END SET @errCode = 1 RETURN HANDLE_DBERR: SET @errCode = -1 RETURN END GO
So, if anyone has any suggestions, I would like to hear them.And btw, it also happens to the image field (@picture) but I commented it out to try to get the rest working until I'm ready to tackle that one.Thanks,Zath