October 22, 2010 at 12:16 am
I have table "FAQ" which has ntext column.
I want to capture raw that has been inserted/updated/deleted in FAQ table. So I have written AFTER trigger for insert, update and delete.
In trigger, "inserted" table does not provide value of ntext column. So I have done innerjoin of "inserted" table with FAQ table and get ntext column value from FAQ table. It works fine for insert and update trigger.
But for delete trigger how can I get ntext column value as raw is deleted from FAQ table.
Thanks,
Ruchir
October 22, 2010 at 12:51 pm
First thought is to ask if you really need this field as ntext. Can you use nvarchar instead. It is pretty big at 4,000 characters. If you specify it with nvarchar(max) you would have the same issue because it is handled internally as a blob like ntext. At some point you are going to have to convert it anyway when the ntext field is removed.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 22, 2010 at 12:58 pm
SeanLange (10/22/2010)
First thought is to ask if you really need this field as ntext. Can you use nvarchar instead. It is pretty big at 4,000 characters. If you specify it with nvarchar(max) you would have the same issue because it is handled internally as a blob like ntext. At some point you are going to have to convert it anyway when the ntext field is removed.
Actually you can use varchar(max), nvarchar(max), varbinary(max) with inserted/deleted tables. It's just the deprecated ones that don't work.
October 22, 2010 at 1:01 pm
Derrick Smith (10/22/2010)
Actually you can use varchar(max), nvarchar(max), varbinary(max) with inserted/deleted tables. It's just the deprecated ones that don't work.
o rly? I didn't know that. I thought it would be the same problem.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 22, 2010 at 1:06 pm
SeanLange (10/22/2010)
Derrick Smith (10/22/2010)
Actually you can use varchar(max), nvarchar(max), varbinary(max) with inserted/deleted tables. It's just the deprecated ones that don't work.
o rly? I didn't know that. I thought it would be the same problem.
Nope...in reality they are handled very similarly in the backend (they all show up as LOB reads, etc), but since ntext is deprecated, they probably did this to encourage people to change to nvarchar(max).
To the OP: This was most likely a SQL 2000 database that was upgraded to 2005, correct? If so, you should look into changing that column to nvarchar(max). It takes up roughly half the space of ntext, is faster, indexable, has more comparison options available, and is much more supported as far as issues like this go. There really is no downside. Just do a test on your dev environment first to make sure there are no adverse effects since every organization is different..but I've done this many times and I've never had an issue.
October 25, 2010 at 12:05 am
Thanks for providing good solution.
Before I change datatype from ntext to nvarchar(max), I would like to know the difference between ntext and vvarchar(max).
Thanks,
Ruchir
October 25, 2010 at 12:17 am
ruchir 71291 (10/25/2010)
Thanks for providing good solution.Before I change datatype from ntext to nvarchar(max), I would like to know the difference between ntext and vvarchar(max).
Thanks,
Ruchir
ntext datatypes will be deprecated in next/future release of SQL Server.
Refer: http://msdn.microsoft.com/en-us/library/ms143729.aspx
So, it's good to change the datatype of columns which has NText datatype.
Thanks
October 26, 2010 at 5:37 am
I need help on implementing error handling.
I have created below trigger which calls one store procedure to store audit data. Now I want to catch the error that might occurs in store procedure while inserting data. So I have implemented try/catch in store procedure but I get below error. Let me know how to solve this.
"The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction."
Trigger: tri_AUD_FAQs
CREATE Trigger trg_AUD_FAQs ON FAQs
FOR INSERT, UPDATE, DELETE
AS
BEGIN
EXEC dbo.AUD_AddDataLog 'FAQs' , 'insert', '10'
END
Procedure: AUD_AddDataLog
CREATE Procedure AUD_AddDataLog
(
@tabelName varchar(200),
@action varchar(50),
@identity varchar(200)
)
AS
BEGIN
BEGIN TRY
DECLARE @strDataQuery as nvarchar(1000)
DECLARE @strPrimaryColumn as nvarchar(200)
INSERT INTO AuditDataLog
VALUES( @tabelName, @action, @identity, 'Audit Data', 'host',getdate(),'host',getdate())
END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRANSACTION;
END
--If error occurs then insert error information in APEmailsQueue table.
DECLARE @strBody as nvarchar(4000)
SET @strBody = 'Error Number = ' + CAST(ERROR_NUMBER() AS NVARCHAR(100)) +
'Error Severity = ' + CAST(ERROR_SEVERITY() AS NVARCHAR(100)) +
'Error State = ' + CAST(ERROR_STATE() AS NVARCHAR(100)) +
'Error Procedure = ' + ERROR_PROCEDURE() +
'Error Line = ' + CAST(ERROR_LINE() AS NVARCHAR(100)) +
'Error Message =' + ERROR_MESSAGE()
INSERT INTO ErrorLog(TemplateKey, ExceptionMessage)
VALUES(DB Error' , @strBody)
END CATCH;
END
October 27, 2010 at 4:05 am
ruchir 71291 (10/25/2010)
I would like to know the difference between ntext and vvarchar(max).
See this http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 27, 2010 at 4:13 am
Bhuvnesh (10/27/2010)
ruchir 71291 (10/25/2010)
I would like to know the difference between ntext and vvarchar(max).See this http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx
Thanks for sharing the link. nice article for NTEXT vs NVARCHAR(MAX).
Thanks
October 27, 2010 at 4:21 am
Derrick Smith (10/22/2010)
If so, you should look into changing that column to nvarchar(max). It takes up roughly half the space of ntext, is faster, indexable
Why would it be half the space of ntext? They're both unicode (2 bytes per character).
The only index that can go onto nvarchar(max) is a fulltext index, same as ntext. It can be included in an index, but that's generally not a good idea.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply