April 27, 2004 at 7:45 am
Hello All,
I'm a developer with an issue that I may have resolved on my own but am not convinced it is the most practical solution. We just replatformed our DB from DB2 on the mainframe to SQL Server 2000. I have a situation where I will want to insert a value that is greater than 7,996 characters into one table column. Here is the error I get in the event log on the application server.
**************************************************************************
[Microsoft][ODBC SQL Server Driver]String data, right truncationINSERT INTO EXTERNAL_EVENT (N_EXTRL_EVNT_ID, N_XXXX_ID, M_INTRFC_NAME, N_EVENT_TYPE, T_MSG_DATA, Q_SEND_ATMPT, D_CREATE_TS, D_LAST_UPDT_TS) VALUES (XXXXXXXXX, OOOOOOOOO, TEST, 1197, <env><action>A</action><file_note_id>XXXXXXXXXXXXXX</file_note_id>.......
**************************************************************************
T_MSG_DATA is a column that will hold an xml string that will, on occasion, be larger than 8,000 characters. My DBA's have defined the column to the max size allowed. What is the appropriate way to insert this column data? This is what I came up with but am not convinced it is the most economical way to do it:
***********************************************************************
insert into external_event
values ('XXXXXXXXX', 'OOOOOOOOO', 'TEST', 1197, '', 0,
'2004-01-01 12:00:00.000', '2004-01-01 12:00:00.000')
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(T_MSG_DATA)
FROM external_event
WHERE n_extrl_evnt_id = 'XXXXXXXXX'
WRITETEXT external_event.t_msg_data @ptrval
'<env><action>A</action><file_note_id>XXXXXXXXXXXXXX</file_note_id>.......'
***********************************************************************
Ideas, suggestions, right, wrong, bad performing SQL?
April 28, 2004 at 12:11 am
- keep in mind that sql also has to manage rowlayout, nullable columns also have the need for management-place (1 byte per 7 nullable columns). Check "dbcc showcontig" for your table to have some rowlength-statistics.
- use text/image datatype and you can have up to 2gb per text-column.
Check BOL "Managing ntext, text, and image Data" for advantages and disadvantages
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply