Random Technical Thoughts

April 10, 2013

Tulsa SQL Server User Group presentation

Filed under: SQL, T-SQL — Tags: — chrisbarba @ 8:21 am

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.

February 22, 2013

How to view SQL Server property information

Filed under: T-SQL — Tags: — chrisbarba @ 10:52 am

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

February 6, 2013

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

Filed under: SQL, T-SQL — Tags: — chrisbarba @ 9:34 am

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).

January 25, 2013

Where can I find Active Directory Tools

Filed under: Active Directory — Tags: — chrisbarba @ 9:22 am

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)
image

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

 

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

July 31, 2012

Live Mesh sync error

Filed under: Uncategorized — Tags: — chrisbarba @ 9:12 pm

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.

December 6, 2011

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

Filed under: SQL — Tags: , — chrisbarba @ 2:16 pm

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

April 9, 2011

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

Filed under: SharePoint, Web Part — Tags: , — chrisbarba @ 10:40 pm

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.

Filed under: C# — Tags: — chrisbarba @ 10:12 pm

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

Filed under: SharePoint — Tags: — chrisbarba @ 10:07 pm

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; }

February 4, 2011

Detect who the current user in a SharePoint web part

Filed under: SharePoint, Web Part — Tags: , — chrisbarba @ 11:12 pm

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; }

Older Posts »

Theme: Silver is the New Black. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: