| Author |
Topic |
|
uggers2k
Starting Member
6 Posts |
 Posted - 2010-10-07 : 12:59:38
|
| Hi All,I am a absolute newbie to SQL Server, with many years experience in Linux/Oracle.I have decided to join the SQL Server bandwagon and am producing a simple script to select data from a table and then dump this data to a text file. This script also needs to run automatically at 12pm each day and the daily script that is produced needs to be timestamped. e.g file.07102010.Using BCP, I have managed to create the text file with the relevant data.I would like information on:a) Automatically running the script at a certain time (In Linux, I would just create a Cron job)b) How I could name the daily created file with a timestampMany Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
uggers2k
Starting Member
6 Posts |
Posted - 2010-10-07 : 13:54:36
|
| Tara, you are a legend thanks. Will post the full script once tested.regards |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
uggers2k
Starting Member
6 Posts |
Posted - 2010-10-08 : 06:20:25
|
| Hi all,1 more issueEnclosed is the procedure I have created to satisy my requirement:create Procedure myProcedure as Begin Declare @str varchar(1000), @s varchar(30), @table varchar(30) set @table = 'audit' SET @s = 'c:\temp\File_' SET @s = @s + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120), '-', ''), ' ', ''), ':', '') SET @s = @s + '.log' set @str='Exec Master..xp_Cmdshell ''bcp "Select * from '+db_name()+'..'+@table+'" queryout "'+@s+'" -T -S "'+@@servername+'" -c''' Exec(@str) endMy issue is that I need the returned SQL to be in single quotes and each returned attribute to be seperated by a ,i.e. 'column1','column2','colum3'Is this possible? Was thinking about installing active perl and creating a script to parse the created file and edit? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
uggers2k
Starting Member
6 Posts |
Posted - 2010-10-10 : 15:15:30
|
| Can you use SSIS to run automatically on a daily basis? regards |
|
|
|
uggers2k
Starting Member
6 Posts |
Posted - 2010-10-10 : 16:21:07
|
| Also, this works in sql serverselect name +', '+age from detailsbut changing the procedure (as above) to include this statement, when calling the procedure, it fails with:Procedure expects parameter 'no_output' of type 'varchar'.Any reason??? |
|
|
|
uggers2k
Starting Member
6 Posts |
Posted - 2010-10-11 : 11:45:35
|
| All sorted. Installed active Perl, and wrote a Perl script to sort it out. Nice and simple.I guess creating a procedure was not the way to go to sort this out.Cheers all |
|
|
|
|
Export data from a SQLserver DB to a text file