sp_recompile problems

Question

sp_recompile problems

Ruggiero Lauria

SSC Veteran

Points: 296
More actions
November 15, 2007 at 5:22 am

#126990

Hy guys,
have a big problem. I made a recompile of all of views on a db. Now we have some Access.adp programs accessing the database, when i try to access views trough access give me strange errors, no problem using enterprise manager.
The only way I found right now to make things work again is to drop the view and recreating it. Tryied also sp_refreshview but ahas no effect.
Any suggestion?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What are those 'strange errors'?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ruggiero Lauria SSC Veteran Points: 296 More actions · Answer 2

two kind of syntomps when in access you click on a view:

1) it simply doesn't open, anything appens.

2) gives error saying there is some missing field or table

I repeat on enterprise manager everything work fine

Tks

Ruggiero Lauria SSC Veteran Points: 296 More actions · Answer 3

I know that sp_recompile mark's the obiect to recompiled to the next execution, now i wonder what does it change that put in crysis a ADP project using views?

Ruggiero Lauria SSC Veteran Points: 296 More actions · Answer 4

another issue trying to open a view: field field_name is readonly

Ruggiero Lauria SSC Veteran Points: 296 More actions · Answer 5

Ok gone up with my analisys, but still have problems, sp_recompile mainly does

a dbcc LockObjectSchema (obj_name) - undocumented!

This option locks out schema changes until commit. Now I think this kind of lock gives problems to ADO connections, like access ADP projects , and inibhits them full objects accesses.

It's very difficoult to find documentation any help is very apreciated

tskelley SSCommitted Points: 1870 More actions · Answer 6

You could try a sp_refreshview. Below is a script to loop thru all your views:

http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31902

Just to be sure, sp_recompile is for stored procedures and triggers. From BOL: If object is the name of a table or view, all the stored procedures that reference the table or view will be recompiled the next time they are run.

Typically, Access treats the view as read only when it cannot identify a primary (or composite) key.

Hope this helps,

Tom