| Author |
 Topic  |
|
agusmer
Starting Member
1 Posts |
 Posted - 07/30/2009 : 14:45:53
|
hey, i have used exactly as it says and it works, but the file i download from my ftp is a .exe file, and when i try to open it, it doesnt work, it has the properties of a DOS file.. any ideas?
thanks! |
 |
|
|
TallPaulInJax
Starting Member
5 Posts |
Posted - 09/03/2010 : 21:29:37
|
When I execute this, I get:
Msg 1038, Level 15, State 4, Procedure spGetFileViaFtpAndStoreLocally, Line 54
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
Msg 1038, Level 15, State 4, Procedure spGetFileViaFtpAndStoreLocally, Line 55
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
This refers to the two lines:
@proxy="",
@bypass="",
When I change those to:
@proxy="A",
@bypass="B",
it all works fine. Not being a SQL expert, anybody have any idea what the CORRECT thing to do is here?
|
|
|
|
TallPaulInJax
Starting Member
5 Posts |
Posted - 09/04/2010 : 00:22:17
|
One more thing. I have changed this into a stored procedure and added a parameter to say whether the file stored locally should be unzipped. Yet when I implement this, the utility I am using from the command line via xp_cmdshell is giving me a CRC error. Example:
IF @ZIPPED_FILES_SHOULD_BE_UNZIPPED <> 0
BEGIN
DECLARE @ARG VARCHAR(300)
SET @ARG = 'IZArce -e '+@FULL_LOCAL_PATH_AND_FILENAME_TO_SAVE_AS
EXEC @hr=master..xp_cmdshell @ARG
print 'hr='+@hr
IF @hr<>0
PRINT 'Unable to unzip file'+@FULL_LOCAL_PATH_AND_FILENAME_TO_SAVE_AS
END
Gives me:
ERROR File fails CRC check.
I am thinking this might be because control was sent back to SQL to quickly, that the FTP transfer has not yet completed. Possible? If so, is there a way of making SQL wait for the full FTP load? |
|
|
|
TallPaulInJax
Starting Member
5 Posts |
Posted - 09/04/2010 : 11:39:47
|
| I've read up on the CRC error and it may be because the transfer is in ASCII instead of binary? Is there a flag to force the FTP transfer in WinInet.dll to be in binary? |
|
|
|
TallPaulInJax
Starting Member
5 Posts |
Posted - 09/04/2010 : 11:57:18
|
Whilst I'm talking to myself... :-) I made the following change and it SEEMS the file was transfered in binary mode because the unzip worked correctly:
Exec @hr=sp_OAMethod @oPkg,Get_File,@getfile OUT,
@sessionid=@Connected,
@remotefile=@REMAINDER_OF_PATH_AND_FILENAME_TO_RETRIEVE,
@newfile= @FULL_LOCAL_PATH_AND_FILENAME_TO_SAVE_AS,
@failifexists=0,
@flagsandattr=0,
@flags=2, -- ??? 1=ASCII mode, 2= BINARY mode?
@context=0
|
|
|
|
TallPaulInJax
Starting Member
5 Posts |
Posted - 09/04/2010 : 17:38:04
|
Ok, one more SLIGHT problem! :-) This portion of the code seems to always have @hr=0 even if the file was not found on the FTP site. What's the workaround for that?
IF @hr <> 0 --Paul: it's seems to ALWAYS be 0, even if the file doesn't exist on the FTP site.
BEGIN
Print 'Error in Get File Method'
EXEC sp_oaGeterrorinfo @oPkg, @hr
|
|
|
|
leij9857
Starting Member
Netherlands
1 Posts |
Posted - 07/19/2011 : 10:38:12
|
To build a fully featured SQL FTP client take a closer look at the source code and examples on http://www.15seconds.com/issue/981203.htm
From the code it's very easy to produce a stored procedure. Register the DLL and work it from here
--Create the object and store it's handle in @oPKG
EXEC @hr = sp_OACreate 'NIBLACK.ASPFTP', @oPKG OUT
IF @hr <> 0
BEGIN
PRINT 'Create object failed'
EXEC sp_OAGetErrorInfo @oPKG, @source OUT, @description OUT
select @description ,@source
RETURN
END
ELSE
BEGIN
PRINT 'Something worked Create object'
EXEC sp_OAGetErrorInfo @oPKG, @source OUT, @description OUT
select @description ,@source
END
--Set FTP connection mode to active / passive
EXEC @hr=sp_OASetProperty @oPkg, 'bPassiveMode', 'False'
IF @hr <> 0
BEGIN
PRINT 'Error in bPassiveMode Property'
EXEC sp_OAGetErrorInfo @oPKG, @source OUT, @description OUT
select @description ,@source
RETURN
END
ELSE
BEGIN
PRINT 'Something worked bPassiveMode Property'
EXEC sp_OAGetErrorInfo @oPKG, @source OUT, @description OUT
select @description ,@source
END
|
|
|
|
Topic |
|
|
|
Article: Using FTP in Transact-SQL
