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

Free unused space in database

Hello,
I need to free unused space in one database, but neither
the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
commands shows the desired results.
Inside my database i've two tables:
t1 - is the original table
t2 - was created with a select into...from t1
when i use the sp_spaceused in these tables:
t1 unused space = 1822968 KB
t2 unused space = 39992 KB
for me its obvious that when the table t2 was created the
unused space(physical space) its not allocated in the new
table.
The question is that i want to free unused space but cant
see results w/ dbcc shrink...
Best regards
Did you try
DBCC SHRINKDATABASE(DBName, 'TruncateOnly')
However, be very aware that if your database grew to this size once, it will
likely do so again... so don't expect this to be permanent disk space
relief.
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>
|||try to update statistics. Look in BOL.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>
|||Perhaps the sp_spaceused results are inaccurate? Try running DBCC
UPDATEUSAGE to get the latest stats.
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>
|||Perhaps some index is occupying space - try reindexing (DBCC DBREINDEX).
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>
|||Or do a showcontig to get more information on any kind of fragmentation.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Free unused space in database

Hello,
I need to free unused space in one database, but neither
the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
commands shows the desired results.
Inside my database i've two tables:
t1 - is the original table
t2 - was created with a select into...from t1
when i use the sp_spaceused in these tables:
t1 unused space = 1822968 KB
t2 unused space = 39992 KB
for me its obvious that when the table t2 was created the
unused space(physical space) its not allocated in the new
table.
The question is that i want to free unused space but cant
see results w/ dbcc shrink...
Best regardsDid you try
DBCC SHRINKDATABASE(DBName, 'TruncateOnly')
However, be very aware that if your database grew to this size once, it will
likely do so again... so don't expect this to be permanent disk space
relief.
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx
.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||try to update statistics. Look in BOL.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx
.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||Perhaps the sp_spaceused results are inaccurate? Try running DBCC
UPDATEUSAGE to get the latest stats.
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx
.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||Perhaps some index is occupying space - try reindexing (DBCC DBREINDEX).
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx
.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||Or do a showcontig to get more information on any kind of fragmentation.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Free unused space in database

Hello,
I need to free unused space in one database, but neither
the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
commands shows the desired results.
Inside my database i've two tables:
t1 - is the original table
t2 - was created with a select into...from t1
when i use the sp_spaceused in these tables:
t1 unused space = 1822968 KB
t2 unused space = 39992 KB
for me its obvious that when the table t2 was created the
unused space(physical space) its not allocated in the new
table.
The question is that i want to free unused space but cant
see results w/ dbcc shrink...
Best regardsDid you try
DBCC SHRINKDATABASE(DBName, 'TruncateOnly')
However, be very aware that if your database grew to this size once, it will
likely do so again... so don't expect this to be permanent disk space
relief.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||try to update statistics. Look in BOL.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||Perhaps the sp_spaceused results are inaccurate? Try running DBCC
UPDATEUSAGE to get the latest stats.
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||Perhaps some index is occupying space - try reindexing (DBCC DBREINDEX).
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||Or do a showcontig to get more information on any kind of fragmentation.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.