| Author |
Topic |
|
pranjalmehr
Starting Member
2 Posts |
 Posted - 2009-05-29 : 20:33:04
|
Hi,
I am facing the following error:
Server: Msg 156, Level 15, State 1, Procedure pranjal1, Line 43
Incorrect syntax near the keyword 'NULL'.
Here below my code:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER proc dbo.pranjal1
as
begin
SET NOCOUNT ON
declare @message varchar(7990)
,@subject varchar(255)
,@date1 varchar(20)
,@date2 varchar(20)
,@attachments varchar(4000)
,@outputfile NVARCHAR(255)
,@query NVARCHAR(500)
set @date1 = datename(month, dateadd(month,-1,getdate()))
set @date2 = datename(year, dateadd(year,0,getdate()))
select @subject = 'Report - ' + @date1 + char(32) + @date2 + char(13)
select @message = 'Hello Gurdeep, Deepak,' + char(13)
select @message = @message + char(13) + 'Please find attached the file containing Report for the month of ' + @date1 + ','+ char(32) + @date2 + char(13)
select @message = @message + char(13) + 'Kindly check and revert in case of any concerns/queries.' + char(13)
select @message = @message + char(13) + 'Regards,'
select @message = @message + char(13) + Team'
select @message = @message + char(13) + 'Email: ' + char(13)
select @message = @message + char(13) + '*****Please DO NOT reply. This is a system generated email message. If you have any questions / concerns related to report, please revert to Columbo Support Team*****'
exec clb_monthly_rpt_submission_sp @date = null, @is_denormalize =0
exec clb_monthly_rpt_submission_sp @date = null, @is_denormalize =1
EXEC sp_makewebtask
@outputfile = 'D:\Columbo Report\Report.xls',
--@query = 'Select User_Survey_Id,Part_Number,Requester,convert(VARCHAR, Date_Submitted) "Date_submitted",PL,ISNULL(CONVERT(CHAR, Entity),NULL),Priority,Country_Name from columbo..clb_rpt_submission order by Date_Submitted',
@query = 'Select User_Survey_Id,Part_Number,Requester,convert(VARCHAR, Date_Submitted, 113) "Date_submitted",PL,ISNULL(Entity, 'NULL') Entity,Priority,Country_Name from columbo..clb_rpt_submission order by Date_Submitted',
@colheaders =1,
@FixedFont=0,
@bold=0,
@tabborder =2,
@HTMLheader=1,
@codepage=65001,
@lastupdated=0,
@resultstitle=Report'
--@charset=N'utf-8'
EXEC sp_makewebtask
@outputfile = 'D:\Columbo Report\Report1.xls',
-- @query = 'Select User_Survey_Id,Part_Number,Requester,convert(VARCHAR, Date_Submitted) "Date_submitted",PL,ISNULL(Entity,0),Priority,Countries_Requested from columbo..clb_rpt_submission_denormalize order by Date_Submitted',
@query = 'Select User_Survey_Id,Part_Number,Requester,convert(VARCHAR, Date_Submitted, 113) "Date_submitted",PL,ISNULL(Entity, 'NULL') Entity,Priority,Countries_Requested from columbo..clb_rpt_submission_denormalize order by Date_Submitted',
@colheaders =1,
@FixedFont=0,
@bold=0,
@tabborder =2,
@HTMLheader=1,
@codepage=65001,
@lastupdated=0,
@resultstitle=Report1'
--@charset=N'utf-8'
select @attachments = 'D:\Columbo Report\Report.xls;Report1.xls'
EXEC master..xp_sendmail @recipients = ''
--,@blind_copy_recipients = ''
,@subject = @subject
,@message = @message
,@attachments = @attachments
,@no_output = 'TRUE'
SET NOCOUNT OFF
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Hope to c ur reply soon |
|
|
pranjalmehr
Starting Member
2 Posts |
Posted - 2009-05-29 : 21:26:51
|
| In the above code what I actually want is to get the NULL values from the query 'Select User_Survey_Id,Part_Number,Requester,convert(VARCHAR, Date_Submitted, 113) "Date_submitted",PL,ISNULL(Entity, 'NULL') Entity,Priority,Countries_Requested from columbo..clb_rpt_submission_denormalize order by Date_Submitted' exported to excel sheet as NULL only.. However in the excel sheet it's coming as n/a |
 |
|
|
|
|
|
Server: Msg 156 error