April 10, 2015 at 2:41 am
Hi,
I've tried all sorts of variants on the OPENROWSET theme to import a file (not file contents, the whole file) into a SQL Server 2005 database table, but I either get a null column (importing a .txt file) or a blank column (importing a .xls file). What could I be doing wrong? Here's an example of my code:
[font="Courier New"]DECLARE @sql nvarchar(max)
SET @sql = 'UPDATE CAS_DocumentStorage SET [Picture] = (SELECT * FROM OPENROWSET(BULK N''' + @fullPath + ''', SINGLE_BLOB) AS x) WHERE DocRefNo = ''' + @letterRefCode + ''''
Print @sql
EXEC sp_executesql @sql[/font]
The destination table has only three columns -
[PicId] [int] IDENTITY(1,1) NOT NULL,
[DocRefNo] [char](50) NULL,
[Picture] [image] NULL
There's no error when I try the import, but the file does not appear when I select that row from the database.
I'd really appreciate some ideas!
April 22, 2015 at 12:22 pm
Not much to go on, but ...
If you run this:
SELECT * FROM OPENROWSET(BULK N''' + @fullPath + ''', SINGLE_BLOB) AS x
Does it run silently or do you get an error?
If you 'PRINT x' does x have any value?
Does @fullPath have the right value?
Do you have permission to execute the BULK statement. It's usually off by default.
HTH,
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
April 27, 2015 at 3:25 am
Thanks for your input. It looks as though I was just being stupid though!
The code was running without error and I did have bulkadmin rights, but when I did a SELECT from the destination table, the column containing the image (attachment) was just showing as blank whereas all the other rows in that table were displaying a load of hex for the attachment so I assumed the file wasn't being imported. When I actually went and bothered to check in the UI of this application, the file was there.
It seems as though the hex appearing in the column depends on the type of file being imported. xlsx, txt and doc files are represented with hex. Xls, pdf and jpg files show as blanks. It would be interesting to know why this is the case - perhaps something to do with system registered file types? - but happy that my problem wasn't actually a problem!
Thanks again for taking a look at my question Sigerson!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply