File Date - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

File Date

Author

Topic

sardinka
Posting Yak Master

142 Posts

Posted - 2004-09-24 : 13:05:06

I am trying to get a lastFile date modified using the following sp.
However when I am trying to pass a parameters from cursor it is not working. Please help
CREATE PROCEDURE [dbo].[MyTEST]
AS
DECLARE @ImportDirectory varchar(50)
DECLARE @ImportFileName varchar(50)
DECLARE File_Info CURSOR FOR
SELECT ImportDirectory,ImportFileName
FROM FileLocation
OPEN File_Info
FETCH NEXT FROM File_Info
INTO @ImportDirectory,@ImportFileName
WHILE @@FETCH_STATUS = 0
BEGIN
insert into dirlist(filename)
exec master..xp_cmdshell 'dir ' + @ImportDirectory + @ImportFileName
VALUES (@ImportDirectory,@ImportFileName)
FETCH NEXT FROM File_Info
INTO @ImportDirectory,@ImportFileName
END
CLOSE File_Info
DEALLOCATE File_Info

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-24 : 13:14:11

What is the error? Your stored procedure doesn't have any input parameters but you say you are passing parameters. How does that work?

Tara

sardinka
Posting Yak Master

142 Posts

Posted - 2004-09-24 : 13:38:02

I am using cursor:
SELECT ImportDirectory,ImportFileName
FROM FileLocation
error:
Server: Msg 170, Level 15, State 1, Procedure MyTEST, Line 14
Line 14: Incorrect syntax near '+'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-24 : 13:43:25

Ah, I see your problem. You can't build the string at the same time calling xp_cmdshell. So you have to build up the string to execute in a variable, then just execute the variable. Like this:

DECLARE @command NVARCHAR(256)

SET @command = 'dir' + @ImportDirectory + @ImportFileName

exec master..xp_cmdshell @command

For why, see the comments in this blog entry that I made:

http://weblogs.sqlteam.com/tarad/archive/2004/03/29/1129.aspx

Tara

sardinka
Posting Yak Master

142 Posts

Posted - 2004-09-24 : 14:52:14

Below is my compete code. When I try to run it running forever...why?
DECLARE @command NVARCHAR(256)
DECLARE @ImportDirectory varchar(50)
DECLARE @ImportFileName varchar(50)
DECLARE File_Info CURSOR FOR
SELECT ImportDirectory,ImportFileName
FROM FileLocation
OPEN File_Info
FETCH NEXT FROM File_Info
INTO @ImportDirectory,@ImportFileName
WHILE @@FETCH_STATUS = 0
SET @command = 'dir' + @ImportDirectory + @ImportFileName

BEGIN
insert into dirlist(filename)
exec master..xp_cmdshell @command
FETCH NEXT FROM File_Info
INTO @ImportDirectory,@ImportFileName
END
CLOSE File_Info
DEALLOCATE File_Info

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-24 : 15:07:12

Because it's malformed....you need to move the SET before the BGIN, and then replicate the statement after the fetch in the loop...

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-24 : 15:12:04

Why is this even being done in T-SQL? This seems like a job for VBScript. Is this an Admin script?

Tara

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-24 : 17:35:10

Another way to get file info without parsing output from dir:

Create table ##filedetails(
alternatename char(20),
size char(20),
creation_date char(20),
creation_time char(20),
last_written_date char(20),
last_written_time char(20),
last_accessed_date char(20),
last_accessed_time char(20),
attributes char(20)
)

INSERT ##filedetails EXEC master.dbo.xp_getfiledetails "\\server\share\path\file.name"

select * from ##filedetails

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-24 : 18:54:08

Don't see xp_getfiledetails in BOL or in the QA object list

Surf On Dude!

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-24 : 19:00:21

I guess it's undocumented. sp_helpextendedproc will show it. It still works in SQL 2005.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources