Tulsa Techfest – SQL Support Scripts presentation

Thanks to Tulsa Tech Fest for letting me present.
Attached is my presentation about SQL Support Scripts.

In the notes are the links to the scripts.
Tulsa TechFest 2013 Presentation

I hope you find something valuable to use.

Advertisements

List all Primary Keys in a database

This script will list all primary keys in a database.

SELECT ST.name AS TABLE_NAME, SKC.name AS CONSTRAINT_NAME, SC.name AS CONSTRAINT_COLUMN_NAME, SC.column_id AS COLUMN_ORDER 
FROM sys.key_constraints AS SKC 
INNER JOIN sys.tables AS ST ON ST.object_id = SKC.parent_object_id 
INNER JOIN sys.index_columns AS SIC ON SIC.object_id = ST.object_id AND SIC.index_id = SKC.unique_index_id 
INNER JOIN sys.columns AS SC ON SC.object_id = ST.object_id AND SC.column_id = SIC.column_id WHERE (LEFT(ST.name, 1) = N't') AND (ST.type = N'U')
[tweetmeme only_single=”false”]

List of all views and scripts to create

Here is a script to list all views in a database and provide the script to create them.  This helps when moving views to new environments.

SELECT a.name AS VIEW_NAME, LEFT(b.definition, 4000) AS SQL1, SUBSTRING(b.definition, 4001, 4000) AS SQL2, LEN(b.definition) AS SQL_Length 
FROM sys.sql_modules AS b 
INNER JOIN sys.views AS a ON b.object_id = a.object_id 
[tweetmeme only_single=”false”]

Simple command to disable all Foreign Key checks

After blogging about how to disable all Foreign Key constraints and the undocumented sp for looping through all tables in a database, I thought about combining the two.

So I’ve now got a much easier way to disable FK constraints in a database.
The script below will loop through each table and disable the FK check.

--Disable FK Checks
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

I’ve got an application that has a SQL CE offline mode where the Foreign Key keyword is not supported when running scripts against it.  (At least that’s what I’ve been told by the original developer).

[tweetmeme only_single=”false”]

How to create a new site based on a custom site template

Here is a method to create a sub site based on a custom site template.  You need to pass in your new site name and the name of the site template.  It will check the available site templates for the one you specified (so you must deploy the site template first).  If it’s not there an error gets displayed.  I put a label on my web part to display the error _lblErrMsg.  You will need that control (or you can remove it and handle errors whatever way you want.

private bool CreateSubSite(string newSiteName, string ProjectsiteTemplateName)
      {
          try
          {
              SPWeb webSite = SPContext.Current.Web;

              webSite.AllowUnsafeUpdates = true;

              //Get all the site templates
              SPWebTemplateCollection Templates = webSite.GetAvailableWebTemplates(Convert.ToUInt32(LOCALE_ID_ENGLISH));

              //Get the specific project template
              SPWebTemplate siteTemplate = null;

              //Verify the custom site template exists
              if (templateExists(Templates, ProjectsiteTemplateName))
              {
                  siteTemplate = Templates[ProjectsiteTemplateName];
              }
              else
              {
                  _lblErrMsg.Text = "Could not find the custom site template.";
              }

              if (siteTemplate != null)
              {

                  SPWeb newWeb; 
                  newWeb = SPContext.GetContext(HttpContext.Current).Web.Webs.Add(
                      newSiteName.Trim(),
                      newSiteName,
                              "Project Site",
                              LOCALE_ID_ENGLISH, siteTemplate,
                              true, false);

                  //Inherit navigation from parent site
                  newWeb.Navigation.UseShared = true;

                  newWeb.Update();

                  siteCreated = true;

                  webSite.AllowUnsafeUpdates = false;

              }

              webSite.Close();

          }
          catch (Exception ex)
          {
              this._lblErrMsg.Text = ex.Message.ToString();
          }

          return siteCreated;
      }
[tweetmeme only_single=”false”]

How to deploy a feature through PowerShell

Every time I go to deploy a feature from outside Visual Studio I forget the PowerShell commands.
I found the Corey Roth has a great blog entry about it and I end up going there.

So I decided to finally write up what I do.  After using stsadmin for so long it’s hard to make the adjustment to PowerShell.

Here is the first step to add a Farm Solution.
Add-SPSolution <File Path>\<WSP Filename>
(For ex, Add-SPSolution C:\Users\Administrator\Desktop\Deploy\Deploy_Service_Lists.wsp)

The next step is to install the solution.
Install-SPSolution –Identity <WSP Filename> p  –GACDeployment
(For ex, Install-SPSolution –Identity Deploy_Service_Requests_Lists.wsp  –GACDeployment)

*If you use Add-SPUserSolution it will be sandboxed solution.  Which I’ve done before at a client and I couldn’t find the solution because I used the wrong command.

** Workflows are a little different because they are not scoped at the farm level.  Here is the link to deploy a workflow.

[tweetmeme only_single=”false”]

How to add your own WebPart properties

If you are creating your own part you might want to add a property for the users to set when editing the webpart.

At the top of your web part class add the following code (before any methods).

[FriendlyName("Project Site Template")]
[Personalizable(PersonalizationScope.Shared)]
[WebBrowsable(true)]
public string ProjectsiteTemplateName { get; set; }

This will add a property (in my case called Project Site Template) for the users to set when editing the web part.

This allows the user configure the web part for what you need (instead of hard coding some things).

[tweetmeme only_single=”false”]

SharePoint generic handle web part error

If you have web part that has code behind to do something (like add data to a list) and it has an exception during processing you need a way to display that error.
Here is a generic method I use to display the error.
I put this in all my catches.  It will add a new literal control with the error.

        /// <summary>
       /// Clear all child controls and add an error message for display.
       /// </summary>
       /// <param name="ex"></param>
       private void HandleException(Exception ex)
       {
           this._error = true;
           this.Controls.Clear();
           this.Controls.Add(new LiteralControl(ex.Message));
       }
[tweetmeme only_single=”false”]

Generic handle error method

Here is a generic handle error method I put in my catches.  It will send an email with the inner exception unless it’s null then you get the exception message.  The stack trace is included.

You should the subject from “Application Name” to whatever your application is.
Also you should check out a previous post about a generic send mail method since this method calls that one.

private static void HandleError(Exception ex)
        {
            String errorMessage = String.Empty;

            if (ex.InnerException != null)
            {
                errorMessage = ex.InnerException.ToString();
            }
            else
            {
                errorMessage = ex.Message.ToString();
            }

            var trace = new System.Diagnostics.StackTrace(ex);

            StringBuilder errorBodyString = new StringBuilder();

            errorBodyString = errorBodyString.Append("Exception:" + errorMessage);
            errorBodyString = errorBodyString.Append("<br />");
            errorBodyString = errorBodyString.Append(trace);

String Subject = "Application Name";
            String Body = errorMessage;

           // sendNotification(Subject, errorBodyString.ToString());
        
[tweetmeme only_single=”false”]

Enable/Disable table indexes

Sometimes you need to disable indexes (and then re-enable them).
Here is the sql to disable indexes on a table.

ALTER INDEX ALL ON <TABLENAME> DISABLE;

Here is the sql to enable indexes on a table.

--Reenable indexes 
ALTER INDEX ALL ON <TABLENAME> REBUILD ;

Notice that to enable the indexes you just need to do a rebuild.

[tweetmeme only_single=”false”]