Finding if SP Parameters are Nullable

I’m working on something where I wanted to know if some parameters to a SQL Server Stored Procedure were nullable (or, in otherwords, if they had a default value).

The API I was working with provided a way to find info about the SP, but the isNullable value was never accurate.

I tried working with with the MS DAABs which have a way to fetch SP parameter info, but this method also produced faulty information.

Looking into the code of the DAAB, it was internally calling:

Dim command As SqlCommand

SqlCommandBuilder.DeriveParameters(command)

Somewhere I came across some code for the DeriveParameters method (I think from the Mono project maybe?) which showed it calling this system SP:

sp_procedure_params_rowset

Which returns output fields,:

PARAMETER_HASDEFAULT

IS_NULLABLE

But, again, neither of these appear correctly.

However, I finally came across this thread where a MSFT poster indicates that this data is simply not available:

http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/900756fd-3980-48e3-ae59-a15d7fc15b4c/

Instead, you need to parse the SP definition to see what the default parameter value is, which is a pain, but at least I know why these other methods kept not working.

 

Advertisements

Avoid Overwriting Modified SPs With Code Generation

One of the challenges of using code generation is to avoid overwriting your custom changes when you re-generate the code later.

This is a trick I am using to avoid overwriting my stored procedures if I update them manually.  The trick is to include a comment indicating that the SP has been code generated, and then to remove that comment if you update the SP.  So lets say that you generate:

CREATE PROCEDURE OrderDetails_Get
@OrderId as int
AS
-- CODE GENERATED
SELECT * FROM
OrderDetails
WHERE
OrderId = @OrderId

and you made some customizations to the SP so it now looks like this:

CREATE PROCEDURE OrderDetails_Get
@OrderId as int
AS
SELECT * FROM
OrderDetails
INNER JOIN
Orders ON
Orders.OrderId = OrderDetails.OrderId
WHERE
OrderId = @OrderId

The following code will help you when generating your SPs to avoid overwriting this change:

IF EXISTS(
  SELECT [definition] AS objectText
  FROM sys.sql_modules
  WHERE [object_id] = OBJECT_ID(N'OrderDetails_Get', 'P')
  AND definition LIKE '%CODE GENERATED%')
BEGIN
   -- overwrite is ok
END

 

I hate Verizon

My lenovo came with Sierra Wireless MC5725 for use with Verizon’s EVDO network.

VZAccess manager (Verizons POS software) told me today that there was a firmware update for my Sierra Wireless device.

This update failed, and now my device won’t work.  I basically can’t enable the device.  It doen’t show up in my network list, and I can’t turn it on using the fn+F5 screen.

Where can I get the firmware for this thing?  Sierra Wireless says they don’t support these OEM devices, and Lenovo has nothing regarding firmware on their site, only drivers.

VZAccess Manager is one of the worst pieces of software I have ever had the chance to work with.  It never stops suprising me how crappy it can be, and yet I am still forced to use it every day.  I hate Verizon for doing this to me.

ASP.NET Apps Recycling Because of FCN Issues

I have written before about some of the problems that a frequently recycling asp.net application can cause, espically if you are using session for anything.

ASP.NET Process Recycling Too Often

But I ran into a situation where the recycle was caused by the application itself for a client of mine.  I had bought and customized a asp.net application that allows my client to give THEIR clients access to a section of their site where they can upload/download/manage files very nicely.  But after moving to a new hosting environment, users kept losing their login sessions very quickly.

After some research, I found out that session was being used to track if the user was logged in, and session was being lost very quickly because the application was recycling.

I used the following code to help diagnose the cause for the application restarting:

(this goes in Application_End)

Dim runtime As HttpRuntime = GetType(System.Web.HttpRuntime).InvokeMember("_theRuntime", _
    BindingFlags.NonPublic Or _
     BindingFlags.Static Or _
    BindingFlags.GetField, _
    Nothing, Nothing, Nothing)

If runtime Is Nothing Then
    logger.Error("The application is closing at " & Now.ToShortDateString & " " & Now.ToLongTimeString)
    Return
End If

Dim shutDownMessage As String = runtime.GetType().InvokeMember("_shutDownMessage", BindingFlags.NonPublic Or BindingFlags.Instance Or BindingFlags.GetField, Nothing, runtime, Nothing)
'If shutDownMessage = "HostingEnvironment caused shutdown" Then
'    '*** this is normal, you can include this commented out section or not
'    Return
'End If
Dim shutDownStack As String = runtime.GetType().InvokeMember("_shutDownStack", BindingFlags.NonPublic Or BindingFlags.Instance Or BindingFlags.GetField, Nothing, runtime, Nothing)
'*** email this error
logger.Error(String.Format("The application is closing at " & Now.ToShortDateString & " " & Now.ToLongTimeString & " " & vbCrLf & vbCrLf & "_shutDownMessage={0}" & vbCrLf & vbCrLf & "_shutDownStack={1}", shutDownMessage, shutDownStack))


Dim log As New EventLog
log.Source = ".NET Runtime"
log.WriteEntry(String.Format(vbCrLf & vbCrLf & "_shutDownMessage={0}" & vbCrLf & vbCrLf & "_shutDownStack={1}", shutDownMessage, shutDownStack), EventLogEntryType.Error)

The cause was:

Directory rename change notification for 'APP PATH HERE.
Clients dir change or directory rename
HostingEnvironment caused shutdown

Because this app was now hosted with a 3rd party, I didn’t have control over the environment to go see if there was an AV scanner or backup manager running that was touching the files.

However, I because I was able to reproduce the error in my dev environment, I was confident I could rule out those causes.

I eventually found that the problem was 2 fold:

1) The application allows the user to upload files/create folders that reside in subfolders of the application.  The app could think these changes require a recycle.

2) The application creates a temp folder for the user for each session under the root as well.  Changing folder structure can cause a recycle.

So what can be done?

Well, I adapted some code from a few places to turn off the FCN (File change notifications) for sub directories of the website.  This isn’t easy to do if you don’t have something to go off of, becaues you basically have to hijack your way into private methods on classes in the .net framework, using reflection to call methods that you shouldn’t be accessing, in order to turn off specific behavior.

Here are the methods I created to help me do this.  I also have some log4net code in here, so you’ll have to pull that out if you want to use this.

Private Sub TurnOffDirectoryMonitoring()
    Try
        Dim p As System.Reflection.PropertyInfo = GetType(System.Web.HttpRuntime).GetProperty("FileChangesMonitor", BindingFlags.NonPublic Or BindingFlags.Public Or BindingFlags.Static)
        Dim o As Object = p.GetValue(Nothing, Nothing)

        Dim f As FieldInfo = o.GetType.GetField("_dirMonSubdirs", BindingFlags.Instance Or BindingFlags.NonPublic Or BindingFlags.IgnoreCase)

        Dim monitor As Object = f.GetValue(o)

        Dim asdf As System.Reflection.MethodInfo() = monitor.GetType.GetMethods()

        Dim propIsMonitoring As System.Reflection.MethodInfo = monitor.GetType.GetMethod("IsMonitoring", System.Reflection.BindingFlags.Instance Or System.Reflection.BindingFlags.NonPublic)
        Dim bIsMonitoring As Boolean = propIsMonitoring.Invoke(monitor, Nothing)
        Dim logger As log4net.ILog = log4net.LogManager.GetLogger("File")
        logger.Info("Directory monitoring IsMonitoring was " & bIsMonitoring)


        Dim m As System.Reflection.MethodInfo = monitor.GetType.GetMethod("StopMonitoring", System.Reflection.BindingFlags.Instance Or System.Reflection.BindingFlags.NonPublic)

        Dim objArray() As Object = {}
        m.Invoke(monitor, objArray)

        logger.Info("Directory monitoring IsMonitoring after change is " & bIsMonitoring)

    Catch ex As Exception
        Dim logger As log4net.ILog = log4net.LogManager.GetLogger("File")
        logger.Error(ex)
    End Try
End Sub

Private Sub CheckDirectoryMonitoring()
    Try
        Dim p As System.Reflection.PropertyInfo = GetType(System.Web.HttpRuntime).GetProperty("FileChangesMonitor", BindingFlags.NonPublic Or BindingFlags.Public Or BindingFlags.Static)
        Dim o As Object = p.GetValue(Nothing, Nothing)

        Dim f As FieldInfo = o.GetType.GetField("_dirMonSubdirs", BindingFlags.Instance Or BindingFlags.NonPublic Or BindingFlags.IgnoreCase)

        Dim monitor As Object = f.GetValue(o)

        Dim asdf As System.Reflection.MethodInfo() = monitor.GetType.GetMethods()

        Dim propIsMonitoring As System.Reflection.MethodInfo = monitor.GetType.GetMethod("IsMonitoring", System.Reflection.BindingFlags.Instance Or System.Reflection.BindingFlags.NonPublic)
        Dim bIsMonitoring As Boolean = propIsMonitoring.Invoke(monitor, Nothing)
        Dim logger As log4net.ILog = log4net.LogManager.GetLogger("File")
        logger.Info("In CheckDirectoryMonitoring, Directory monitoring IsMonitoring is " & bIsMonitoring)
    Catch ex As Exception
        Dim logger As log4net.ILog = log4net.LogManager.GetLogger("File")
        logger.Error(ex)
    End Try
End Sub

Private Sub CheckFCNMode()
    Try
        Dim p As System.Reflection.PropertyInfo = GetType(System.Web.HttpRuntime).GetProperty("FileChangesMonitor", BindingFlags.NonPublic Or BindingFlags.Public Or BindingFlags.Static)
        Dim o As Object = p.GetValue(Nothing, Nothing)

        Dim f As FieldInfo = o.GetType.GetField("_FCNMode", BindingFlags.Instance Or BindingFlags.NonPublic Or BindingFlags.IgnoreCase)

        Dim iFCNMode As Integer = f.GetValue(o)
        Dim logger As log4net.ILog = log4net.LogManager.GetLogger("File")
        logger.Info("In CheckFCNMode, FCNMode is " & iFCNMode)
    Catch ex As Exception
        Dim logger As log4net.ILog = log4net.LogManager.GetLogger("File")
        logger.Error(ex)
    End Try
End Sub

These methods are not refactored or “best practice” at all, there is a lot of copy/paste code in here, but you’ll get the idea of what I’m doing.

You can call these methods from the beginning on Application_Start. 

1 thing to note.  I found that my code that checks to make sure that logging was turned off, doesn’t seem to report the right value the first time I check it.  But by the time the first session starts, it has indeed stopped monitoring.

Just try calling these methods from a few places after application_start and you can see what happens.