Our sister project for IT Pros – All-In-One Script Framework – is releasing 7 new T-SQL script samples based on frequently asked questions in the SQL Server forums.   Considering that many developers are writing T-SQL scripts too, we hope that the scripts could be useful to you.

http://blogs.technet.com/b/onescript/archive/2012/10/16/microsoft-all-in-one-script-framework-sample-release-on-october-15th.aspx 

 

--------------------------------------------------------------

SQL Server Script Samples

image

 

Enroll SQL Server instances on multi server into an existing SQL Server Utility

Download: enrollinstances.zip

http://gallery.technet.microsoft.com/scriptcenter/Enroll-SQL-Server-66687842

Description: This PowerShell sample script is used to enroll SQL Server instances on multi server into an existing SQL Server Utility. This script applies to SQL Server 2008 R2 and SQL Server 2012 editions.

User Scenarios: SQL Server Utility can manage the SQL Server environment as a whole through the concept of application and multiserver management in it, so after created one utility control point, administrator need to enroll instances of SQL Server into the SQL Server Utility. If your task included in below two scenarios, this script will be one good assistant.

  • There are multiple managed instances need to be enrolled tothe SQL Server Utility.
  • We can store the server and instance names in one .txt file.

Acknowledgement: Thanks Stephanie Lv for collecting the script idea.  Thanks Sharp Wang for producing the script sample.

 

Check SQL Server missing KB2277078 to prevent leak of security audit entries

Download: CheckSQLInstanceVersionNumber.zip

http://gallery.technet.microsoft.com/scriptcenter/Check-SQL-Server-missing-ac29a3ed

Description: This script will demo how to check SQL Server missing update KB2277078 to prevent leak of security audit entries in TokenAndPermUserStore.

User Scenarios: When we create and enable a SQL Server audit on a computer that is running Microsoft SQL Server 2008 R2 or Microsoft SQL Server 2008, the TokenAndPermUserStore cache store continues to grow in size. Additionally, the size does not decrease even when we run the DBCC FREESYSTEMCACHE command, this issue occurs because a memory leak occurs when a SQL Server audit is enabled on an object. To fix it, for different versions, we should install the corresponding updates.

    Acknowledgement: Thanks Sharp Wang for collecting the script idea and producing the script sample.

     

    How to retrieve the top N rows for each group

    Download: TopNRowsofGroup.zip

    http://gallery.technet.microsoft.com/scriptcenter/How-to-retrieve-the-top-N-248cdc67

    Description:  This script illustrates how to retrieve the top N rows for each group. The first solution can be used with SQL Server 2000 and upward versions, the second solution applies to SQL Server 2005 and upward versions with common table expression feature and Row_Number() function.

    User Scenarios:

    For example, there is a sample table that has three columns and eight rows:

    ProductID   CategoryID  UnitPrice

    ----------- ----------- -----------

    1           1           100

    2           1           25

    3           1           46

    4           1           22

    5           2           11

    6           2           44

    7           2           21

    8           3           15

    If we want to see the three of the most expensive (UnitPrice) products for each category in the table, the expected result should be:

    ProductID   CategoryID  UnitPrice

    ----------- ----------- -----------

    1           1           100

    3           1           46

    2           1           25

    6           2           44

    7           2           21

    5           2           11

    8           3           15

    Acknowledgement: Thanks Jian Kang for collecting the script idea.  Thanks Sharp Wang for producing the script sample.

     

    Publish report to Reporting Services in SharePoint Integrated Mode

    Download: PublishReportInIntegratedMode.zip

    http://gallery.technet.microsoft.com/scriptcenter/Publish-report-to-0ca69662

    Description: This VB.NET sample script illustrates how to publish a report from a computer path to Reporting Services in SharePoint integrated mode. This script applies to SQL Server Reporting Services 2008 R2 integrated with SharePoint 2010.

    User Scenarios: Starting from SQL Server 2008 R2, the rs utility is supported against report servers that are configured for SharePoint integrated mode as well as servers configured in native mode. Today, most of the available script samples correspond to the native mode.  This sample script will ease the problems in below two scenarios:

    The report server configured to SharePoint integrated mode.

    The published report use embedded data source or shared data source.

    Acknowledgement: Thanks Challen Fu for collecting the script idea.  Thanks Sharp Wang for producing the script sample.

     

    Bulk set the Timeout property of the reports that in one specified folder

    Download: bulksetTimeout.zip

    http://gallery.technet.microsoft.com/scriptcenter/Bulk-set-the-Timeout-10dcd062

    Description: This Visual Basic.NET sample script illustrates how to bulk set the Timeout property of the reports that in one specified folder via SSRS web services. This script applies to SQL Server Reporting Services 2005, 2008, 2008 R2 and 2012 versions in native mode.

    User Scenarios: Manually, we can make use of Report Manager to modify the Report Timeout property of the reports one by one, but if we have a lot of reports which need to apply this setting, don't we want to replicate in doing this tedious action? The answer is no, we can author a VB.NET script and save it into a .rss file, then take use of RS utility to execute this script with specified parameters. As a result, we can bulk apply this setting to multiple reports. This script will ease IT profs' work in these two scenarios:

    • Need to set this property for multiple reports
    • These reports organized in the same folder or path

    Acknowledgement: Thanks Bill Lu for collecting the script idea.  Thanks Sharp Wang for producing the script sample.

     

    Add one user/group to a specified Reporting Services item

    Download: addpolicy.zip

    http://gallery.technet.microsoft.com/scriptcenter/Add-one-usergroup-to-one-59b89695

    Description: This Visual Basic.NET sample script illustrates how to add one user or one group to a SQL Server Reporting Services item by referencing SSRS web services. This script applies to SQL Server Reporting Services 2005, 2008, 2008 R2 and 2012 versions in native mode.

    User Scenarios: Add a user or a group and grant certain permission on specify item is one question frequently meet in Reporting Services administration. Traditionally, IT professionals can finish this task in Report Manager web site. Actually, using one rss script with variables can also achieve the same effect as using UI.

      Acknowledgement: Thanks Sharp Wang for collecting the script idea, and producing the script sample.

       

      Get properties of the objects that in multiple SQL Server instances

      Download: PropertiesOfObjectsInMultipleInstances.zip

      http://gallery.technet.microsoft.com/scriptcenter/Get-properties-of-the-41d810f6

      Description: This Windows PowerShell sample script helps you to get the properties of the objects in multiple SQL Server instances or SQL Server groups.  It applies to SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012.

      User Scenarios: IT administrators frequently ask this question in the TechNet forums: how to get all logins/databases/agent jobs and their properties of multiple SQL Server instances?  In traditional ways, we can write T-SQL query and obtain the expected results one by one.  By using the following script, you can resolve the problems in below scenarios:

      • The objects belong to different instances or SQL Server group.
      • For different object, you would like to obtain the different properties.
      • Output the results in individual files for each object.

      Acknowledgement: Thanks Stephanie Lv for collecting the script idea.  Thanks Sharp Wang for producing the script sample.