Hi,
Can anybody tell me how to append data to ntext field?
JoydeepOn the client side if that is one of your choices!
If you absolutely MUST do it within SQL Server, the first step is to re-evalute the requirement ;) If you are convinced that the pain is worth it, then you need to pursue UPDATETEXT (http://msdn2.microsoft.com/en-us/library/ms189466.aspx).
-PatP|||On the client side if that is one of your choices!
If you absolutely MUST do it within SQL Server, the first step is to re-evalute the requirement ;) If you are convinced that the pain is worth it, then you need to pursue UPDATETEXT (http://msdn2.microsoft.com/en-us/library/ms189466.aspx).
-PatP
Hi PatP,
I already tried the above one,but can't make out thats why I put it in the forum.I am trying it in Sql Server.
The Updatetext syntax required the insert_off parameter.How can I find out the length of a ntext field?
Be sure that I want to append text so I need the total no of characters after which this piece of text will be inserted.
Plz comment.
Joydeep|||Check out the datalength() function in BOL. I forget if you have to divide by 2 for ntext, and am too lazy to look it up, now.|||Check out the datalength() function in BOL. I forget if you have to divide by 2 for ntext, and am too lazy to look it up, now.
Sorry,Nothing found like dividing like /2,just
http://msdn2.microsoft.com/en-us/library(d=robot)/ms176068.aspx
I was testing this one..
use test
declare
@.pp binary(16),
@.txt numeric
set @.txt=(select datalength(info) from table1)
set @.pp=(select textptr(info) from table1)
updatetext table1.info @.pp @.txt 0 'asljdklsadjKLKLSDJLJAdlkdjlKAJKLSJDKLAS
asjDHjkhkjlJHSDJKASHDJKASHDJKhkjasdhKJHsfajdfkljas dkljflksdjflkj
asdfjkdlaskjfklajsdfkljsdafkljsldkfjsdkljfklj
sdfjal;dasjkfkljalsdkfjldksjfklsdjflkjlkdsjfkjfkls djfklsdfjlskfjskladf JOYDEEP'
getting an error as ...
Server: Msg 7116, Level 16, State 4, Line 10
Offset 1091077014 is not in the range of available text, ntext, or image data.
The statement has been terminated.
??:(
Thanks anyway..
Joydeep|||Sorry,Nothing found like dividing like /2,just
http://msdn2.microsoft.com/en-us/library(d=robot)/ms176068.aspx
I was testing this one..
getting an error as ...
??:(
Thanks anyway..
Joydeep
Again if I change the datatype to Text this is working fine
use test
create table tom
(info text)
insert into tom values('Starting Text')
use test
declare
@.pp binary(16),
@.txt int
select @.pp=textptr(info) from tom
select @.txt=datalength(info) from tom
updatetext tom.info @.pp @.txt 0 'joydeep'
select * from tom
Result..
info Starting Textjoydeep
(1 row(s) affected)
Plz comment...
Joydeep|||Consider this code:
use pubs
go
create table test1
(col1 text, col2 ntext)
insert into test1
values ('hello', 'hello')
select datalength(col1), datalength(col2)
from test1
drop table test1
The answer should jump at you...|||Consider this code:
use pubs
go
create table test1
(col1 text, col2 ntext)
insert into test1
values ('hello', 'hello')
select datalength(col1), datalength(col2)
from test1
drop table test1
The answer should jump at you...
Well,You are absolutely right,but same problem with the statement.I am getting the same error..
use test
create table table1(info ntext)
insert into table1 values('First string')
declare
@.pp binary(16),
@.txt numeric
set @.txt=(select datalength(info) from table1)
set @.txt=@.txt/2
print @.txt
set @.pp=(select textptr(info) from table1)
updatetext table1.info @.pp @.txt 0 'JOYDEEP'
go
select * from table1
drop table table1
error ...
(1 row(s) affected)
12
Server: Msg 7116, Level 16, State 4, Line 14
Offset 1087931286 is not in the range of available text, ntext, or image data.
The statement has been terminated.
info
----------------------------------------------------------------------------
First string
(1 row(s) affected)
Thanks
Joydeep|||Well,well,well at last I got it.....:D :D
use test
create table gogo(col1 ntext)
insert into gogo values('first string')
Declare @.pointer binary(16),@.txtlen int
select @.pointer=textptr(col1) from gogo
select @.txtlen=datalength(col1) from gogo
set @.txtlen=@.txtlen/2
updatetext gogo.col1 @.pointer @.txtlen 0 'JoydeepDattagfhgfhfghgfdhgdfhdfhfggfhgfhgfhgfhcxn bnvn
fghgfhdfdfh892739281837`2jihqwjkhdkhjh$&^%&^%*%^*&%^*&%^&^%jghjkhakdhaskdhsk'
go
select * from gogo
go
drop table gogo
;) testing is the best solution
Thanks!!
Joydeep|||Well,well,well at last I got it.....:D :D
;) testing is the best solution
Thanks!!
Joydeep
I just changed the datatype of the variable from numeric to int.That solved the problem.I mean the variable that was keeping the length.:D :p
Showing posts with label ntext. Show all posts
Showing posts with label ntext. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
How to alter a 'text' column to 'ntext' ?
Hi all,
how to do that with SQL7 or 2000 by Transact-SQL ?
If I read the SQL2000 doc on 'Alter Table' :[vbcol=seagreen]
ALTER COLUMN
Specifies that the given column is to be changed or altered. ALTER COLUMN is
not allowed if the compatibility level is 65 or earlier. For more
information, see sp_dbcmptlevel.
The altered column cannot be:
a.. A column with a text, image, ntext, or timestamp data type.
<<<<<<<<<<<<<<<<<<<<<<<
Lilian.
Hi,
You cannot alter the text column directly.
2 Approaches to do this
I.
1.Create an ntext column,
2.update the text column's data to the ntext column,
3.drop the text column (Alter table drop column <column_name>
4.rename the ntext column to the prior text column's name (
5.sp_rename 'table_name.column_name',old_col_name,'column')
II.
1. You have export the data out (BCP OUT/DTS)
2. Take the script of table and dependant objects
3. Drop the table
4. In create tabl script change TEXT datatype to NTEXT
5. Craete the table.
6. BCP IN/ DTS to Import data
7. Create the dependant objects
You can also try the below, which requires less downtime
Thanks
Hari
MCDBA
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:u3iKxbGVEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> how to do that with SQL7 or 2000 by Transact-SQL ?
> If I read the SQL2000 doc on 'Alter Table' :
> ALTER COLUMN
> Specifies that the given column is to be changed or altered. ALTER COLUMN
is
> not allowed if the compatibility level is 65 or earlier. For more
> information, see sp_dbcmptlevel.
> The altered column cannot be:
> a.. A column with a text, image, ntext, or timestamp data type.
> <<<<<<<<<<<<<<<<<<<<<<<
>
>
> Lilian.
>
|||Thanks
)
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23HoJXlGVEHA.2544@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hi,
> You cannot alter the text column directly.
> 2 Approaches to do this
> --
> I.
> 1.Create an ntext column,
> 2.update the text column's data to the ntext column,
> 3.drop the text column (Alter table drop column <column_name>
> 4.rename the ntext column to the prior text column's name (
> 5.sp_rename 'table_name.column_name',old_col_name,'column')
>
> II.
> 1. You have export the data out (BCP OUT/DTS)
> 2. Take the script of table and dependant objects
> 3. Drop the table
> 4. In create tabl script change TEXT datatype to NTEXT
> 5. Craete the table.
> 6. BCP IN/ DTS to Import data
> 7. Create the dependant objects
>
> You can also try the below, which requires less downtime
> --
> Thanks
> Hari
> MCDBA
> "Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
> news:u3iKxbGVEHA.4092@.TK2MSFTNGP11.phx.gbl...
COLUMN
> is
>
how to do that with SQL7 or 2000 by Transact-SQL ?
If I read the SQL2000 doc on 'Alter Table' :[vbcol=seagreen]
ALTER COLUMN
Specifies that the given column is to be changed or altered. ALTER COLUMN is
not allowed if the compatibility level is 65 or earlier. For more
information, see sp_dbcmptlevel.
The altered column cannot be:
a.. A column with a text, image, ntext, or timestamp data type.
<<<<<<<<<<<<<<<<<<<<<<<
Lilian.
Hi,
You cannot alter the text column directly.
2 Approaches to do this
I.
1.Create an ntext column,
2.update the text column's data to the ntext column,
3.drop the text column (Alter table drop column <column_name>
4.rename the ntext column to the prior text column's name (
5.sp_rename 'table_name.column_name',old_col_name,'column')
II.
1. You have export the data out (BCP OUT/DTS)
2. Take the script of table and dependant objects
3. Drop the table
4. In create tabl script change TEXT datatype to NTEXT
5. Craete the table.
6. BCP IN/ DTS to Import data
7. Create the dependant objects
You can also try the below, which requires less downtime
Thanks
Hari
MCDBA
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:u3iKxbGVEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> how to do that with SQL7 or 2000 by Transact-SQL ?
> If I read the SQL2000 doc on 'Alter Table' :
> ALTER COLUMN
> Specifies that the given column is to be changed or altered. ALTER COLUMN
is
> not allowed if the compatibility level is 65 or earlier. For more
> information, see sp_dbcmptlevel.
> The altered column cannot be:
> a.. A column with a text, image, ntext, or timestamp data type.
> <<<<<<<<<<<<<<<<<<<<<<<
>
>
> Lilian.
>
|||Thanks
)"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23HoJXlGVEHA.2544@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hi,
> You cannot alter the text column directly.
> 2 Approaches to do this
> --
> I.
> 1.Create an ntext column,
> 2.update the text column's data to the ntext column,
> 3.drop the text column (Alter table drop column <column_name>
> 4.rename the ntext column to the prior text column's name (
> 5.sp_rename 'table_name.column_name',old_col_name,'column')
>
> II.
> 1. You have export the data out (BCP OUT/DTS)
> 2. Take the script of table and dependant objects
> 3. Drop the table
> 4. In create tabl script change TEXT datatype to NTEXT
> 5. Craete the table.
> 6. BCP IN/ DTS to Import data
> 7. Create the dependant objects
>
> You can also try the below, which requires less downtime
> --
> Thanks
> Hari
> MCDBA
> "Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
> news:u3iKxbGVEHA.4092@.TK2MSFTNGP11.phx.gbl...
COLUMN
> is
>
How to alter a 'text' column to 'ntext' ?
Hi all,
how to do that with SQL7 or 2000 by Transact-SQL '
If I read the SQL2000 doc on 'Alter Table' :[vbcol=seagreen]
ALTER COLUMN
Specifies that the given column is to be changed or altered. ALTER COLUMN is
not allowed if the compatibility level is 65 or earlier. For more
information, see sp_dbcmptlevel.
The altered column cannot be:
a.. A column with a text, image, ntext, or timestamp data type.
<<<<<<<<<<<<<<<<<<<<<<<
Lilian.Hi,
You cannot alter the text column directly.
2 Approaches to do this
--
I.
1.Create an ntext column,
2.update the text column's data to the ntext column,
3.drop the text column (Alter table drop column <column_name>
4.rename the ntext column to the prior text column's name (
5.sp_rename 'table_name.column_name',old_col_name,'column')
II.
1. You have export the data out (BCP OUT/DTS)
2. Take the script of table and dependant objects
3. Drop the table
4. In create tabl script change TEXT datatype to NTEXT
5. Craete the table.
6. BCP IN/ DTS to Import data
7. Create the dependant objects
You can also try the below, which requires less downtime
Thanks
Hari
MCDBA
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:u3iKxbGVEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> how to do that with SQL7 or 2000 by Transact-SQL '
> If I read the SQL2000 doc on 'Alter Table' :
> ALTER COLUMN
> Specifies that the given column is to be changed or altered. ALTER COLUMN
is
> not allowed if the compatibility level is 65 or earlier. For more
> information, see sp_dbcmptlevel.
> The altered column cannot be:
> a.. A column with a text, image, ntext, or timestamp data type.
> <<<<<<<<<<<<<<<<<<<<<<<
>
>
> Lilian.
>|||Thanks)
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23HoJXlGVEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi,
> You cannot alter the text column directly.
> 2 Approaches to do this
> --
> I.
> 1.Create an ntext column,
> 2.update the text column's data to the ntext column,
> 3.drop the text column (Alter table drop column <column_name>
> 4.rename the ntext column to the prior text column's name (
> 5.sp_rename 'table_name.column_name',old_col_name,'column')
>
> II.
> 1. You have export the data out (BCP OUT/DTS)
> 2. Take the script of table and dependant objects
> 3. Drop the table
> 4. In create tabl script change TEXT datatype to NTEXT
> 5. Craete the table.
> 6. BCP IN/ DTS to Import data
> 7. Create the dependant objects
>
> You can also try the below, which requires less downtime
> --
> Thanks
> Hari
> MCDBA
> "Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
> news:u3iKxbGVEHA.4092@.TK2MSFTNGP11.phx.gbl...
COLUMN[vbcol=seagreen]
> is
>sql
how to do that with SQL7 or 2000 by Transact-SQL '
If I read the SQL2000 doc on 'Alter Table' :[vbcol=seagreen]
ALTER COLUMN
Specifies that the given column is to be changed or altered. ALTER COLUMN is
not allowed if the compatibility level is 65 or earlier. For more
information, see sp_dbcmptlevel.
The altered column cannot be:
a.. A column with a text, image, ntext, or timestamp data type.
<<<<<<<<<<<<<<<<<<<<<<<
Lilian.Hi,
You cannot alter the text column directly.
2 Approaches to do this
--
I.
1.Create an ntext column,
2.update the text column's data to the ntext column,
3.drop the text column (Alter table drop column <column_name>
4.rename the ntext column to the prior text column's name (
5.sp_rename 'table_name.column_name',old_col_name,'column')
II.
1. You have export the data out (BCP OUT/DTS)
2. Take the script of table and dependant objects
3. Drop the table
4. In create tabl script change TEXT datatype to NTEXT
5. Craete the table.
6. BCP IN/ DTS to Import data
7. Create the dependant objects
You can also try the below, which requires less downtime
Thanks
Hari
MCDBA
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:u3iKxbGVEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> how to do that with SQL7 or 2000 by Transact-SQL '
> If I read the SQL2000 doc on 'Alter Table' :
> ALTER COLUMN
> Specifies that the given column is to be changed or altered. ALTER COLUMN
is
> not allowed if the compatibility level is 65 or earlier. For more
> information, see sp_dbcmptlevel.
> The altered column cannot be:
> a.. A column with a text, image, ntext, or timestamp data type.
> <<<<<<<<<<<<<<<<<<<<<<<
>
>
> Lilian.
>|||Thanks
)"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23HoJXlGVEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi,
> You cannot alter the text column directly.
> 2 Approaches to do this
> --
> I.
> 1.Create an ntext column,
> 2.update the text column's data to the ntext column,
> 3.drop the text column (Alter table drop column <column_name>
> 4.rename the ntext column to the prior text column's name (
> 5.sp_rename 'table_name.column_name',old_col_name,'column')
>
> II.
> 1. You have export the data out (BCP OUT/DTS)
> 2. Take the script of table and dependant objects
> 3. Drop the table
> 4. In create tabl script change TEXT datatype to NTEXT
> 5. Craete the table.
> 6. BCP IN/ DTS to Import data
> 7. Create the dependant objects
>
> You can also try the below, which requires less downtime
> --
> Thanks
> Hari
> MCDBA
> "Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
> news:u3iKxbGVEHA.4092@.TK2MSFTNGP11.phx.gbl...
COLUMN[vbcol=seagreen]
> is
>sql
How to alter a 'text' column to 'ntext' ?
Hi all,
how to do that with SQL7 or 2000 by Transact-SQL '
If I read the SQL2000 doc on 'Alter Table' :
>>>>>>>>>>>>..
ALTER COLUMN
Specifies that the given column is to be changed or altered. ALTER COLUMN is
not allowed if the compatibility level is 65 or earlier. For more
information, see sp_dbcmptlevel.
The altered column cannot be:
a.. A column with a text, image, ntext, or timestamp data type.
<<<<<<<<<<<<<<<<<<<<<<<
:(
Lilian.Hi,
You cannot alter the text column directly.
2 Approaches to do this
--
I.
1.Create an ntext column,
2.update the text column's data to the ntext column,
3.drop the text column (Alter table drop column <column_name>
4.rename the ntext column to the prior text column's name (
5.sp_rename 'table_name.column_name',old_col_name,'column')
II.
1. You have export the data out (BCP OUT/DTS)
2. Take the script of table and dependant objects
3. Drop the table
4. In create tabl script change TEXT datatype to NTEXT
5. Craete the table.
6. BCP IN/ DTS to Import data
7. Create the dependant objects
You can also try the below, which requires less downtime
--
Thanks
Hari
MCDBA
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:u3iKxbGVEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> how to do that with SQL7 or 2000 by Transact-SQL '
> If I read the SQL2000 doc on 'Alter Table' :
> >>>>>>>>>>>>..
> ALTER COLUMN
> Specifies that the given column is to be changed or altered. ALTER COLUMN
is
> not allowed if the compatibility level is 65 or earlier. For more
> information, see sp_dbcmptlevel.
> The altered column cannot be:
> a.. A column with a text, image, ntext, or timestamp data type.
> <<<<<<<<<<<<<<<<<<<<<<<
> :(
>
> Lilian.
>|||Thanks :))
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23HoJXlGVEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi,
> You cannot alter the text column directly.
> 2 Approaches to do this
> --
> I.
> 1.Create an ntext column,
> 2.update the text column's data to the ntext column,
> 3.drop the text column (Alter table drop column <column_name>
> 4.rename the ntext column to the prior text column's name (
> 5.sp_rename 'table_name.column_name',old_col_name,'column')
>
> II.
> 1. You have export the data out (BCP OUT/DTS)
> 2. Take the script of table and dependant objects
> 3. Drop the table
> 4. In create tabl script change TEXT datatype to NTEXT
> 5. Craete the table.
> 6. BCP IN/ DTS to Import data
> 7. Create the dependant objects
>
> You can also try the below, which requires less downtime
> --
> Thanks
> Hari
> MCDBA
> "Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
> news:u3iKxbGVEHA.4092@.TK2MSFTNGP11.phx.gbl...
> > Hi all,
> >
> > how to do that with SQL7 or 2000 by Transact-SQL '
> >
> > If I read the SQL2000 doc on 'Alter Table' :
> > >>>>>>>>>>>>..
> > ALTER COLUMN
> >
> > Specifies that the given column is to be changed or altered. ALTER
COLUMN
> is
> > not allowed if the compatibility level is 65 or earlier. For more
> > information, see sp_dbcmptlevel.
> >
> > The altered column cannot be:
> >
> > a.. A column with a text, image, ntext, or timestamp data type.
> > <<<<<<<<<<<<<<<<<<<<<<<
> > :(
> >
> >
> > Lilian.
> >
> >
>
how to do that with SQL7 or 2000 by Transact-SQL '
If I read the SQL2000 doc on 'Alter Table' :
>>>>>>>>>>>>..
ALTER COLUMN
Specifies that the given column is to be changed or altered. ALTER COLUMN is
not allowed if the compatibility level is 65 or earlier. For more
information, see sp_dbcmptlevel.
The altered column cannot be:
a.. A column with a text, image, ntext, or timestamp data type.
<<<<<<<<<<<<<<<<<<<<<<<
:(
Lilian.Hi,
You cannot alter the text column directly.
2 Approaches to do this
--
I.
1.Create an ntext column,
2.update the text column's data to the ntext column,
3.drop the text column (Alter table drop column <column_name>
4.rename the ntext column to the prior text column's name (
5.sp_rename 'table_name.column_name',old_col_name,'column')
II.
1. You have export the data out (BCP OUT/DTS)
2. Take the script of table and dependant objects
3. Drop the table
4. In create tabl script change TEXT datatype to NTEXT
5. Craete the table.
6. BCP IN/ DTS to Import data
7. Create the dependant objects
You can also try the below, which requires less downtime
--
Thanks
Hari
MCDBA
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:u3iKxbGVEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> how to do that with SQL7 or 2000 by Transact-SQL '
> If I read the SQL2000 doc on 'Alter Table' :
> >>>>>>>>>>>>..
> ALTER COLUMN
> Specifies that the given column is to be changed or altered. ALTER COLUMN
is
> not allowed if the compatibility level is 65 or earlier. For more
> information, see sp_dbcmptlevel.
> The altered column cannot be:
> a.. A column with a text, image, ntext, or timestamp data type.
> <<<<<<<<<<<<<<<<<<<<<<<
> :(
>
> Lilian.
>|||Thanks :))
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23HoJXlGVEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi,
> You cannot alter the text column directly.
> 2 Approaches to do this
> --
> I.
> 1.Create an ntext column,
> 2.update the text column's data to the ntext column,
> 3.drop the text column (Alter table drop column <column_name>
> 4.rename the ntext column to the prior text column's name (
> 5.sp_rename 'table_name.column_name',old_col_name,'column')
>
> II.
> 1. You have export the data out (BCP OUT/DTS)
> 2. Take the script of table and dependant objects
> 3. Drop the table
> 4. In create tabl script change TEXT datatype to NTEXT
> 5. Craete the table.
> 6. BCP IN/ DTS to Import data
> 7. Create the dependant objects
>
> You can also try the below, which requires less downtime
> --
> Thanks
> Hari
> MCDBA
> "Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
> news:u3iKxbGVEHA.4092@.TK2MSFTNGP11.phx.gbl...
> > Hi all,
> >
> > how to do that with SQL7 or 2000 by Transact-SQL '
> >
> > If I read the SQL2000 doc on 'Alter Table' :
> > >>>>>>>>>>>>..
> > ALTER COLUMN
> >
> > Specifies that the given column is to be changed or altered. ALTER
COLUMN
> is
> > not allowed if the compatibility level is 65 or earlier. For more
> > information, see sp_dbcmptlevel.
> >
> > The altered column cannot be:
> >
> > a.. A column with a text, image, ntext, or timestamp data type.
> > <<<<<<<<<<<<<<<<<<<<<<<
> > :(
> >
> >
> > Lilian.
> >
> >
>
Monday, March 19, 2012
How to add strings to NTEXT data type
Hi, All,
First of all thanks for reading my post. I’m trying to figure out how to
add strings to a NTEXT data type column.
TABLE (
NoteID VARCHAR(10) PRIMARY KEY,
NoteOppID VARCHAR(10) FOREIGN KEY,
NoteDetails VARCHAR(1000)
)
I need to take all NoteDetails in the above table and group by NoteOppID and
insert them into a single NTEXT field…
i.e)
NoteOppID NoteDetails (VARCHAR)
----
Opp123 Matt please send this customer a prototype
Opp123 Customer wants to see the G Series
Opp123 John, I send customer prototype I'll get back to
you on their response.
Opp456 Customer is not satisfied with the 12000 Series
components.
Opp456 John - Please give me your thoughts on this
-- End result should look like
OppId Details (NTEXT)
----
Opp123 Matt please send this customer a prototype
[New Line]
Customer wants to see the MIR 900 Series
[New Line]
John, I send customer prototype I'll get back to
you on their response.
Opp456 Customer is not satisfied with the 12000 Series
Components
[New Line]
John - Please give me your thoughts on this
I cannot leave it at VARCHAR because I'll go over the 8kb constraint.
I’ve tried…
SELECT [NTEXT] + VARCHAR -- Errors
SELECT SUBSTRING([NTEXT], 0, DATALEN([NTEXT]) + VARCHAR – works but
potentially [NTEXT] will be larger than the 8kb constraint.
Thanks,
-NeilAphex3000 (Aphex3000@.discussions.microsoft.com) writes:
> First of all thanks for reading my post. I'm trying to figure out how to
> add strings to a NTEXT data type column.
You will most likely need to use WRITETEXT and UPDATETEXT. Please look
these up in Books Online.
(Or you upgrade to SQL 2005 where you can use nvarchar(MAX) instead
which is a lot easier to deal with.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
First of all thanks for reading my post. I’m trying to figure out how to
add strings to a NTEXT data type column.
TABLE (
NoteID VARCHAR(10) PRIMARY KEY,
NoteOppID VARCHAR(10) FOREIGN KEY,
NoteDetails VARCHAR(1000)
)
I need to take all NoteDetails in the above table and group by NoteOppID and
insert them into a single NTEXT field…
i.e)
NoteOppID NoteDetails (VARCHAR)
----
Opp123 Matt please send this customer a prototype
Opp123 Customer wants to see the G Series
Opp123 John, I send customer prototype I'll get back to
you on their response.
Opp456 Customer is not satisfied with the 12000 Series
components.
Opp456 John - Please give me your thoughts on this
-- End result should look like
OppId Details (NTEXT)
----
Opp123 Matt please send this customer a prototype
[New Line]
Customer wants to see the MIR 900 Series
[New Line]
John, I send customer prototype I'll get back to
you on their response.
Opp456 Customer is not satisfied with the 12000 Series
Components
[New Line]
John - Please give me your thoughts on this
I cannot leave it at VARCHAR because I'll go over the 8kb constraint.
I’ve tried…
SELECT [NTEXT] + VARCHAR -- Errors
SELECT SUBSTRING([NTEXT], 0, DATALEN([NTEXT]) + VARCHAR – works but
potentially [NTEXT] will be larger than the 8kb constraint.
Thanks,
-NeilAphex3000 (Aphex3000@.discussions.microsoft.com) writes:
> First of all thanks for reading my post. I'm trying to figure out how to
> add strings to a NTEXT data type column.
You will most likely need to use WRITETEXT and UPDATETEXT. Please look
these up in Books Online.
(Or you upgrade to SQL 2005 where you can use nvarchar(MAX) instead
which is a lot easier to deal with.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Wednesday, March 7, 2012
how to add a nvarchar parameter to ntext or text types?
please help >>
how to add a nvarchar parameter to ntext or text types like :
set @.ntext_param = @.ntext_param + @.nvarchar_param
You cannot declare variables of text/ntext/image data type in SQL Server and manipulate them. You can only have parameters of SPs or functions with these data types. You can however use UPDATETEXT to append a varchar/char/nvarchar/char/text/ntext/varbinary/binary value to text/ntext/image value.
In SQL Server 2005, you can use the new nvarchar(max) and nvarbinary(max) data types similar to regular character data types. This will allow you to use concatenation operators. These data types are replacement for text/ntext/image data types.
|||Concatenation is not supported on ntext, but is supported on nvarchar(max). You can do the following:
set @.ntext_param = convert(nvarchar(max), @.ntext_param) + @.nvarchar_param
Sunday, February 19, 2012
How to access Sql TEXTPTR from a page
Hello everyone,
I have a stored procedure which returns a txtptr to ntext data (resulted from READTEXT operation) and its datatype is varbinary(16). In my web page, I want to get the text read and display it using a mutiline textbox. Does anyone know how to access that ntext data ?
Thank you very much.
Regards,
Billy.Just return the TEXT field via a DataReader instead of returning the txtptr.|||Thanks for your reply Darrell.
Subscribe to:
Posts (Atom)