| Author |
Topic |
|
fireballbrady
Starting Member
6 Posts |
 Posted - 2011-10-31 : 14:19:35
|
| HELP!I am new to sql and have gotten a lot of things doing what I want them to. I have one last thing that I have been struggling with.I set up thes SQL Server agent to kick off once a day and run the following query.select c.masterorderid, c.createdatetime, a.orderItemId, a.orderId, a.serialNumber, a.filled, d.itemNumber, d.equipName, e.*, f.commentText from orderItems a inner join orders b on a.orderId = b.orderId inner join masterOrders c on b.masterOrderId = c.masterOrderId inner join equipmentTypes d on a.equipTypeId = d.equipmentTypeId inner join shipAddresses e on b.shipaddress = e.shipaddressId left join comments f on f.commentOrderId = b.masterorderidThe agent runs the query and then shoots me an email. I need to email to contain the query's results. RIght now it just sends me the email saying the query was ran.Can anyone help me with this? Is there a way to do that?Please remember, I am very new to this. I am trying my best!Thanks in advance for your help!fireballbrady |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-10-31 : 18:00:47
|
| try sp_send_dbmail - you can include the result as an attachment or in the body.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
pduffin
Yak Posting Veteran
68 Posts |
Posted - 2011-10-31 : 21:24:01
|
| exec master.dbo.xp_sendmail @recipients = 'bob@bob.com', @query = 'select ''bob rules''',@subject = 'Bobs Results',@message = 'Bobs Message',@attach_results = 'true', @width = 250Likes to run, hates the runs! |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-01 : 05:52:07
|
| I would still go for sp_send_dbmail - xp_sendmail is (supposedly) going to be dropped. Haven't seen it used in quite a while.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
fireballbrady
Starting Member
6 Posts |
Posted - 2011-11-01 : 15:17:06
|
| Visakh16 and everyone else! Thanks for you input. I have been able to get the data to flow. Any ideas on how to get the data to be formatted correctly? My fully functioning deathstar... er .. I mean sql query looks like this:EXEC msdb.dbo.sp_send_dbmail@profile_name = 'SIMPL Dropship',@body = 'SIMPL Nightly Dropship',@subject = 'SIMPL Nightly Dropship',@recipients = 'test@test.com',@execute_query_database = 'SIMPL',@attach_query_result_as_file = 1,--@query_result_separator = ''@query_attachment_filename = 'dropship.xls',@query = 'select c.masterorderid, c.createdatetime, a.orderItemId, a.orderId, a.serialNumber, a.filled, d.itemNumber, d.equipName, e.*, f.commentText from orderItems ainner join orders b on a.orderId = b.orderIdinner join masterOrders c on b.masterOrderId = c.masterOrderIdinner join equipmentTypes d on a.equipTypeId = d.equipmentTypeIdinner join shipAddresses e on b.shipaddress = e.shipaddressIdleft join comments f on f.commentOrderId = b.masterorderid'My problem is that nothing is formatted correctly. Whether I user a csv or send it to excel. It is messed up.All the data is there, but it isn't in a way that you can disseminate it. Any Ideas on the formatting?fireballbrady |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-02 : 05:53:51
|
| why dont you bcp out query results to a textfile and send it as an attachment in mail?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
fireballbrady
Starting Member
6 Posts |
Posted - 2011-11-02 : 16:44:12
|
| I would, if I could figure out the bcp function and add it to the what I have! I am googling it right now. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-11-02 : 17:09:37
|
| I can tell you from past experience and experimentation that Excel will find a way to mess up your CSV files no matter what you do. It won't interpret a CSV file correctly if it has an .XLS extension. If you expect people to click on the attachment and have it open in Excel it's better to use a .CSV extension on the file and use commas to delimit the columns. Using quotes to delimit text doesn't help, so if you have embedded commas don't use them as delimiters.Excel 2003 used to open tab-delimited files with .XLS extensions correctly, but since 2007 it doesn't work the same way. (It still works but does wonky things sometimes) You can try tab-delimited with a .TSV or .TXT extension but these may not be associated with Excel on the end users' machines, so double-clicking may not work.You still have to deal with Excel (mis)interpreting dates, zip codes or other leading-zero data, and splitting on commas that are part of the data. The only way to ensure that works correctly is to use the text import wizard, which doesn't come up automatically when double-clicking.Don't spend more than an hour getting this to work right, your users will have to choose between automatic email that's unformatted or hand-crafted Excel files that take a lot longer to produce. You won't get DB Mail to do both. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
HELP ME PLEASE!! SQL Agent to email query results?