--Temporarily enable xp_cmdshell
sp_configure 'show advanced options',1;
RECONFIGURE
GO

sp_configure 'xp_cmdshell',1;
RECONFIGURE
GO

--Declare variables 
DECLARE @fileText varchar(max);
DECLARE @fileName VARCHAR(500) ;
DECLARE @command varchar(1000);

--
--Declare parameters
--
--Here you need to specify the output directory for the files to be created
DECLARE @outputDir VARCHAR(250)='c:\temp\txt_files\';

-- to check the file or directory
-- EXEC master.dbo.xp_Fileexist 'c:\temp\txt_files\0_EO-00000F.txt'

--Here you need to specify the SQL instance from which the data will be exported
DECLARE @instanceName VARCHAR(50)='INHYNRVADIYAL1'

--Here you specify whether you are connecting to the SQL instance with a 
--trusted connection (Windows Authentication) or not
DECLARE @isTrustedConnection BIT=1

--If isTrustedConnection is set to 0 then you will need to 
--add username and password for connecting to the SQL Server instance
DECLARE @userName VARCHAR(50)='sa'
DECLARE @password VARCHAR(50)='Infor0n!y'

--get the current db name its is needed in bcp query
DECLARE @CUR_DB_NAME VARCHAR(128) 

SET @CUR_DB_NAME = DB_NAME()

--Create temporary table if it does not exist
IF OBJECT_ID('dbo.tmpSource') IS NULL
CREATE TABLE dbo.tmpSource(
 [FileText] [TEXT] NULL
);

--Declare cursor and populate with data
DECLARE db_cursor CURSOR FOR  

SELECT	TEXT_COMMENT as [FILE_TEXT], 
		CONCAT(ZZ_ID,'_',EO_ID, '.txt') as [FILE_NAME] 
FROM EO_EXT_CMNT

--SELECT CONVERT(varbinary(max),THUMBNAIL) as TUMB, CONCAT(DSET_KEY,'_',DSET_REVISION,'.', THUMBNAIL_TYPE ) as [FILE_NAME] FROM FILE_IMAGES

--select FILE_TEXT, CONCAT(DSET_KEY,'_',DSET_REVISION,'.', THUMBNAIL_TYPE ) as [FILE_NAME]
--from FILE_IMAGES
--cross apply (select THUMBNAIL '*' for xml path('')) T (FILE_TEXT)


--Open cursor
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @fileText, @fileName

WHILE @@FETCH_STATUS = 0   
BEGIN   
    --Clean temporary table
    DELETE FROM tmpSource
    
    --Insert record into temporary table
    INSERT INTO dbo.tmpSource (FileText) VALUES(@fileText);
      
    --Dynamically construct the BCP command
    --If the user connects to the SQL instance using a trusted connection
    IF (@isTrustedConnection=1)
       SET @command = 'bcp "SELECT * FROM "'+@CUR_DB_NAME+'".dbo.tmpSource " queryout "'+ @outputDir + @fileName + '" -S ' + @instanceName +' -T -c'
    --If the user connects to the SQL instance using username/password
    ELSE
       SET @command = 'bcp "SELECT * FROM "'+@CUR_DB_NAME+'".dbo.tmpSource " queryout "'+ @outputDir + @fileName + '" -S ' + @instanceName +' -c -U ' + @userName + ' -P '+ @password

    --Execute the BCP command
    EXEC xp_cmdshell @command , no_output

    --Fetch next records
    FETCH NEXT FROM db_cursor INTO @fileText, @fileName
END
   
--Close and deallocate cursor
CLOSE db_cursor   
DEALLOCATE db_cursor

IF OBJECT_ID('dbo.tmpSource') IS NOT NULL
DROP TABLE dbo.tmpSource
GO

--Disable xp_cmdshell
sp_configure 'xp_cmdshell',0
RECONFIGURE
GO

sp_configure 'show advanced options',0
RECONFIGURE
GO