Query Help

Question

Post reply

Query Help

Mvs2k11

Ten Centuries

Points: 1223
More actions
May 23, 2012 at 8:03 am

#392325

I need to export the data from a query(select col1,col2,col3 from table where col1 is not null) to generate excel file on different machine and after successful export to excel alert an email.this process needs to run every week.
Please help..
Thanks for your help in advance

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 1

Hi

You can do all of this using SSIS packages

This Stairway may help you

http://www.sqlservercentral.com/stairway/72494/

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Sounds like a good candidate for SSIS. The stairway series here is a great start. http://www.sqlservercentral.com/stairway/72494/[/url]

--EDIT--

Is there an echo in here??? Seems like Andy had the same answer as me...only a few seconds earlier. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 3

Another approach would be bcp and export to a .csv file, then use the CDO functionality available in VBScript (put in a batch file envelope) to handle the e-mail.

I have a similar setup with an e-mail alert to tell me if there's problems with a DB clone operation that runs once a week, the code parses the log file for keywords and e-mails me about it.

You can find more information on CDO here ->

http://www.paulsadowski.com/wsh/cdo.htm

You can find more information about bcp here ->

http://msdn.microsoft.com/en-us/library/ms162802.aspx

To put any VBScript you write in a batch file, simply create a .bat file in Notepad and put 'cscript <myfile>.vbs /nologo' in it. Then run the batch file. For more info Google 'Windows Scripting Host'.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.