question

ksmith75 avatar image
ksmith75 asked

Server agent job need to find files starting with specific text?

Hi folks, this is my first time asking a question, I have gotten a lot of help from this site before. my question is: I am currently using a server agent to run a stored procedure using files from a specific folder. The line I am using is as follows. exec [dbo].[Sintel_Labor_Tracking]'\\sintelserv\compony\test\kevin\ DC-Collect.txt' This works perfecctly for files named DC-Collect however it is feasible that i will have multiple DC-Collect files in this folder... DC-Collect - copy, DC-collect - copy (1), etc... I have tried many different wildcards to get it to recognize the copies, but nothing seems to work. Can anyone help me with the syntax to get this line to recognize any dc-collect files? I apologize if I asked this question wrong, I am extremely new to SQL server agents.
serverjobsagent
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

We would need to see the T-SQL logic in the stored procedure you are calling. How are you accessing the file system in that procedure?
0 Likes 0 ·

1 Answer

· Write an Answer
ksmith75 avatar image
ksmith75 answered
Ill be honest with you, I did not write this, only modified it from an existing procedure we are using. I am very much a novice. I am not 100% sure what all is happening in this code. Hopefully this is what you are looking for. This is inside of the rest of the procedure but this is the code that deals with the files I believe. -- Check that the target file exists IF dbo.[SRI_File_Exists](@CSVFileName) = 0 RETURN PRINT 'Step 1 ' + @CSVFileName SET @Filepath = LEFT(@CSVFileName, LEN(@CSVFileName) - CHARINDEX('\',REVERSE(@CSVFileName),1) + 0) SET @FilenameOld = REVERSE(LEFT(REVERSE(@CSVFileName), CHARINDEX('\',REVERSE(@CSVFileName), 1) - 1)) SET @FilenameNew = REPLACE(@FilenameOld, '.', '~.') WHILE(dbo.[SRI_File_Exists](@Filepath + '\' + @FilenameNew) = 0) BEGIN SET @Filepath = LEFT(@CSVFileName, LEN(@CSVFileName) - CHARINDEX('\',REVERSE(@CSVFileName),1) + 0) SET @FilenameOld = REVERSE(LEFT(REVERSE(@CSVFileName), CHARINDEX('\',REVERSE(@CSVFileName), 1) - 1)) SET @FilenameNew = REPLACE(@FilenameOld, '.', '~.') EXEC [dbo].[sp_ReplaceFileOrDirNames] @Filepath, @FilenameOld, @FilenameNew END SET @CSVFileName = @Filepath + '\' + @FilenameNew PRINT 'Step 2 ' + @CSVFileName
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.