February 26, 2013 at 8:15 am
I'd love to have some pointers on this problem. I see postings on the 'net claiming to correct the problem but have had no success.
I am using msdb.dbo.sp_send_dbmail to query an ERP system tables and email the results in a spreadsheet to users.
I found what works is to email a csv file. They open in Excel beautifully - except for one email, one field.
The customer PO field is defined by the system documentation as Alphabetic 20. If I select it in a query, it is right aligned. Once it is opened in Excel, the fields with any text in them are left aligned, and fields with all numbers are right aligned. I have tried using REPLICATE and CONVERT but they don't fix the problem. In fact Excel opens the Replicate cell with a width of 255.
To add to the fun, the example below I pasted in from one of the emailed spreadsheets, and the formatting was changed to 100% left aligned, so I've had to add spaces to make it appear the way it appears in a spreadsheet. (EDITED TO ADDD) But it still doesn't post correctly. I added comments. I've attached a small excel file as well.
Thanks in advance for any pointers. I'm new on SQL 2008 and don't recall having this problem on earlier versions. 🙂
Customer PO #
-------------------- < (And, does anyone know how to get rid of this line of dashes?)
CLE0007639 < Left Aligned
CLE0007639 < Left Aligned
7840 <Right
913 for Samples < Left
9693<Right
1979-0 < Left
1996<Right
February 26, 2013 at 12:58 pm
Have you tried adding a blank character in front of your PO field during the query? ie. ' '
Generally Excel will consider that a string and left-align it.
February 26, 2013 at 3:07 pm
Erin, that's a neat sounding trick. I'll try it tomorrow morning and report the results. Thanks!
February 28, 2013 at 9:06 am
Erin and all, I'm sorry to say that didn't work. It looks fine in a SSMS Query to grid, but when emailed in a CSV file by the executed job, Excel opens it in the same way, with the alphanumeric values left aligned. Even when I try to format them to right align, they will center but not right align. It appears there is some padding in the field to the right of the value but when I try to remove it using RTRIM and LTRIM, nothing is accomplished.
Are there other suggestions?
February 28, 2013 at 10:40 am
Not sure if it will work as I haven't tried it but you define a text field in Excel by using the single quote. ie. 2459 when put in a cell is numeric but '2459 self-defines as a text. Perhaps prepending a single quote might force excel to define them as texts?
February 28, 2013 at 11:36 am
Thanks for the suggestion Erin. I tried that a number of ways and can't make it work.
Fussing with the dreaded single quote in jobs is one of my pastimes. A query containing single quotes may run fine in management studio. When converted to a string in the @query parameter, all single quotes have to be surrounded by more single quotes or the job errors out with a syntax error. I've never been able to get an odd number of single quotes to behave in a job, but I just tried a few combinations and everything returns an error.
Incorrect syntax near ' '+ SORDER.CUSORDREF_0 < with an even number of '
Incorrect syntax near '+' <with an odd number of ' a number of ways: with and without spaces around the middle '
I've never discovered official rules for use of single quotes in jobs, if such exist.
February 28, 2013 at 1:06 pm
I don't suppose you have access to Integration Services?
Barring that, what about using an Excel file template and using [COde="sql"]INSERT INTO OPENDATASOURCE (.....[/CODE] to write your query into it? That way your columns are already formatted.
February 28, 2013 at 3:08 pm
Hi Erin,
I am supposed to have access to SSIS but don't know how to find it, or get it installed, or whatever. Not opposed to learning something new.
As to the Excel suggestion, that's unknown to me also. Do you put that in an Excel spreadsheet? I've written SPs as sources of external data for spreadsheets, but what you're suggesting is another technique to learn. (That's always a good thing. )
February 28, 2013 at 3:20 pm
This link demonstrates how to use OPENDATASOURCE:
http://msdn.microsoft.com/en-us/library/ms179856.aspx
You'll probably need to exercise your GoogleFu to look up some of the information the OPENDATASOURCE requires.
As for SSIS, if you're using SSMS there should be another link your Start Folder called "SQL Server Business Intelligence Development Studio" which, when you launch it, will allow you to start an SSIS project. From there it's a learning process. 🙂
This is assuming it's been installed on your system. If it hasn't been but you are supposed to have access to it, perhaps talk to the sys admin and have them install it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply