We have a requirement that we need to extract the data from the SQL server table and save that text file with current date and store that file in the current month folder and if the folder is not available in this case we need to create current month folder automatically using SQL script and store that file in this folder.
Here we are using the “AdventureWorksDW2017”
database.
Below is the SQL script to fetch the data from the table.
Below is the script to extract the data from the table and create the
text file and folder.
|
DECLARE @Folder_Path VARCHAR(80),
@Folder_Base_Name VARCHAR(100), @Folder_Name VARCHAR(500),
@Sql_File_Path VARCHAR(4000),
@Execution_Result_Path VARCHAR(4000), @Server_Name VARCHAR(20), @sql_String VARCHAR(4000),
@Database_Name VARCHAR(100),
@OLE_Folder_ID INT, @OLE_Source VARCHAR(255), @OLE_Des VARCHAR(255), @init INT, @OLE_File_Sytem_Object INT; SET @Folder_Path='J:\Product'; SET @Folder_Base_Name ='Product'; SET @Folder_Name = @Folder_Path + '\' +
@Folder_Base_Name + '_' + CONVERT(VARCHAR(6), Getdate(), 112); SET @Sql_File_Path = 'J:\code\DimProduct.sql'; SET @Execution_Result_Path= @Folder_Name + '\' + 'DimProduct_resultset_'
+ CONVERT(VARCHAR(8), Getdate(), 112) + '.txt'; SET @Server_Name ='BAGESH\BAGESHDB'; SET @Database_Name ='AdventureWorksDW2017'; -- it will fail if OLE automation not enabled EXEC @init=Sp_oacreate 'Scripting.FileSystemObject', @OLE_File_Sytem_Object out IF @init <> 0 BEGIN EXEC Sp_oageterrorinfo @OLE_File_Sytem_Object RETURN END -- check if folder exists EXEC @init=Sp_oamethod @OLE_File_Sytem_Object, 'FolderExists', @OLE_Folder_ID out, @Folder_Name --if @OLE_Folder_ID =1 it means folder is exist if @OLE_Folder_ID=0 folder is not exist -- if folder doesnt exist, create it IF @OLE_Folder_ID = 0 BEGIN EXEC @init=Sp_oamethod @OLE_File_Sytem_Object, 'CreateFolder', @OLE_Folder_ID out, @Folder_Name END -- in case of error, raise it IF @init <> 0 BEGIN EXEC Sp_oageterrorinfo @OLE_File_Sytem_Object, @OLE_Source out, @OLE_Des out SELECT @OLE_Des = 'Could not create folder: ' + @OLE_Des RAISERROR (@OLE_Des,16,1) END EXECUTE @init = Sp_oadestroy @OLE_File_Sytem_Object SET @sql_String = 'SQLCMD -E -S ' + @Server_Name + ' -d ' + @Database_Name + ' -i ' + @Sql_File_Path
+ ' -o ' + @Execution_Result_Path EXEC master..Xp_cmdshell @sql_String |
In this folder there are no any folders and files.
Learn here how to create SQL server Jobs in SQL Server Agent:
https://bageshkumarbagi-msbi.blogspot.com/2018/12/creating-new-job-in-sql-server-agent.html
Let’s run this job first time.
Job executed successfully.
Let’s see the report.
Folder created
successfully.
In this folder file is created.
File and
file data.
Next day run
Next month run.
Now folder
created with current year with month.
File with
file data.
We got
the expected result.
Happy Learning!
J
