SQL Server COMPILE locks?

To find out, run this query below with the ObjId from your table above. There are things that you can do to mitigate stored procedure re-compiles. The biggest thing is to avoid naming your stored procedures with an "sp_" prefix, see this article on page 10.

Also avoid using if/else branches in the code, use where clauses with case statements instead. I hope this helps Edit: I believe sp_binddefault/rule is used in conjunction with user defined types (UDT). Does your view make reference to any UDT's?

SELECT * FROM sys. Objects where object_id = 1445580188.

Take a look at this link. Are you sure it's views that are blocking and not stored procedures? To find out, run this query below with the ObjId from your table above.

There are things that you can do to mitigate stored procedure re-compiles. The biggest thing is to avoid naming your stored procedures with an "sp_" prefix, see this article on page 10. Also avoid using if/else branches in the code, use where clauses with case statements instead.

I hope this helps. Edit: I believe sp_binddefault/rule is used in conjunction with user defined types (UDT). Does your view make reference to any UDT's?

SELECT * FROM sys. Objects where object_id = 1445580188.

Hunh, coming back with sp_bindefault. Edited the question summary to reflect it. It's a system sproc in the master database, and I have no idea why it's calling that.

– Chris Apr 9 '09 at 16:55 Perhaps you could put the source of the view on your post, to help get to the bottom of this. – James Apr 9 '09 at 16:58 No UDTs on the database that houses the views. About to post the view now, changed to protect the proprietary.

– Chris Apr 9 '09 at 17:11 What about TableA and TableB on the remote server? – James Apr 9 '09 at 17:15 Doesn't appear that way, no. Nothing under Types->User-defined Data Types in Management Studio for the databases.

– Chris Apr 9 '09 at 17:20.

Also, it shows resource = "TAB" = table. USE master SELECT OBJECT_NAME(1445580188), OBJECT_ID('sp_bindefault') USE mydb SELECT OBJECT_NAME(1445580188) If the 2nd query returns NULL, then the object is a work table. I'm guessing it's a work table being generated to deal with the results locally.

The JOIN will happen locally and all data must be pulled across. Now, I can't shed light on the compile lock: the view should be compiled already. This is complicated by the remote server access and my experience of compile locks is all related to stored procs.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.