| Author |
Topic |
|
dzach
Starting Member
38 Posts |
 Posted - 2006-10-27 : 15:33:57
|
| Hi All,I have what I thought would be a fairly simple request to handle, but it's turned into a nightmare. I've created a web report from a query using sp_makewebtask and it came out nice. Now the customer would rather have the report emailed. No problem I thought, so I figured I'd use osql to generate the output. Ugly output for sure. So is there a way to format the output to make it look more presentable?I've been doing some reading on bcp but I'm not sure this the most efficient way either.Thanks,Dale |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-10-27 : 15:43:22
|
| Why not email the sp_makewebtask file as an attachment? |
 |
|
|
dzach
Starting Member
38 Posts |
Posted - 2006-10-27 : 15:52:01
|
| I tried that but it's HTML format and my sendmail exe doesn't support HTML format. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-10-27 : 16:23:20
|
| If it's an attachment it shouldn't matter what format the file is. You can make up any text for the body of the email, like "see attachment". You can also use xp_sendmail to send such a message. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-10-27 : 16:24:24
|
| Or you could dump your sendmail program and use this instead:http://www.blat.net/ |
|
|
|
dzach
Starting Member
38 Posts |
Posted - 2006-10-27 : 16:47:35
|
| I'm somewhat limited as to what I can do on this network and xp_sendmail is not an option unfortunately. I've used Blat in the past but I'm not sure the customer would allow that either. I'm limited in that I have to use existing apps and tools.It appears that osql will have to suffice but I'm having trouble with the output format. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-27 : 17:26:54
|
| osql is just the command line version of Query Analyzer. If you can get your query formatted properly inside Query Analyzer, then put that in a sql file and pass the sql file to the i switch of osql. bcp might be better for you though is it can do certain formats such as csv or tab delimited.Tara Kizer |
|
|
|
dzach
Starting Member
38 Posts |
Posted - 2006-10-28 : 08:15:50
|
| I'm looking at bcp now. In query analyzer I've got the output set to grid because when I use text, the dash line separator goes out to 512 characters. So when I run the query with bcp, the format is too wide and the email wraps in the wrong place. Is there a way to change the output to grid-like from bcp? |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-10-28 : 10:57:08
|
If you are on sql 2005, this post may help. If not, it won't  In 2005, you can use the -Y option in sqlcmd.exe to set the column widths for char type columns. osql is deprecated for 2005. anyone on 2005 ought to try sqlcmd - it's much better.search for "sqlcmd utility" in 2005 BOL.also, take a look at Database Mail in 2005 for sending email from sql server.
SqlSpec - a fast, cheap, and comprehensive data dictionary generator forSQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org |
|
|
|
dzach
Starting Member
38 Posts |
Posted - 2006-10-28 : 11:02:17
|
| I'm not running SQL 2005, unfortunately. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
|
|
|
Help with Output Requirements