Create Files from Records

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.
LVL 5
CIC AdminAsked:
Who is Participating?
 
lcohanDatabase AnalystCommented:
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
 
lcohanDatabase AnalystCommented:
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
 
CIC AdminAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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
 
CIC AdminAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.