Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

"Incorrect syntax near 'AS'. Expecting ID, '(', or PRIMARY" Expand / Collapse
Author
Message
Posted Friday, April 15, 2011 12:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 15, 2011 12:35 PM
Points: 3, Visits: 4
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,
Post #1093918
Posted Friday, April 15, 2011 2:46 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 42,814, Visits: 35,936
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 2008, MVP
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

Post #1093975
Posted Friday, April 15, 2011 7:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 15, 2011 12:35 PM
Points: 3, Visits: 4
You got it! Thanks.
Post #1094151
Posted Wednesday, October 17, 2012 1:16 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 21, 2014 6:56 AM
Points: 50, Visits: 212
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))
Post #1373653
Posted Wednesday, October 17, 2012 1:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 42,814, Visits: 35,936
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 2008, MVP
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

Post #1373659
Posted Wednesday, October 17, 2012 2:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 21, 2014 6:56 AM
Points: 50, Visits: 212
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))
Post #1373675
Posted Wednesday, October 17, 2012 2:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 42,814, Visits: 35,936
You haven't finished the last join in the from clause.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1373681
Posted Wednesday, October 17, 2012 4:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 21, 2014 6:56 AM
Points: 50, Visits: 212
Thanks mil got it..
Post #1373727
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse