December 4, 2008 at 2:26 am
hello
im trying to transfer data from one data base into another database with a Trigger , but there is a problem , the only difference between two tables (Source and Destination) is a field that its name is Body (in Source table and Destination table) but data type of Body in Source table is ntext and data type of Body in Destination Table is nvarchar(max)
the schema of these two table is this"
Source table:
Id int
Title nvarchar(25)
Body ntext
and Destination :
Id int
Title nvarchar(25)
Body nvarchar(max)
and this is my Trigger:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER TRIGGER [TRG993]
ON [dbo].[Source]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO DBntext2.dbo.Destination (Id, Title, Body)
SELECT Id, Title, CAST(Body as nvarchar (MAX))
FROM DBnetxt1
END
but it does not work,
i dont know that there is aproblem in my Trriger or this is because transfering data from a field with ntext typeto a field with nvarchar(max).
regards.
December 4, 2008 at 2:42 am
I think you should be using the "inserted" table rather than referencing the table itself. So your insert statement would be more like this:
INSERT INTO DBntext2.dbo.Destination (Id, Title, Body)
SELECT Id, Title, CAST(Body as nvarchar (MAX))
FROM inserted
Are you getting an error message?
December 4, 2008 at 3:00 am
Thank you for your reply
when i used from (inserted)
this error mesage came:
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
December 4, 2008 at 3:03 am
nazaninahmady_sh,
INSERT INTO DBntext2.dbo.Destination (Id, Title, Body)
SELECT Id, Title, CAST(Body as nvarchar (MAX))
FROM DBnetxt1
The problem here is that you are missing the table name after the FROM caluse in your trigger. It should be DBnetxt1.dbo.Source or any other object. You are just writing the db name 'DBnetxt1'.
You should be receiving message 'Invalid object name 'DBnetxt1'.'
Alan Wood,
INSERT INTO DBntext2.dbo.Destination (Id, Title, Body)
SELECT Id, Title, CAST(Body as nvarchar (MAX))
FROM inserted
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
December 4, 2008 at 3:13 am
December 4, 2008 at 3:45 am
Ah yes. You'll need to join the inserted table to the source table, otherwise the insert will try to insert all rows from the table to the destination.
Yes, you can join as follows, if you want to insert only for the last inserted Id:
alter TRIGGER [TRG993]
ON [dbo].[Source]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO DBntext2.dbo.Destination (Id, Title, Body)
SELECT a.Id, a.Title, CAST(a.Body as nvarchar (MAX))
FROM DBnetxt1.dbo.Source a, inserted b
where a.Id = b.Id
END
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
December 4, 2008 at 3:50 am
" You'll need to join the inserted table to the source table"
please help me how
and write for me that if i do so may i to transfer data from a field with ntext type to a field with nvarchar(max) or not?
December 4, 2008 at 4:03 am
Thank you very much,
the Trigger is working ,
but how can i become sure about this Trigger transfer all of my data in Source table in field that its type is ntext to the Destination table to the field that its type is nvarchar(max)?
because as you know ntext is very large amount data and it is a pointer and it save data in different places .
December 4, 2008 at 6:13 am
ntext and bvarchar(max) can store the same amount of data (2 GB maximum or a 16-bytes pointer).
I fact SQL 2005 has depreciated the NTEXT in favor of NVARCHAR(MAX), though NTEXT is still available just for backward compatibility.
as you know ntext is very large amount data and it is a pointer and it save data in different places
Not necessary nazanin. The data will be stored in the row or outside the row depends on how you specify it using the system stored procedure sp_tableoption . When this option is on, a pointer to the root node of of the B-Tree for the data is store din the row; when the option is off, values that are small enough are stored directly in the row, and a pointer is used only for values that are to large(larger than 8K) to fit the row.
The trigger will work both for the pointer an row values.
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
December 4, 2008 at 7:05 am
Mazharuddin Ehsan (12/4/2008)
Not necessary nazanin. The data will be stored in the row or outside the row depends on how you specify it using the system stored procedure sp_tableoption . When this option is on, a pointer to the root node of of the B-Tree for the data is store din the row; when the option is off, values that are small enough are stored directly in the row, and a pointer is used only for values that are to large(larger than 8K) to fit the row.The trigger will work both for the pointer an row values.
Not quite - let me more clear (one of my teams used to own the code for indexes and text amongst many other things in the Storage Engine)
The 'text in row' option is for legacy types (text, ntext, image), is off by default, and when on, will store values in the data row *as long as* they fit on the page and up to the size limit you specify. When its off, the row holds a root pointer to the text tree storing the value. It's not a B-tree (either the classic B-tree, or the B+-tree that SQL Server implements to store indexes), just a loose tree stucture.
The 'large values types off row' option is for the new LOB types in 2005 (nvarchar(max), varchar(max), varbinary(max), XML) and its OFF by default. When it's off, these LOB types are in-row up to a maximum of 8k, as long as they fit. When the option is ON (or the LOB value doesn't fit on the page), the value is stored off-row in exactly the same way as for the legacy types.
Hope this helps.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
December 4, 2008 at 7:34 am
Thank you very much,
would you please help me to write a T_SQL block for transfering data from one table in one data base to another table in another data base? by using sp_table option ?
the schema of Source table is:
Id int
Title nvarchar(25)
Body ntext
and the schema of Destination table is:
Id int
Title nvarchar(25)
Body nvarchar(max)
regards
December 4, 2008 at 9:08 am
Not my area of expertise - someone else on the thread will help out I'm sure.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
December 4, 2008 at 10:12 am
Thank you very much Paul for your elaboration. However, I have just one doubt.
Paul Randal 12/4/2008 10:50 AM
-------------------------------------------------------------------
It's not a B-tree (either the classic B-tree, or the B+-tree that SQL Server implements to store indexes), just a loose tree stucture.
Do you mean this is also true for the storage of VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) data types in SQL Server 2005.
I could not find anything about it on BOL. But what I said about this is about SQL Server 2005 and is clearly stated in the 'Microsoft Official Course' material for the MCTS-SQL Server 2005 - 70431 (Course 2779B, Page 2-15).
Find Below some insight into it also.
Understanding VARCHAR(MAX) in SQL Server 2005
It talks about the over-flow page for these data types.
=====================================================
nazaninahmady_sh,
The trigger, I provided will work irrespective of the inner data storage mechanism in the databases.
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
December 4, 2008 at 11:11 am
nazaninahmady_sh (12/4/2008)
Thank you very much,would you please help me to write a T_SQL block for transfering data from one table in one data base to another table in another data base? by using sp_table option ?
Insert into DB2.dbo.Table1 (id, title, body)
SELECT id, title, body
FROM DB1.dbo.Table1
That ntext is a pointer and stored off page is quite irrelevance. Where the data is stored within the storage engine doesn't affect how queries are written. It's part of the principle of relational databases
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
December 4, 2008 at 2:07 pm
Mazharuddin Ehsan (12/4/2008)
Thank you very much Paul for your elaboration. However, I have just one doubt.Paul Randal 12/4/2008 10:50 AM
-------------------------------------------------------------------
It's not a B-tree (either the classic B-tree, or the B+-tree that SQL Server implements to store indexes), just a loose tree stucture.
Do you mean this is also true for the storage of VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) data types in SQL Server 2005.
I could not find anything about it on BOL. But what I said about this is about SQL Server 2005 and is clearly stated in the 'Microsoft Official Course' material for the MCTS-SQL Server 2005 - 70431 (Course 2779B, Page 2-15).
Find Below some insight into it also.
Understanding VARCHAR(MAX) in SQL Server 2005
It talks about the over-flow page for these data types.
=====================================================
nazaninahmady_sh,
The trigger, I provided will work irrespective of the inner data storage mechanism in the databases.
The article is incorrect in its specifics, and very probably the MOC course too. [Edit: I should add that I've seen plenty of incorrect things in MOC courses over the years. They're not necessarily written, reviewed, or tested by the SQL team or people that have deep knowledge of the subject at hand.]
The MAX datatypes are stored in-row (i.e. as part of the table data row, or a nonclustered index row if they've been INCLUDEd) as long as they fit in the row, do not exceed 8000 bytes, and the large-value-types-off-row is not set to ON.
When they're stored off-row, they are not stored on data or index pages. They're stored on the two types of text page, just like other LOB values. They're not called over-flow pages.
Row over-flow refers to another feature of 2005 where rows can exceed 8k. When this happens one or more variable length columns (including VARCHAR (0-8000) and SQLVARIANT) will be pushed off-row and stored as if it was an off-row LOB value. They will be stored in text pages - not in data or index pages.
[Another edit on re-reading that article: off-row LOB or overflow values are *not* stored in a B-tree format. The loose tree structure involved allows downwards traversal of the text tree, but not side-ways, ordered traversal of the tree. Also, even indexes in SQL Server are not store in *true* B-trees (even though the common phrase used to describe them by everyone including Books Online is indeed 'b-tree'. The literal meaning of a B-tree a computer science context is a Balanced-tree - and the trees used to store indexes in SQL Server are not balanced. They are what's called B+-trees (pronounced 'B-plus trees') where they have the b-tree structure but are not balanced.]
In either case, the off-row value does have a physical link in the data or index row that points to it. It's either a 16-byte or 24-byte 'root' column containing the File:Page:Row of the top of the text tree storing the value. That part of the article you linked to was correct.
I truly hate having to say this in these kinds of discussions, but I'm the one that's right. I used to own the code that implements all of this within the SQL Server Storage Engine for 2005, and I wrote DBCC CHECKDB so I have complete knowledge of the database storage structures. I also teach SQL Server internals to engineers inside Microsoft and for the Microsoft Certified Masters course. Please don't take this the wrong way - I really don't mean to sound arrogant or anything like that, but I *know* that I'm 100% right (and sometimes I feel like I have to give my qualifications to back up an explanation).
Thanks!
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply