{"id":5824,"date":"2016-11-24T03:39:00","date_gmt":"2016-11-24T03:39:00","guid":{"rendered":"http:\/\/www.jitendrazaa.com\/blog\/?p=5824"},"modified":"2016-11-24T03:40:28","modified_gmt":"2016-11-24T03:40:28","slug":"sql-server-create-folders-using-t-sql-ole-automation","status":"publish","type":"post","link":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/sql-server-create-folders-using-t-sql-ole-automation\/","title":{"rendered":"SQL Server \u2013 Create Folders using T-SQL & OLE automation"},"content":{"rendered":"

SQL Server has some standard stored procedures that allow OLE automation<\/a>.<\/p>\n

First step, is to check whether\u00a0Ole Automation Procedures are enabled in SQL Server or not ? It can be enabled by simply executing below T-SQL commands.<\/p>\n

T-SQL to enable\u00a0Ole Automation Procedures in SQL Server<\/strong><\/p>\n

\r\n        sp_configure 'show advanced options', 1;\r\n\tGO\r\n\tRECONFIGURE;\r\n\tGO\r\n\tsp_configure 'Ole Automation Procedures', 1;\r\n\tGO\r\n\tRECONFIGURE;\r\n\tGO\r\n<\/pre>\n
Next step is to create a stored procedure, which will use an OLE Automation procedures and create a folder on system drive<\/p>\n
\r\n--\tDATE\t:\t23-Nov\r\n--\tAUTHOR\t:\tJitendra Zaa\r\n\r\nCREATE PROCEDURE [dbo].[CreateFolder] (@newfolder varchar(1000)) AS  \r\nBEGIN  \r\nDECLARE @OLEfolder   INT  \r\nDECLARE @OLEsource   VARCHAR(255)  \r\nDECLARE @OLEdescription  VARCHAR(255) \r\nDECLARE @init   INT  \r\nDECLARE @OLEfilesytemobject INT  \r\n \r\n-- it will fail if OLE automation not enabled\r\nEXEC @init=sp_OACreate 'Scripting.FileSystemObject', @OLEfilesytemobject OUT  \r\nIF @init <> 0  \r\nBEGIN  \r\n\tEXEC sp_OAGetErrorInfo @OLEfilesytemobject  \r\n\tRETURN  \r\nEND  \r\n-- check if folder exists  \r\nEXEC @init=sp_OAMethod @OLEfilesytemobject, 'FolderExists', @OLEfolder OUT, @newfolder  \r\n-- if folder doesnt exist, create it  \r\nIF @OLEfolder=0  \r\n\tBEGIN  \r\n\tEXEC @init=sp_OAMethod @OLEfilesytemobject, 'CreateFolder', @OLEfolder OUT, @newfolder  \r\nEND  \r\n-- in case of error, raise it   \r\nIF @init <> 0  \r\n\tBEGIN  \r\n\t\tEXEC sp_OAGetErrorInfo @OLEfilesytemobject, @OLEsource OUT, @OLEdescription OUT  \r\n\t\tSELECT @OLEdescription='Could not create folder: ' + @OLEdescription  \r\n\t\tRAISERROR (@OLEdescription, 16, 1)   \r\n\tEND  \r\nEXECUTE @init = sp_OADestroy @OLEfilesytemobject  \r\nEND  \r\n<\/pre>\n
Its time to finally see OLE Automation in action. We would try to create three nested folders in\u00a0tmp\u00a0<\/em>folder of\u00a0c\u00a0<\/em>drive. Assumption is\u00a0c:\\tmp\u00a0<\/em>folder already exists.<\/p>\n
Below code iterates through string which contains folder path @folderName<\/em>\u00a0and iteratively it will create folders using stored procedure CreateFolder<\/em>.<\/p>\n
\r\n--Prerequisite , make sure that @path exists on drive\r\nDECLARE @path varchar(80) = 'C:\\tmp',\r\n @folderName varchar(100)= 'folder1\\folder2\\folder3\\folder4',\r\n @fullPath varchar(500),\r\n @progressivePath varchar(500)\r\n\r\nSET @fullPath = @path +'\\'+@folderName\r\n \r\nDECLARE @pos INT\r\nDECLARE @len INT\r\nDECLARE @curentFolder varchar(8000)\r\n \r\nset @pos = 0\r\nset @len = 0\r\nSET @progressivePath = @path\r\n\r\n--Loop through path and create folder iteratively\r\nWHILE CHARINDEX('\\', @folderName, @pos+1) > 0\r\nBEGIN\r\n    set @len = CHARINDEX('\\', @folderName, @pos+1) - @pos \r\n    set @curentFolder = SUBSTRING(@folderName, @pos, @len) \r\n\tSET @progressivePath = @progressivePath + '\\'+@curentFolder\r\n    PRINT @progressivePath \r\n\tEXEC CreateFolder @progressivePath\r\n    set @pos = CHARINDEX('\\', @folderName, @pos+@len) +1\r\nEND\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
Tutorial to create folders in SQL Server using Transact-SQL and OLE Automation Stored Procedures <\/p>\n","protected":false},"author":1,"featured_media":5826,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"jz_research_post":"","_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[14],"tags":[292,375],"class_list":["post-5824","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sqlserver","tag-sql-server","tag-stored-procedure"],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-%E2%80%93-Create-Folders-using-T-SQL-OLE-automation.png?fit=1202%2C581&ssl=1","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":5882,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/sql-server-read-all-files-in-directory-and-store-in-table\/","url_meta":{"origin":5824,"position":0},"title":"SQL Server – Read all files in directory and store in Table","author":"Jitendra","date":"January 18, 2017","format":false,"excerpt":"T-SQL Script to read all files in directory and store back in table - SQL Server","rel":"","context":"In "SQL Server"","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":5873,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/export-documents-saved-as-blob-binary-from-sql-server\/","url_meta":{"origin":5824,"position":1},"title":"Export Documents saved as Blob \/ Binary from SQL Server","author":"Jitendra","date":"December 24, 2016","format":false,"excerpt":"T-SQL Scripts to Export Blob or Binary data stored in SQL Server","rel":"","context":"In "SQL Server"","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"SQL Server Export Blob as File","src":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/12\/SQL-Server-Export-Blob.gif?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/12\/SQL-Server-Export-Blob.gif?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/12\/SQL-Server-Export-Blob.gif?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/12\/SQL-Server-Export-Blob.gif?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":1376,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/use-of-openxml-in-sql-server\/","url_meta":{"origin":5824,"position":2},"title":"Use of OPENXML in SQL Server","author":"Jitendra","date":"December 4, 2010","format":false,"excerpt":"In SQL Server, OpenXML is very powerful method for the XML data manipulation. This article shows that how to use the OpenXML for XML string.","rel":"","context":"In "SQL Server"","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"OpenXML in SQL Server","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/12\/OpenXML-in-SQL-Server.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2861,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/pagination-and-switch-case-in-where-clause-sql-server\/","url_meta":{"origin":5824,"position":3},"title":"Pagination and Switch Case in Where clause – SQL Server","author":"Jitendra","date":"May 19, 2012","format":false,"excerpt":"Example of using pagination and switch case in where clause in SQL server","rel":"","context":"In "SQL"","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"SQL Server Employee Table","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2012\/05\/SQL-Server-Employee-Table.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":492,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/get-first-day-and-last-day-of-month-sql-server\/","url_meta":{"origin":5824,"position":4},"title":"Get First Day and Last Day of Month \u2013 SQL Server","author":"Jitendra","date":"July 2, 2010","format":false,"excerpt":"Get the first day and last day of the month in SQL Server using Scalar functions","rel":"","context":"In "SQL"","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1422,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/update-from-statement-in-sql-server\/","url_meta":{"origin":5824,"position":5},"title":"“Update From” Statement in SQL Server","author":"Jitendra","date":"December 17, 2010","format":false,"excerpt":"Example to demonstrate the \"Update From\" Statement in SQL Server. This example can be used in scenarios where developer wants to update the Table with the help of Inner Join.","rel":"","context":"In "SQL"","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"SQL Server Table 1","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/12\/SQL-Server-Table-1.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/5824","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/comments?post=5824"}],"version-history":[{"count":1,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/5824\/revisions"}],"predecessor-version":[{"id":5825,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/5824\/revisions\/5825"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/media\/5826"}],"wp:attachment":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/media?parent=5824"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/categories?post=5824"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/tags?post=5824"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}