none
Incorrect syntax near ':'.

    Question

  • I am attempting to execute a stored procedure and am getting a syntax error...not sure what is causing the error though.  When I execute the proc I get the following errors:

    Msg 102, Level 15, State 1, Procedure GetFaxReconByServer, Line 30

    Incorrect syntax near ':'.

    Msg 102, Level 15, State 1, Procedure GetFaxReconByServer, Line 34

    Incorrect syntax near ':'.

    Msg 102, Level 15, State 1, Procedure GetFaxReconByServer, Line 39

    Incorrect syntax near ':'.

    Msg 102, Level 15, State 1, Procedure GetFaxReconByServer, Line 43

    Incorrect syntax near ':'.

    Here is the stored procedure, any ideas?

    1 set ANSI_NULLS ON 
    2 set QUOTED_IDENTIFIER ON 
    3 GO  
    4 -- =============================================  
    5 -- Author:      <Author,,Name>  
    6 -- Create date: <Create Date,,>  
    7 -- Description: <Description,,>  
    8 -- =============================================  
    9 ALTER PROCEDURE [dbo].[GetFaxReconByServer]  
    10     -- Add the parameters for the stored procedure here  
    11     @StartDate varchar(10),  
    12     @EndDate varchar(10),  
    13     @ServerName varchar(20),  
    14     @ServerCode varchar(20)  
    15       
    16 /*  
    17 Need to add/subtract 4 or 5 hours for GMT offset  
    18 */  
    19  
    20 AS 
    21 BEGIN 
    22     -- SET NOCOUNT ON added to prevent extra result sets from  
    23     -- interfering with SELECT statements.  
    24     SET NOCOUNT ON;  
    25     declare @sql nvarchar(4000);  
    26     declare @DLSStart smalldatetime, @DLSEnd smalldatetime;  
    27  
    28     set @DLSStart = (select RightFax.dbo.fn_GetDaylightSavingsTimeStart(convert(varchar,datepart(year,getdate()))));  
    29     set @DLSEnd = (select RightFax.dbo.fn_GetDaylightSavingsTimeEnd(convert(varchar,datepart(year,getdate()))));  
    30       
    31     if (@StartDate between @DLSStart and @DLSEnd)  
    32         BEGIN 
    33         set @StartDate = dateadd(hour,4,@StartDate + 00:00:00.000)  
    34         END 
    35     ELSE 
    36         BEGIN 
    37         set @StartDate = dateadd(hour,5,@StartDate + 00:00:00.000)  
    38         END 
    39  
    40     if (@EndDate between @DLSStart and @DLSEnd)  
    41         BEGIN 
    42         set @EndDate = dateadd(hour,4,@EndDate + 00:00:00.000)  
    43         END 
    44     ELSE 
    45         BEGIN 
    46         set @EndDate = dateadd(hour,5,@EndDate + 00:00:00.000)  
    47         END 
    48       
    49         -- Insert statements for procedure here  
    50     set @sql = 'SELECT
    51                     (case when D.CreationTime between ' + @DLSStart + ' and ' + @DLSEnd + ' then dateadd(hour,-4,D.CreationTime) else dateadd(hour,-5,D.CreationTime) end) as "CreationTime",
    52                     D.UniqueID as "UniqueID",''' +  
    53                     @ServerName + ''AS "ServerName", ''' +  
    54                     @ServerCode + ''AS "ServerCode",   
    55                     DF.NumPages as "NumPages",   
    56                     D.RemoteID as "RemoteID",   
    57                     (case when D.FaxDIDNum='''then D.BillInfo1 else D.FaxDIDNum endas "FaxDIDNum",   
    58                     D.Status as "Status",   
    59                     D.ErrorCode as "ErrorCode",   
    60                     D.TermStat as "TermStat" 
    61                 FROM ' +  
    62                     @ServerName + '.RightFax.dbo.Documents AS D INNER JOIN ' +   
    63                     @ServerName + '.RightFax.dbo.DocFiles AS DF ON D.DocFileDBA = DF.handle INNER JOIN
    64                     TermStatStrings AS TSS ON D.TermStat = TSS.TermStatValue INNER JOIN
    65                     FaxErrorStrings AS FES ON D.ErrorCode = FES.FaxErrorCode INNER JOIN ' +   
    66                     @ServerName + '.RightFax.dbo.Users AS U ON D.OwnerID = U.handle INNER JOIN ' +  
    67                     @ServerName + '.RightFax.dbo.Groups AS G ON U.GroupID = G.handle
    68                 WHERE D.CreationTime between dateadd(hour,5,''' + @StartDate + ' 00:00:00.000'') and dateadd(hour,5,''' + @EndDate + ' 23:59:59.999'')  
    69                     and D.Status=6  
    70                     and DF.NumPages > 0  
    71                     and G.GroupID=''Everyone''';  
    72  
    73     exec sp_executesql @sql;  
    74 END 



     

    Monday, December 15, 2008 5:54 PM

All replies

  • hi,
    you wrote:
    set @StartDate = dateadd(hour,4,@StartDate + 00:00:00.000)  

    and so on for the other asignements..
    you have to enclose the time part you like to add into '', like
    '00:00:00.000' in order to let the compiler correctly understand it has to convert that value to a "time portion" ...
    by the way... using dates that way is not that correct.. you should pass the @StartDate and @EndDate as datetime data type and not as varchars.. more.. in the datetime data type, you do not have to perform the "addition" you are doing as datetime already contains the time part and, if you do not set it, it already is set to midnight, thus 00:00:00.000..
    so assigning the paramenter as
    EXECUTE dbo.GetFaxReconByServer @StartDate = '20081210', @EndDate = '20081215', .....
    means the midnighto of dec. 10th and dec. 15th, thus '20081210 00:00:00.000' and '20081215 00:00:00.000'.. the whole assignement can be rewritten as
    set @StartDate = dateadd(hour, 4, CONVERT(datetime, @StartDate) );
    or, if @StartDate datatype is modified as a datetime,
    set @StartDate = dateadd(hour, 4, @StartDate)  


    wow... you use dynamic sql to perform linked server execution.. :(
    not the piece of code i'd like to see in my dbs :)

    regards
    http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools
    Monday, December 15, 2008 6:18 PM
    Moderator
  • J-Oz,

    Why are you doing this?

    dateadd(hour
    ,4,@StartDate + 00:00:00.000) 

    Take a look at the below and see if it helps.

    declare @StartDate datetime

    set @StartDate = '12/15/2008'

    set @StartDate = dateadd(hh, 4 , @startdate)

    select @StartDate

    go

    declare @StartDate datetime

    set @StartDate = getdate()

    set @StartDate = dateadd(hh, 4 , dateadd(dd, datediff(dd, 0, @startdate), 0))

    select @StartDate

    go


    Sankar Reddy | http://sankarreddy.spaces.live.com/
    Monday, December 15, 2008 6:22 PM