SQLTeam.com | Weblogs | Forums

Insert file to sql server without front end using Stored Procedure


#1

I am trying to insert file through sql. I use following query.

INSERT INTO [dbo].[Attachments]

(FileName, FileBinary)
SELECT 'non-date-in-sql-server-column', 
	BulkColumn FROM OPENROWSET(
		Bulk 'C:\Users\Pictures\Picture.JPG', SINGLE_BLOB) AS BLOB

Its working fine.

I want to write the procedure that take dynamic path. Its giving me error that I cannot take Filebinary in addin. Which is datatype varbinary. What is the best way to do ?

I have done following but its not taking properly binary value.

DECLARE @SQLString NVARCHAR(MAX)

SET @SQLString = 'SELECT ' + '''' +@Filename +'''' + ' AS Name,' + 'FileBinary

FROM OPENROWSET(BULK N''' + @ImagePath + ''',SINGLE_BLOB) AS FileBinary(FileBinary);'

Insert Into Attachments

(

	ApplicantID,
	FileName,
	FileBinary

)

Values

(

	@ApplicantID ,
	@FileName,
         Convert(varbinary(max),@SQLString)

)