Server: Msg 156 error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Server: Msg 156 error

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources