Export assembly from database into dll file

/*

    Summary: 

    Use Ole Automation Procedures to export assemble to local dll file. Configure file system      permissions for Database Engine Access when need (Missed here). Then send the file to      another work computer from database server by xp_cmdshell.

*/

-- Turn on Ole Automation Procedures & xp_cmdshell. 

sp_configure 'show advanced options', 1;  

GO  

RECONFIGURE;  

GO  

sp_configure 'Ole Automation Procedures', 1; 

GO 

EXEC SP_CONFIGURE N'xp_cmdshell', 1

GO  

RECONFIGURE;  

GO 

DECLARE @IMG_PATH VARBINARY(MAX)

DECLARE @ObjectToken INT

DECLARE @intObject INT

DECLARE @intResult INT

DECLARE @strSource VARCHAR(255)

DECLARE @strDescription VARCHAR(255)

DECLARE @Directory VARCHAR(255)

DECLARE @DirDirectory VARCHAR(255)

DECLARE @MkDirDirectory VARCHAR(255)

DECLARE @File VARCHAR(255)

DECLARE @FilePath VARCHAR(255)

DECLARE @Xcopy VARCHAR(255)

SET @Directory = 'C:\Temp\Test\'

SET @DirDirectory = 'dir ' + @Directory

SET @File = 'MaterialExt.dll'

EXEC @intResult = master..xp_cmdshell @DirDirectory, no_output

IF @intResult <> 0

BEGIN

   SET @MkDirDirectory = 'mkdir ' + @Directory

   EXEC @intResult = master..xp_cmdshell @MkDirDirectory, no_output

   IF @intResult <> 0

   BEGIN

      PRINT 'Failed to create directory'

  GOTO Exit_Point

   END

END

--SELECT @IMG_PATH = content FROM sys.assembly_files WHERE assembly_id = 65536

SELECT @IMG_PATH = assemblyImage FROM [dbo].[ObjCustomAssembly] WHERE assemblyname = 'MaterialExt'

EXEC  @intResult = sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT

IF @intResult <> 0

BEGIN

   PRINT 'Failed to create object'

   GOTO Handle_Error

END

EXEC sp_OASetProperty @ObjectToken, 'Type', 1

EXEC @intResult = sp_OAMethod @ObjectToken, 'Open'

IF @intResult <> 0

BEGIN

   PRINT 'Failed to open stream'

   GOTO Handle_Error

END

EXEC @intResult = sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH

IF @intResult <> 0

BEGIN

   PRINT 'Failed to write to stream'

   GOTO Handle_Error

END

SET @FilePath = @Directory + @File

EXEC @intResult = sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FilePath, 2

IF @intResult <> 0

BEGIN

   PRINT 'Failed to write to file'

   GOTO Handle_Error

END

EXEC sp_OAMethod @ObjectToken, 'Close'

EXEC sp_OADestroy @ObjectToken

Handle_Error:

BEGIN

   EXEC @intResult = sp_OAGetErrorInfo @intObject, @strSource OUT, @strDescription OUT

   PRINT @strSource + ' - ' + @StrDescription

END

exec @intResult = xp_cmdshell 'net use \\cnshdbqin01\Test "Summer_01" /USER:infor\bqin', no_output

IF @intResult <> 0

BEGIN

   PRINT 'Failed to net use'

   GOTO Exit_Point

END

SET @Xcopy = 'xcopy /Y ' + @FilePath + ' \\cnshdbqin01\Test\'

EXEC @intResult = master..xp_cmdshell @Xcopy, no_output

IF @intResult <> 0

BEGIN

   PRINT 'Failed to xcopy file'

   GOTO Exit_Point

END

Exit_Point:

-- Turn off Ole Automation Procedures & xp_cmdshell. 

EXEC SP_CONFIGURE 'Ole Automation Procedures', 0

GO

-- Disable xp_cmdshell option. 

EXEC SP_CONFIGURE 'xp_cmdshell', 0

GO

RECONFIGURE

GO

-- Disable advanced options to be changed.

EXEC SP_CONFIGURE 'show advanced options', 0

GO

RECONFIGURE

GO