This is my script - it simply looks for the image file associated to a person record.
Now the error will display if there is NO .jpg evident when the query runs.
Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "C:\Dev\ClientServices\Defence\RAN\Shore\Config\Photos\002054.2009469432270600.001.jpg" does not exist.
Is there a way to write this query to 'IF not exists then set id_number = '002054.2009469432270427.001' - so it wil always display this photo for any records without a picture.
ALTER
procedure [dbo].[as_ngn_sp_REP_PH108_photo] (@PMKEYS nvarchar(50))
AS
---exec [as_ngn_sp_REP_PH108_photo] '8550733'
SET
NOCOUNT ON
DECLARE @PATH AS NVARCHAR(255)
DECLARE @ID_NUMBER NVARCHAR(27)
DECLARE @SQL AS NVARCHAR(MAX)
EXEC DB_GET_DB_SETTING'STAFF PICTURE FILE LOCATION', 0, @PATH OUTPUT
IF RIGHT(@PATH,1) <> '\'
SET @PATH = @PATH + '\'
SELECT @ID_NUMBER = ID_NUMBER FROM aView_person WHERE EXTRA_CODE_1 = @PMKEYS
SET @PATH = @PATH + @ID_NUMBER + '.jpg'
SET @SQL = 'SELECT ''Picture1'' [Picture], BulkColumn FROM OPENROWSET(Bulk ''' + REPLACE(@PATH,'''','''''') + ''', SINGLE_BLOB) AS RAN'
EXEC SP_EXECUTESQL @SQL
