Sleep if file not available. Load if it is.

One of my old friends, asked me couple of days back how to design a process that will check for file existence in a folder. On finding the file the process should load the file else should sleep for 15 mins and then check for file existence. This process can be handled using advanced T-SQL batch that contains "WAITFOR" and "BCP" like below:

-- Code Starts here
DECLARE @File VARCHAR(500), @Sleeps INT, @FileAvailable INT, @Iteration INT

SET @FileAvailable = 0
SET @Iteration = 0
SET @Sleeps = 4
SET @File = 'C:\Data_DEPT.dat'

-- Check for File Existence
EXEC MASTER.dbo.xp_FileExist @File, @FileAvailable OUTPUT

-- If the file is not available, then the process will wait (sleep) for specified time frame.
-- Here I am placing just 15 seconds time frame, but can you can specify anything.
WHILE @FileAvailable = 0 AND @Iteration < @Sleeps
  BEGIN
    SET @Iteration = @Iteration + 1
    WAITFOR DELAY '00:00:15'
    EXEC MASTER.dbo.xp_FileExist @File, @FileAvailable OUTPUT
  END 

-- If the file is available, then load the file into DEPT2 table.
IF @FileAvailable = 1
BEGIN
DECLARE @BCP VARCHAR(500)
SET @BCP = 'bcp AdventureWorks2012.dbo.DEPT2 IN ' + @File + ' -f C:\Metadata_DEPT.fmt -S HPS\SQL -T'
EXEC MASTER..xp_cmdshell @BCP
END
-- Code Ends here

Execution of above code will result this:

Output of the data in table after file load:

Conclusion: I used two master stored procedures here "xp_FileExist" and "xp_cmdshell". The metadata file (format file) of the FlatFile is assumed to be predefined here. So similar structured files carrying different data can be processed. Hope this helps! Happy TSQLing ;)

Extract (Export) word document from SQL Server Table to a Windows Folder

In the last post, we have seen how to upload (insert) into a SQL Server Table. Now let's see how to extract it from the SQL Server table back to a folder.

Query:
-- Extracting the Resume from SQL Server Table to a folder location.
DECLARE @SQL VARCHAR(1000)
SET @SQL = 'BCP "SELECT ResumeDoc FROM AdventureWorks2012.dbo.Consultants WHERE ConsultantName = ''Steve''" QueryOut "D:\Steve_Resume2.docx" -T -n -S HPS\SQL'
EXEC MASTER.dbo.xp_CmdShell @SQL

In above query, HPS\SQL is the SQL Server Instance.

Verifying in Folder Location:
SQL Server exported the word document into the specified location.

Note: By default, dbo.xp_CmdShell is disabled. In order to enable the stored procedure functionality, run the below code.

--Code Starts here:---->
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_CmdShell', 1
GO
RECONFIGURE
GO

Conclusion: For every import of word document, there exists an export, Just kidding ;) Hope this helps! Happy TSQLing :)