Getting file name - 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

SQL Server Development (2000)

Getting file name

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-06-30 : 07:53:53

Wilbur writes "How do i get the file name in SQL or rename a file using SQL and filesystemobject

I m only able to copy a file as of now but i also want to rename the file before copyin it.

CREATE PROCEDURE sp_CopyFile(@FilePathAndName varchar(8000),@DestinationPath varchar(8000)) AS
DECLARE @FS int, @OLEResult int, @FileID int,@FileName varchar(500),@src varchar(5000),@desc varchar(5000)

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'
execute @OLEResult = sp_OAMethod @FS, 'CopyFile', null, @FilePathAndName,@DestinationPath

if @OLEResult <> 0
begin
exec sp_OAGetErrorInfo @FS, @src out, @desc out
print 'Error Source: ' + @src + ' Error Description: ' + @desc
end
Print @FilePathAndName + ' your filename'

execute @OLEResult = sp_OAMethod @FS, 'name',@FileName out
Print @FileName + ' is the file name'

if @OLEResult <> 0
begin
exec sp_OAGetErrorInfo @FS, @src out, @desc out
print 'Error Source: ' + @src + ' Error Description for Name: ' + @desc
end

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
GO
SQL Server 2000 SP4
Windows 2003"

Kristen
Test

22859 Posts

Posted - 2005-06-30 : 13:36:44

I would use xp_cmdshell, dunno if thats an option for you?

Kristen