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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lmayer4
Starting Member

USA
33 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
38200 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

3873 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
38200 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

3873 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
33 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
Aged Yak Warrior

USA
550 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
38200 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  
 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.05 seconds. Powered By: Snitz Forums 2000