March 10, 2010 at 9:01 am
Can someone tell me or point me to an article that tells me how to retrieve a file from an image data type? By that I mean, I need to be able to pull the saved file (xls, txt, etc.) out of the table and recreate the file so I can attach it to an email. Basically, change the binary form of the file back into the xls or txt file it was saved as. I have found ReadText, TextCopy and the OLE automation procedures, but not sure if any of these is what I need. Thanks in advance for any help.
March 10, 2010 at 9:50 pm
Managing ntext, text, and image data (SQL Server 2000)
I am assuming you are using SQL Server 2000, since these data types are deprecated in 2005 onwards in favour of the MAX data types - which are considerably easier to work with.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 11, 2010 at 7:37 am
Unfortunately, no. This is an application that determined to use these datatypes but it is running on SQL 2005.
March 12, 2010 at 3:07 am
andy russell (3/10/2010)
Can someone tell me or point me to an article that tells me how to retrieve a file from an image data type? By that I mean, I need to be able to pull the saved file (xls, txt, etc.) out of the table and recreate the file so I can attach it to an email. Basically, change the binary form of the file back into the xls or txt file it was saved as. I have found ReadText, TextCopy and the OLE automation procedures, but not sure if any of these is what I need. Thanks in advance for any help.
IMV this is easier to do with a CLR function than with ReadText, etc.
This will come to about 10 lines of C# code:
1) Read the data from the field.
2) Write to a file. This is dead easy in .NET.
3) (optional) Return the name /path to the caller so that you can pass it to sp_send_dbmail.
HTH,
Gavin
March 12, 2010 at 4:32 am
Gavin,
Yes, that's true - if you know your way around a .NET language, which many people do.
I don't think I would necessarily recommend doing it inside SQL Server though (that is, using the CLR hosted environment), since the memory requirements may be quite large. An external .NET application might be a better bet - unless it can be coded to read and write in smaller chunks.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 12, 2010 at 7:42 am
Can it be done with READTEXT?
March 12, 2010 at 8:56 am
andy russell (3/10/2010)
Basically, change the binary form of the file back into the xls or txt file it was saved as.
The data in the column is in the format it was saved as. SQL Server doesn't know whether the data is a txt, xls, jpeg, or whatever, it is just a bunch of bytes.
The textcopy utilty is not shipped with 2005. If you want to use it for testing, bear in mind that you need to copy both textcopy.exe and ntwdblib.dll from a SQL2K installation into a folder on the SQL2K5 box.
READTEXT can return some or all of the contents of the field, but I don't think this is what you need; assuming you want the whole file, then SELECT will do the same job.
I had to do this for several thousand images once, and I used a SSIS write file transformation - is this any use to you?
March 12, 2010 at 9:10 am
That is exactly what I wanted to know. Thank you so much for the help. I had been researching this for a couple of days (not sure if I was using the correct terms in my search) but hadn't been able to find any answers for SQL Server. Nearly everyone said use C#, VB, etc... Thanks again!!
March 16, 2010 at 2:23 pm
GavinC,
I guess I spoke too soon. I don't see a Write File Transformation. Is the task called something different?
March 17, 2010 at 1:43 am
Just a snippet of my code that works, hope it helps
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
conn.Open();
SqlCommand command = new SqlCommand("Select * from tblImages where ImageID=" + "'" + Request.QueryString["ImageID"] + "'",conn);
SqlDataReader dr = command.ExecuteReader();
dr.Read();
Response.Clear();
Response.ContentType = dr.ContentType;
Response.BinaryWrite((byte[])dr["Image"]);
Close();
Response.End();
March 17, 2010 at 8:20 am
sthe4u,
Thanks. I might take a look at that as well. I did find Export column in the SSIS Data flow that says it is supposed to create an output file from the data flow. I can tell it which column has the image data and the full path and it's supposed to create the file. I read something in MSDN that said I might need to convert the image to DT_Image before sending it to the Export column task so I added a Derived column. It creates the file, however, it's just binary data within the file. Not sure how to get this to work. I'm still researching it. If you know of anything please let me know. If I figure it out I'll post the info.
March 17, 2010 at 10:01 am
andy russell (3/16/2010)
GavinC,I guess I spoke too soon. I don't see a Write File Transformation. Is the task called something different?
Sorry, version confusion there. "Export Column" is what you need. The name DT_IMAGE is a red herring; it is just a binary type: http://msdn.microsoft.com/en-us/library/ms141036%28SQL.90%29.aspx.
If you aren't confident, go ahead and export a few, and make sure Word / Excel / whatever can read them.
Gavin
March 26, 2010 at 1:44 pm
OK.
Here's what I figured out. You can use the Export Column Transformation Editor in the SSIS Data flow to create an output file from the data flow. Basically, you have an excel file stored in a table in an image datatype field. You can use the Export to pull this file out of the table and recreate the file on a file system. You tell the Export Column Transformation Editor which column has the image data and which column has the full path including the filename and the Export creates the file. The path and filename are where you want the file created, not where the file was loaded from.
In my case, I did not need to convert the image to DT_Image before sending it to the Export Column Transformation Editor.
Thanks again for all your help.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply