How to write / Save files in Remote location using sp_OAMethod in SQL SERVER

• manavairajan

  Right there with Babe

  Points: 781

  More actions

  September 11, 2017 at 8:06 am

  #339064

  Hi Every one. 

  I want to convert blob data into normal file using SQL, So, I refered below script, its working fine, but, the converted file not able to write/Save through sql script in remote location(Another server location). I wants to save files in remote location using sp_OAMethod . Please kindly help me how to achieve this 

  Declare @folderPath varchar(500)
  Declare @cmdpath varchar(500)
  SET @cmdpath = 'MD '+ @folderPath
  SET @folderPath = '\\<SERVERName>\d$\Storage\Activity_2017\123.png'

  insert into @tempresult (result) exec master.dbo.xp_cmdshell @cmdpath
  select @message = ISNULL(@message + ' - ','') + result from @tempresult where result is not null
  select @message
  EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created
  EXEC sp_OASetProperty @init, 'Type', 1; 
  EXEC sp_OAMethod @init, 'Open'; -- Calling a method
  EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method
  EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method
  EXEC sp_OAMethod @init, 'Close'; -- Calling a method
  EXEC sp_OADestroy @init; -- Closed the resources print 'Document Generated at - '+ @fPath

• Phil Parkin

  SSC Guru

  Points: 246961

  More actions

  September 11, 2017 at 10:26 am

  #1958971

  manavairajan - Monday, September 11, 2017 8:06 AM

  Hi Every one. 

  I want to convert blob data into normal file using SQL, So, I refered below script, its working fine, but, the converted file not able to write/Save through sql script in remote location(Another server location). I wants to save files in remote location using sp_OAMethod . Please kindly help me how to achieve this 

  Declare @folderPath varchar(500)
  Declare @cmdpath varchar(500)
  SET @cmdpath = 'MD '+ @folderPath
  SET @folderPath = '\\<SERVERName>\d$\Storage\Activity_2017\123.png'

  insert into @tempresult (result) exec master.dbo.xp_cmdshell @cmdpath
  select @message = ISNULL(@message + ' - ','') + result from @tempresult where result is not null
  select @message
  EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created
  EXEC sp_OASetProperty @init, 'Type', 1; 
  EXEC sp_OAMethod @init, 'Open'; -- Calling a method
  EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method
  EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method
  EXEC sp_OAMethod @init, 'Close'; -- Calling a method
  EXEC sp_OADestroy @init; -- Closed the resources print 'Document Generated at - '+ @fPath

  Please post the error message you receive.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin