| Author |
Topic |
|
wydyCH
Starting Member
5 Posts |
 Posted - 2013-06-20 : 02:23:33
|
| Moin,I have a little Problem with a binary Column in my Database. I have a table named docs with the columns doc_id(PK) and doc_image(image, null).I want upload an Excel file in this table with an SQL Script. First of all I triedSELECT BulkColumnFROM OPENROWSET( BULK 'C:\test.xls',SINGLE_BLOB) as ExternalFileIt worked on my local machine, but the updater for our clients doesn't have the permission for bulk. Also the Excel file is on the local machine and the script works on a server.In a next step i tried to upload the binary from the bulk command directly with an INSERT statement. But it doesn't worked. The generated Excel Sheet contains only cryptic symbols like:®à0ïð�``0ïÿÿðÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿßÿÿòP0@P`p€� °ÀÐàð 0@P`p€� °ÀÐàð 0@ïÿÿóÐ0€@ °ÀÐàð 0ïÿÿóP`p€� °Ààð€ @pP`p�` °ÀÐàð 0@P€ïÿÿÿïÿÿõ�ïÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿõ ðð@Pà@ �`Pÿÿÿÿÿÿÿð € `À=ûçRüà°<ì–Ìàp0pð ° ðð° pÿÿÿÿÿÿÿð @ð` ð ð P0@ð0P P0 ŸKHere the insert statement:Insert into doc(doc_id, doc_image)values('8CDA1EFB-733A-425E-90A6-BF492937B740',0xD0CF11E0A1B11AE1000000000000000000000000000000003E000300FEFF09000600000000000..)The binary has about 44k characters. What is the problem? I tried different ways for the upload such as CONVERT, but nothing worked. Is the binary too big for the upload? Is there another way to upload this binary?I apologize for my mistakes in the question. English isn't my first language. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-20 : 02:43:27
|
| whats the datatype of field as recognized by excel?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
wydyCH
Starting Member
5 Posts |
Posted - 2013-06-20 : 05:04:37
|
| Sry I don't unterstand the question.I will save the whole excel in the database as a binary.The table in the database is:CREATE TABLE doc( doc_id uniqueidentifier ROWGUIDCOL NOT NULL, dok_image image NULL)The excel file is a template without any data. There are only a title, some column headings, 2 datefields and a bit layout. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-20 : 05:14:37
|
| one way is you can use SSIS. put a data flow task in ssis with excel source and your table as destination and you'll be able to transfer excel binary data to table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
wydyCH
Starting Member
5 Posts |
Posted - 2013-06-20 : 05:52:53
|
| I don't want to transfer data between my excel and the database.In our solution you can create a report. The program take the excel sheet, put some data in this excel and save it local on the pc.And the developer of this function had a special idea. Why we don't save the excel file as an image in the database.Now I have to change this report with some new information. Also I have to change this excel file for the new information. But the file is saved as an image on the server.Now I have the update the excel on every client of us and thats the problem. The only way for this is a sql skript, which replace the old excel file. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-20 : 06:03:30
|
| then why dont you use same datasource the earlier report used, change the source statement (or procedure) to add your new changes and make it save in image(or excel) format so that it overwrites current version? you can do this in reporting tools like SQL Reporting Services.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
wydyCH
Starting Member
5 Posts |
Posted - 2013-06-20 : 06:49:01
|
| I can change the file in the "administration" tool, where the excelfile will replace. This function works with a streamwriter.But we have about 50 customer, which use our solution. Every customer has his own database, where I have to change the excel file. We created an "autoupdater" which can execute sql Scripts on the different servers/databases of our customers. My idea was to create an sql script, which replace every excelfile and put it on the "autoupdater". |
|
|
|
wydyCH
Starting Member
5 Posts |
Posted - 2013-06-20 : 11:03:52
|
| I found a solution:I create a base64 stringThen i update this string and convert it in a image.How to update it: http://www.codemeit.com/sql/sql-2005-base64-decode-query-syntax.htmlHere is the solution-- CodeSET ANSI_WARNINGS ON--base64 Daten vom ExcelDECLARE @data varchar(max), @XmlData xmlSET @data = '...'SET @XmlData = CAST('<data>' + @data + '</data>' as xml)insert into doc(doc_id, doc_image)values('8CDA1EFB-733A-425E-90A6-BF492937B740', CONVERT(image, @XmlData.value('(data)[1]', 'varbinary(max)')) ) |
|
|
|
|
Create a binary from an Excel Sheet