Thursday, January 26, 2012

Visual Studio's Immediate Window - C# vs VB.Net

I am primarily a C# developer, I will admit up front that I dislike VB.Net and will avoid using it when I can. I am not going to get into why here because I don't want to spark all kind of needless arguments. Let's just say I am heavily biased towards C# just like I am heavily biased towards using and loving Microsoft Products. I am a Microsoft whore - it keeps me employed so I feel indebted to it...and I like it...makes me feel dirty...

So I made a grimey discovery yesterday and I want to share it here because this is a big gotcha. When I use the immediate window with C# I just type in whatever it is that I want to inspect and hit return. I started using the immediate window with VB.Net recently and I kept getting this meaningless error that read:
"Property access must assign to the property or use its value"
This is a screen capture of my immediate window. The first line is the failure and the second line is the right way to do it with VB.Net.
This was very troubling to me at first because this is not the first time I have ever used the immediate window, so I then remembered that I had to do something with a question mark, but I thought that was only for the Command Window, apparently I was wrong... kind of.

I did a google search and I found this page:

I had a head palm moment and realized that you are really supposed to use a "?" before your arguments in the immediate window. I never had to bother with this before for C#, it appears to only happen with VB.Net.

So the Gotcha of today is...
  • When using an immediate window with VB.Net you are required to start your arguments with a question mark "?".
  • When using an immediate window with C# you are not required in most instances to use a question mark before your arguments. I can't prove this, but if you look at this link you will see that they show the example with VB.Net and don't bother with C#, I think that means something.
  • When you want to evaluate a statement (IE: Set a value) in the immediate window with VB.Net, do not include the question mark "?" because you will just receive an unhelpful error. This is not required in C#.

Wednesday, January 25, 2012

Visual Studio Macros

I just wanted to share some very basic things that you can do with Visual Studio Macros. This explanation will be valid for both Visual Studio 2008 and Visual Studio 2010, I can't vouch for anything earlier because I don't use them (I used to use Visual Studio 2003 a lot up until recently...and I don't miss it). I think Macros in Visual Studio are an overlooked thing that some people just toss aside as "Why the hell would I need to use this?" well I can give you a few good examples of where it is useful. Mind you though, I am pretty sure I am only using like an 1/18th of Macros usefulness.

Why would I want to use Macros?
You would want to use Macros to save yourself time and frustration. Do you have any repetitive text that you have to type over and over? Especially for those times where you have to add a new argument to a bunch of methods or if you want to add a comments stub to something. If you answered yes, then you might want to start using Macros to help you do your repetitive typing for you.

Where do I begin?
  1. You want to start by opening the Macro Explorer which is located in: Tools > Macros > Macro Explorer, Or you can press Alt + F8.

    On a side note, if you get an error while doing this, don't worry just press the OK button. It is just complaining about a file that probably isn't there, which isn't a problem because it is going to recreate it for you anyhow.
  2. By default you will see Module1, which might as well be labeled Module1.vb because you will be building your macros in VB. In order to create a new Macro right click on Module1 and select New Macro.
  3. This action will open up what resembles another Visual Studio window that looks more like Visual Studio 2008 than anything in my opinion. This is where you will build your macros. As you can see you created a new Macro called "Macro1" which is really a method that has a return type of void (VB Terms: You created a Sub Procedure).
  4. For this first exercise we will stick with the infamous hello world example. This is what your Macro1 should look like:
        Sub Macro1()
            DTE.ActiveDocument.Selection.Text = "//Hello World!"
        End Sub
  5. When you are finished copying that in, save it, lower or close the window and go to a test space. Your test space can be ANYTHING, just any file where you can type text.
  6. Place your cursor on the document
  7. Double click on Macro1 in the Macro Explorer and as you can see it placed the "//Hello World!" text right where your cursor was placed in the text editor. Now take this concept and apply it to anything you have to type too often.
Comment Stub Example
Here is a simple example that I use often:
'This function returns my comment stub base. My Initials are EHH.
Function CommentBase() As String
    Return "'EHH - " & DateTime.Today.ToString("MM/dd/yyyy") & " - "
End Function
'Here is my Macro for placing my comment base
Sub ChangeComment()
    DTE.ActiveDocument.Selection.Text = CommentBase()
End Sub

Console Application Main Method Pause
I need this snippet when ever write console applications for testing or for anything else quick and dirty. Either way I always need this snippet.
Sub PrintConsolePause()
    DTE.ActiveDocument.Selection.Text = "//DO NOT DELETE THIS LINE"
    DTE.ActiveDocument.Selection.Text = "Console.WriteLine(""Press Any Key to Continue..."");"
    DTE.ActiveDocument.Selection.Text = "Console.ReadLine();"
End Sub

So those are the most basic examples of Macros and at this point that is all I am interested in knowing so that is all I have to share. In order to edit or delete a Macro just use the Macro Explorer window and right click on the individual Macros to maintain them. You can pretty much do everything from the Macro Editor Window.

Thursday, January 19, 2012

Windows Services and Creating a Setup Project

I want to create a constructive post on this with a full explanation on how to create a setup project for anything, my main example will use a windows service since I think there is a lot more configuration to worry about with Windows Services. In the mean time here are a bunch of great links that helped me put together my Windows Service:
  1. Search for "Create a Setup Project for a Windows Service" on this page - This gives you a step by step on how to create the installer project.
  2. This link explains that your Installer will only let you install a single instance at a time. You cannot install multiple instances using the same installer without jumping through some major hoops it seems. 
  3. If you keep getting a prompt for UserName and Password then you might want to consider making the installer default to using the LocalSystem as the user to run the windows service here is how you do that :
  4. Here is how you auto start your windows service after the installation is finished.
  5. Here is how you would specify user input during the installation but I recommend not doing this is you don't have to it just seems like way more trouble than it is actually worth doing.
Additional Tips
I realized that a huge chunk of the setup options that you are looking for are all in the properties window in visual studio. Of course that properties window will shift its focus when you select certain things so here are some of the places I found myself clicking on the most:
  • Installer Setup Project
  • Installer "ProjectInstaller.cs" Design View
  • Main Windows Service "Service.cs" Design View
  • Any objects visible on the Design View Window such as a Service Process Installer or a Timer.
By click on those things you will find a ton of options you might have otherwise missed or were trying to edit directly in the design view which is not recommended because the changes can be overwritten etc...

Very Common Questions
  • Question: How do I add the Project Installer to my Windows Service Project?

    Answer: The easiest way to achieve this is by going to the design view of your Windows Service (Service.cs etc...) right click on any of the blank space and clicking the "Add Installer" option in the context menu.
  • I don't have anymore to add at this time...

Wednesday, January 18, 2012

SQL Server: Attaching a Second Copy of a Database that Exists Already in the Same Server

This was a fun one, I couldn't find a definitive answer on this so here is mine.

How can I attach a database (MDF/LDF) to my server that has the exact same name as one of the databases in my server already?

  1. Come up with a different name for the database that is to be attached (obviously)
    • This sounds obvious, but it matters. Come up with a decent name for your database.
    • If you are trying to attach an MDF and LDF that exist already then you need to rename the MDF and LDF files as well.
  2. Find a safe place where the MDF and LDF are going to live permanently
    • This is important because while the server is running the MDF and LDF cannot be moved
    • If you stop the server and move the files later your server is going to complain
    • You can place the MDF and LDF where SQL Server stores everything by default. It looks like this: C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\
      Just make sure that the files are named uniquely.
  3. Modify the script below to your liking

USE [master]
CREATE DATABASE [Desired_Unique_Name_Goes_Here] ON 
( FILENAME = N'C:\...\DataBaseToAttach.mdf' ),
( FILENAME = N'C:\...\DataBaseToAttach_log.ldf' )

You can use the GUI to do this too, but the script is just as fast if not faster to work with if you are doing this repeatedly. I generated this script from the GUI just as an FYI.

Some Warnings
If you are doing testing with a database and you need to keep swapping out the database with an archived version, you will need to stop the SQL Server, drop the MDF and LDF in and then start the SQL Server again. You may or may not encounter a problem with accessing the database after doing that. If you cannot access the database then you need to drop the one that is displayed in the object explorer and re-attach it: Hence the script - it's just faster that way. All of the query windows that you had open will complain about nothing listening at the other end of the pipe after you re-attach the database, just ignore the complaints and press the parse button (the blue check mark), this will jog the connection kind of like jangling the handle on a toilet.

Sunday, January 15, 2012

SQL Server 2008 R2 will not install - Unhandled Exception has occurred in your application.

The post you are looking for still exists, it was just moved here:

Visual Studio 2008 Installation - Debugger Not Installed!

Some Background
Like most Microsoft installations, ever individual's experience installing the same piece of software can be very different. I must be special because I had particularly bad luck installing Visual Studio 2008. Only once did it install with no problems for me, but on many other occasions my instance would only partially install and leave out the most important part of the installation - the debugger! You know, the thing that makes Visual Studio worth using, the one thing that make Visual Studio King in the land of IDEs; aside from its other numerous achievements - the debugger is incredibly necessary in order to program effectively.

The Problem
Here is the dreadful Installer Screen of Death that I would encounter no matter what OS I installed it on. I installed this on XP, Vista and Windows7. I would install this from an ISO that I would mount on a virtual drive, not even a CD. I tried several copies and they all failed the same way.

The Solution
So on to the point of this post, in order to install the debugger yourself (which I would have to do after every failed installation) you need to navigate the Visual Studio 2008 ISO or Disc and look for the debugger installation:

Let's assume for argument's sake the CD drive or Virtual Drive is the Letter "D". Depending on your CPU architecture choose the installer path x86 vs. x64 etc...

  • D:\Remote Debugger\x86\rdbgsetup.exe
  • D:\Remote Debugger\x64\rdbgsetup.exe

I know it says Remote Debugger, but you can ignore that word since you will be requiring it anyhow. Plus if you look at the screenshot above, that is exactly what didn't install anyhow. The rest of the crap that didn't install I never needed or missed. If I did need it, I would install it just like the debugger. If you don't install the debugger - well then you simply cannot debug your code and Visual Studio will let you know this as soon as you press the play button or F5 with an annoying pop up box, which I wish I could show you, but I forgot to take a screenshot of it before fixing my installation. Take my word for it, it says something like "Remote Debugger could not be started - it is not installed."

Friday, January 13, 2012

Notepad++ Errors: Doing a full Uninstall

Misbehaving Plugins
I changed jobs and the new machine that I was using kept giving me pop up errors every time I opened up NotePad++ and it was due to some plugins that were installed. For whatever reason NotePad++ doesn't have an explicit uninstall feature for the plugins that might be in the program. So I did some quick searches on the errors I got and I came up cold, however when I went to the NotePad++ website and read some of the documentation it says that in order to uninstall a plugin you just need to delete it from the plugins directory. So I tried that, but I still had issues, so I went a few steps further and I just uninstalled NotePad++ and re-installed it which unfortunately didn't fix the problem again.

Doing a Full Uninstall Of NotePad++
This turned into an unusual problem. I solved it by taking the following steps:
  1. Run the uninstaller for NotePad++
  2. Delete (Backup first if you want) its directories located in these two places:
    • Go to the start menu and type in "%appdata%", locate the NotePad++ folder
    • Go to "C:\Program Files (x86)\Notepad++\"
  3. If you get an error while trying to delete the main Notepad++ folder then restart your computer because you have the Notepad++ shell extension installed. The dll that controls that won't be released until you restart your computer. After restarting delete the folder again
  4. Install the latest copy of NotePad++
What you accomplish by doing all of this is fully uninstalling NotePad++ and then installing it again, that way you remove anything that might have been causing the issues.

Pop Up Errors I Received
If anyone is interested I was getting errors for Snippets and Pork2Sausage. For Pork2Sausage an "ini" file could not be found, even when I created it. With regards to Snippets I was getting two errors, one that explicitly said "Not an Error" and another one that said "A proper database could not be loaded".

Tuesday, January 10, 2012

Straight to the Point: Query for Database and Table Sizes

I love how I have to search too much to find simple information, one liners, it is just like in College where my college course book would be about 100,000,000,000 pages long and really it says absolutely nothing. The damn book could be condensed down to about 1/10th of it's size and be more useful. In other words a lot of people out there that write articles about how to do something TALK TOO MUCH just get to the fucking point.

How to get the DataBase size and other Info
EXEC sp_spaceused -- This will give you the size of the database, don't provide arguments (0 arguments)

How to get an Individual Table's size and other Info
EXEC sp_spaceused 'TableNameHere' -- This will give you the size of a particular table, (1 argument)

How to get All Tables' sizes and other Info
EXEC sp_MSforeachtable @command1 = 'EXEC sp_spaceused ''?''' -- This is for all tables, the '?' serves as a wild card

The idea of the above is that the SP "sp_MSforeachtable" will execute another SP for all tables in the DataBase. The downside is that it will also return each result in its own result set. In .Net terms that translates into a DataSet, so I took that point and wrote a powershell script to iterate through all that stuff and dump all of the rows into a single table and then into a CSV file...

How to export this data to a single data result and CSV
[string]$query = "EXEC sp_MSforeachtable @command1 = 'EXEC sp_spaceused ''?''' ";

#You can use which ever method you prefer
#$connString = "Data Source=localhost,port;Initial Catalog=DataBaseNameHere;Integrated Security=True"
$connString = "Data Source=localhost,port;Initial Catalog=DataBaseNameHere; uID=userName; password=1234"

#setup your connection object
$connection = new-object$connString);

#get the adapter object
$adapter = new-object$query, $connection);

#initialize the DataSet object
$set = new-object;

#fill the DataSet object

#initialize the DataTable object
$table = new-object;

#Don't bother with the next operation if the DataSet is empty
if($set.Tables.Count -gt 0)
 #declare a DataRow object
        [System.Data.DataRow]$dr = $null;
        #Get the structure of the table once so it doesn't have to be done manually
 $table = $set.Tables[0].Clone();
        #for each DataTable in the DataSet transfer each row over
 foreach($dt in $set.Tables)
  #Create a new row
                $dr = $table.NewRow();
                #For my needs I needed to specify the column names, but you can make this more generic by
                #iterating through each column by index. There is only a single row per table, so the 0 
                #is hard coded on purpose
  $dr["name"] = $dt.Rows[0]["name"];
  $dr["rows"] = $dt.Rows[0]["rows"];
  $dr["reserved"] = $dt.Rows[0]["reserved"];
  $dr["data"] = $dt.Rows[0]["data"];
  $dr["index_size"] = $dt.Rows[0]["index_size"];
  $dr["unused"] = $dt.Rows[0]["unused"];  

                #Finally add the new row to our table

#This line will auto format a table
$table | format-table -AutoSize;

#This line will export the table results to a CSV file
$table | Export-Csv ".\TableSizes.csv";

BAM Straight to the point, that's how you do it!

Query SQL Server using PowerShell

I don't understand why it was so difficult for me to find an example on this, so I compiled one from three posts so I could put it here:

[string]$query = "SELECT TOP 10 * FROM TableName "

#I'm using integrated security here:
$connString = "Data Source=localhost,port;Initial Catalog=DBNameHere;Integrated Security=True"

#If you want to use a UN/PW use this code:
#$connString = "Data Source=localhost,port;Initial Catalog=DBNameHere; uID=userName; password=1234"

#Create a new connection object like you would always
$connection = new-object$connString);

#Setup the adapter object like you would always
$adapter = new-object ($query, $connection)

#Instantiate a DataSet (I included this just for the demo, I usually use a DataTable directly)
$set = new-object

#Fill the DataSet

#Instantiate a DataTable
$table = new-object

#This table gets the value of the first table in the DataSet
$table = $set.Tables[0]

#This line will auto format our table
$table | format-table -AutoSize


Monday, January 9, 2012

Selecting Random Date Between a Date Range with tSQL

I had a very interesting experience recently trying to figure out how to select a random date between a date range using tSQL, it was a lot of trial and error, but ultimately this is what I came up with. It is only as random as the RAND() function will allow it to be unfortunately.

Read up on the function here: MSDN tSQL RAND()

WARNING When using the RAND() function with a specified seed for multiple results/rows in a select statement, unfortunately the RAND() function with a specified seed will yield the same result each time. To get a different result for each row you must unfortunately use a loop (while, cursor, etc...). I think this is highly inconvenient, but it is the truth. Examples: SELECT (RAND(100)*100 - RAND(100)*100) will yield zero, where as SELECT (RAND() - RAND()) will yield a random number.

Here are some of the basics first about how to work with this stuff:
/* Some of the basics first */
-- Get the int value for today's date
Select cast(getdate() as int) -- 40913
Select cast(cast('1 Jan 2010' as datetime) as int) -- 40177

Next this is a worked out version of selecting random dates based on a range.
/* Selecting a random date based on a range */

-- Get the High date int value
Select cast(cast('1/6/2012' as datetime) as int) -- 40912

-- Get the Low date int value
Select cast(cast('1/6/2010' as datetime) as int) -- 40182

-- Compute the Difference
Select 40912 - 40182 -- 730

-- Get a number that is not higher than the higher limit and not lower than the lower limit
-- High - Random * Difference
SELECT (40912 - RAND()*730) -- Random limited multiplier
SELECT cast(cast((40912 - RAND()*730) as int) as datetime) -- Selecting random date based on limited multiplier

Here is a full script to make this work:
declare @intDateDiff int,  -- The numerical difference between dates
        @intDtmHigh int,   -- The numerical representation of the High Date
        @dtmLow datetime,  -- The Low Date
        @dtmHigh datetime; -- The High Date

-- I always initialize my variables, I have run into avoidable hard to trace problems when I haven't in the past
set @intDtmHigh = 0;
set @intDateDiff = 0;
set @dtmHigh = GETDATE(); -- I just happened to use GETDATE(), you can use whatever you want
set @dtmLow = DATEADD(YEAR, -2, GETDATE()); -- I put a negative 2 to indicate a two year spread, change to whatever you want

-- Get the High date int value
Select @intDtmHigh = cast(@dtmHigh as int);

-- Subtract the Low date int value from the High Date int value
Select @intDateDiff = (@intDtmHigh - cast(@dtmLow as int));

-- Select the random date
Select cast(cast((@intDtmHigh - RAND()*@intDateDiff) as int) as datetime);

As usual, writing something semi-complicated in tSQL is a giant pain in the ass, but expected...