.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Policy Management - Use of ExecuteSQL() for Index facet to define naming convention policy

Posted By:      Posted Date: October 18, 2010    Points: 0   Category :Sql Server

I am trying to define an advanced policy involving use of executeSql() function and @@SchemaName, @@ObjectName parameters. It is related to non-clustered index naming convention and as such I am making use of 'Index' facet.  I have defined policies for tables and procedures and I have successfully used executesql with @@SchemaName and @@ObjectName parameters. However, when I run Index policy I get the following error;

TITLE: Microsoft SQL Server Management Studio

Exception encountered while executing policy 'CompanyName - Index - Naming Convention'.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=PolicyEvaluationException&LinkId=20476


Must declare the scalar variable "@@SchemaName". (Microsoft SQL Server, Error: 137)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft&#

View Complete Post

More Related Resource Links

Policy Management ensure a table name is in the index name

I am trying to write some policies to enforce our coding standards.  One of the standards we have is to prefix indexes with IX_<tablename>; the IX_ is easy to do with the index facet.  How do you access the tables name in the condition?
Thanx, Adam

using policy based management to exclude database access

After reading information on the link http://www.mssqltips.com/tip.asp?tip=1492 I was wondering whether its possible to have a policy in place such that database A cannot make reference to objects in database_B, I am guessing that one would have a policy such that the contents of the objects must not be like %database_B.%   Let me know whether there is a better way to do this. Thanks.

Policy based management : what rights do ##MS_PolicyTsqlExecutionLogin## need

Hi, In order to run Policies on scheduled basis, what permissions should I grant to ##MS_PolicyTsqlExecutionLogin## login? Problem is: When I evaluate the policies myself, they dont fail, but when I schedule them, they fail reporting ##MS_PolicyTsqlExecutionLogin## login doesn't have access to <> database. Any help please?

Information Management Policy - Expiration not working

I've defined a new policy for my list.
The policy has expiration enabled.
The expiration date is WarningDate (required list field - date and time) + 0 days and the expiration should trigger the custom workflow, which would send the warning, but is now only setting the status to Warned.
The workflow works fine when executed manually, but never seems to be actually executed by the item expiration.

I've also updated the "information management policy" job to run every 5 minutes, but it didn't help.
What as I missing?

Policy-Based Management and On Change - Prevent




I’m working on Policy-Based management feature and I’m keen to implement some of the features. I tried to setup a Policy for a “backup device” facet based on the condition that the name starts with bkd_. I tried to set the evaluation mode to “On Change – Prevent” but unfortunately I could not find the mode at all in the Policy Screen. I also made sure the “Nested Triggers” advanced Option is set to 1.


I went through this  link http://blogs.msdn.com/b/sqlpbm/archive/2009/04/13/policy-evaluation-modes.aspx and checked for the execution_mode in  msdb..sysp

SQL Server 2008 Management Studio - "Show policy health state for all nodes"

What does "Show policy health state for all nodes" option do in SQL Server 2008 under the Object Explorer?

Screen Shot

SQL Server 2008 R2 Enhancements to Policy-Based Management


I'm reasearching enhancements which have been made to Policy-Based Management in SQL Server 2008 R2.  I"ve found several articles and posts on PBM in general, but nothing that explicitly describes what enhancements have been implemented in SQL 2008 R2 which are not available in SQL 2008.  Does anyone know of any white papers or articles which discuss this?

Information Management Policy & Disposition Workflow


I would like to set an information management policy that enbles expiration of documents in my document library. I then want this to kick off a disposition workflow that will then allow the owner of the content (i.e. the last person to modify the document, or creator if it has not been modified) to either delete the document or do nothing. This is all simple to do. My question is: When the disposition workflow kicks off, does it send an email to the document owner or does it simply ceate a task in the specified task list?


If it only creates a task, is it possible to notify the document owner that a task has been created? I don't want docment owners to have to have to keep checking the task list, I want an email sent to them whenever a document is about to expire. Is this possible?

Using ExecuteSQL to create a new policy in SQL Server 2008


Trying to create a new Policy in Policy Based Management within SQL Server 2008 to show any users with elevated privileges.

Uisng the following SQL:


('String', 'SELECT p1.[name] as principal_name,p1.[type_desc],p2.name as role_name

FROM [master].[sys].[server_principals] p1

join [master].[sys].[server_role_members] r

on p1.principal_id = r.member_principal_id

join [master].[sys].[server_principals] p2

on r.role_principal_id = p2.principal_id

where p2.type_desc = ''SERVER_ROLE''

and p2.name <> ''sysadmin''

and not exists

(select *

from uhtdba.dbo.server_role_member_exception ex

where ex.principal_name = p1.name

and ex.role_name = p2.name)


Policy based management missing evaluation modes


For some reason, I only get the on demand and on schedule evaluation modes with my instance of SQL server, what is even more suprising is the fact that i am following a step by step guide and the guide seems to have the on change log only evaluation mode.

Is this a bug or something ?

I am using SQL 2008 SP1 developer edition, I get the same issue even on my standard edition.

Information Management Policy & Exempt from Policy flag


Hi all,


does anyone know why the "Information Management Policy" in MOSS sets a 'yes' flag (in the 'exempt from policy' column) on the document library automatically when the specific document expires? 


And more importantly is there a way of getting around this?


What I am trying to achieve is a periodic document review schedule without having to create custom workflows in Visual Studio, and preferably sticking with the out of the box approval workflow to ease the rollout of team sites from a site template.


I thought this could be accomplished by using the Information Management Policy on the DL, but I guess not if after the first review date arrives the document is then exempt from any following dates.





Information Management Policy to clean up the Workflow History List?


I have several InfoPath 2007 forms (with workflows) that are heavily used in my MOSS 2007 environment, and the some of the Workflow History lists are very large need to be cleaned up.  I'm trying to use an (Expiration) Information Management Policy to delete items in the Workflow History list that are Last Modified + X days , but haven't had much success.  Has anyone else had success doing this?


Defining information management policy on a library not working


I've created a WF in SPD that generates a task to copy an expired item to a Records Center. The WF works properly.  I then defined an IMP on the library to fire off the WF when the expiration date is reached. But nothing happens. The IMP never fires the WF. Any ideas?


Chris G.

Information Management Policy not triggering SPD workflow


Hi All,

I have a document library with no special fields (i.e. Lookups) and I have created a workflow via SPD to create a task to review the document and send to a Records Center, if necessary. The WF is working properly.

I then set an Information Management Policy on the library through the SharePoint UI following these steps:

1. Define a policy...
2. Policy Statement: This document expires in 1 day (testing)
3. Checked "Enable Expiration ":
       The retention period is: Modified + 1 + days
       When the item expires:
       Start this workflow: IMP WF Test 2

But the IMP never triggers the workflow.

  • I have checked the "Information Management Policy Configuration" via Central Administration, and "Expiration" is available and set to run daily.
  • I have tried the "Process Expired Items Now" manual option via the CA "Configure Expiration" page.
  • I have tried this with "Require Check Out" enabled and disabled with no success in either case.
  • I have tried this with Publishing features enabled and disabled with no success.
  • I have combed this forum thoroughly looking for similar situations and possible resolutions w

Policy Management for a user using a specific application


I am new to Policy Management and still reading up on things. That said, I'm looking to see if it is possible to limit a user to logging in using a specific application. Example: I have a service account that is used to access database A using a .Net Program. I want to prevent someone from using this service account and running queries directly from Management Studio or any other program and only allowing access to this specific .Net Program. I've gone through several of the Facets and I'm not seeing where it specifies the application the user connection is using.

Is this something that is possible?

Big Ern

Information Management Policy Settings link not display


I am trying to enable Information Management in Sharepoint  server 2010 but when i go to the Library or List Settings page there is no link to Information Management Policy Settings on the page.

I've been searching for an hour and can find no information to tell me how to enable this option.  Any hints would be appreciated.


PIAB And WCF: Integrating the Policy Injection Application Block with WCF Services


Using the PIAB you can enhance WCF services with policies such as validation, performance monitoring, authorization and caching without having to change a line of code.

Hugh Ang and David San Filippo

MSDN Magazine February 2008

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend