Manish Patat

Manish Patat

  • 2k
  • 66
  • 2.5k

Store procedure through folder path file get update filestream field

Aug 5 2021 5:55 AM

I have Some image Store my system folder his image get and convert varbinary and store database  table filestream field update 

my storeprocedure look like This

ALTER PROCEDURE [dbo].[RFS21_GetPathTDCImgwax]
        Declare  @Loop int, @Cnt int,@Tdcno nvarchar(25)
        Declare @FPath nvarchar(max)
        Declare @Command nvarchar(1000)
        Select @FPath=WaxTDC from GlobalPath
set @Loop=0
    select @cnt=Count(*) from TDCWax
    while(@Loop < @cnt)
        set @Loop=@Loop+1
        Select @Tdcno=TDCNO  From (Select TDCNo,ROW_NUMBER() over (Order By TDCNo)AS RowNum from TDCWax)As SubOA1 where Rownum=@Loop
        DECLARE @File_Exists INT
        Declare @fpathlong as nvarchar(Max)
        Set @fpathlong=@FPath + '\' + @Tdcno + '.Jpeg'
        EXEC master.dbo.xp_fileexist @fpathlong ,@File_Exists OUTPUT
        If @File_Exists=1
          DECLARE @file_stream VARBINARY(MAX)
          DECLARE @Query nvarchar(mAX)
          set @Query = 'SELECT @file_stream1 = CAST(bulkcolumn AS varbinary(MAX)) from OPENROWSET(BULK ''' + @fpathlong + ''', SINGLE_BLOB) ROW_SET'
          EXEC sp_executesql @Query, '@file_stream1 VARBINARY(MAX) OUTPUT',@file_stream1 =@file_stream OUTPUT
          select @file_stream
          Update TDCWax Set TDCImg=@file_stream Where TDCNo=@Tdcno


But error are occured

Msg 22027, Level 15, State 1, Line 0
Usage:  EXECUTE xp_fileexist <filename> [, <file_exists INT> OUTPUT]