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

Question

Post reply

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

rojas9198

Old Hand

Points: 395
More actions
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,

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

rojas9198 Old Hand Points: 395 More actions · Answer 2

rojas9198

Old Hand

Points: 395

April 15, 2011 at 7:44 am

#1312145

You got it! Thanks.

GOODS Ten Centuries Points: 1360 More actions · Answer 3

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))

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

GOODS Ten Centuries Points: 1360 More actions · Answer 5

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))

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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

GOODS Ten Centuries Points: 1360 More actions · Answer 7

GOODS

Ten Centuries

Points: 1360

October 17, 2012 at 4:54 am

#1549952

Thanks mil got it:-D..