Post reply

"Incorrect syntax near 'AS'. Expecting ID, '(', or PRIMARY"

  • April 15, 2011 at 12:07 am

    #249041

    I am getting the error above while trying to implement a Snapshot. Before the snapshot, I created three filegroups with their corresponding files as follows:

    NAME / FILENAME

    fg0103SALES; 'J:\AdventureWorks_FileGroups\FG0103SALES\0103SALES.ndf'

    fg0406SALES; 'J:\AdventureWorks_FileGroups\FG0103SALES\0406SALES.ndf'

    fg0708SALES; 'J:\AdventureWorks_FileGroups\FG0103SALES\0708SALES.ndf'

    My code is as follows:

    USE Master

    GO

    CREATE DATABASE AdventureWorks_snap021607 ON

    (NAME = AdventureWorks_Data, FILENAME = 'C:\AdventureWorks_data_021607.ss' ),

    (NAME = fg0103SALES , FILENAME = 'J:\AdventureWorks_FileGroups\FG0103SALES\0103SALES.ndf'),

    (NAME = fg0406SALES , FILENAME = 'J:\AdventureWorks_FileGroups\FG0406SALES\0406SALES.ndf'),

    (NAME = fg0708SALES , FILENAME = 'J:\AdventureWorks_FileGroups\FG0708SALES\0708SALES.ndf'),

    AS SNAPSHOT OF AdventureWorks

    GO

    Please help.

    Thanks,

  • April 15, 2011 at 2:46 am

    #1311991

    There's a comma before the AS where it shouldn't be.

    USE Master

    GO

    CREATE DATABASE AdventureWorks_snap021607 ON

    (NAME = AdventureWorks_Data, FILENAME = 'C:\AdventureWorks_data_021607.ss' ),

    (NAME = fg0103SALES , FILENAME = 'J:\AdventureWorks_FileGroups\FG0103SALES\0103SALES.ndf'),

    (NAME = fg0406SALES , FILENAME = 'J:\AdventureWorks_FileGroups\FG0406SALES\0406SALES.ndf'),

    (NAME = fg0708SALES , FILENAME = 'J:\AdventureWorks_FileGroups\FG0708SALES\0708SALES.ndf')

    AS SNAPSHOT OF AdventureWorks

    GO

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 15, 2011 at 7:44 am

    #1312145

    You got it! Thanks.

  • October 17, 2012 at 1:16 am

    #1549888

    Hi guys I'm also getting the same error:This is my query please assist:

    Select

    a.ApplicantName

    ,a.ApplicationUniqueNumber

    ,a.MeetingDate as DateOfApproval

    ,a.ProjectSICDescription as Sector

    ,b.Description as Product

    ,a.Province

    ,a.Investment

    ,a.GrantAmount

    ,g.FIG

    ,dbo.fn_ProjectSize(c.TotalIncentive)as ProjectSize

    ,d.status

    ,dbo.fn_GetProjectJobsNew(e.id) as NewJobs

    ,dbo.fn_GetProjectJobsExisting(e.ID) as JobsExisting

    ,(f.BFemale +f.CFemale + f.DFemale +f.IFemale + f.OFemale + f.WFemale) AS NumberOfFemales

    ,(f.BMale + f.CMale + f.DMale + f.IMale + f.OMale + f.WMale) as NumberOfMale

    from ApplicationApprovalsList a inner join

    ProductDescriptionForApproval b on a.Id= b.ApplicationHeaderId inner join

    ApplicationHeader c on c.Id = b.ApplicationHeaderId inner join

    AppStatus d on d.id = c.Status inner join

    Project e on e.ApplicationHeaderId = c.Id left join

    ProjectEmployment f on f.ProjectId = e.Id left join

    Budget g on g.ProjectId = e.Id join

    ( select sh.Description from ShareHolder sh)

    WHERE (c.AcknowledgeDate BETWEEN CONVERT(DATETIME, '2012-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2012-09-30 00:00:00',

    102))

  • October 17, 2012 at 1:30 am

    #1549894

    You need an alias for the subquery in the from, and you need to finish the missing portion of the join clause.

    p.s. Please post new questions in new threads in future. Thanks.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 17, 2012 at 2:32 am

    #1549907

    Thanks will in future,I did finish the join and put in alias but I'm getting two errors:1 next to alias h I the following error "An expression of non-Boolean type specified in a context where a condition is expected:

    2 next to the WHERE clause "Incorrect syntax near 'WHERE"

    Select

    a.ApplicantName

    ,a.ApplicationUniqueNumber

    ,a.MeetingDate as DateOfApproval

    ,a.ProjectSICDescription as Sector

    ,b.Description as Product

    ,a.Province

    ,a.Investment

    ,a.GrantAmount

    ,g.FIG

    ,dbo.fn_ProjectSize(c.TotalIncentive)as ProjectSize

    ,d.status

    ,dbo.fn_GetProjectJobsNew(e.id) as NewJobs

    ,dbo.fn_GetProjectJobsExisting(e.ID) as JobsExisting

    ,(f.BFemale +f.CFemale + f.DFemale +f.IFemale + f.OFemale + f.WFemale) AS NumberOfFemales

    ,(f.BMale + f.CMale + f.DMale + f.IMale + f.OMale + f.WMale) as NumberOfMale

    from ApplicationApprovalsList a inner join

    ProductDescriptionForApproval b on a.Id= b.ApplicationHeaderId inner join

    ApplicationHeader c on c.Id = b.ApplicationHeaderId inner join

    AppStatus d on d.id = c.Status inner join

    Project e on e.ApplicationHeaderId = c.Id left join

    ProjectEmployment f on f.ProjectId = e.Id left join

    Budget g on g.ProjectId = e.Id left join

    ( select sh.Description from ShareHolder sh ) As h

    WHERE (c.AcknowledgeDate BETWEEN CONVERT(DATETIME, '2012-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2012-09-30 00:00:00',

    102))

  • October 17, 2012 at 2:45 am

    #1549911

    You haven't finished the last join in the from clause.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 17, 2012 at 4:54 am

    #1549952

    Thanks mil got it:-D..

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply