SQL Server

Search Stored Procedure Code in SQL Server

Every now and then, I need to find all stored procedures containing some string. Here’s how to do that:

SELECT
ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%foobar%' AND ROUTINE_TYPE='PROCEDURE'

Replace “foobar” with the string to search for.

SQL Server

Fix Orphan Users in SQL Server

Show all users without a login:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'
.NET, JavaScript

Amplify.js & ASP.NET Web API: Getting the error message

I’m still learning my way around ASP.NET Web API and have started using Amplify.js in my web apps to consume my web services. I originally learned about Amplify.js from John Papa’s excellent Pluralsight course, “Single Page Apps with HTML5, Web API, Knockout and jQuery”.

Today I noticed that when a server-side error occurs, I was not getting the JSON response that contains the error information in my client-side Amplify error() function (see “Exception Handling in ASP.NET Web API” for an explanation). Taking a look at the Amplify source code revealed that, “out of the box”, Amplify always sends null to the error callback function’s data parameter.

To override this behavior, I created a custom decoder that would use the XHR responseText to feed the error callback data parameter, like so:

amplify.request.decoders.mydecoder =
        function (data, status, xhr, success, error) {
            if (status === "success") {
                success(data, status);
            } else if (status === "fail" || status === "error") {
                try {
                    error(JSON.parse(xhr.responseText), status);
                } catch(er) {
                    error(xhr.responseText, status);
                }
            }
        };

Now I’m able to read the Message property and any other information returned from Web API when a server-side exception is thrown.

.NET

Entity Framework DbContext and mapping decimal properties with more than 2 digits of scale

Posting this mostly as a reminder to myself that when more than two decimal places of scale (i.e. to the right of the decimal point) is needed when mapping an EF entity property, you must specify the property’s precision in your DbContext’s OnModelCreating event like so: 

public class MyDbContext : DbContext, IDbContext
{
   protected override void OnModelCreating(DbModelBuilder modelBuilder)
   {
      modelBuilder.Entity<OutYearPoolRate>().Property(r => r.Rate).HasPrecision(14, 8);
      base.OnModelCreating(modelBuilder);
   }
...

Failure to do so causes EF to truncate the values to 2 decimal places when updating the database.

.NET

ASP.NET Wizard Control Oddness

Haven’t posted anything in over a year, but decided to break the silence to write about something that caused me to waste more time than it should have. Hopefully, it will save someone else (or future me) from wasting more time on it.

I’m developing an ASP.NET site using the Wizard control and have code that saves the user’s progress in a database. The idea here is that the user can return to the site and pick up where they left off. Simple enough, but I got caught in what seemed like a never ending debugging session when I couldn’t figure out why the "Previous" button didn’t work when I came back into the site to resume a previous session. The wizard simply would not step backwards when starting on any step in the middle. To make a long story short, turns out the genius at Microsoft who implemented this control decided to make the "previous" button work like the browser "back" button, i.e. clicking previous steps you back in your "history" since you starting using the wizard. In this case, there is no "history" when starting in the middle, so the button does nothing! Not at all intuitive.

I found this discussion enlightening: http://forums.asp.net/p/973337/1235981.aspx

The workaround is simple. Handle the PrevousButtonClick event and decrement the ActiveStepIndex property:

protected void MainWizard_PreviousButtonClick(object sender, WizardNavigationEventArgs e)
{
    MainWizard.ActiveStepIndex--;
}

Hope this helps.

Windows

Flash Player 10 Causing Internet Explorer Hang

Some folks (including me) are experiencing problems with version 10 of the Adobe Flash player and Internet Explorer. Oddly enough, I didn’t seem to have any trouble until after this month’s “patch Tuesday” (Microsoft updates).

For now, I’ve downgraded Flash to version 9 to workaround the problem. Adobe has older versions of Flash available for download here:

http://kb.adobe.com/selfservice/viewContent.do?externalId=tn_14266