Various object existence checks in Sql Server

Here are the basic scripts you need to check for the existence of various objects in a Sql Server database (tables, columns, etc)

Check if Database exists:

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'DataBaseName')
begin

    -- Database Exists

end

Check if table exists:

IF  EXISTS (SELECT * FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'[schema].[tableName]') 
 AND type in (N'U'))
BEGIN
    --Table exists
END

Check if foreign key exists:

IF  EXISTS (SELECT * FROM sys.foreign_keys
WHERE object_id = OBJECT_ID(N'[schema].[foreignKeyName]')
AND parent_object_id = OBJECT_ID(N'[schema].[tableName]'))
Begin
    --foreign key exists
End

Check if index exists:

IF  EXISTS (SELECT * FROM sys.indexes
 WHERE object_id = OBJECT_ID(N'[schema].[tableName]')
 AND name = N'IndexName')Begin
    --Index exists
END

Check if view exists:

IF  EXISTS (SELECT * FROM sys.views 
WHERE object_id = OBJECT_ID(N'[schema].[viewName]'))
Begin
    --View exists
END

Check if column exists:

if Exists(select * from sys.columns 
 where Name = N'columnName' 
 and Object_ID = Object_ID(N'tableName'))

begin

    -- Column Exists

end

Check if Stored Proc exists:

IF  EXISTS (SELECT * FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'[Schema].[StoredProcName]') 
 AND type in (N'P', N'PC'))
begin

    -- Stored Proc exists

end

Check if Function exists:

IF  EXISTS (SELECT * FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'[Schema].[FunctionName]') 
 AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
begin

    -- Function Exists

end

Colorado voter registration status

Do you know if you are current registered as a voter in Colorado, or if your registration status is still active? If you don’t, you can quickly check your status at the Secretary of State’s website: https://www.sos.state.co.us/voter-classic/secuVoterSearch.do?transactionType=voterSearch

And if you are not registered, you can register on the same site: https://www.sos.state.co.us/voter-classic/secuVerifyExist.do

Allocating arrays larger than 2GB in .Net

Prior to .Net 4.5, one could create arrays of a max size of 2gb even on 64 bit machines. (On 32 bit machines, this is a theoretical limit which is very hard to hit in practice, because of the level of fragmentation of the memory).

In .Net 4.5, one can now allocate arrays that are greater than 2gb in size. But before you can do that, you need to enable a setting on the machine’s runtime settings. To enable it you need to set “gcAllowVeryLargeObjects” to true. (Its set to false by default).

More info:

<gcAllowVeryLargeObjects> Element: http://msdn.microsoft.com/en-us/library/hh285054(v=VS.110).aspx

Runtime performance improvements in .Net 4.5:http://blogs.msdn.com/b/somasegar/archive/2012/05/16/net-improvements-for-cloud-and-server-applications.aspx

The GC changes were made in recognition of the importance of memory management for various kinds of server workloads.  Another such change has to do with array sizes.  In .NET 4 and earlier, no object could be larger than 2GB in size, even in 64-bit processes.  For certain workloads that use large arrays, however, this size limit can be constraining.  As such, .NET 4.5 supports lifting the limit for arrays in 64-bit processes, such that arrays may be larger than 2GB.  This means, for example, that you could allocate an array of Int32.MaxValue Int32s, even though such an array will consume 8GB.  Large objects in .NET are allocated on a special heap, known not coincidentally as the Large Object Heap (LOH);  .NET 4.5 has also seen significant performance improvements to the LOH, including usage of better algorithms for managing free memory in the heap.

-Somasegar

PowerShell: Recycle an AppPool using AppCmd

Script:

cls
$path = "$env:windir\system32\inetsrv\appcmd"
$return = ."$path" list APPPOOL /apppool.name:AppPoolName
if ($return.Contains("state:Started"))
{
    ."$path" Stop APPPOOL /apppool.name:AppPoolName
}
."$path" Start APPPOOL /apppool.name:AppPoolName

Powershell–Run Sql queries and returning data-sets

Here is how you can run sql queries that return data-sets:

function execute-Sql{
    param($server, $databaseName, $sql )
   
    $sqlConn = new-object System.Data.SqlClient.SqlConnection
    $sqlConn.ConnectionString = 'server=' + $server + ';integrated security=TRUE;database=' + $databaseName
    $sqlConn.Open()
    $sqlCmd = new-object System.Data.SqlClient.SqlCommand
    $sqlCmd.CommandTimeout = 120
    $sqlCmd.Connection = $sqlConn
    $sqlCmd.CommandText= $sql
    $text = $sql.Substring(0, 50)
    Write-Host "Executing SQL => $text..."
   
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $sqlCmd
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
   
    $sqlConn.Close()
   
    return $DataSet.Tables[0]
}

Example on how to call above script

cls;

$sqlQuery = "select sub.DomainName from SystemUserBase sub join `
SystemUserRoles sur on sub.SystemUserId = sur.SystemUserId join Role rb on rb.RoleId = sur.RoleId `
where sub.IsDisabled = 0 and rb.Name = 'System Administrator'"

$result = execute-Sql -server "CrmDbServer" -databaseName "National_Cinemedia_MSCRM" -sql $sqlQuery

$result

NotePad++, Regular Expressions and Replacement using found text

Scenario:

You have the following text and you want to copy the values in old to new

<UserMapping old="abc\dfs" new="" />
<UserMapping old="abc\adfad" new="" />
<UserMapping old="abc\sfsafsd" new="" />
<UserMapping old="abc\jdjfgg" new="" />

First you need to figure out the regex to match what you want. Here is what I have:

old="([A-Za-z\\]+)".*$

What you need to notice is that the part of the string that I want to match is inside parenthesis ( () ). This allows us to use the value in the replacement. The regex will match starting from old and end at the end of line. During the match a group will be created from whatever is in the parenthesis, in this case it will include all alphabets (upper and lower case), as well as the slash.

For the replacement, we will use the following string:

old="\1" new="\1" />

Here, notice that I use \1 for the old and new. The matched group value will be used for the old and new values.

CRM 2011–Powershell: Import-CRMOrganization

If you get a “The Deployment Service cannot process the request because one or more validation checks failed”, then know this:

Import-CRMOrganization will fail if the organization already exists. For the import to succeed, you need to drop the organization if it already exists. Wish this was documented somewhere!

Note: If you have already checked to make sure the organization doesn’t already exist, make sure that the “XRMDeployment” webservice is running under an appropriate account. Check this for more information on that: http://blogs.msdn.com/b/darrenliu/archive/2011/04/27/minimum-permissions-required-for-dynamics-crm-2011-setup-services-and-components.aspx (See the section on Deployment Service).

TopShelf–Framework for writing Windows Services

TopShelf is a .Net framework that makes it extremely simple to write Windows Services.

A simple example from their documentation shows just how easy it is to create a Windows Service from a Console based project:

 

public class TownCrier
{
    readonly Timer _timer;
    public TownCrier()
    {
        _timer = new Timer(1000) {AutoReset = true};
        _timer.Elapsed += (sender, eventArgs) => Console.WriteLine("It is {0} an all is well", DateTime.Now);
    }
    public void Start() { _timer.Start(); }
    public void Stop() { _timer.Stop(); }
}

public class Program
{
    public static void Main()
    {
        HostFactory.Run(x =>                                 
        {
            x.Service<TownCrier>(s =>                        
            {
               s.ConstructUsing(name=> new TownCrier());     
               s.WhenStarted(tc => tc.Start());              
               s.WhenStopped(tc => tc.Stop());               
            });
            x.RunAsLocalSystem();                            

            x.SetDescription("Sample Topshelf Host");        
            x.SetDisplayName("Stuff");                       
            x.SetServiceName("stuff");                       
        });                                                  
    }
}

Samsung Series 7 Slate–Installing Windows 8 RTM

I just received my Samsung Series 7 Slate that I won at the Denver Windows 8 Hackathon. There was only one problem with it, it came installed with Windows 7. No problem, it was an easy one to remedy:

First create a Windows 8 installation USB stick. (you will need to use the Windows 7 USB/DVD tool available here: http://wudt.codeplex.com/)

Insert the USB stick into the Slate.

Reboot the Slate. While the Slate is rebooting, press and hold the windows button. This will open the BIOS menu.
Use the right side “orientation” button and the volume Up/Down buttons, to go to the “Exit” tab. There you should be able to see your USB stick listed as one of the boot options.
Select it and click the “Windows” button.

This will start of the Windows 8 installer. After this, you pretty much follow along with the installation and prompts and you should be able to successfully install Windows 8 onto the Slate.

Update software:

Visit: http://www.samsung.com/us/support/owners/product/XE700T1A-A03US and download and install the Easy Settings application. Also update the BIOS from that site. Over the next couple of months you should also see updates drivers and software specifically made for Windows 8.

After thoughts:

Upgrade to Windows 8 was painless and it just worked. And Windows 8 on a touch pad device is awesome! Love it!

Windows 7 USB/DVD download tool fails to copy files

I was trying to create a bootable USB with Windows 8 using the “Windows 7 USB/DVD download tool”. It kept failing with a message that it was unable to copy the files. (I was using a 16gb USB stick).

After some searching, I found that if you make the USB stick bootable manually, then you can use the tool and it copies all the files successfully.

Here are the steps:

Open a command prompt window in administrator mode.

Run the following commands

diskpart
list disk
select disk #
      (Here replace # with the disk number. Be careful to select the correct disk number, else you may end up loosing all your data).

clean
create partition primary
select partition 1
active
format quick fs=fat32
assign
exit

Now re-run the “Windows 7 USB/DVD download tool” and you should be able to successfully create a Windows 8 installation USB stick.

Extracting CRM plugin unsecure configuration information

Here is the SQL to extract the unsecure configuration information for a CRM plugin:

Select step.* from SdkMessageProcessingStepBase as step
LEFT JOIN PluginTypeBase as pluginType on pluginType.PluginTypeId = step.PluginTypeId
LEFT JOIN PluginAssemblyBase as pluginAssembly on pluginAssembly.PluginAssemblyId = pluginType.PluginAssemblyId
LEFT JOIN SdkMessageProcessingStepImageBase as stepImage on step.SdkMessageProcessingStepId = stepImage.SdkMessageProcessingStepId
--WHERE pluginAssembly.Name like 'plugin name'

Sql Server converting Base64 to varchar and back

Especially useful to convert all the CRM data stored in the MSCRM database

Convert from base64 to varchar:

DECLARE
    @Encoded VARCHAR(max) = 'VGVzdA==',
    @Decoded VARCHAR(max)    

SELECT @Decoded = 
CAST(
    CAST(N'' AS XML).value(
        'xs:base64Binary(sql:column("bin"))'
      , 'VARBINARY(MAX)'
    ) 
    AS VARCHAR(MAX)
) 
FROM (
    SELECT CAST(@Encoded AS VARCHAR(MAX)) AS bin
) AS bin_sql_server_temp;

PRINT @Decoded 

Convert from varchar to base64:

DECLARE
    @plain VARCHAR(max) = 'Test',
    @encoded VARCHAR(max)

SELECT @encoded = 
    CAST(N'' AS XML).value(
          'xs:base64Binary(xs:hexBinary(sql:column("bin")))'
        , 'VARCHAR(MAX)'
    )
FROM (
    SELECT CAST(@plain AS VARBINARY(MAX)) AS bin
) AS bin_sql_server_temp;

PRINT @encoded