?
Solved

Create Files from Records

Posted on 2011-05-12
5
Medium Priority
?
276 Views
Last Modified: 2012-05-11
We have a third party piece of software which will be getting an upgrade in the near future.  One of the changes that will occur to the database is that all text fields will be changed to varchar(4000) datatype.  Therefore, I need to deal with all records that currently contain more than 4000 characters or else they will be truncated during the upgrade.

I have run some preliminary queries and found that there are only two tables that will be affected.  One only has four records too large so that is not an issue.  The second table has 562 records greater than 4000 characters, which isn't too bad considering there are over 100K records in that table.

Without going in to too much detail about the software, it is a case-based system.  You bring up a case and a number of features are available.  Actions, people, notes, fees, documents, etc.  The offending records are located down in the actions section, where you may find many different actions.  The notes on a specific action will be where the large chunk of text lives.  The document section allows you to attach documents of many different file types.  When attaching a document, the software copies it to a central location and a record is added to a documents table containing the path, filename, etc.

My initial plan on how to tackle this problem goes like so :
  1)  Create a script that will go through the actions table and take all the records with 4000+ sized text fields and export each record's text field to an individual file with a specific name.
  2)  Replace that records text with a message similar to "[This note has moved.  Please check document section for full notes.]"
  3)  Add a record to the document table that points to the newly created files, so the document section will then contain a new document containing the text from the old record.

I shouldn’t have a problem with 2 and 3, but my question to the experts is how to create a script to loop through all records with 4000+ records and export each one to an individual file.

This is just my initial thoughts on how to tackle this issue.  If someone has another approach I would open to hearing it as well.

Thanks!

P.S.  The database is on a SQL Server 2008 box if that matters, and we also have full access to it.
0
Comment
Question by:CIC Admin
  • 3
  • 2
5 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 35747415
You could use datalength function in SQL to loop through all these records and do that  but here's what I suggets besides what you did already. You could add a column like "Attachment" where you could store the pointer to the exported file so the old column will have the varchar(4000)  as a "text" or "description" column and the "attachment" could be downloaded/viewd for these cases where you will have more than 4000 chars to store.
0
 
LVL 5

Author Comment

by:CIC Admin
ID: 35750225
I already used the DATALENGTH function to get the list of 500+ records that are larger than 4000 characters.  What i need to know now is how to export the text field from each record into it's own file.  (text, word, whatever)

I will, as you mention, need to some way to flag each of those records after they are exported so i know which records need to be have their text field truncated and changed to the [see attachment] message (step 2 above).  That part shouldn't be too much of a problem though.
0
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 35755062
you could do something like this or if you don't have an ID for the row to asociate it and create a unique file name you could use newid() sql function instead.

--lets say table_name has id as row identifier and text_data as text that you want to export as a file

declare @id sysname
declare @sqlstr varchar(8000)

DECLARE id_list CURSOR FOR
select id from table_name where datalength(text_data) > 4000 order by id
OPEN id_list
FETCH next FROM id_list INTO @id
      WHILE @@fetch_status=0
      BEGIN
            --you would need to execute a command like below to get that data out in a file
            --exec master..xp_cmdshell N'sqlcmd -E -Q"select text_data from server_name.dba_name.schema_name.table_name" -h-1 -s"," -W -o"c:\output.txt"', no_output
            set @sqlstr = 'exec master..xp_cmdshell N''sqlcmd -E -Q"select text_data from server_name.dba_name.schema_name.table_name" -h-1 -s"," -W -o"c:\'+@id+'text_data_output.txt"'', no_output'
            exec @sqlstr      
      FETCH next FROM id_list INTO @id
      END
CLOSE update_clients
DEALLOCATE update_clients
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35755066
Also you can take out the switches -h-1 -s"," -W from the sqlcmd that you don't need - sorry I left them in there...
0
 
LVL 5

Author Comment

by:CIC Admin
ID: 35786879
Thanks for the code!  After some tweaking, I was able to get it to work.  For reference of future viewers, here is the final code :

DECLARE @id sysname
DECLARE @sqlstr varchar(8000)

DECLARE id_list CURSOR FOR
  SELECT csa_id FROM case_action WHERE DATALENGTH(csa_notes) > 4000 ORDER BY csa_id
OPEN id_list
FETCH next FROM id_list INTO @id
      WHILE @@fetch_status=0
      BEGIN
            set @sqlstr = 'exec master..xp_cmdshell N''sqlcmd -E -Q"SELECT csa_notes FROM sqlserver.dbname.dbo.case_action WHERE csa_id = '''''+@id+'''''" -o"\\myserver\data\TXT\'+@id+'.txt"'', no_output'
            exec(@sqlstr)
      FETCH next FROM id_list INTO @id
      END
CLOSE id_list
DEALLOCATE id_list

The closing cursor name didn't match the opening one.  I also kept getting "SQL String NOT a valid identifier" until i changed the exec @sqlstr line to exec(@sqlstr).  That could have been from the extra quotes i had to put in to get it to work correctly.  Lots of single and double quotes mashed together up there.  

I also had to get xp_cmdshell working with :

EXECUTE SP_CONFIGURE 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXECUTE SP_CONFIGURE 'xp_cmdshell', '1'
RECONFIGURE WITH OVERRIDE
GO
EXECUTE SP_CONFIGURE 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

Thanks again, Icohan, for the help.  It will save us a tremendous amount of time!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question