March 17, 2014 at 8:05 am
I have a lot of PDFs stored in a table, and it works fine when extracting (and displaying) them using WriteBLOB (http://support.microsoft.com/kb/103257).
I'm looking for a way to extracting the files directly from the SQL Server. I've tried using 'bcp "SELECT Document FROM myTable WHERE ID=1" queryout "C:\TEST.pdf" -T -c' but it doesn't work for me. The files is damaged when trying to open them afterwards. I simply do not understand what I'm doing wrong.
I have been looking through a lot of articles, but I haven't been able to find a solution.
Anyone who can help me?
March 17, 2014 at 8:34 am
sten.f.soerensen (3/17/2014)
I have a lot of PDFs stored in a table, and it works fine when extracting (and displaying) them using WriteBLOB (http://support.microsoft.com/kb/103257).I'm looking for a way to extracting the files directly from the SQL Server. I've tried using 'bcp "SELECT Document FROM myTable WHERE ID=1" queryout "C:\TEST.pdf" -T -c' but it doesn't work for me. The files is damaged when trying to open them afterwards. I simply do not understand what I'm doing wrong.
I have been looking through a lot of articles, but I haven't been able to find a solution.
Anyone who can help me?
I don't know of any way do it in a single step. It is stored as a byte array and you need a way to put them back together. Also, you might consider changing your datatype to varbinary(max) instead of image because the image datatype is deprecated. I have been able to simply change the datatype without issue but you need to make sure it would work for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 18, 2014 at 2:01 am
I've tried to convert the datatype from Image to Varbinary, but that didn't help. I keep getting file is damaged. I have also tried to change the code_page to different settings, but nothing seems to help.
I have also tried the following script, but with same result.
Can anyone help me??
____ Script : ______
DECLARE @SQLIMG NVARCHAR(MAX),
@IMG_PATH VARBINARY(MAX),
@TIMESTAMP NVARCHAR(MAX),
@ObjectToken INT
DECLARE IMGPATH CURSOR FAST_FORWARD FOR
SELECT [Document] FROM MyTable WHERE (ID = 1)
OPEN IMGPATH
FETCH NEXT FROM IMGPATH INTO @IMG_PATH
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TIMESTAMP = 'C:\TEST.pdf'
PRINT @TIMESTAMP
PRINT @SQLIMG
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken
FETCH NEXT FROM IMGPATH INTO @IMG_PATH
END
CLOSE IMGPATH
DEALLOCATE IMGPATH
March 18, 2014 at 3:56 am
For PDF, .DOC, .XLS etc. one can think of FILESTREEM feature. Refer following link for more detail
http://technet.microsoft.com/en-us/library/bb933993(v=sql.105).aspx
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 18, 2014 at 7:32 am
sten.f.soerensen (3/18/2014)
I've tried to convert the datatype from Image to Varbinary, but that didn't help. I keep getting file is damaged. I have also tried to change the code_page to different settings, but nothing seems to help.I have also tried the following script, but with same result.
Can anyone help me??
____ Script : ______
DECLARE @SQLIMG NVARCHAR(MAX),
@IMG_PATH VARBINARY(MAX),
@TIMESTAMP NVARCHAR(MAX),
@ObjectToken INT
DECLARE IMGPATH CURSOR FAST_FORWARD FOR
SELECT [Document] FROM MyTable WHERE (ID = 1)
OPEN IMGPATH
FETCH NEXT FROM IMGPATH INTO @IMG_PATH
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TIMESTAMP = 'C:\TEST.pdf'
PRINT @TIMESTAMP
PRINT @SQLIMG
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken
FETCH NEXT FROM IMGPATH INTO @IMG_PATH
END
CLOSE IMGPATH
DEALLOCATE IMGPATH
This looks a lot like something you grabbed from the internet and don't understand what it does. There are variables that are never used and @TIMESTAMP as NVARCHAR(MAX)???? A cursor to iterate a single row?
Can you use CLR? This might be a great place to use it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 19, 2014 at 8:42 am
Sean Lange (3/18/2014)
sten.f.soerensen (3/18/2014)
SET @TIMESTAMP = 'C:\TEST.pdf'
This looks a lot like something you grabbed from the internet and don't understand what it does. There are variables that are never used and @TIMESTAMP as NVARCHAR(MAX)???? A cursor to iterate a single row?
Can you use CLR? This might be a great place to use it.
Well I can see why its a varchar but it doesn't need to be varchar(max) - An 8000 char file path will be truncated by the OS if it doesn't generate an error when you try to access the file.
However the real question should be why assign a filepath to a variable that looks like it should hold timestamp data.
I think Sean's assesment of where the script came from is probably accurate and without understanding and cleaning the code up it will come back to bite the OP
March 20, 2014 at 4:34 am
You are right. I just took the script as it was posted on the internet. I too wondered why it was called Timestamp.
However, I have been looking into FILESTREEM and it does look a bit more advanced and I don't fully understand what it takes. It looks like I will have to change the table to "VARBINARY(MAX) FILESTREAM NULL" which worries me a bit, since I've already a lot of pdf-files stored in a existing table.
I haven't really looked at the CLR part yet. It looks like its a way to create a external program which can be run via SSIS/SQL(?)
March 24, 2014 at 8:16 am
sten.f.soerensen (3/20/2014)
You are right. I just took the script as it was posted on the internet. I too wondered why it was called Timestamp.However, I have been looking into FILESTREEM and it does look a bit more advanced and I don't fully understand what it takes. It looks like I will have to change the table to "VARBINARY(MAX) FILESTREAM NULL" which worries me a bit, since I've already a lot of pdf-files stored in a existing table.
I haven't really looked at the CLR part yet. It looks like its a way to create a external program which can be run via SSIS/SQL(?)
I think the real question which you still need to answer is what are you trying to do? It sounds like you have a front end somewhere that can open these documents without a problem. Are you trying to move these files from the database to the file system? In your original post you said something about wanting to open this with just sql. If you can explain your end goal we can help you find a way to get there.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 25, 2014 at 1:40 am
Yes, you are of course right - I will try to explain what I really want.
I have a table where I have stored PDF files in a field with datatype Image. The PDF files are today uploaded to the table using VB script ReadBLOB. When I want to display a PDF file I extract the file using WriteBLOB on to our file server, wherefrom I open the file. All this are done via MS Access which I use as front end.
I would like to know if it possible to do the extract part (WriteBLOB) from an SQL Server (2008 R2) - on to our file server? Or if this can be done through SSIS.
March 25, 2014 at 5:59 am
Just to clarify: Is there a way for me to avoid using the front-end (Access) - I would like to be able to run the extract directly from the SQL server (as a Query/Stored Procedure) or from a SSIS packages?
March 25, 2014 at 7:18 am
sten.f.soerensen (3/25/2014)
Just to clarify: Is there a way for me to avoid using the front-end (Access) - I would like to be able to run the extract directly from the SQL server (as a Query/Stored Procedure) or from a SSIS packages?
Is this a one-time thing so you can pull these out of the database and into the file system? Or is this some sort of ongoing thing? You need to have something more than just t-sql.
A quick binoogle search for "SSIS extract pdf from image column" produced this as the third option. Sounds like exactly what you are looking for.
http://www.mssqltips.com/sqlservertip/2693/export-images-from-a-sql-server-table-to-a-folder-with-ssis/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 25, 2014 at 7:46 am
This is an ongoing thing - a job I need to do quite often.
I actually saw this article and I was quite eager to try it - but it didn't work (just tried again). It seems to create/place a PDF file, but I cannot open the file (file damaged). 🙁
I guess it has to do with the way I uploade the files (ReadBLOB).
March 25, 2014 at 7:53 am
sten.f.soerensen (3/25/2014)
This is an ongoing thing - a job I need to do quite often.I actually saw this article and I was quite eager to try it - but it didn't work (just tried again). It seems to create/place a PDF file, but I cannot open the file (file damaged). 🙁
I guess it has to do with the way I uploade the files (ReadBLOB).
You have me a bit confused here. You say you need to do this repeatedly. Wouldn't this be from a front end of some sort? Why can't you just extract the pdf from whatever front end you are using?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 25, 2014 at 8:05 am
I have a job sending e-mails automatically, and I want to attach a PDF file to these e-mails. That requires the job to place the file to the file server, whereafter it attach the file. Unfortunately, I haven't been able to extract the file(s) successfully - yet.
March 25, 2014 at 8:26 am
Have you checked the properties of the PDF file extracted - there should be a PDF tab which will indicate the version of the writer used to create it and made sure that the copy of Acrobat Reader you are using to open it is at least that version?
It may also be that the problem is with the metadata for this rather than the document itself.
I presume you have confirmed that the document could be read before storing it in SQL.
Personally for something like this I would tend to prefer keeping the document in the filesystem in its original format and only storing a reference to it in SQL. Both processes are valid and there are benifits and problems associated with each.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply