SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 use xp_fileexist with a variable?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lmayer4
Starting Member

USA
29 Posts

Posted - 10/01/2013 :  12:46:14  Show Profile  Reply with Quote
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

USA
35932 Posts

Posted - 10/01/2013 :  13:05:34  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

3322 Posts

Posted - 10/01/2013 :  13:11:21  Show Profile  Reply with Quote
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.

Edited by - James K on 10/01/2013 13:12:32
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35932 Posts

Posted - 10/01/2013 :  13:14:24  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

3322 Posts

Posted - 10/01/2013 :  13:20:25  Show Profile  Reply with Quote
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

USA
29 Posts

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

ScottPletcher
Constraint Violating Yak Guru

USA
268 Posts

Posted - 10/01/2013 :  16:45:34  Show Profile  Reply with Quote
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

USA
35932 Posts

Posted - 10/01/2013 :  16:52:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000