FTP from within T-SQL

I am using SQL 2000 on a Win 2k server. I have read some where that
you can fire FTP commands from within t-sql statements.

Something like a stored proc that gets called by a job every night
that would build a recordset and then ftp it out to another server as
a txt file.

I understand that DTS can do something like this, but I would like to
see if it can be handled from within a t-sql procedure.

Curious and stubborn!

Any help would be greatly appreciated.

Thanks,
Davefunkdm1@.yahoo.com (Dave) wrote in message news:<f5174e0f.0405101837.5f6c1e53@.posting.google.com>...
> I am using SQL 2000 on a Win 2k server. I have read some where that
> you can fire FTP commands from within t-sql statements.
> Something like a stored proc that gets called by a job every night
> that would build a recordset and then ftp it out to another server as
> a txt file.
> I understand that DTS can do something like this, but I would like to
> see if it can be handled from within a t-sql procedure.
> Curious and stubborn!
> Any help would be greatly appreciated.
> Thanks,
> Dave

You could use FTP from TSQL in a couple of ways, although it's
debatable if you should or not. The simplest is probably to use
xp_cmdshell to run an external batch file or script which does the
FTP. A more complex option is to use the sp_OA% procedures to work
with a COM component which can do FTP.

If you want to get results into a file, it may be easier to use
bcp.exe or osql.exe in a batch file to get the result set from the
procedure directly into a file, rather than drive it from the stored
procedure itself.

DTS is often a better general option, although the FTP task can only
GET, not PUT. There is an alternative implementation that will do
both, however:

http://www.sqldts.com/?302

Simon

frying pan to fire - log ship or something else

The sql server 2000 is on its last legs and although we have a new 2005
server, no plan for migration has had time to congeal. Meanwhile the concern
that the older server will die has prompted us to construct a second
duplicate server which we are planning to use to log ship from the old server
to the new. In the event of a disaster, the plan is to switch the plant load
over to the server receiving the logs. Tomorrow is the day to try the plan
but I am having last minute doubts and wonder if a better solution may exist.
Takers?
Regards,
Jamie
Jamie - this is quite a well-used and well-supported methodology and will
ensure that everything is maintained. I have an article which illustrates
the differences between log shipping and transactional replication
(http://www.replicationanswers.com/Standby.asp) which might be useful.
Clustering will give you automatic failover, as will database mirroring when
you move to SQL Server 2005.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||You have two options log shipping and transactional replication. With log
shipping your exposure to data loss is greater - it is the time period since
the last database dump.
With transactional replication your subscriber/destination can be within
seconds of the publisher/source.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:02849BEF-1DBE-447C-823A-972939D09393@.microsoft.com...
> The sql server 2000 is on its last legs and although we have a new 2005
> server, no plan for migration has had time to congeal. Meanwhile the
> concern
> that the older server will die has prompted us to construct a second
> duplicate server which we are planning to use to log ship from the old
> server
> to the new. In the event of a disaster, the plan is to switch the plant
> load
> over to the server receiving the logs. Tomorrow is the day to try the
> plan
> but I am having last minute doubts and wonder if a better solution may
> exist.
> Takers?
> --
> Regards,
> Jamie
|||Log shipping does not appear to be an option when right clicking the database
and selecting properties. (SQLMgr 2005)
Regards,
Jamie
"Hilary Cotter" wrote:

> You have two options log shipping and transactional replication. With log
> shipping your exposure to data loss is greater - it is the time period since
> the last database dump.
> With transactional replication your subscriber/destination can be within
> seconds of the publisher/source.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:02849BEF-1DBE-447C-823A-972939D09393@.microsoft.com...
>
>
|||Are you using Express Edition? Any other edition should have the option
"Transaction Log Shipping" on the database properties screen.
Rgds,
Paul Ibison
|||It specifically states on the screen (SQL Server 2000) that log shipping is
an Enterprise only option. Perhaps SP4 removes this restriction? We would
like to upgrade but fear issues with the production server and the firmware
that cannot yet be applied.
Regards,
Jamie
"Paul Ibison" wrote:

> Are you using Express Edition? Any other edition should have the option
> "Transaction Log Shipping" on the database properties screen.
> Rgds,
> Paul Ibison
>
>
|||I should add, that if it helps, we also have 3 MSDN subscriptions and that
two of us are Partners and two of us have personal MSDN subscriptions.
Unfortunately the company would require two processor licenses to go
Enterprise.
Regards,
Jamie
"Paul Ibison" wrote:

> Are you using Express Edition? Any other edition should have the option
> "Transaction Log Shipping" on the database properties screen.
> Rgds,
> Paul Ibison
>
>
|||OK - I've answered this issue in your other post as there is a big
overlap...
Cheers,
Paul Ibison
sql