Relative paths in openrowset

Question

Hi,

I am importing an image (successfully on my dev machine) using the following code:

[Thumbnail] = (SELECT MyImage.* from Openrowset(Bulk 'C:\SRC\Tx3SqlScripts\Initialise\Images\Tasks.png', Single_Blob) MyImage)

However, I need to specify the path as a relative path because the build server extracts to a different folder.

I've tried:

.\Images\Tasks.png

...and other variations but none work.

Can OpenRowSet be used with relative paths and if so how?

Paul.

Answer 1

The parameter can only handle a full path. The only suggestion I can come up with is to somehow detect where the file is on the build server, e.g.

 

DECLARE @result int
EXEC @result = xp_cmdshell 'dir /S c:\Tasks.png >c:\result.txt'
IF (@result = 0)
 PRINT 'Success'
ELSE
 PRINT 'Failure'

 

This is only a start. If the technique is acceptable, then you will have to expand this to read back the search results.

 

Answer 2

Hi,

The parameter is the full path of the data file whose data is to be copied into the target table. Please see:

OPENROWSET (Transact-SQL)

Thanks,

Ai-Hua Qiu

---

Constant dropping wears away a stone.