SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using FTP in Transact-SQL
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

agusmer
Starting Member

1 Posts

Posted - 07/30/2009 :  14:45:53  Show Profile  Reply with Quote
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!
Go to Top of Page

TallPaulInJax
Starting Member

5 Posts

Posted - 09/03/2010 :  21:29:37  Show Profile  Reply with Quote
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?
Go to Top of Page

TallPaulInJax
Starting Member

5 Posts

Posted - 09/04/2010 :  00:22:17  Show Profile  Reply with Quote
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?
Go to Top of Page

TallPaulInJax
Starting Member

5 Posts

Posted - 09/04/2010 :  11:39:47  Show Profile  Reply with Quote
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?
Go to Top of Page

TallPaulInJax
Starting Member

5 Posts

Posted - 09/04/2010 :  11:57:18  Show Profile  Reply with Quote
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 
Go to Top of Page

TallPaulInJax
Starting Member

5 Posts

Posted - 09/04/2010 :  17:38:04  Show Profile  Reply with Quote
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
Go to Top of Page

leij9857
Starting Member

Netherlands
1 Posts

Posted - 07/19/2011 :  10:38:12  Show Profile  Reply with Quote
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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000