January 5, 2011 at 7:12 am
Hi Folks,
I am using SQL Server 2005 and MS Office 2007 on DB server:
Here is my code:
--------------------------------------------------
set @Qry='Select s.ServiceID, s.MSISDN, s.RequestBy, s.RequestDateTime, s.ServiceStatus, '''' + e.ESMSID + '''' AS [ESMS ID] from Service s, Service_ESMS e Where s.ServiceID = e.ServiceID AND s.RequestDateTime Between '''+convert(varchar,@StartDate,0)+''' AND '''+convert(varchar,@EndDate,0)+''''
set @filepath = 'E:\Scheduled Reports\SmartAgent_DateWise_Report_ESMS.xlsx'
EXEC sp_makewebtask
@outputfile = @filepath,
@query = @Qry,
@colheaders =1,
@FixedFont=0,@lastupdated=0,@resultstitle='Auto Generated Date Wise ESMS Report'
--------------------------------------------------
The problem is that, column ESMSID in Service_ESMS table is of type VARCHAR(500) and contains comma separated 3 digit numeric values. When the file is exported to Excel using above code, Excel treats the column as numeric and does not show the values surrounded by single quotes.
When i execute the above SQL query in New Query window, and copy the results from result pan to excel sheet, the column values are shown surrounded by single quotes (as required).
Please suggest how i can make sp_makewebtask to export data to excel sheet with ESMSID column
surrounded by single quotes so that it is treated as string.
Thanking in advance.
Regards,
Zain.
January 5, 2011 at 8:59 am
Guys........ Waiting for response
January 5, 2011 at 9:39 am
First, you do understand that this isn't paid support with some sort of time-to-reply contract, right? People will answer as they are able, have time, and feel the inclination.
Second, you do understand that Microsoft recommends not using that proc, right? It's only in SQL 2005 for backwards compatibility, and shouldn't be used for new development, as per http://msdn.microsoft.com/en-us/library/ms180099(SQL.90).aspx.
Third, if you're trying to export to Excel, why not use SSIS or a linked server, instead of an obsolete proc that's designed for HTML output?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply