I have two tasks and since I am new to it, I need some help/advice from the masters.
What I need to do is send the result of q select query to a csv file which can have delimeter as comma or tab and then send this file as an attachement to a particular receipient.
Hoping for some great advice
You can do this with SQL Server Integration Services (SSIS) quite easily. A data flow task to copy from the query to a flat (CSV) file, then a task to email the flat file.
SSIS is probably the way to go, but if you want T-SQL to do it, try this..
I'm not going to write the complete program for you, but here are the major points...
determine the split character:
SET @CSV=',' --use CHAR(9) for tab
build your CSV string one line at a time, looping over the results (do this for each line):
SET @FileMessage=ISNULL(CONVERT(varchar(10),column1),'')+@CSV+ISNULL(CONVERT(varchar(10),column2),'')
SET @ExecuteString = RTRIM('echo ' + @FileMessage + ' >>' + @FileName)
--append it to you file, you'll need write permissions
EXEC @ReturnValue=master..xp_cmdshell @ExecuteString, no_output
actually send the mail, after the file is completely built:
EXEC xp_sendmail { [ @recipients= ] 'recipients [ ;...n ]' }
[ ,[ @message= ] 'message' ]
[ ,[ @query= ] 'query' ]
[ ,[ @attachments= ] 'attachments [ ;...n ]' ]
[ ,[ @copy_recipients= ] 'copy_recipients [ ;...n ]'
[ ,[ @blind_copy_recipients= ] 'blind_copy_recipients [ ;...n ]'
[ ,[ @subject= ] 'subject' ]
[ ,[ @type= ] 'type' ]
[ ,[ @attach_results= ] 'attach_value' ]
[ ,[ @no_output= ] 'output_value' ]
[ ,[ @no_header= ] 'header_value' ]
[ ,[ @width= ] width ]
[ ,[ @separator= ] 'separator' ]
[ ,[ @echo_error= ] 'echo_value' ]
[ ,[ @set_user= ] 'user' ]
[ ,[ @dbuse= ] 'database' ]
SSIS would be your best option. Take a look at this link on how to transport data from your csv file into SQL: Import Data From CSV