|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
I need to extract .pdf/.doc/.xls documents from an sql 2000 table.I have a table in sql 2000 where I can see all the file names ex.
how-to-restore-cisci-3700.doc. The table is over 40mb in size. All other tables less than 1mb. I am fairly sure that the files are stored in the table and its just not a link to the file. I need to be able to extract all .doc .xls .pdf files out of the table in their native format so I dont have to retype all of my site documentation. Thanks Chris.lee.johns***@gmail.com (Chris.lee.johns***@gmail.com) writes:
> I have a table in sql 2000 where I can see all the file names ex. If you see a file name, that is a string. It's not even a link, it> how-to-restore-cisci-3700.doc. The table is over 40mb in size. All > other tables less than 1mb. I am fairly sure that the files are stored > in the table and its just not a link to the file. I need to be able to > extract all .doc .xls .pdf files out of the table in their native > format so I dont have to retype all of my site documentation. is just a string. It is a piece of data, and it probably has a meaning, but not having defined the table, nor having seen it, I can't tell you what it means. Are there any image or varbinary(MAX) columns in the table? -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Id uniqueidentifier 16 0
SiteId uniqueidentifier 16 0 DirName nvarchar 256 0 LeafName nvarchar 128 0 WebId uniqueidentifier 16 0 ListId uniqueidentifier 16 1 DoclibRowId int 4 1 Type tinyint 1 0 [Size] int 4 1 MetaInfoSize int 4 1 Version int 4 1 UIVersion int 4 0 Dirty bit 1 1 CacheParseId uniqueidentifier 16 1 DocFlags tinyint 1 1 ThicketFlag bit 1 1 CharSet int 4 1 TimeCreated datetime 8 0 TimeLastModified datetime 8 0 NextToLastTimeModified datetime 8 1 MetaInfoTimeLastModified datetime 8 1 TimeLastWritten datetime 8 1 SetupPath nvarchar 255 1 CheckoutUserId int 4 1 CheckoutDate datetime 8 1 CheckoutExpires datetime 8 1 CheckoutSize int 4 1 VersionCreatedSinceSTCheckout bit 1 0 LTCheckoutUserId int 4 1 VirusVendorID int 4 1 VirusStatus int 4 1 VirusInfo nvarchar 255 1 MetaInfo image 16 1 Content image 16 1 CheckoutContent image 16 1 Extension nvarchar 128 1 Erland Sommarskog wrote: Show quote > Chris.lee.johns***@gmail.com (Chris.lee.johns***@gmail.com) writes: > > I have a table in sql 2000 where I can see all the file names ex. > > how-to-restore-cisci-3700.doc. The table is over 40mb in size. All > > other tables less than 1mb. I am fairly sure that the files are stored > > in the table and its just not a link to the file. I need to be able to > > extract all .doc .xls .pdf files out of the table in their native > > format so I dont have to retype all of my site documentation. > > If you see a file name, that is a string. It's not even a link, it > is just a string. It is a piece of data, and it probably has a meaning, > but not having defined the table, nor having seen it, I can't tell you > what it means. > > Are there any image or varbinary(MAX) columns in the table? > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx I found this posting and it probaly describes my problem better then my
first posting "OK, here is my problem: I have stored an image, as an image datatype, in a SQL Server 2000 table. When you store an image in an image datatype field it is stored as a binary. Now I can't retrieve the image because it is stored as a binary. How can I store an image (.bmp, .jpg, .gif) in a SQL Server 2000 table and retrieve that same image as an image (.bmp, .jpg, .gif) and not a binary? Any help would be greatly appreciated. " Chris.lee.johns***@gmail.com wrote: Show quote > Id uniqueidentifier 16 0 > SiteId uniqueidentifier 16 0 > DirName nvarchar 256 0 > LeafName nvarchar 128 0 > WebId uniqueidentifier 16 0 > ListId uniqueidentifier 16 1 > DoclibRowId int 4 1 > Type tinyint 1 0 > [Size] int 4 1 > MetaInfoSize int 4 1 > Version int 4 1 > UIVersion int 4 0 > Dirty bit 1 1 > CacheParseId uniqueidentifier 16 1 > DocFlags tinyint 1 1 > ThicketFlag bit 1 1 > CharSet int 4 1 > TimeCreated datetime 8 0 > TimeLastModified datetime 8 0 > NextToLastTimeModified datetime 8 1 > MetaInfoTimeLastModified datetime 8 1 > TimeLastWritten datetime 8 1 > SetupPath nvarchar 255 1 > CheckoutUserId int 4 1 > CheckoutDate datetime 8 1 > CheckoutExpires datetime 8 1 > CheckoutSize int 4 1 > VersionCreatedSinceSTCheckout bit 1 0 > LTCheckoutUserId int 4 1 > VirusVendorID int 4 1 > VirusStatus int 4 1 > VirusInfo nvarchar 255 1 > MetaInfo image 16 1 > Content image 16 1 > CheckoutContent image 16 1 > Extension nvarchar 128 1 > > > Erland Sommarskog wrote: > > Chris.lee.johns***@gmail.com (Chris.lee.johns***@gmail.com) writes: > > > I have a table in sql 2000 where I can see all the file names ex. > > > how-to-restore-cisci-3700.doc. The table is over 40mb in size. All > > > other tables less than 1mb. I am fairly sure that the files are stored > > > in the table and its just not a link to the file. I need to be able to > > > extract all .doc .xls .pdf files out of the table in their native > > > format so I dont have to retype all of my site documentation. > > > > If you see a file name, that is a string. It's not even a link, it > > is just a string. It is a piece of data, and it probably has a meaning, > > but not having defined the table, nor having seen it, I can't tell you > > what it means. > > > > Are there any image or varbinary(MAX) columns in the table? > > > > > > -- > > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > > > Books Online for SQL Server 2005 at > > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > > Books Online for SQL Server 2000 at > > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Chris.lee.johns***@gmail.com (Chris.lee.johns***@gmail.com) writes:
> I found this posting and it probaly describes my problem better then my The normal way to store and retrieve data from a database, is to > first posting > > "OK, here is my problem: > I have stored an image, as an image datatype, in a SQL Server 2000 > table. When you store an image in an image datatype field it is stored > as a binary. > Now I > can't retrieve the image because it is stored as a binary. How can I > store an image (.bmp, .jpg, .gif) in a SQL Server 2000 table and > retrieve that same image as an image (.bmp, .jpg, .gif) and not a > binary? Any help would be greatly appreciated. " use an client program which is more or less custom-written for the task. For simpler data-access and update purposes simple query tools like Enterprise Manager and Query Analyzer may do, but that's not the way you normally work. And for binary data they are really insufficient. You get to see the data as a hexstring, but that is not very useful. The normal way would be to have an application that retrieves the data and writes it to disk. SQL Server 2000 comes with textcopy which is a simple interactive command-line tool which may be able to do the task. However, it's implemented in DB-Library which is an old API that does not support newer features of SQL Server. In your case, I assume that these documents were entered into the database by some program. That program should also be able to extract the documents I suppose. ....and a really crazy way would be to use BCP to extract them. You would need a format file, that specifies that only one column should be extracted. Furthermore, you would need to use the queryout option to restricted the selection to a single row. But I haven't actually tested whether this works. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Id uniqueidentifier 16 0
SiteId uniqueidentifier 16 0 DirName nvarchar 256 0 LeafName nvarchar 128 0 WebId uniqueidentifier 16 0 ListId uniqueidentifier 16 1 DoclibRowId int 4 1 Type tinyint 1 0 [Size] int 4 1 MetaInfoSize int 4 1 Version int 4 1 UIVersion int 4 0 Dirty bit 1 1 CacheParseId uniqueidentifier 16 1 DocFlags tinyint 1 1 ThicketFlag bit 1 1 CharSet int 4 1 TimeCreated datetime 8 0 TimeLastModified datetime 8 0 NextToLastTimeModified datetime 8 1 MetaInfoTimeLastModified datetime 8 1 TimeLastWritten datetime 8 1 SetupPath nvarchar 255 1 CheckoutUserId int 4 1 CheckoutDate datetime 8 1 CheckoutExpires datetime 8 1 CheckoutSize int 4 1 VersionCreatedSinceSTCheckout bit 1 0 LTCheckoutUserId int 4 1 VirusVendorID int 4 1 VirusStatus int 4 1 VirusInfo nvarchar 255 1 MetaInfo image 16 1 Content image 16 1 CheckoutContent image 16 1 Extension nvarchar 128 1 Erland Sommarskog wrote: Show quote > Chris.lee.johns***@gmail.com (Chris.lee.johns***@gmail.com) writes: > > I have a table in sql 2000 where I can see all the file names ex. > > how-to-restore-cisci-3700.doc. The table is over 40mb in size. All > > other tables less than 1mb. I am fairly sure that the files are stored > > in the table and its just not a link to the file. I need to be able to > > extract all .doc .xls .pdf files out of the table in their native > > format so I dont have to retype all of my site documentation. > > If you see a file name, that is a string. It's not even a link, it > is just a string. It is a piece of data, and it probably has a meaning, > but not having defined the table, nor having seen it, I can't tell you > what it means. > > Are there any image or varbinary(MAX) columns in the table? > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics