Wednesday, September 14, 2016

Concatenating the same column from different rows in a sql query

SELECT DISTINCT a.Author,
 Books = STUFF(( SELECT ', ' + BookTitle
   FROM Books as b
   WHERE b.Author=a.Author 
   ORDER BY BookTitle
   FOR XML PATH('')
   ), 1, 1, '')
FROM Books as a
INNER JOIN (
 SELECT Author, COUNT(BookTitle) as BookCount
 FROM Books
 WHERE NOT Author IS NULL
 GROUP BY Author
 HAVING COUNT(BookTitle) > 5) as c ON a.Author=c.Author

Thursday, October 1, 2015

Query for suggesting potentially useful indexes for the database

Here is a query I found useful for determining which indexes should be created on a table. It is only good for the tables that have been in use, not the brand new ones.
SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) 
AS [index_advantage] ,
migs.last_user_seek , 
mid.[statement] AS [Database.Schema.Table] ,
mid.equality_columns , 
mid.inequality_columns , 
mid.included_columns , migs.unique_compiles , 
migs.user_seeks , 
migs.avg_total_user_cost , 
migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK ) 
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK ) 
ON migs.group_handle = mig.index_group_handle 
INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK ) 
ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY index_advantage DESC

Wednesday, September 30, 2015

Reading data from Azure Storage Blob into a string

The code below I used to read the text from the file located on Azure blob into a string:
string filename ="mytargetfile.txt"
string blobConn = CloudConfigurationManager.GetSetting("BlobConn");
CloudStorageAccount storageAcct = CloudStorageAccount.Parse(blobConn);
CloudBlobClient blobClient = storageAcct.CreateCloudBlobClient();
CloudBlobContainer blobContainer = blobClient.GetContainerReference("container1");
if (blobContainer.Exists(null, null))
{
    CloudBlockBlob blockBlob = blobContainer.GetBlockBlobReference(filename);
    if (blockBlob.Exists(null, null))
    {
         //read data from the blob into a stream and get it into an array
         using (var ms = new MemoryStream())
        {
             blockBlob.DownloadToStream(ms);
             ms.Position = 0;
             using (var reader = new StreamReader(ms, Encoding.Unicode))
             {
                   var fileText = reader.ReadToEnd();
             }
        }
    }
}




Tuesday, September 29, 2015

Saving file from Azure Storage Blob onto a hard drive using C#

Recently started working with Azure storage blob. So far I am really enjoying it so I wanted to share some code snippet I have wrote so far for various purposes. The one below I have used to save the file from the blob onto my hard drive.
//reading blob file data into a file on your hard drive
string filename ="mytargetfile.txt"
string blobConn = CloudConfigurationManager.GetSetting("BlobConn");
CloudStorageAccount storageAcct = CloudStorageAccount.Parse(blobConn);
CloudBlobClient blobClient = storageAcct.CreateCloudBlobClient();
CloudBlobContainer blobContainer = blobClient.GetContainerReference("container1");
if (blobContainer.Exists(null, null))
{
     CloudBlockBlob blockBlob = blobContainer.GetBlockBlobReference(filename);
     if (blockBlob.Exists(null, null))
    {
          string tempFile = Path.Combine(@"C:\BlobFiles",filename);
          blockBlob.DownloadToFile(tempFile, FileMode.Create);
    }
}

Wednesday, September 2, 2015

How to loop through multiple cookies from an HttpWebResponse

Dim request As HttpWebRequest = CType(WebRequest.Create(args(0)), HttpWebRequest)
request.CookieContainer = New CookieContainer()
Dim response As HttpWebResponse = CType(request.GetResponse(), HttpWebResponse)

' Loop through the cookie collection and display each property 
Dim _cookie As Cookie
For Each _cookie In  response.Cookies
     Console.WriteLine("Cookie:")
     Console.WriteLine("{0} = {1}", _cookie.Name, _cookie.Value)
     Console.WriteLine("Domain: {0}", _cookie.Domain)
     Console.WriteLine("Path: {0}", _cookie.Path)
     Console.WriteLine("Port: {0}", _cookie.Port)
     Console.WriteLine("Secure: {0}", _cookie.Secure)

     Console.WriteLine("When issued: {0}", _cookie.TimeStamp)
     Console.WriteLine("Expires: {0} (expired? {1})", _cookie.Expires, _cookie.Expired)
     Console.WriteLine("Don't save: {0}", _cookie.Discard)
     Console.WriteLine("Comment: {0}", _cookie.Comment)
     Console.WriteLine("Uri for comments: {0}", _cookie.CommentUri)
     Console.WriteLine("Version: RFC {0}", IIf(_cookie.Version = 1, "2109", "2965"))

     ' Show the string representation of the cookie.
     Console.WriteLine("String: {0}", _cookie.ToString())

     ' Show sessionId of the cookie.
     Console.WriteLine("SessionId: {0}", _cookie.Value)
Next _cookie


Friday, July 24, 2015

Windows 8 Hell or why my Internet Explorer does not function properly

Have you ever opened your Internet Explorer (on Windows 8 computer) and find that it has an address bar at the bottom and is always maximized? Well, I did. I have been using my laptop for a while but only a few days ago I came to worked, opened IE and saw this new and unwelcome change. I tweaked it around for a while until magically things were back to normal. And here is what I found - apparently Windows 8 had two versions of Internet Explorer, the hellish one (tile version) and... well, the normal one. To open the normal one, open it by clicking on the Desktop tile and then click IE icon in the taskbar. If it is not there, go to all programs, right-click on Internet Explorer and select "Pin to Taskbar". Hope this will help someone

Thursday, July 23, 2015

How to pass multiple variables to the Arguments property of a Execute Process Task

  1. Right-click anywhere in the Control Flow window and select "Variables"
  2. Create whatever number of variables you will need to pass to your task
  3. Double-click on the Execute Process Task and open it in Editor
  4. Select "Expressions". On the right-hand side, click elipse to open Property Expressions Editor
  5. In Property dropdown select "Arguments", and in Expressions click on elipse and to open Expression Builder
  6. In expression window type your expression. (Example: @[User::Variable1] + " " + @[User::Variable2] + " " @[User::Variable3])
  7. When done click "Evaluate Expression" button then click OK to close the editor

Note: If variables you create are not of type String, you will have to cast them to String in your expression