use xp_fileexist with a variable? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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/

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.

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/

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 :)

lmayer4
Starting Member

33 Posts

Posted - 2013-10-01 : 13:42:43

Thanks James much appreciated. I'll give it a shot.

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.

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources