query hints problem

[DrewConn]

I have a query where I need to turn off parallelism. I add the statement option(maxdop 1)to do this. When I run the query in analyzer it works fine, however when I try to run this in EM I get the error "Hints not allowd in this query type"

As I have reports tied to this view I need to get this to work in a saved view.

Any suggestions?

 

[JamesLean]

Are you trying to create a view using EM? If so, try using the CREATE VIEW statement thru QA instead. Using EM has certain limitations - this may be one of them?

--James

 

[DrewConn]

Tried that but get error message, "Incorrect syntax near the keyword 'option'."

Although if I run only the query portion with the maxdrop option there it runs fine?

 

[JamesLean]

Can you post your CREATE VIEW statement that is throwing the error?

--James

 

[DrewConn]

Sure:

create view qryLenderTurnTime_New
as
SELECT dbo.qryApp_Entered.ACCT_NBR, dbo.qryApp_Entered.ACCT_NBR_NOBIN, dbo.qryApp_Entered.AppEntered, dbo.qryApp_Entered.[ACTION],
dbo.qryFirst_Decision_Full.datetime, dbo.qryFirst_Decision_Full.Decision, dbo.qryFirst_Decision_Full.Lender, DATEDIFF(mi,
dbo.qryApp_Entered.AppEntered, dbo.qryFirst_Decision_Full.datetime) AS TurnTime

FROM dbo.qryApp_Entered INNER JOIN
dbo.qryFirst_Decision_Full ON dbo.qryApp_Entered.ACCT_NBR = dbo.qryFirst_Decision_Full.ACCT_NBR AND
dbo.qryApp_Entered.ACCT_NBR_NOBIN = dbo.qryFirst_Decision_Full.ACCT_NBR_NOBIN

option(maxdop 1)

--------------------------------------------------------
Server: Msg 156, Level 15, State 1, Procedure qryLenderTurnTime_New, Line 11
Incorrect syntax near the keyword 'option'.


thanks

 

[JamesLean]

Well, I can only assume that specifying an option in a view is not allowed (which makes sense logically I think) - I have been looking everywhere but can't find it written down!

Remember an OPTION you specify on a query is applicable for the whole query. When creating a view you still have to write a select statement to read data from it - therefore the view becomes part of a bigger query and so can't have it's own OPTION.

As I say, this is only my assumption. Maybe someone can confirm it?

--James