Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Pulling Filename from Windows directory into table Expand / Collapse
Author
Message
Posted Monday, September 20, 2010 2:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 21, 2010 6:15 PM
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.
Post #989772
Posted Thursday, September 30, 2010 11:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,770, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #996419
Posted Friday, May 17, 2013 2:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:42 AM
Points: 4, Visits: 68
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?
Post #1454187
Posted Friday, May 17, 2013 4:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,770, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1454207
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse