How can we Upload files to SFTP server using SQLSERVER 2005 Stored procedures.

Question

How can we Upload files to SFTP server using SQLSERVER 2005 Stored procedures.

Sarath Vellampalli

SSCertifiable

Points: 5822
More actions
September 14, 2011 at 11:22 am

#142922

Hi All,
How can we Upload the files to SFTP server using SQLSERVER 2005 Stored procedures. I need this in Stored Procedure only not in SSIS. If so is there any free tools for this.
Kindest Regards,
Sarath Vellampalli

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you can call CoreFTP via a command line, and it supports SFTP.

once a profile is setup for the destination SFTP with the username and password in it, you can call CoreFTP with that profile in theparameters, along with the file(s) you want to send.

you'll probably have to fiddle with permissions, as the job/proc will need to have permissions to both the fodler where the executable lies, as well as whatever file you are intending to transfer.

something like this is representative of the right command, i think:

DECLARE @ShellCommand varchar(2000

SET @ShellCommand = '"C:\Program Files\CoreFTP\coreftp.exe" -s -O'

+ ' -site CoreProfileName -u '

+ '"D:\UNSENT\FileObjtxt"'

+ ' -p /edi/tohud/ -output "D:\ftplog\output.log '

+ ' -log D:\ftplog\uload.txt"

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sarath Vellampalli SSCertifiable Points: 5822 More actions · Answer 2

Hi Lowell,

Thank you for your reply.

Please can you explain in detail, because which version of Core FTP we need to download.

wat we need to do after download. please if you have any articles about this please share the link.

Please do me the needful.

Kindest Regards,

Sarath Vellampalli

Lowell SSC Guru Points: 323518 More actions · Answer 3

any recent version, including the latest version2.2. they all support command line for version 2.0 and above, and maybe earlier verisons.

here's the command line syntax doc:

http://www.coreftp.com/docs/web1/Command_Line_FTP.htm

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sarath Vellampalli SSCertifiable Points: 5822 More actions · Answer 4

Hi Lowell,

Thank you,

I have downloaded Core FTP LE 2.2 : free version, and created the site profile as CVE.

I have used the below code to execute in SSMS.

DECLARE @ShellCommand varchar(4000)

SET @ShellCommand = '""C:\Program Files (x86)\CoreFTP\coreftp.exe"" -s -O'

+ ' -site CVE -u '

+ '""F:\ftplog\CVGVER.20110819.0101.pgp""'

+ ' -p /IN/'

EXEC master..xp_cmdshell @ShellCommand

when we execute this code we are getting the below error

"The filename, directory name, or volume label syntax is incorrect."

Please help me on resolve this.

NOTE: My SP will run on nightly process (SQL JOB)

Kindest Regards,

Sarath Vellampalli

Lowell SSC Guru Points: 323518 More actions · Answer 5

"The filename, directory name, or volume label syntax is incorrect."

that says the user running the procedure via xp_cmsshell does not have permissions to get to the file you want to SFTP, and maybe also does nto have acces to teh program files(x86) folder where the CoreFTP.exe executable exists.

The problem is that when SQL Server tires to access any resource OUTSIDE of SQL server, like network shares, local hard drives and folders,xp_cmdshell,bcp with a "trusted" connection, sp_OA type functions etc.

it doesn't matter what YOUR credentials are, because SQL will not carry those credentials to the "outside of SQL" security context.

SQL Server uses either the account set up as the proxy account, or if that is left blank(the default) it uses account it starts with to try and access the resource:

or if the above was blank, the account in services:

That account is often an account which has never logged into the domain, and was never assigned permissions to get to the local disk or network share.

As a result, you usually need to create a domain account in Active Directory, specifically grant it share access if it doesn't inherit it from Domain\Users or Domain\AuthenticatedUsers and change the account SQL Server starts with to that account.

Once that is done, and you stop and start the SQL service to make it use that account instead of old running values, your linked server/xp_cmdshell would work.

you can prove this is the issue by simply putting in your credentials, with your domain account and password, and confirm the external object you were trying to access/use works when SQL is run your credentials, so you'd know you need a domain account to access the resource.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!