SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Pulling Filename from Windows directory into table


Pulling Filename from Windows directory into table

Author
Message
brianparow
brianparow
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 24
Nevermind;
I figured out how to get rid of the trailing spaces within my batch file...

Hi Jeff,
That works great. The only problem I'm having with this is that the results seem to have a lot of trailing spaces in it.
I tried to edit the following portion of your query but they are still showing up:

SELECT rtrim(FileName) FROM #FileList WHERE Extension = '.txt' AND IsFile = 1
EXCEPT
SELECT rtrim(FileName) FROM #FileList WHERE Extension = '.pdf' AND IsFile = 1

I'm sending the results of this to a text file that is referenced in a batch file to trigger a conversion and the trailing spaces are killing it.

Also how difficult would it be to add the .txt extension back to the filename after checking. I tried to add Extension back in to the select but it says it needs to be in both. When adding it to both it obviously breaks the check from happening.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84437 Visits: 41061
brianparow (9/20/2010)
Nevermind;
I figured out how to get rid of the trailing spaces within my batch file...

Hi Jeff,
That works great. The only problem I'm having with this is that the results seem to have a lot of trailing spaces in it.
I tried to edit the following portion of your query but they are still showing up:

SELECT rtrim(FileName) FROM #FileList WHERE Extension = '.txt' AND IsFile = 1
EXCEPT
SELECT rtrim(FileName) FROM #FileList WHERE Extension = '.pdf' AND IsFile = 1

I'm sending the results of this to a text file that is referenced in a batch file to trigger a conversion and the trailing spaces are killing it.

Also how difficult would it be to add the .txt extension back to the filename after checking. I tried to add Extension back in to the select but it says it needs to be in both. When adding it to both it obviously breaks the check from happening.


Use the FullFileName in the SELECT list. It has filename and extension. Leave the WHERE clause as it is.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
erouse
erouse
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 140
Great procedure, thank you.

If I run it on the local drive it works great.

----------
USE [master]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[GetDirDetails]
@piFullPath = N'C:\'

SELECT 'Return Value' = @return_value

GO

----------
However if try to run it on a network drive no records are returned
----------

USE [master]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[GetDirDetails]
@piFullPath = N'\\ELK\Share\Dealers\'

SELECT 'Return Value' = @return_value

GO
-----------

I do have security rights to read on the network drive and can browse it from the same box SQL is running on. I'm at a loss. Am I missing something here?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84437 Visits: 41061
erouse (5/17/2013)
Great procedure, thank you.

If I run it on the local drive it works great.

----------
USE [master]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[GetDirDetails]
@piFullPath = N'C:\'

SELECT 'Return Value' = @return_value

GO

----------
However if try to run it on a network drive no records are returned
----------

USE [master]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[GetDirDetails]
@piFullPath = N'\\ELK\Share\Dealers\'

SELECT 'Return Value' = @return_value

GO
-----------

I do have security rights to read on the network drive and can browse it from the same box SQL is running on. I'm at a loss. Am I missing something here?


I just tried the code at work using a UNC and it worked fine. The only way that nothing will be returned is if the path doesn't exist, there are no files in the path, or the system doesn't have the privs to the path. To be sure, it's what the system can see... not what you can see.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search