Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 use xp_fileexist with a variable?

Author  Topic 

lmayer4
Starting Member

33 Posts

Posted - 2013-10-01 : 12:46:14
I am trying to see a file exists in a directory. I want to use xp_fileexist becasue I dont want to do anything with the file I just want to see if its there then run a job if it is. I dont know the file name and I have a little script that sets the name based on the date. The provisioner also adds a time stamp which I cant know. I have something like this:


DECLARE
@FileName varchar(255),
@name varchar(100);

set
@name = 'sov01_usg21130915_'

SELECT @FileName='\\fileserver-01\SpecialBillingProjects$\Usage Archive\2013\' + @name + '_*.zip'

EXEC Master.dbo.xp_fileexist @filename
GO


How can I use a wildcard with xp_fileexist? Or can I? I just want a simple script no VB.

Thanks

Laura

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-01 : 13:05:34
A wildcard is not possible with xp_fileexist. Is xp_cmdshell an option? Otherwise, you'll need to use CLR, Powershell, etc.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-01 : 13:11:21
Another option you could try. I know it is kludgy, so if it works, I wrote it. But if it doesn't work, I disown it.
CREATE TABLE #tmp(filenames VARCHAR(64));

INSERT INTO #tmp
EXEC xp_cmdshell 'dir /B c:\temp\* | findstr /I "test"';

-- see the files that match the pattern.
SELECT * FROM #tmp;

-- test whether there are any files that match the pattern
IF EXISTS (SELECT * FROM #tmp)
SELECT 'Yes, there are files that match the pattern c:\temp\*test*';

DROP TABLE #tmp;
By the way, you can apply the filter either in the dir itself, or in findstr. Both are not necessary.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-01 : 13:14:24
Our Minimum Security Baseline requires that xp_cmdshell be disabled. Does anyone else work in environments like this. I really love xp_cmdshell and sure do miss it!

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-01 : 13:20:25
Disabled in my environment as well but only on production and staging servers. On dev servers, I can do whatever my heart desires :)
Go to Top of Page

lmayer4
Starting Member

33 Posts

Posted - 2013-10-01 : 13:42:43
Thanks James much appreciated. I'll give it a shot.
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-10-01 : 16:45:34
You could use sp_OA* methods to test for a file as well, if those are allowed.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-01 : 16:52:03
Those are disabled in my environment too. It's so strict here!

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -