June 1, 2015 at 9:06 am
HI Experts
I'm, using my script to many location to create folder to save output files and if the folder is removed/not present it can create it without any noise. But the problem is, while I use the same sort of script to check if a folder is present in the sharedpath it will not create it to copy all bkp files from local to remote path works good, but if you delete the folder or rename the exisitng folder and if the below script tries to create the folder it created as "fILE", very interesting. Per IT team they have given SQL Server account the full rights to create/delete/alter folder/files.
Do I need to use seperate script or way to create / alter folders in the sharepath?
Please suggest.
SET NOCOUNT ON
Declare @len INT, @i INT, @Left varchar(max), @right varchar(max), @SERVERNAME sysname,@finalServer sysname
declare @rc int, @dir nvarchar(4000)
DECLARE @cmd1 sysname, @cmd2 sysname, @cmd sysname;
Declare @extPAth varchar(max)
declare @chkdirectory as nvarchar(4000),@folder_exists as int;
set @chkdirectory = '\\shared_path\Location\'; -- Define the Remote Location.
Declare @deletedate nvarchar(19) ,@strDir varchar(250)
declare @cmd11 nvarchar(2000)
declare @mainBackupDir varchar(2000),
@result1 nvarchar(max);
--set @daysold = 3
SET @SERVERNAME = (SELECT @@servername);
--select @backup_path AS [TLOG Backup PATH]
--PRINT @SERVERNAME
IF @SERVERNAME like '%\%'
BEGIN
SET @Left= (select Left(@SERVERNAME, charindex('\', @SERVERNAME) - 1))
--SELECT @Left AS [DEFAULT SERVER NAME]
SET @len = (SELECT LEN(@SERVERNAME))
--SELECT @LEN
SET @right = (SELECT SUBSTRING(@SERVERNAME,Charindex ('\', @SERVERNAME)+1,@len))
--SELECT @right AS [SQL INSTANCE NAME]
SET @finalServer = @Left + '_'+ @right;
SELECT @finalServer
END
ELSE
BEGIN
SET @left = @SERVERNAME
--SELECT @Left AS [DEFAULT SERVER NAME]
-- SELECT @right = 'default'
--SELECT @right AS [SQL INSTANCE NAME]
SET @finalServer = @Left
SELECT @finalServer
END
set @chkdirectory = @chkdirectory + @finalServer
declare @file_results table
(file_exists int,
file_is_a_directory int,
parent_directory_exists int
)
insert into @file_results
(file_exists, file_is_a_directory, parent_directory_exists)
exec master.dbo.xp_fileexist @chkdirectory
select @folder_exists = file_is_a_directory
from @file_results
--script to create directory
if @folder_exists = 0
begin
print 'Directory does not exist, creating new one:->'
EXECUTE master.dbo.xp_create_subdir @chkdirectory
print @chkdirectory + ' created in sharepath: ' + quotename(@chkdirectory)
end
else
print 'Directory already exists'
SET NOCOUNT OFF
Thanks.
June 1, 2015 at 9:59 am
I usually make folders using VBScript, but you can use a Script Task within SSIS, and use Visual Basic. I usually do it in a separate task from any SQL operation, so as to help maintain separate error handling.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 3, 2015 at 8:44 am
I created a simple scalar function that checks if the folder exists, if it doesn't it creates it - it depends on Jeff Moden's awesome function DelimitedSplit8K, but you can find that here: http://www.sqlservercentral.com/articles/Tally+Table/72993/
USE [YourDB]
CREATE FUNCTION [dbo].[fx_FolderExists] (
@Folder nvarchar(500),
@Action tinyint
)
RETURNS int AS
/*
----------------------------------------------------------------------------------------------------------------
Purpose: Checks to see if a directory exists in any location
Department: DBA
----------------------------------------------------------------------------------------------------------------
NOTES: 1. Returns 0 if the folder being checked does NOT exist
2. @Action = 0, Do nothing just test if folder exists, if @Action = 1, create the folder
* OLE AUTOMATION MUST BE ENABLED ON THE DATABASE SERVER FOR THIS TO WORK *
----------------------------------------------------------------------------------------------------------------
Created On:6/04/2014
Create By:MyDoggieJessie
----------------------------------------------------------------------------------------------------------------
Modified On:
Modified By:
Changes:
1.
----------------------------------------------------------------------------------------------------------------
SELECT dbo.fx_FolderExists('\\NetworkPath\Folder', 1)
*/
BEGIN
/* ############## START MAIN PROCEDURE HERE ############## */
DECLARE @Exists int, @ObjFile int, @ObjFileSystem INT, @Idx tinyint
DECLARE @Folders TABLE (idx INT IDENTITY(1,1) , FLevel varchar(100))
EXEC dbo.sp_OACreate 'Scripting.FileSystemObject', @ObjFileSystem OUT
EXEC dbo.sp_OAMethod @ObjFileSystem, 'FolderExists', @Exists OUT, @Folder
IF @Action = 1
BEGIN
IF @Exists = 0
BEGIN
INSERT INTO @Folders
SELECT Item FROM YourDB.dbo.fx_DelimitedSplit8K(@Folder, '\')
WHERE Item <> ''
SET @Idx = (SELECT TOP 1 idx FROM @Folders)
SET @Folder = '\\'
WHILE (@idx <= (SELECT MAX(idx) FROM @Folders))
BEGIN
SELECT @Folder = @Folder + (SELECT FLevel FROM @Folders WHERE idx = @Idx) + '\'
EXEC dbo.sp_OAMethod @ObjFileSystem, 'CreateFolder', @Folder OUT, @Folder
SET @Idx = @idx + 1
IF @@ERROR = 0 AND @Exists <> 0
BEGIN
SET @Exists = 1
END
ELSE
BEGIN
SET @Exists = -1
END
END
END
END
EXEC dbo.sp_OADestroy @ObjFileSystem
RETURN ISNULL(@Exists, -1)
END
/* ########################################## END MAIN PROCEDURE HERE ########################################### */
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 3, 2015 at 12:32 pm
Hello Jessie
I found that you mentioned about one function: "fx_DelimitedSplit8K". Can you please reply me with the function code?
Thanks.
June 3, 2015 at 12:51 pm
It was in the link I added in my OP CREATE FUNCTION [dbo].[fx_DelimitedSplit8K]
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 U-NION ALL SELECT 1 U-NION ALL SELECT 1 U-NION ALL
SELECT 1 U-NION ALL SELECT 1 U-NION ALL SELECT 1 U-NION ALL
SELECT 1 U-NION ALL SELECT 1 U-NION ALL SELECT 1 U-NION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
You will need to REMOVE the hyphen in the "U-NION" all statements - had to add that in order to get our silly proxy to allow me to post the code;-)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 3, 2015 at 1:04 pm
If you aren't familiar with Jeff's DelimitedSplit8K function, the article is at http://www.sqlservercentral.com/articles/Tally+Table/72993/. It's well worth taking the time to read it. Be warned, though, that it might change your expectations of performance.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply