One Way to Work Around the .NET Setup Project’s Picky Version Number Behavior

If you don’t change the version number of your .NET project output every time you create a setup package, you may have found that it’s difficult to have the Setup program reliably uninstall the existing version of your software before installing your update.

Here’s how to add a batch file to your setup project to accommodate this.

First, left-click to select your Setup project in the Solution Explorer, and copy the “ProductCode” field’s value (it will be a GUID) from the properties window (if you don’t see it, hit F4 to bring up the properties listing).

Next, right-click your Setup project and choose Open Folder in Windows Explorer.

Create a file there called RunMeToSetup.BAT with the following content (substituting the GUID below with the one you copied):

MsiExec.exe /uninstall {00000000-0000-0000-0000-000000000000} /qb!-
"%~dp0setup.exe"

Save the batch file.  Then go back into Visual Studio.

If your Setup project is under source control you may have to right-click it and do “Check Out for Edit…” prior to completing the next step.  (If it is not, dude WTF?)

Right click your Setup project and choose Add… File… and add the .BAT file.

Then select the .BAT file  in the list and hit F4 to show the properties window for it.  You need to change the “PackageAs” property to “vsdpaLoose” which is the non-intuitive way of saying that this should be copied into the installer directory, but not zipped up into the MSI.  (Stay classy, MSFT!)

Then when you right-click and build your Setup project, the output will be the setup.exe, the MSI file, and your new batch file.  When you double-click the new batch file, it will run a “bare” uninstaller first for your application’s GUID (non-interactive UI), and then run the setup program.  The crazy %~dp0 thing is just to work around the command-line not fully understanding UNC paths as a starting point (if you double-click the batch file starting in \\myserver\myshare, the “%~dp0setup.exe” will resolve to “\\myserver\myshare\setup.exe”).

If you wish to customize the uninstaller behavior, you can find the switches that MSIEXEC accepts here:  http://msdn.microsoft.com/en-us/library/windows/desktop/aa367988(v=vs.85).aspx

Once you test this successfully on a VM or however else you test your install software, check your setup program and the new batch file back into source control.

SmartPaster

I recently discovered this great little free Visual Studio Add-in that allows pasting text as preformatted strings in VB or C#.

Meaning it will turn this text:

This is line 1.

This is line 2.

To this when pasted:

“This is line 1.” & vbCrLf & _

“This is line 2.”

 

SmartPaster adds itself to the right-click menu and allows some basic customization.  So far so good and it has done exactly what I was looking for.

http://www.inedo.com/downloads/smart-paster

Case Sensitive searches in T-SQL

I just ran into an issue with one of my applications where a SQL statement had updated a status code field to a lower-case status code such as “active”.  We were expecting “Active” in the application and as a result our application began exhibiting odd behavior.  There are a host of things that went wrong here and that could have been done better, but it certainly does illustrate an issue with a case-insensitive database (our collation sequence is the USA default SQL_Latin1_General_CP1_CS_AS) interacting with a case-sensitive application.  Here are some ideas we came up with.

How to find the incorrect records

First off, we have to find the offending records.  I was able to run this to find the records we needed.

SELECT ID, [Status] FROM MyTable WHERE [Status] = 'active' COLLATE SQL_Latin1_General_CP1_CS_AS;

Note the COLLATE keyword refers just to the string ‘active’.  It’s not a query modifier like ORDER BY would be – instead it actually directly modifies the value that precedes it.

Sidebar: This is conceptually similar to how some implicit typing works in VB.Net – In VB, coding x=“s” implicitly declares x as a string type, but x=“s”c implicitly declares x as a character type; x=1.1F declares x as a single precision float, but x=1.1D declares x as a decimal type.  The COLLATE directive in this T-SQL statement declares the text 'active' as SQL_Latin1_General_CP1_CS_AS (case sensitive Latin ascending sort) so SQL will treat it as such when evaluating the statement.

Another note is that STATUS is a reserved word in T-SQL, so I am delimiting my field name, [Status], with square brackets. Delimiting with square brackets instead of single- or double-quotes makes SQL Server very happy – particularly the IntelliSense feature of SQL Management Studio.

So this is how you do a case-sensitive search on a SQL server with a case-insensitive sort order.  (For non-USA default SQL server installations, you should specify your current server collation and change “CI” to “CS”.)

How to fix the incorrect records (only)

I was able to fix the records in my database by using code like this.  This has the advantage of only doing updates on the rows that have the incorrect case.  To do this in a different environment, you’d have to provide a line per bad code.  I am using SET XACT_ABORT ON in this case so that the entire transaction will be stopped and rolled-back automatically in case of any errors encountered.

SET XACT_ABORT ON;

BEGIN TRAN;

 UPDATE MyTable SET [Status] = 'Active'

  WHERE [Status] = 'active' COLLATE SQL_Latin1_General_CP1_CS_AS;

 UPDATE MyTable SET [Status] = 'Inactive'

  WHERE [Status] = 'inactive' COLLATE SQL_Latin1_General_CP1_CS_AS;

 UPDATE MyTable SET [Status] = 'Withdrawn'

  WHERE [Status] = 'withdrawn' COLLATE SQL_Latin1_General_CP1_CS_AS;

COMMIT TRAN;

If you wanted to fix this in a single statement, you could do something like this:

UPDATE MyTable

  SET [Status] = CASE

    WHEN [Status] = 'active' COLLATE SQL_Latin1_General_CP1_CS_AS THEN 'Active'

    WHEN [Status] = 'inactive' COLLATE SQL_Latin1_General_CP1_CS_AS THEN 'Inactive'

    WHEN [Status] = 'withdrawn' COLLATE SQL_Latin1_General_CP1_CS_AS THEN 'Withdrawn'

  END

  WHERE [Status] IN ('active' COLLATE SQL_Latin1_General_CP1_CS_AS,

    'inactive' COLLATE SQL_Latin1_General_CP1_CS_AS,

    'withdrawn' COLLATE SQL_Latin1_General_CP1_CS_AS);

 

Notice how it is required to put the COLLATE after every string.  The “Sidebar” above will explain why.

 

How to prevent this from happening in the first place

In this situation, I didn’t have a foreign key relationship on this field, so the best I could do was enforce a constraint.  This is the constraint I put into place:

 

ALTER TABLE MyTable

ADD CONSTRAINT MyTable$ValidStatus

  CHECK([Status] IS NULL OR

    [Status] IN ('Active' COLLATE SQL_Latin1_General_CP1_CS_AS,

      'Withdrawn' COLLATE SQL_Latin1_General_CP1_CS_AS,

      'Inactive' COLLATE SQL_Latin1_General_CP1_CS_AS);

With this constraint in place, an insert or update that tried to set the value of the Status field to something other than NULL, “Active”, “Withdrawn”, or “Inactive” would fail.  I can check that my check constraint is working by querying the sys.checkconstraints view.  If is_not_trusted comes back as 0, then my constraint should be working.

 

SELECT is_not_trusted, * FROM sys.check_constraints

  WHERE name = 'MyTable$ValidStatus';

Excel for the DBA, Part 1–The Basics

This will be a series of handy hints for people that know SQL and are frustrated by Excel.  I am going to be writing this for Excel 2007 (what I currently have at work) but the tips should work for most recent versions.  The SQL stuff should also work with SQL 2000 and higher, but I have only tested on 2008.

The Basics

Run this on a test DB in your SQL Server, making sure you’re set to the default of “Results to Grid” (CRTL-D):

SELECT 1 AS [ID],'Jim' AS [Name], 'Red' AS [FavoriteColor],

CAST('20110101 14:00:00' AS DATETIME2) AS [ModifedDate],

'007' AS [SpecialCode] UNION ALL

      SELECT 2, 'Sally','Blue', '20110205 12:30:00','025' UNION ALL

      SELECT 3, 'Tim', 'Orange', '20110308 04:17:00','097'  UNION ALL

      SELECT 4, 'Todd', 'Green', '20110512 18:22:00','101'  UNION ALL

      SELECT 5, 'Ann', 'Orange','20110719 19:59:00','002'

 

 

Left-click the origin of the results (the blank space left of the “ID” column header and above row number 1) to select all the results.  Then right click in the same spot and choose “Copy with Headers”.  Launch Excel and paste the data into Sheet1 in cell A1.  You should get a mess that looks like this:

Several things can quickly be fixed up about the presentation of this data.

1. First of all, for some insane reason, Excel does not properly handle formatting date data pasted from SQL Management Studio – it assumes that the correct thing to do is format it as a time.  At least in this case, the data is still there and it’s just a presentation issue.
• Highlight column D by clicking the column D header (above where it says “ModifiedD”) and then right clicking in the same location and choosing Format Cells…
• Under the first tab, select category “Date” and then pick the closest one to what you want to see.
• If you find one that is close, but not exact, select it, and then click “Custom” where you can see the actual formatting code under the “Type:” field for customization.  “Military” SQL time format for the Gregorian calendar is yyyy-mm-dd hh:mm:ss;@
• Don’t worry about selecting the column header row when you do the formatting – Excel will usually ignore it (see below for some ways that this magic works).
2. To fix the column widths, on the other fields, left-click the origin in Excel.  This should do a “Select All” on the current sheet.  Then, double click the vertical divider between cells A and B in the column header (when you are in the right place, the pointer should look like a vertical bar with an arrow pointing left and right).  If you did it correctly, all of the columns should have auto-sized to the width of the data.  You can do this for individual columns by just not doing “Select All” first.
3. The other issue is that the leading zeros on the “SpecialCode” field have been dropped.  In this case, unfortunately, Excel has actually dropped the data and is treating the data as numeric.  There are two ways to fix this – a formula or a format.
• To fix via formula, click on cell F2 and type =RIGHT(CONCATENATE("000",E2),3) and press <ENTER>.  Assuming you were doing a select statement in T-SQL from a table with a field named E2, this would be equivalent to RIGHT('000' + CAST(E2 AS VARCHAR(30)),3) To duplicate this formula to all successive cells in the column, you can just double-click on the “bump” on the lower right of the F2 cell border when it is selected.
• The other way to fix it (since Excel actually does shred out the leading zeros) is to pre-format the column as text.  Select the column by clicking the column header for E, right-click and do Format Cells… and on the first tab, choose “Text”.  (You can also choose “Custom” and use the format code @ which basically means “whatever was in there”.).  Then go back to SQL Management Studio, reselect all and recopy the data with headers.  Then paste the data back in to Excel in A1 and it should no longer drop the leading 0s; the only difference is that now Excel puts a green triangle in the cell to warn you that the data is a “Number stored as text”.  Note that these format codes are doubly-useful for SQL DBAs as they are also used by SSRS as the number/date formatting codes.
  
4. The column headers in Excel are not differentiated from the data in any meaningful way.  Excel is a bit magic so it does make a difference to hint to it that row 1 is not data but header info.  The easiest way to do this is to click the “1” row header to select all of row 1 and hit CTRL-B to make the row bolded.  This is enough to ensure that Excel knows that these are really column names and not data.
• Note that Excel is generally pretty smart and will realize that row 1 is a header row automatically if for example you have text in all fields in row 1, but some columns have numerics or other data types in all successive columns.
5. One last handy thing is to do “Freeze Panes”.  Click Cell A2 (this is the upper left data field).  Then in Excel 2007, click the “View” ribbon tab and “Freeze Panes… Freeze Panes”.  In Excel 2003, it’s under Window… Freeze Panes.  Then if you scroll down, the column headers will remain, but only data will move. This is not super useful with a small data set, but it’s great for large ones.  Just remember to click cell A2 before you do it because anything above and to the left of the selected cell will be frozen when the feature is enabled.

Coming Next

In the next post, I will be showing you how to use VLOOKUP and some other functions in Excel.  VLOOKUP is an awesome function that allows Excel to do some fairly clever things to facilitate analyzing different sets of data (it is also how some users start to develop scary monster sheets, so be careful!).  I will also show you the Filter feature(awesome!!!) and also how to get a bit quicker with copying formulas around via the keyboard.

An Attempt to Make SSIS Package Deployment More Reasonable

Full disclosure: There might be a better way to do this.  I am not aware of it.

Problems:

1. Maintaining multiple DB environments (Local, Shared Dev, Staging, Prod) means needing to maintain multiple SSIS environments.
2. SSIS packages are tricky to configure using the existing toolset – it’s easy to accidentally use the wrong config file, or forget that you turned config files off, etc.
3. There’s insanity regarding different developers configuring different connection strings.
4. There’s no good out of the box way to say “deploy package X to environment Y”, etc.
5. There’s no good way to keep your DTSConfig files for different environments in your Source Control system.

Idea:

I want to write a script (or something) to take an SSIS package that I have built and:

1. Have a sane way to copy that SSIS package to the correct spot on a file system for the “environment” that I have selected (Local, Shared Dev, Staging, Prod).
2. Ensure that “Use SSIS Config File” is enabled on the package that is copied to that location regardless of if it is enabled on the package that I am editing in Visual Studio locally.
3. Ensure that an up-to date dtsConfig file is deployed along with the SSIS package (if desired) and also to ensure that the SSIS package is hacked to use a relative path to that dtsConfig file such as .\myconfig.dtsConfig (if desired, absolute paths also supported).
4. Allow updating the SSIS config file appropriately with connection strings appropriate for that environment.

Result:

I’ve now built such a thing.  It’s a VBScript where the top of the script looks like this (skipping Option Explicit):

---

 

CONST DtsPackage = "MyPackageName.dtsx"

CONST DtsConfig = ".\MyPackageName.dtsConfig"

CONST DeployTo = "\\MyProdServer\e$\SomePathGoesHere\Packages"

CONST DeployConfig = True

Const MaxDTSXFileAgeInMinutes = 10

Dim ConnectionStrings

ConnectionStrings = Array( "OutputTextFile","E:\SomePathGoeshere\Output.txt", _

   "DBConnection","Data Source=MyProdServer;Initial Catalog=MyDatabase;Integrated Security=True;")

 

---

That’s the production one.  The Dev one looks like this (differences highlighted):

---

 

CONST DtsPackage = "MyPackageName.dtsx"

CONST DtsConfig = ".\MyPackageName.dtsConfig"

CONST DeployTo = "\\MyDevServer\D$\SomePathGoesHere\DevPackages"

CONST DeployConfig = True

Const MaxDTSXFileAgeInMinutes = 10

Dim ConnectionStrings

ConnectionStrings = Array( "OutputTextFile","D:\SomePathGoeshere\Dev\Output.txt", _

    "DBConnection","Data Source=MyDevServer;Initial Catalog=MyDatabase;Integrated Security=True;")

---

I am able to check both of these VBScripts into source control.  This means any of my developers can use them.  The way it’s written, if DeployConfig is set to false, you don’t need to put the connection strings stuff in the script so the production info can stay secure (you’d just have to pre-stage the config file and not grant the devs access to the destination folder).

In the current VBScript incarnation, to run the deploy script, you just open the SSIS project folder, open a command prompt, type CSCRIPT<space>, drag and drop the script onto the command line and hit ENTER.  After a few seconds, your SSIS package is deployed to the correct environment.  DONE!!!  Once the script for an environment is written, this process takes about 15 seconds.  Writing the script for an environment takes 15 minutes or so just to confirm all the details, give it a quick test, and get the new script into Source Control.

Downsides:

The current version of this uses VBScript. I know, I know – 2003 called and it wants its script engine back.  I wanted to write v1 in PowerShell but I wanted to get it done more than I wanted it to be cool.  I promise that rev 2 will be in PowerShell.  This will allow the deploy scripts to really be just the config data on top and a reference to a shared ps1 file for the actual code.  The current incarnation unfortunately requires copying and pasting the main VBS body between files and that is obviously poor practice.

Next Steps:

I need some help with ideas for this and I need some help testing this out.  If you are interested and you support multiple environments/multiple servers/multiple developers and have your SSIS packages in some sort of source control system, please send me a PM on the SQLServerCentral.com forums (my handle is nycdotnet).

http://www.sqlservercentral.com/Forums/UserInfo680048.aspx

Of course this could completely crash and burn your SSIS environments so please only reach out if you’re really OK with some buggy code or issues with testing.  Thanks!

Save a step: Using Computed Columns in a Table Variable (or Temp Table)

One of the databases that I support reports all money values in both the local currency and US Dollars.  Very often we will do all of our calculations in a table variable using the local currency and then calculate USD equivalents at the very end.

With this methodology, adding a new money field to a table variable requires making four code changes – adding the local money field, adding the USD money field, writing the code to retrieve the appropriate data for the local money field, and then modifying the final update or select statement to include the USD equivalent calculation.

I recently realized that we could save a step and do our FX calculations “automatically” by just including the USD equivalent columns in our table variables as computed columns.

If you are already using temporary tables or table variables in a “Set By Agonizing Set” SBAS style (perhaps I just coined this?), using this technique may allow you to keep your code a bit cleaner.

Here is an example:

DECLARE @FXRatesToUSD TABLE (

      Currency VARCHAR(3) NOT NULL PRIMARY KEY,

      RateToUSD FLOAT);

     

--Approximate FX Rates in October 2011

INSERT INTO @FXRatesToUSD

      VALUES ('USD',1.0), ('GBP',0.62), ('NOK',5.43);

 

DECLARE @Example TABLE (

      VehicleID INT NOT NULL PRIMARY KEY,

      Currency VARCHAR(3) NULL,

      PriceLocal MONEY NULL,

      FXRate FLOAT NULL,

       --computed column in the table variable

      PriceUSD AS CAST(ROUND(PriceLocal / FXRate,-3) AS MONEY)

      );

 

--Insert sample data

INSERT INTO @Example (VehicleID, Currency, PriceLocal)

      VALUES (1, 'USD', 45000),

            (2, 'USD', 14000),

            (3, 'USD', 31000),

            (4, 'GBP', 20000),

            (5, 'NOK', 190000);

 

--take a look at the table

SELECT 'FX Rates not yet set' AS [example], * FROM @Example;

 

--merge in our FX rates

UPDATE e1

      SET e1.FXRate = fx.RateToUSD

      FROM @Example e1

      INNER JOIN @FXRatesToUSD AS fx ON fx.Currency = e1.currency;

 

--now that the FX rates are present, our USD prices are available

SELECT 'FX Rates are set' AS [example], * FROM @Example;

 

--discount one of the cars

UPDATE @Example SET PriceLocal = 18000 WHERE VehicleID = 4;

 

--You should see that both prices are updated now for vehicle 4.

SELECT 'Updated GBP and USD price for Vehicle 4' AS [example],

* FROM @Example;

 

One of the only problems with this approach is that it does not seem possible to “chain” computed columns.  If I added a maximum USD discount field such as this to the table:

MaxDiscountUSD AS PriceUSD * 0.1

I would unfortunately get this error.

Msg 1759, Level 16, State 0, Line 11

Computed column 'PriceUSD' in table '@Example' is not allowed to be used in another computed-column definition.

In this case, it would seem that this technique should only be used for very simple one-pass cases or in conjunction with deterministic scalar UDFs that modularize the underlying logic.

Tobias Ternstrom’s Great Denali Preview

Tobias Ternstrom from Microsoft gave a great talk at Tech Ed regarding upcoming features of SQL Server Denali.  His presentation was engaging, funny, and informative!

You can check it out on the Channel 9 site here (72 minutes):

http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/DBI318

I have to say that I am very excited about all of the improvements that he demonstrated for T-SQL programmers, and I’m also pleased to see that SQL Server has been upgraded to use the “Visual Studio 2010” UI.