Using table hints on SQL views
Posted on 2007-07-31
I have made a view from one sql server (Win2003, SQL2000) to another (Win2003, SQL2005) using linked servers. I now try executing SELECT statements with table hints like
SELECT * FROM table_name WITH (FASTFIRSTROW)
which executes correctly but when I user an extra table hint like
SELECT * FROM table_name WITH (FASTFIRSTROW, UPDLOCK)
it fails with the error
Statement(s) could not be prepared
Incorrect syntax near 'FASTFIRSTROW'. If this is intended as a part of a table hint, a WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.
There's nothing wrong with the command as I can execute it on local tables on both source and target server. Is this just a problem with views? Can it be resolved?
Thanks in advance!