Trying to join to xp_fileexist

Question

Post reply

Trying to join to xp_fileexist

mtassin

SSC-Insane

Points: 23099
More actions
September 29, 2008 at 2:25 pm

#191552

I have a table with 800,000 records that we need to test if the generated name that the records should point to actually exists.
I was hoping to use xp_fileexist to test each record to see if it exists and return the values alongside the file... declare the whole mess as a CTE and then pull some statistics from it.
I'm running into a stumbling block trying to join to a extended stored proc... so I thought instead I would execute the extended stored proc in a user defined function.
That isn't working either
CREATE FUNCTION dbo.file_exists (@filenam as varchar(max))
RETURNS @tabexist TABLE (fileexist int,fileisdir int,parentexist int)
AS
BEGIN
INSERT INTO @tabexist EXEC master..xp_fileexist @filenam
RETURN
END
Gives back
Msg 443, Level 16, State 14, Procedure file_exists, Line 5
Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC' within a function.
Any other ideas?

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 1

Yep... but I need to know... do you expect that all of the file names are going to be in the same directory, or not?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mtassin SSC-Insane Points: 23099 More actions · Answer 2

Jeff Moden (9/29/2008)
Yep... but I need to know... do you expect that all of the file names are going to be in the same directory, or not?

No the files will all be in the same root path... but they're going to be by \\root path\ (company code)\year\month\day\filename.ext

That's why I was hoping to get by with using a fuction so I could cross apply it...

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 3

Ummm... ok... can you use xp_CmdShell or sp_OA* routines?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mtassin SSC-Insane Points: 23099 More actions · Answer 4

Jeff Moden (9/30/2008)
Ummm... ok... can you use xp_CmdShell or sp_OA* routines?

I can if I must... I'd rather avoid them...

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Heh. Now this is one of the few things that SQLCLR is good for. It is fairly easy to write a SQLCLR Function with EXTERNAL_ACCESS rights to do this for you.

On the other hand, it might be faster to just scan the whole file structure's directory tree into a table and then compare/test the file names by referring to that table.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

I just noticed:

CREATE FUNCTION dbo.file_exists (@filenam as varchar(max))

RETURNS @tabexist TABLE (fileexist int,fileisdir int,parentexist int)

AS

BEGIN

Declare @Exists int

Set @Exists = 0

--INSERT INTO @tabexist EXEC master..xp_fileexist @filenam

EXEC master..xp_fileexist @filenam, @Exists OUTPUT

Insert into @tabexist(fileexist) Values(@Exists)

RETURN

END

I think that this will work just fine.

Of course, with only one value returned, you may want change it into a scalar function.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 7

rbarryyoung (9/30/2008)
Heh. Now this is one of the few things that SQLCLR is good for. It is fairly easy to write a SQLCLR Function with EXTERNAL_ACCESS rights to do this for you.

Nah... 😛 No SQLCLR... you just proved it. :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 8

Mark... you showed the naming convention for the directories... what's the naming convention for the file names and extensions themselves?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mtassin SSC-Insane Points: 23099 More actions · Answer 9

Jeff Moden (9/30/2008)
Mark... you showed the naming convention for the directories... what's the naming convention for the file names and extensions themselves?

The file names are (in/out) + recipientid + .zfx

The in/out depends on the table it's coming from.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 10

Crud... I was hoping they were unique so we could use xp_Dirtree to solve your problem very easily. xp_FileExists is not available in SQL Server 2k8 like it is in 2k5... but, xp_Dirtree is.

As a sidebar, if you look it up on the internet, most folks don't know about the wonderful "3rd parameter". Rather than explain, try it with an without the 3rd parameter...

EXEC Master.dbo.xp_Dirtree 'C:\',3 --Produces a list of directories 3 levels deep.

EXEC Master.dbo.xp_Dirtree 'C:\',3,1 --Produces a list of directories 3 levels deep AND a list of file names

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mtassin SSC-Insane Points: 23099 More actions · Answer 11

Neither of them seem to like UNC paths either... :/

I had read that xp_fileexists was supposed to work nicely with UNC's... since it hates mapped drives... but alas...

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 12

Dunno about that, Mark... I've never had a problem with xp_DirTree and UNC's. If SQL Server can see it, xp_Dirtree can read it in my humble experiences.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mtassin SSC-Insane Points: 23099 More actions · Answer 13

If I remember right the account running the SQL Agent is used to access the UNC...

Since we lock our service accounts down tighter than drums... likely it doesn't have the rights to that share and it will take an act of god to get it permission.

Ah well 🙂 thanks everybody.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]