SQL Server 2005/2008 ; Cannot create or update statistics on view because both FULLSCAN and NORECOMPUTE options are required

SQL Server 2005/2008


Failed:(-1073548784) Executing the query "UPDATE STATISTICS [dbo].[v_errorlog]
WITH SAMPLE 50 PERCENT,NORECOMPUTE

Cannot create or update statistics on view because both FULLSCAN and NORECOMPUTE options are required

Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The problem is due to the fact that the maintenance plan was trying to update the stats on views too. And on a view we can not update the stats with anything other than full scan.
The maintenance plan was configured to run update stats on all the databases. But when you do that, you do not get the choice to pick if you want to exclude or include tables/views or specific tables/views.

It pretty much runs the plan against all DBs, all tables and all views. You can exclude the views only if you select one database at a time. If you have limited number of databases in the instance then create a maintenance plan like the following.

1)       Create two ‘Update statistics’ tasks for each database.
2)       The first task for each database will update the statistics only on tables. You can choose the  
           sampling percentage in this step.
          
3)       The second task  for each database will update the statistics only on views. But make sure you 
           pick full scan option in this step.
4)       Repeat step 2 and 3 for each database.

If it is not possible to create multiple tasks due to the large of databases in the instance, then you will have to write a script to this. If you need help then let us know.

SQ Server 2005/2008 : TRUNCATE Command over a linked server

So today I came across something that I thought seemed like a simple task. A client wanted to run certain commands on a remote server via a linked server. So I created a linked server for him and granted the required read/write permissions to his ID on the remote server.
But he reported that he was not able to run any TRUNCATE commands on the remote server.
Upon further investigation found that you need ALTER rights on the tables that you want to truncate. I assumed that TRUNCATE was a DML statement but that does not seem to be the case. It's considered as a DDL statement and the Login requires ALTER permissions on the table. So I was able to grant that access and his Login could truncate tables when he logged into the server. But when he executed the TRUNCATE commands on the linked server it would not let him do that. The error that he received was as follows.

Error
Msg 4701, Level 16, State 1, Line 1
Cannot find the object "TableXYZ" because it does not exist or you do not
have permissions.

After looking over the net for similar errors I found out that you can not run TRUNCATE statement on a remote server via a linked server. But fortunately someone had figured out a work around and I thought I will share this with you.

You can run a RPC for sp_executesql and run the truncate statement which seems to work. So first I change the settings for the linked server to run remote procedure by settings RPC to TRUE. Then executed the following command.

execute mylinkedserver.dbname.dbo.sp_executesql "TRUNCATE TABLE dbo.TableXYZ"