I'm interested in setting up scheduled a daily FTP transfer of a simple data file from our database. The transfer would be just a list of barcodes for an event each day that would then be scanned using a different point-of-sale system.
I've done a little research and I think this can be done through an SQL script using xp_cmdshell extended stored procedure, but I'm having trouble getting in working. I think the main problem is that in our RAMP SQL Server environment I don't have access to any local storage to stage a file before initiating the FTP transfer - it's all network drives and SQL Server doesn't like that.
Has anyone ever tried to setup a scheduled FTP transfer from the Tessitura database? Could it be done using any functionality in the regular scheduled reporting process in the Tessitura client? Any ideas at all would be appreciated.
Cheers,
Bill WaughStarlight Theatre
Unknown said: I'm interested in setting up scheduled a daily FTP transfer of a simple data file from our database. The transfer would be just a list of barcodes for an event each day that would then be scanned using a different point-of-sale system.
If it's output intended for another system, couldn't you create it as a custom report, run it via the Report Server, and send where it needs to go as a CSV attachment?
Yes - generally speaking I could do that and I was hoping something like that might be a viable option. My specific scenario requires that the file lands on an FTP server. If I could work out some sort of email to FTP solution that might work. I'm sure someone on the internet has worked that out before.
Unknown said: My specific scenario requires that the file lands on an FTP server.
My specific scenario requires that the file lands on an FTP server.
Hmm, I think you're right above: the lack of a local temp dir is a significant snag. SQL-to-FTP code I find all want that.
I suspect E-mail-to-FTP will want to create an attachment, so we're back to needing a temp dir.
You could code the SQL to send its output within an HTML e-mail, send it to another, non-RAMP system, and send that via FTP, like this person:
http://www.howtogeek.com/forum/topic/email-to-ftp-possible
...but that sort of thing looks rather fiddly and error-prone.
A toughie.