osql script

I have two Osql scripting questions for windows?
1. How can I get my OSQL script to output to a CVS file. Such as "select *
from itemtable"
2. How can I create a osql backup script to will name the backup filename
system_date.bck. This way I have a backup for each day by date.
Thanks
Dan
hi Dan,
Danny wrote:
> I have two Osql scripting questions for windows?
> 1. How can I get my OSQL script to output to a CVS file. Such as "select *
> from itemtable"
SET NOCOUNT ON;
USE pubs;
GO
DECLARE @.fullcmd varchar(1000);
DECLARE @.cmd varchar(1000);
SET @.cmd = 'SELECT * FROM pubs.dbo.authors';
SET @.fullcmd = 'osql -S(Local) -E -q "' + @.cmd + '" -o c:\authors.txt';
-- this will result in a tab delimited columns output..
EXEC master..xp_cmdshell @.fullcmd ;
-- but you can prepare a format file as required, see BOL
PRINT 'PREPARE a format file';
SET @.fullcmd = 'bcp pubs.dbo.authors format nul -c -f c:\f.txt -T'
--EXEC master..xp_cmdshell @.fullcmd ;
PRINT 'MANUALLY MODIFY THE format file setting the "," as column separator
instead of the tab char';
SET @.fullcmd = 'bcp "' + @.cmd + '" queryout "c:\bcpAuthors.txt" -f
c:\f.txt -T';
SELECT @.fullcmd;
EXEC master..xp_cmdshell @.fullcmd;

> 2. How can I create a osql backup script to will name the backup
> filename system_date.bck. This way I have a backup for each day by
> date.
DECLARE @.db sysname;
DECLARE @.dir varchar(500);
DECLARE @.file varchar(256);
SELECT @.db = 'pubs', @.dir = 'c:\', @.file = CONVERT(varchar(8), GETDATE(),
112) + '_' + @.db + '.bak';
DECLARE @.fullpath varchar(1000);
SET @.fullpath = @.dir + @.file;
SELECT @.fullpath;
BACKUP DATABASE @.db
TO DISK = @.fullpath
WITH INIT;
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply