• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1784
  • Last Modified:

Using table hints on SQL views

Hi,
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!
Best regards
0
navipartner
Asked:
navipartner
  • 2
  • 2
2 Solutions
 
lahousdenCommented:
Maybe use OPTION (FAST n) instead of FASTFIRSTROW; e.g.:

SELECT * FROM table_name WITH (UPDLOCK)
OPTION (FAST 1)
0
 
m1tk4Commented:
I don't think UPDLOCK works for views or tables with calculated fields.  See http://msdn2.microsoft.com/en-us/library/ms187373.aspx for details
0
 
navipartnerAuthor Commented:
lahousden, unfortunately I cannot control the SQL statements because they are actually coming from Microsoft Navision (now called Dynamics) and there is no way to change these calls. So I was hoping that it was a configuration issue with the SQL view I made.

m1tk4, ok that sounds reasonable but UPDLOCK was actually just used as an example. The real problem seems to be the FASTFIRSTROW.

I made this solution before with views through Navision but that was not between two different servers. Can this be the problem?
0
 
lahousdenCommented:
Sounds likely - I have had to remove locking hints like WITH (ROWLOCK) and WITH (NOLOCK) when querying from linked servers in the past...

Best bet is to raise a trouble-ticket with Dynamics or look at their user forum (if those things are available for this product)
0
 
navipartnerAuthor Commented:
UPDATE:
I have found one of the actual statements, that Navision executes. It goes like:
SELECT  * FROM "the_view" WITH (UPDLOCK, REPEATABLEREAD, ROWLOCK)  WHERE "Key"='1'  

So now, I test with these two setups:
1. SQL Server 2000 (the view server) --> SQL Server 2005 (the target server)
2. SQL Server 2005 (the view server) --> SQL Server 2005 (the target server)

The above statement is unable to run on 1 but is able to run on 2. Also, a Navision client on the SQL 2005 view server can execute sql statements with succes. So clearly, this seems to be a problem when linking SQL 2000 with SQL 2005. Maybe it is the OLE DB Provider, maybe it is a configuration problem. It sure would be nice to know the answer :-)

Thank you for your input
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now