Tulsa SQL Server User Group presentation

I presented at the Tulsa SQL Server user group on 4-9-2013.  I spoke about support scripts for the app developers sql server toolbox.
For those that came, thanks for showing up and listening to me.

Attached is my presentation.
SQL Server Support Scripts

You will have to rename it from .pptx to .zip.
Wordpress won’t let up upload a zip file.
I wanted to include the sql scripts with the powerpoint presentation.

If you just want the powerpoint here is the link for that.
SQL Server Support Scripts

I’d love to hear any thoughts or feedback about it.

How to view SQL Server property information

Run the query below to find out the property information about SQL Server.
This is helpful when try to determine which features (based on the version) that are available.

 SELECT  'BuildClrVersion' ColumnName, SERVERPROPERTY('BuildClrVersion') ColumnValue
 UNION ALL  SELECT 'Collation', SERVERPROPERTY('Collation')
 UNION ALL  SELECT 'CollationID', SERVERPROPERTY('CollationID')
 UNION ALL  SELECT 'ComparisonStyle', SERVERPROPERTY('ComparisonStyle')
 UNION ALL  SELECT 'ComputerNamePhysicalNetBIOS', SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
 UNION ALL  SELECT 'Edition', SERVERPROPERTY('Edition')
 UNION ALL  SELECT 'EditionID', SERVERPROPERTY('EditionID')
 UNION ALL  SELECT 'EngineEdition', SERVERPROPERTY('EngineEdition')
 UNION ALL  SELECT 'InstanceName', SERVERPROPERTY('InstanceName')
 UNION ALL  SELECT 'IsClustered', SERVERPROPERTY('IsClustered')
 UNION ALL  SELECT 'IsFullTextInstalled', SERVERPROPERTY('IsFullTextInstalled')
 UNION ALL  SELECT 'IsIntegratedSecurityOnly', SERVERPROPERTY('IsIntegratedSecurityOnly')
 UNION ALL  SELECT 'IsSingleUser', SERVERPROPERTY('IsSingleUser')
 UNION ALL  SELECT 'LCID', SERVERPROPERTY('LCID')
 UNION ALL  SELECT 'LicenseType', SERVERPROPERTY('LicenseType')
 UNION ALL  SELECT 'MachineName', SERVERPROPERTY('MachineName')
 UNION ALL  SELECT 'NumLicenses', SERVERPROPERTY('NumLicenses')
 UNION ALL  SELECT 'ProcessID', SERVERPROPERTY('ProcessID')
 UNION ALL  SELECT 'ProductVersion', SERVERPROPERTY('ProductVersion')
 UNION ALL  SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')
 UNION ALL  SELECT 'ResourceLastUpdateDateTime', SERVERPROPERTY('ResourceLastUpdateDateTime')
 UNION ALL  SELECT 'ResourceVersion', SERVERPROPERTY('ResourceVersion')
 UNION ALL  SELECT 'ServerName', SERVERPROPERTY('ServerName')
 UNION ALL  SELECT 'SqlCharSet', SERVERPROPERTY('SqlCharSet')
 UNION ALL  SELECT 'SqlCharSetName', SERVERPROPERTY('SqlCharSetName')
 UNION ALL  SELECT 'SqlSortOrder', SERVERPROPERTY('SqlSortOrder')
 UNION ALL  SELECT 'SqlSortOrderName', SERVERPROPERTY('SqlSortOrderName')

If you want a very simple view of the version info run this query.
Select @@version as Version

How to tell what SQL objects have been added/updated recently.

Sometimes it helps to see what database objects have been added/updated recently.  If you pick up support of a new database it helps to see where the latest action has been going on. Or if you are doing development and want to make sure you have all the objects that need to get moved to the next environment.

Here is code that will return what objects have been created and when they were created *.
Just set the @dateAddedToGoBack  variable to how many days back you need.

DECLARE @dateAddedToGoBack int = 30

SELECT name, modify_date
FROM sys.objects
WHERE type IN ('P', 'V', 'U', 'PK', 'TR') --SQL_STORED_PROCEDURE, VIEW, USER_TABLE, PRIMARY_KEY_CONSTRAINT, SQL_TRIGGER
AND DATEDIFF(D,create_date, GETDATE()) < @dateAddedToGoBack

Here is code that will return what objects have been updated and when they were updated *.
Just set the @dateUpdatedToGoBack variable to how many days back you need.

DECLARE @daysUpdatedToGoBack int = 30

SELECT name, modify_date
FROM sys.objects
WHERE type IN ('P', 'V', 'U', 'PK', 'TR') --SQL_STORED_PROCEDURE, VIEW, USER_TABLE, PRIMARY_KEY_CONSTRAINT, SQL_TRIGGER
AND DATEDIFF(D,modify_date, GETDATE()) < @daysUpdatedToGoBack

* Caveat: If you are doing something like a sp_refreshview or sp_recompile it could skew your results (ie. the modify dates will be the last time run).

Where can I find Active Directory Tools

In order to get the option to manage users, computers, etc in AD you need to have the AD tools installed. 
This is a 2 step process.

The first step is you need to install the Remote Server Administration Tools for Windows 7 with SP 1.

The second step is to enable the AD tools in control.
From the control panel, click Programs and Features.
Select Turn Windows Features on or off (on the left side)

Under Remote Server Administration Tools –> Role Administration Tools, select AD DS and AD LDS Tools.
Select the Active Directory Administrative Center.
Click Ok.

 

Now under the Start menu –> Administrative Tools you will find your AD tools

Live Mesh sync error

I was using live mesh to sync my favorites, and some folders.  I had to switch live mesh accounts and when I tried to use mesh with my new I got the following error
“This folder can’t be synced. It is already being synced, contains folders that are already being synced, or is in a folder that is already being synced. Please select a different folder or move this folder to another location.”

In order to solve this I deleted everything I could from the directory C:\users\<MYUSERNAME>\AppData\Local\Microsoft\Windows Live Mesh

Then I stopped and restarted live mesh.  Then I had to setup what was synced, but I had no trouble after that.

Updated – Search for string across all fields in all tables in a database

This is an update to a previous post.

It has been updated to use table variables instead of temp tables.
This helps when you are in an environment (like production) where you don’t have permissions to create/drop temp tables.

DECLARE @Results TABLE (ColumnName nvarchar(370),   ColumnValue nvarchar(3630))

DECLARE @SearchStr nvarchar(100)

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET @TableName = ''

SET @SearchStr = 'STRING TO SEARCH FOR'

SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN
SET @ColumnName = '' SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped' ) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN

INSERT INTO @Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)

END
END
END

SELECT ColumnName, ColumnValue FROM @Results

How to add a content editor web part to a SharePoint page

Here is some code to add a content editor web part to a page.
So if you are creating a page in code (like during feature activation), then you put a content editor web part on the page.

Setting the InnerText (contentXMLElement.InnerText = "";) will set the text in the content editor, incase you want something to be there by default.  User HTML.

 

using (SPWeb webSite = SPContext.Current.Site.OpenWeb(SiteToOpen))

{

     using (SPLimitedWebPartManager mgr = webSite.GetFile("default.aspx").GetLimitedWebPartManager(PersonalizationScope.Shared))

     {

          if (mgr != null)

          {

               # region AddNewLink

               ContentEditorWebPart cewp = new ContentEditorWebPart();

               cewp.AllowClose = false;

               cewp.AllowEdit = false;

               cewp.AllowHide = false;

               cewp.AllowMinimize = false;

               cewp.ID = "ContentEditorWP";

               cewp.Title = "Content Editor Web Part";

               cewp.ChromeType = PartChromeType.None;

               //Add content to the content editor web part

               XmlDocument addNewXMLDoc = new XmlDocument();

               XmlElement contentXMLElement = addNewXMLDoc.CreateElement("Root");

               contentXMLElement.InnerText = "";

               cewp.Content = contentXMLElement;

               cewp.Content.InnerText = contentXMLElement.InnerText;

               // add the web part.   

               // first argument: web part object   

               // second argument: zone   

               // third argument: index (location within the zone)   

               mgr.AddWebPart(cewp,"left", 0);

               # endregion

          }

     }

}        

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Method to strip out special characters from a string.

Special characters can cause all kind of trouble. 
So here is a method that you pass in a string and all the special characters will be removed.

 

public static string StripSpecialCharacters(string stringToConvert)

        {

            stringToConvert = stringToConvert.Replace("~", string.Empty);

            stringToConvert = stringToConvert.Replace("#", string.Empty);

            stringToConvert = stringToConvert.Replace("%", string.Empty);

            stringToConvert = stringToConvert.Replace("&", string.Empty);

            stringToConvert = stringToConvert.Replace("*", string.Empty);

            stringToConvert = stringToConvert.Replace("{", string.Empty);

            stringToConvert = stringToConvert.Replace("}", string.Empty);

            stringToConvert = stringToConvert.Replace("\\", string.Empty);

            stringToConvert = stringToConvert.Replace(":", string.Empty);

            stringToConvert = stringToConvert.Replace("<", string.Empty);

            stringToConvert = stringToConvert.Replace(">", string.Empty);

            stringToConvert = stringToConvert.Replace("?", string.Empty);

            stringToConvert = stringToConvert.Replace("/", string.Empty);

            stringToConvert = stringToConvert.Replace("|", string.Empty);

            stringToConvert = stringToConvert.Replace("\"", string.Empty);

            return stringToConvert;

        } 

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

How to create sub sites in SharePoint 2007

Here is some code to create a subsite in SharePoint.
The important part to remember is to set AllUnsafeUpdates (and unset), otherwise it won’t work.

try
{
    SPWeb webSite = SPContext.Current.Web;

    webSite.AllowUnsafeUpdates = true;


        //Add subsite site
        webSite.Webs.Add(<WEBURL>, <TITLE>, <DESCRIPTION>, LOCALE_ID_ENGLISH, <SITE TEMPLATE>, true, false);
        
    webSite.AllowUnsafeUpdates = false;
}
catch (Exception ex)
{
    throw ex;
}

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Detect who the current user in a SharePoint web part

It’s easy to detect who the current authenticated in ASP.HET (HttpContext.Current.User), but that doesn’t work in a web part.
So in a web part, you need to open a web and then you access the current user.

Below is an example (you just have to specify the “SiteToOpen”).

 

using (SPWeb webSite = SPContext.Current.Site.OpenWeb(SiteToOpen))

{

     //Determine who the current user is

        String currentUser = webSite.CurrentUser.Name;

}

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }