Restoring an MDF/LDF from Another Machine

Posted by Dan | Posted in SQL Server | Posted on 09-08-2013

0

If you need to restore an MDF/LDF from one machine (source) into another machine (target), you copy the source’s MDF/LDF file to the target machine. Then start the target server in single user mode. Then run SSMS as Administrator on the target machine and restore the files. If you don’t run it as Admin, you’ll get the error listed here:

http://stackoverflow.com/questions/2330439/access-is-denied-when-attaching-a-database

http://stackoverflow.com/questions/4661013/sql-server-attaching-database-access-denied

Who’s Locking The Table

Posted by Dan | Posted in SQL Server | Posted on 09-08-2013

0

This is the script I use to view who/what is locking the table in SQL Server:

SELECT  l.request_session_id AS SessionID,
        o.Name AS 'Table Locked',
        DATEDIFF(SECOND, AT.Transaction_begin_time, GETDATE()) AS 'Duration (Seconds)',
        FORMAT(AT.Transaction_begin_time, 'yyyy-mm-dd h:mm:ss tt') 'Started On',
        es.login_name 'User',
        es.[program_name] 'Program',
        COUNT(*) AS Locks
FROM    sys.dm_tran_locks l
        JOIN sys.partitions p ON p.hobt_id = l.resource_associated_entity_id
        JOIN sys.objects o ON o.OBJECT_ID = p.OBJECT_ID
        JOIN sys.dm_exec_sessions es ON es.session_id = l.request_session_id
        JOIN sys.dm_tran_session_transactions st ON es.session_id = st.session_id
        JOIN sys.dm_tran_active_transactions AT ON st.transaction_id = AT.transaction_id
WHERE   resource_database_id = DB_ID()
        AND o.Type = 'U'
GROUP BY AT.Transaction_begin_time,
        l.request_session_id,
        o.Name,
        es.login_name,
        [program_name]

Filesharing and transfering across EC2 Instances

Posted by Dan | Posted in AWS | Posted on 09-08-2013

0

By default, the port is off for file transfer. You have to enable the ports (on both instances you want to share across) on the servers:

139
445

Delete TFS Cache from Local Machine

Posted by Dan | Posted in Visual Studio | Posted on 09-08-2013

0

A lot of times, TFS acts flaky and the best thing to do is to clear the local cache.

DELETE THE CONTENTS OF:

C:\Users\[User]\AppData\Local\Microsoft\Team Foundation\5.0\Cache

Rename Computer Name in Case TFS Gives you Workspace Problems

Posted by Dan | Posted in Visual Studio | Posted on 09-08-2013

0

When you are setting up TFS through Visual Studio, and are trying to map a drive, TFS may give you an error saying that you can’t and that profile exists on the TFS server. It may be because the account+computername+workspace (they’re all bound) is already on the server.

The way around this is to rename the machine. This may have happened because the virtual machine was copied.

Be aware: IF YOU DO THIS, other programs that have the computer name saved to it under privileges, like SQL Server, may have to be changed. In SQL Server, you may have to login via the sa account or through single-user mode.

Invalidate (re-cache) Content on AWS CloudFront

Posted by Dan | Posted in C# | Posted on 09-08-2013

0

This is a handy script I use to invalidate (re-cache) content on AWS’s CloudFront.

using System;
using System.Collections.Generic;
using System.Configuration;
using Amazon;
using Amazon.CloudFront.Model;
using Amazon.S3.Model;
 
namespace AwsConsoleApp1
{
  internal class Program
  {
    /// <summary>
    /// Invalidates only one file. e.g. /images/profile.gif
    /// </summary>
    /// <param name="file"></param>
    public static void InvalidateFiles(string file)
    {
      InvalidateFiles(new List<string>() { file });
    }
 
    /// <summary>
    /// Invalidates only one file. Enter bucket name and key (file in the bucket).
    /// </summary>
    /// <param name="bucket"></param>
    /// <param name="key"></param>
    public static void InvalidateFiles(string bucket, string key)
    {
      InvalidateFiles(new List<string>() { bucket + "/" + key });
    }
 
    /// <summary>
    /// Invalidates a list of files. e.g. /images/profile.gif, /home/index.html
    /// </summary>
    /// <param name="files"></param>
    public static void InvalidateFiles(List<string> files)
    {
      // AWS client. You always create one.
      var cfClient = AWSClientFactory.CreateAmazonCloudFrontClient();
 
      // Create paths to files we want to invalidate
      Paths invalidationPaths = new Paths();
      // invalidationPaths.Items = new List<string>() { "/images/dan1g.zip" };
      invalidationPaths.Items = files;
      invalidationPaths.Quantity = invalidationPaths.Items.Count;
 
      // Now... let's build the request...
      CreateInvalidationRequest invalidationRequest = new CreateInvalidationRequest();
      invalidationRequest.DistributionId = ConfigurationManager.AppSettings["DistributionID"];
 
      // ... and other required paramaters
      invalidationRequest.InvalidationBatch = new InvalidationBatch();
      invalidationRequest.InvalidationBatch.Paths = invalidationPaths;
      invalidationRequest.InvalidationBatch.CallerReference = Guid.NewGuid().ToString();
 
      // Execute the request and get back a response object.
      CreateInvalidationResponse response = cfClient.CreateInvalidation(invalidationRequest);
 
      // Starting
      System.Console.WriteLine("Initiated On: " + response.CreateInvalidationResult.Invalidation.CreateTime);
      System.Console.WriteLine("InvalidationID: " + response.CreateInvalidationResult.Invalidation.Id);
      System.Console.WriteLine("Status: " + response.CreateInvalidationResult.Invalidation.Status);
    }
 
    /// <summary>
    /// Check if a file exists on S3.
    /// </summary>
    /// <param name="bucket"></param>
    /// <param name="key"></param>
    /// <returns></returns>
    public static bool IsFileInBucket(string bucket = "", string key = "")
    {
      bool exist = true;
      var s3Client = AWSClientFactory.CreateAmazonS3Client();
      try
      {
        GetObjectResponse oResponse = s3Client.GetObject(new GetObjectRequest() { BucketName = bucket, Key = key });       
      }
      catch (Exception e)
      {
        Console.WriteLine("\n" + e.Message);
        exist = false;
      }
 
      return exist;
    }
 
    /// <summary>
    /// Main Caller.
    /// </summary>
    /// <param name="args"></param>
    public static void Main(string[] args)
    {
      if (args.Length == 2)
      {
        string bucketName = args[0];
        string key = args[1];
 
        if (args != null && IsFileInBucket(bucketName, key))
        {
          InvalidateFiles(bucketName, key);
        }
        else
        {
          Console.WriteLine("\n\nDone.");
        }
      }
      else
      {
        Console.WriteLine("\nUse:\n   S3-Invalidate BUCKETNAME KEY\n\ne.g.\n   S3-Invalidate images photo.gif\n");
      }
 
      // Console.ReadKey();
    }
  }
}

List All EC2 Instances

Posted by Dan | Posted in C# | Posted on 09-08-2013

0

Been playing around a lot with the AWS API. Going to post a few handy snippets every once in a while.

Typically, you start EC2 instances by ID. In this case, we’re doing it by tags (e.g. the name of the instance).

namespace EC2_Instance
{
  class Program
  {
    public static void StartInstance(AmazonEC2Client c, string InstanceID)
    {
      StartInstancesRequest r = new StartInstancesRequest();
      r.InstanceId.Add(InstanceID);
      c.StartInstances(r);
    }
 
    public static void StopInstance(AmazonEC2Client c, string InstanceID)
    {
      StopInstancesRequest r = new StopInstancesRequest();
      r.InstanceId.Add(InstanceID);
      c.StopInstances(r);
    }
 
 
    public static void Main(string[] args)
    {    
 
      AmazonEC2Client ec2Client = new AmazonEC2Client();   
      var instancesRequest = new DescribeInstancesRequest();
 
      instancesRequest.Filter.Add(new Filter() { Name = "tag:Name", Value = new List<string> { "*" } });
 
      DescribeInstancesResponse statusResponse = ec2Client.DescribeInstances(instancesRequest);
 
      foreach (var item in statusResponse.DescribeInstancesResult.Reservation)
      {
        Console.WriteLine(item.RunningInstance.ElementAt(0).Tag.Where(x => x.Key == "Name").First().Value);
        Console.WriteLine(item.RunningInstance.ElementAt(0).InstanceId);
        Console.WriteLine("\n");
      }     
 
      Console.ReadLine();
      Console.WriteLine("\n\nDone.");
    }
  }
}

Immediate Window in VB.NET

Posted by Dan | Posted in C#, Web Development | Posted on 09-08-2013

0

If you’re in debugging a VB.NET execution in the Immediate Window, to view the value you use the question mark:

? strpassword

Otherwise you’ll get :

Property access must assign to the property or use its value.

Search For Column Name in All Databases

Posted by Dan | Posted in Databases, SQL Server | Posted on 10-18-2012

0

Whipped up this little script today to search for a column in all my databases.

--------------------------------------------------------------------------------------------------
-- Search for the column name
--------------------------------------------------------------------------------------------------
DECLARE @searchFor AS VARCHAR(50)     = 'CustomerID' -- keyword to search for
DECLARE @databaseList AS VARCHAR(MAX) = 'Database1,Database2,Database3,Database4' -- comma delimited
 
DECLARE @orderBy AS VARCHAR(50)       = 'DateCreated ASC' -- You can also append a space and the ASC/DESC
-- Order By One of These
-- ObjectID, ColumnName, Table, Database, Date Created, Modified Date
--------------------------------------------------------------------------------------------------
 
DECLARE @SQL NVARCHAR(MAX) = ''
-- loop
SET @databaseList = @databaseList + ','
DECLARE @pos AS INT
DECLARE @val AS VARCHAR(255)
 
WHILE PATINDEX('%,%', @databaseList) <> 0
BEGIN       
  SELECT  @pos = PATINDEX('%,%', @databaseList)
  SELECT  @val = LEFT(@databaseList, @pos - 1)       
  SELECT  @databaseList = STUFF(@databaseList, 1, @pos, NULL)
  SET @SQL = @SQL + 
             'SELECT  *
              FROM    ( SELECT  portalColumns.OBJECT_ID AS ObjectID ,
                                portalColumns.[NAME] ColumnName ,
                                ''[' + @val + '].dbo.['' + portalTables.[NAME] + '']'' AS [Table] ,
                                ''[' + @val + ']'' AS ''DatabaseName'',                  
                                create_date AS ''DateCreated'' ,
                                modify_date AS ''ModifiedDate''
                        FROM    [' + @val + '].sys.columns portalColumns
                                INNER JOIN [' + @val + '].sys.tables portalTables ON portalColumns.object_id = portalTables.object_id
                        WHERE   portalColumns.name LIKE ''%' + @searchFor + '%''
                      ) AS Results
              UNION '                       
END
 
SET @SQL = LEFT(@SQL, LEN(@SQL) - 5) + ' ORDER BY ' + @orderBy
 
--PRINT @sql    
 
EXECUTE SP_EXECUTESQL  @SQL

Testing JavaScript Code in Sublime Text with Node.js

Posted by Dan | Posted in JavaScript | Posted on 04-07-2012

0

Testing JavaScript code can easily be done now with today’s browser in console mode. If you want to this within an IDE like Sublime Text from the press of a button, read on. :)

The first thing you’ll need is a JavaScript interpreter like Node.js. Get it from http://nodejs.org/ . Then in Sublime Text go to:

Tool -> Build System -> New Build System

Create a new build system file. In this example, we’ll use JavaScript.sublime-build

By default, it’ll create it under the path:

C:\Users\Dan\AppData\Roaming\Sublime Text 2\Packages\User

Once you do that, configure the build file using the settings from the Sublime Text official docs.

So your config file will look like this:

{
  "cmd": ["C:/Program Files (x86)/nodejs/node.exe", "-p", "$file"]  
}

and that should be it.

So now when you press F7, the build system will run and the output will be shown like so: