I was deploying my MVC3 application into a production that did not have the proper DLLs in the GAC.  So I used the handy little tool in Visual Studio that allows you to include the DLLs in the BIN folder.  Simply right-click on the project in Visual Studio and select Add Deployable Dependencies.

image

But when I deployed the project into production, it kept attempting to redirect me to Account/Login, which does not exist.  And I have not routed those values anywhere.

It turns out that the WebMatrix.Data.dll was copied into my BIN directory and into production.  This DLL is the culprit.  I removed all instances of this DLL from my project, and the issue went away.

image

Advertisements

We had a reported application issue in which the user was receiving a “Request entity is too large” over SSL only.  When accessing the same application with the same data over regular HTTP, everything worked fine.

Upon further research, we determined that over SSL, the entire request entity body must be preloaded during negotiation.  In addition, SSL will use the value of the UploadReadAheadSize metabase property to validate the request size.  http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/7e0d74d3-ca01-4d36-8ac7-6b2ca03fd383.mspx?mfr=true

The UploadReadAheadSize metabase property specifies the number of bytes that a Web server will read into a buffer and pass to an ISAPI extension or module. This occurs once per client request. The ISAPI extension or module receives any additional data directly from the client.

To fix this issue, the UploadReadAheadSize metabase property value needs to be increased.  Plesae note that the default value for the UploadReadAheadSize is 49152.  The maximum size for this property is 2147483647.

In this example, we will increase the value to “204800”.

  1. On the web server, open the command prompt Run –> CMD.EXE.
  2. Change directories to the C:\Windows\SysWOW64\inetsrv (assumes 64-bit) –> CD C:\Windows\SysWOW64\inetsrv
  3. Determine the current metabase property value: appcmd.exe list config –section:system.webServer/serverRuntime
  4. Increase the metabase property value:  appcmd.exe set config -section:system.webServer/serverRuntime /uploadReadAheadSize:”204800″
    /commit:apphost

image

Even though most browsers are configured to suppress Javascript errors, you want to ensure that you handle these errors effectively.

The best way to do this is to put all of your Javascript code within try/catch statements:

function HandleBillingCodeFieldChange(idx)
    {
        try
        {
            $(“#bc_changed_” + idx.toString()).val(“1”);
        }
        catch(Exception){}
    }

In addition to that, it is always a good idea to have an overall Javascript error suppressor.  You probably only want to enable this in production, however, because it makes it more difficult to detect and fix Javascript errors in development:

function silentErrorHandler(){return true;}
window.onerror=silentErrorHandler;

I recently ran into an issue in which I was loading a SharePoint view into an iFrame in my application.  It was loading just fine, but it was throwing out a random JS conflict error, even though I had taken the above precautions.

I even tried adding an onerror event to my iFrame in an attempt to suppress the error, but it didn’t work.

I was, however, able to find the workaround for this in IE by adding a security=”restricted” attribute to my iframe:

http://”MySource.aspx”

Follow these steps to clear your Windows 7 clipboard:

  1. Right-click on your desktop, and select New –> Shortcut
  2. Copy and paste the following command into the shortcut:

    cmd /c “echo off | clip”

  3. Choose Next.
  4. Enter a name for this shortcut such as Clear My Clipboard
  5. Double-click the shortcut anytime you want to clear your clipboard.

I was attempting to create a modularized approach to a set of calculations in SQL Server.  The result was the dreaded “Nested stored procedure” error.  I had to use stored procedures instead of functions because I was using a cursor.

I figured that I could resolve this issue pretty easily if I could only pass in a TABLE variable to one of my stored procedures.  At first glance, this did not appear to be a possibility until I discovered TYPES.

TYPES are one of those features that is not readily apparent in SQL Server, because I cannot see them in the SQL Server Manager.  For my purposes, a TYPE is like a named table variable in SQL Server.  And because it is a named variable, you can pass it into a stored procedure just like any other variable.

Here is an example of creating a TYPE in SQL:

CREATE TYPE MonthlyTimesheetEntries AS TABLE
(
timesheet_id int identity,
month_date datetime,
consultant_id int,
consultant_name varchar(50),
billing_code int,
billing_code_name varchar(50),
entry_date datetime,
total_hours money,
billing_rate money,
total_amount money
)

If you need to add or modify any of the fields defined in the type, you can simply drop the TYPE and recreate it, assuming you don’t have any dependencies.

DROP TYPE MonthlyTimesheetEntries

Please Note:  Once you pass the TYPE into a stored procedure, you can no longer DROP it.  To work around this, I backup my stored procedure, delete it.  Drop and recreate the type, and then recreate my stored procedure.

Once you have created the TYPE, you can use it as a stored procedure parameter:

// you must pass it in as read only

CREATE PROCEDURE [dbo].[sCalculateMonthlyInvoiceAmount]
@month_date datetime,
@timesheet_entries ManagedServiceMonthlyTimesheetEntries READONLY
AS
BEGIN

END

And you can simply populate and pass in the TYPE like this:

// populate type

DECLARE @timesheet_entries MonthlyTimesheetEntries
DECLARE @month_date datetime

SET @month_date = ‘8/1/2012’

INSERT INTO @timesheet_entries
(

     timesheet_id,
month_date,
consultant_id,
consultant_name,
billing_code,
billing_code_name,
[entry_date],
[total_hours],
[billing_rate],
total_amount
)
EXEC sGetTimesheetEntries @month_date

// pass in type

EXEC sCalculateMonthlyInvoiceAmount @month_date, @timesheet_entries

When I am populating a data snapshot table with a set of calculations for any given month, I may in some cases blow away any existing records for that month and start with a clean slate.

The only thing that bothered me was that I would end up with huge gaps in my identity keys.  This resets the identity’s seed value, so that the next inserted row will pick up right where the last identity field left off.

I use this a lot for my data snapshot tables, but as a general rule, it’s not a good idea to reseed an identity column in a production table without some serious checks and balances.

Here is the statement, where the item in single quotes is the name of the table you want to RESEED:

DBCC CHECKIDENT (‘my_report_table’, RESEED)

You can also explicitly set the next seed value with this statement:

DBCC CHECKIDENT ('my_report_table', reseed, 1001)

In some cases, I like to use SQL Server Functions to encapsulate some of my SQL Server logic into reusable and maintainable components.

Recently, I have found the use of Table-valued Functions to be extremely helpful.  With Table-valued functions, you can return multiple values, which is useful enough in itself, or even entire result sets.

The following is an example of creating a simple Table-valued Function that prorates a monthly amount based on the number of days which have elapsed.  Notice that the return value is a TABLE variable.

CREATE FUNCTION [dbo].[fnProrateMonthlyAmount]
(
@Month datetime,            — month for which the proration will be calculated
@DurationStart datetime,    — start date of the proration duration
@DurationEnd datetime,      — end date of the proration duration
@MonthlyAmount money,       — monthly amount to be prorated
@MonthlyHours money         — monthly hours to be prorated
)

RETURNS @RtnValue table
(
TotalDays int,             — total days in month
TotalAmount money,         — total monthly amount
ProratedDays int,          — number of prorated days
HoursPerDay money,         — number of hours per day
ProratedHours money,       — number of prorated hours
AmountPerDay money,        — dollar amount per day
ProratedAmount money       — prorated dollar amount
)
AS
BEGIN

    — declare variables
DECLARE @MonthEnd datetime
DECLARE @TotalDays int, @ProratedDays int
DECLARE @HoursPerDay money, @ProratedHours money
DECLARE @AmountPerDay money, @ProratedAmount money

— initialize variables
SET @Month = DATEADD(DAY, -(DATEPART(DAY, @Month)-1), @Month)
SET @MonthEnd = DATEADD(DAY, -1, DATEADD(MONTH, 1, @Month))
SET @TotalDays = DATEPART(dd, @MonthEnd)
SET @ProratedDays = DATEDIFF(DAY, @DurationStart, @DurationEnd) + 1
SET @AmountPerDay = ROUND(@MonthlyAmount/@TotalDays, 2)
SET @HoursPerDay = ROUND(@MonthlyHours/@TotalDays, 2)
SET @ProratedAmount = @MonthlyAmount
SET @ProratedHours = @MonthlyHours
IF @TotalDays > @ProratedDays
BEGIN
SET @ProratedAmount = @ProratedDays * @AmountPerDay
SET @ProratedHours = @ProratedDays * @HoursPerDay
END

— return results
INSERT INTO @RtnValue
(
TotalDays,
TotalAmount,
ProratedDays,
HoursPerDay,
ProratedHours,
AmountPerDay,
ProratedAmount
)
SELECT
@TotalDays,
@MonthlyAmount,
@ProratedDays,
@HoursPerDay,
@ProratedHours,
@AmountPerDay,
@ProratedAmount

Return
END

 

I can simply render the results of the function like this:

SELECT * FROM
dbo.fnProrateMonthlyAmount
(
‘7/1/2012’,
‘7/1/2012’,
‘7/31/2012’,
10000,
50
)

Which gives me this output:

image

Or I can stuff the results of the function into a TABLE variable or SQL TYPE:

DECLARE @prorate TABLE
(
TotalDays int,
TotalAmount money,
ProratedDays int,
HoursPerDay money,
ProratedHours money,
AmountPerDay money,
ProratedAmount money
)

INSERT INTO @prorate
(
TotalDays,
TotalAmount,
ProratedDays,
HoursPerDay,
ProratedHours,
AmountPerDay,
ProratedAmount
)
[dbo].[fnManagedServicesProrateAmount](@Month, @DurationStart, @DurationEnd, @MonthlyRevenue, @MonthlyHours)

 

And if I know that the results are a single record with multiple values, and I want to work with these values to further my calculations, I can stuff the result directly into variables:

SELECT TOP 1
@TotalDays = TotalDays,
@TotalAmount = TotalAmount,
@ProratedDays = ProratedDays,
@HoursPerDay = HoursPerDay,
@ProratedHours = ProratedHours,
@AmountPerDay = AmountPerDay,
@ProratedAmount = ProratedAmount
FROM
[dbo].[fnManagedServicesProrateAmount](@Month, @DurationS, @DurationEnd, @MonthlyRevenue, @MonthlyHours)

I will admit that sometimes I have been known to use temporary tables rather than table variables, because of what I thought were limitations of the table variable. Turns out, table variables can do almost anything that a temporary table can do. Plus, they have the added bonus of having better performance, especially if they are used in a stored procedure. The reason for this is that a stored procedure that contains a temporary table cannot be precompiled. A precompiled script typically executes must faster. Also, while table variables exist only in the same scope in which they are created, they can be returned and passed into a stored procedure by creating a User Defined Type. So the only major limitation of a table variable versus a temporary table is that transaction logs are not recorded for table variables. Therefore, you cannot use them in the scope of a transaction.  One other difference, of course, is that table variables automatically go out of scope, while temporary tables must be explicitly DROPPED.

One thing I must admit that it took me a while to figure out is how to join with a table variable. Seems like it would be easy. For example, support I want to update a temporary table with the results of a subquery:

UPDATE @Users SET billable = (SELECT TOP 1 au.billable FROM application_user au WHERE au.[user_id] = @Users.[user_id]) WHERE u.billable IS NULL

The example above results in an error on the highlighted line stating that I must declare @Users. One simple change to this statement resolve the issue very easily.

UPDATE @Users SET billable = (SELECT TOP 1 au.billable FROM application_user au WHERE au.[user_id] = u.[user_id]) FROM @Users u WHERE u.billable IS NULL

This was definitely one of my DUH moments!  Smile

I have a set of records for employee utilization, and I wanted a quick way to rank them from highest utilization to lowest utilization. It turns out that the ROWNUMBER() function did exactly what I needed.

Here is an example of my data:

ConsultantName UtilizationPercent
Consultant A 100.5%
Consultant B 87.4%
Consultant C 95.0%
Consultant D 101.2%

The following query can be used to return the results of my data by ranking:

SELECT
ConsultantName
UtilizationPercent,
ROW_NUMBER() OVER (ORDER BY UtilizationPercent DESC) AS ConsultantRanking
FROM
ConsultantUtilization
ORDER BY
ConsultantRanking

This query returns the following:

ConsultantName UtilizationPercent ConsultantRanking
Consultant D 101.2% 1
Consultant A 100.5% 2
Consultant C 95.0% 3
Consultant B 87.4% 4

I am starting to appreciate the absolute awesomeness of table variables. Rather than using those dreaded cursors, I use a temporary working table and use update statements to apply my calculations. The performance benefits are incredibly tangible.

Table variables are very powerful and can do just about anything a temporary table can do. The only major difference is that once a table variable is declared, it cannot be altered. In addition, all indexes must use unique constraints.

Declaring a Table Variable with a Primary Key

DECLARE @work TABLE
(
consultant_id int primary key,
consultant_name varchar(50),
month_date datetime,
project_id int,
project_name varchar(255),
total_hours float
)

Declaring a Table Variable with a Composite Primary Key

DECLARE @work TABLE
(
consultant_id int,
consultant_name varchar(50),
month_date datetime,
project_id int,
project_name varchar(255),
total_hours float
primary key (month_date, consultant_id, project_id)
)

Declaring a Table Variable with a Composite Unique Index

DECLARE @work TABLE
(
id int identity primary key,
consultant_id int,
consultant_name varchar(50),
month_date datetime,
project_id int,
project_name varchar(255),
total_hours float
unique clustered (id, consultant_name)
)