Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pprithvi05
Starting Member
3 Posts |
 Posted - 2011-07-04 : 12:15:52
|
| Hi,I have a binary data for eg. 0x450102....... which i want to insert into a ntext column.This binary data represents unicode string. I tried using Convert function to convert the binary data into nvarchar as Convert(nvarchar(4000),0x450102.......)into the insert query. But the length of the binary data is greater than 4000 unicode characters i.e. greater than 8000 bytes. This makes the query to fail giving the error "conversion of image datatype to nvarchar is not allowed".Note that this query works for binary data with less than 4000 unicode characters. Is there any alternative to insert this binary data into the ntext column. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-04 : 15:52:51
|
| You can insert into an ntext in 4000 byte chunks.seehttp://www.nigelrivett.net/SQLTsql/InsertTextData.htmlCan you upgrade to v2008? If not then maybe get a version of 2008 express and use that for the convert to a nvarchar(max) and import that into an ntext.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
pprithvi05
Starting Member
3 Posts |
Posted - 2011-07-05 : 05:39:37
|
| Thanks for the reply,But i have this binary data in memory(i.e stored in a variable). So how this technique can be applied. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-05 : 08:51:34
|
| What is the datatype of the variable?Just loop through it and deal with smaller chunks.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
dipansh
Starting Member
1 Post |
Posted - 2011-12-26 : 07:42:33
|
| u not need to convert your binary, directly insert without single quotex.insert into tableX(name,col1) values ('name',0x450102)where col1 is your ntext column, but be shore that the binary bhould be prefixed with 0xu can use this type of insert in any type of data type like for int, varchar,nvarchar,text or ntext |
|
|
|
|
|
|
insert binary data into ntext