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 |
|
dlorenc
Posting Yak Master
172 Posts |
 Posted - 2012-01-05 : 11:51:10
|
| What is the best practice in dealing with long data fields? In my case, I have 'log' field that the application builds from user comments (user types in a free form text entry, the app pre-pends a datetime stamp then prepends that text stream into the [Work_Log] field...something like this:01/04/2011 08:45 Contacted the local rep and found the UPS failed. Replaced the UPS and recycled power on local net...blah blah blah...01/04/2011 08:10 Report received that a router was down in the DC field office...blah blah blah....In doing ssis imports from the OLTP app into a datamart for analytics, I first ran into the whole truncate problem ... now I have an issue with temporary space... I think... Error: 0xC0208265 at ProvisioningClosed, OLE DB Source [1]: Failed to retrieve long data for column "Work_Log".Error: 0xC020901C at ProvisioningClosed, OLE DB Source [1]: There was an error with output "OLE DB Source Output" (11) on component "OLE DB Source" (1). The column status returned was: "DBSTATUS_UNAVAILABLE".My DBA has set aside 33G, and I set the BLOBTempStoragePath and BufferTempStoragePath to Fast Drives.(http://wills-blog.com/?p=336) ... and it WAS working...last night it failed...and now consistently fails...so I'm thinking an extry was added that now exceeds something..somewhere...mm?sooo...I'm now rethinking what is REALLY needed by an analyst...and IMHO, as the logs are stored DESC on the datetime, there likely is enough information in the first few entries (say the first 3000 chars of the DT_Text field) for my purposes..I do NOT need to store the whole log...sooo...I'm now thinking of forcing a truncate (DT_Text to varchar(3000)??) on the log field to get rid of the whole variable space blob issue in dealing with long text fields...in ssis, advanced editor for OLE DB Source, external (input) source for field [Work_Log] is a text stream [DT_TEXT]. The output is also field [Work_Log] text stream [DT_TEXT]...sooo... what IS best practice in dealing with long text fields imports? and/or what IS the best way to force a truncate?whatchathink? |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-01-30 : 19:37:36
|
| does work_log have any constraints? like NOT NULL. I would suggest you do not stuff everything into one field. instead enter each user's log into a separate row with UserID.If you don't have the passion to help people, you have no passion |
 |
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2012-01-31 : 09:37:18
|
| no constraints... it can be null..but PROCESS provides that there typically is a work log documenting what was/is being done to resolve the trouble ticket...This is a pull from an operational system (OLTP), so I have no control over the help desk application that is generating the data...now, that said..there is no reason that (in ssis) when I pull the data that I cannot parse that log field into the first, most recent log entried... the application begins each log entry with an integer (representing the date, in seconds from 1/1/1970 which I would have to convert back to a datetime)...I have not found a good way to parse that field by those integers (dates)....yet.... |
|
|
|
|
|
|
|
|
best practice dealing with long data..??