SQL Server Performance Forum – Threads Archive

Index/Lock Hints on a Remote Server

Does anyone have an article or link to one on why this cant be done in SQL 2000 (but could on earlier versions). We’re upgrading another of our servers and its one of the things I have to go through all our scripts to verify/change. I want to be able to explain to the developers ‘why’. Its a planned change for 2000 because using it produces a sql error
7377 Cannot specify an index or locking hint for a remote data source.
but there is no more detail on the error message. Thanks
Chris

Did you try using the OPENQUERY Syntax? OPENQUERY Syntax was very helpful in solving some problems with DTS that I had. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Chris KBAhttp://support.microsoft.com/default.aspx?kbid=323711 used to address this issue as its a bug that would not allow to run distributed query on remote server, but somehow its unavailable currently. For temp. time try SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED (for testing purpose), as the negative affects of this statement is high. If this is a production issue to be resolved then I suggest to open a case with MS PSS as its a bug reported, but KBA is unavailable. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Yeah, I could find hints of the KB article around but not the article itself.
I’m more likely going to work through them (as necessary) and write the remote part of the select into an sproc on the originating server (when its necessary).
I think in part this was intenional on MS part. I think they may have felt that sql2000 was ‘smarter’ than the average developer so wanted to allow sql to control remote locking. Not sure. But its very annoying. Thanks all.
Chris

I just tried using the OPENQUERY implementation and it works!!! Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

We ran into the same issue. How we worked around it was to create a view with the nolock statement and then call that view cross server. Sjon

quote:Originally posted by ChrisFretwell Does anyone have an article or link to one on why this cant be done in SQL 2000 (but could on earlier versions). We’re upgrading another of our servers and its one of the things I have to go through all our scripts to verify/change. I want to be able to explain to the developers ‘why’. Its a planned change for 2000 because using it produces a sql error
7377 Cannot specify an index or locking hint for a remote data source.
but there is no more detail on the error message. Thanks
Chris

Yup That works as well! Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Yeah, we’re doing that and in some cases using sprocs, but I still get asked by the developers why its this way in 2000 and not in 7 and was hoping for the official word from MS. Thanks everyone.

Chris Have you tried contacting MS PSS in this regard? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>