Troubleshooting

Google Maps API Quota Exceeded

posted Nov 4, 2016, 5:53 PM by Eric Patrick

ORT was using an old version of the Google Maps Javascript API, including this tag:


This presented two issues:
  1. https://maps-api-ssl.google.com is deprecated in favor of https://maps.googleapis.com, and
  2. no API key was specified in the request
To fix this issue, we used:


In order to create the API Key (in the key parameter above), we:
  • Navigated to the Google APIs Developer Console,
  • Selected an existing project (in this case, the existing qbo-Drive project)
  • From Dashboard, enabled the Google Maps Geocoding API and Google Maps Javascript API for the project
  • From Credentials, created a new API Key, and restricted it to call from ort.quandis.com and uatort.quandis.net
All QBO3 systems should migrate the Google Maps Javascript API calls to leverage this pattern.

AutoBind and Statement Inheritance

posted Sep 19, 2016, 7:34 PM by Eric Patrick   [ updated Sep 20, 2016, 7:01 AM by Philip Raath ]

The ADR system encountered an unusual use case where Organization/AutoBind did not work as expected. The root cause is that statements inherit the parameters of statements defined in their base class. In this instance, Organization inherits from Contact, and both had an AutoBind statement defined:
  • Organization/AutoBind was defined as SELECT OrganizationID, Organization FROM Organization WHERE Organization = @Organization
    • parameters = Organization
  • Contact/AutoBind was defined as SELECT ... FROM Contact WHERE ObjectID = @ObjectID AND Object = @Object AND Sequence = @Sequence
    • parameters = Object, ObjectID, Sequence
The unexpected behavior was introduced as follows:
  • When the Organization/AutoBind statement was wired, it inherited Contact/AutoBind properties, including the Object, ObjectID and Sequence parameters, and then added it's own parameter (Organization). At this point, Organization/AutoBind included the following parameters: Object, ObjectID, Sequence, and Organization
  • Organization/Save?Organization=Freddie Mac was called
  • When Organization/SetProperties was called, each AutoBind statement was compared to the parameters passed to SetProperties, and if all matching parameters were present, AutoBind is executed
    • In this case the only parameter passed was Organization
    • Since Object, ObjectID and Sequence were not present, AutoBind was not executed
  • Thus, an attempt to save a new Freddie Mac Organization was attempted
The fix for this scenario is to create an alternative AutoBind statement, such as Organization/AutoBindByOrganization with just Organization as a parameter. SetProperties will evaluate all AutoBind* statements, so both of the following calls would trigger an AutoBind:
  • Organization/Save?Organization=Freddie Mac
  • Organization/Save?Organization=Acme Rockets&Object=Organization&ObjectID=1&Sequence=1


Freddie Mac LMWS Error Report Procedures

posted May 14, 2015, 2:49 PM by Wes Coulter   [ updated Jul 8, 2015, 3:56 PM ]

Error reports are received from LMWS in response to our twice daily (three times on the first Tuesday of each month), when data conditions exist on loans that prevent the import process from importing them.  These responses are received via email to adr.support@quandis.com , and come from lmwmgr@he2unxpv228.fhlmc.com.  The subject of the email is "Error Report for Foreclosure/Bankruptcy Loan Level Attorney Data"  The following two sections describe the data conditions, and the process on how to correct them and/or notify the appropriate party(ies) of the concern.

Invalid Key/Loan Combination Procedure

Key IDs are sent with each case, and are unique identifiers to LMWS, which allow the ADR system to send updates on existing cases.  The "Invalid Key/Loan Com" error means the Key ID we are attempting to submit was used to identify a previous case associated with a different Freddie Mac loan number.  An example of this is: F001351000 1281 991208293 723147115 Invalid Key/Loan Com.  The Key ID associated with the new loan/case will need to be reset in the ADR system.

You will be using the "LMWSKeyIDFix.xlsx" spreadsheet for this, which is attached to this Blog Post.

  • Open the "inval_loans_lenstar.doc" attachment found in the LMWS Error Report email and copy all rows with a reason of "Invalid Key/Loan Com"
  • Paste these into the LMWSKeyIDFix spreadsheet, starting with row 2 (header row must remain as-is)
    • In some cases, multiple Invalid Key/Loan Com items will not be grouped together, the easiest way to deal with this is to copy all of the contents of the Word Doc, paste them into the spreadsheet and delete the inapplicable rows, being sure to remove any empty rows entirely
  • Once all items have been pasted into the spreadsheet, they will need to be separated into individual columns
    • Select the A column for all rows you have pasted in (starting with A2, do not select A1, which is the header row; example: you have 3 Invalid Key/Loan Com items, you should have A2, A3 and A4 selected)
    • Select the "Data" tab in the toolbar ribbon, then select "Text to Columns", a 3-step wizard will pop up
      • In the first step, select the radio button for "Fixed width", then click "Next"
      • The second step is where you are able to specify what data should be in each column.  This can be left the way Excel assumes it should be, click "Next"
      • Step 3 allows you to define the data formats.  Highlight all rows and select the "Text" radio button.  This will preserve any leading 0s in the columns
      • Click "Finish"
    • Your data is now placed in the appropriate columns
  • Any data that was placed in columns E, F, G and further to the right can be deleted
  • Sort the spreadsheet by the first column and remove any duplicates
  • Save this spreadsheet to your file system (i.e. Desktop)
  • Login to www.freddiemacadr.com and navigate to "Dashboard" >> "Import Data"
  • Select "KeyID Cleanup" from the "Import Dashboard" list
  • Drag and drop the LMWSKeyIDFix spreadsheet that you saved to your file system into the light blue bar above the panel that rendered on the right side of the page
  • The panel to the right will refresh and the import will be queued
  • Select "Refresh" from the "Options" dropdown in that panel and review the status.  If the status of your import does not update to "Complete" within 10 minutes, contact the lead developer for the ADR system to assist with troubleshooting
  • Upon successful import, ensure the loans do not appear in the following transmission.  If found there, contact the lead developer for the ADR system to assist with troubleshooting

Invalid Loan Number Procedure

LMWS rejects any cases that are associated with "Invalid Loans"  There are two definitions of "Invalid Loans", for the purpose of this procedure:

  1. The Loan exists in the LMWS database, but is not in a valid status.  An example of a valid status is "Foreclosure", and an example of an invalid status is "Active"
  2. The Loan does not exist in the LMWS database
Follow the steps below for each Foreclosure case that is identified as "Invalid Loan Number" in the "inval_loans_lenstar.doc" attachment.  Foreclosure cases are identified with the letter "F" at the beginning of the row within the attachment.  Example: F003007910 4111 790944103 NULL Invalid Loan Number.  All items with other leading letters can be skipped.
  • Login to www.freddiemacadr.com and navigate to "Dashboard" >> "Loans" (default landing page)
  • Search for the Loan Number identified as invalid
  • Click on the loan number to access the Loan Summary page
  • Scroll to the Process tabs (Foreclosure, Bankruptcy and Deficiency)
  • Select the tab for the appropriate Process Type
    • Foreclosures are identified by the letter "F" at the beginning of the keyid field in the attachment
    • Bankruptcies are identified by the letters "P" (POC) and "B" (MFR) at the beginning of the keyid field in the attachment
  • Click on the Attorney Case ID link to access the case's Summary page.  If there are more than one cases of that type, additional research will be required to locate the appropriate case
    • If the attorneys are different between the cases, navigate to each attorney's Organization Summary page and locate the four digit code in the "Tags" panel.  Match this code with the four digit code in the id_atty column of the attachment
    • If the attorneys are the same for all cases, the history records of each case, and their Tasks will need to be researched to identify which was updated most recently
  • Once the proper case is found, review its history, and the history records of the Tasks, Holds, Messages and Attachments and obtain the username of the user that performed the most recent update
  • Navigate to the Attorney Organization and obtain the primary contact for the firm
  • Send an email with the following information to these two contacts:
    • Subject: ADR – Invalid Loan Number
    • Body:
Hello,

Freddie Mac has identified the following loan(s) as being in a non-delinquent status: [LoanNumber]

A foreclosure or bankruptcy case was added or updated recently, which triggered the alert from Freddie 

Mac.  Please review your case and contact Freddie Mac directly with any questions on loan level 

information.

Thank you.

IIS 7.5 App Pool Stopping: Rewrite.dll broken

posted Oct 18, 2013, 5:04 AM by Eric Patrick

Upgrading to Windows 8.1, I found that IIS stopped working; the application pool would be stopped under rapid fail protection. The Windows logs indicated:

The Module DLL C:\WINDOWS\system32\inetsrv\rewrite.dll failed to load.  The data is the error.

Googling this phrase led to a technet solution:
  • From Control Panel > Programs and Features > IIS URL Rewrite Module 2, right click and choose Repair
Simple solution; long time to find it.


Debugging: Read the Stack Trace

posted Mar 12, 2013, 6:41 AM by Eric Patrick   [ updated Dec 9, 2013, 12:52 PM ]

Debugging Quandis Business Objects

Troubleshooting a QBO installation requires knowledge of five key debugging tools:
  • browser: 'Developer Tools'
  • network: Fiddler
  • server: Visual Studio
  • database: SQL Server Profiler
  • log files: Notepad

Debugging the Browser

QBO makes use of over 40,000 lines of javascript code (prior to minification), and about 10,000 lines of css code.  The good news is that only about 1/3 of all that code is 'QBO' code; the remainder is off-the-shelf stuff that rarely contributes to problems, but you still need to be able to read through it.

You are welcome to use any browser platform to debug. If you feel you wish to debug in IE, please spend 15 minutes debugging in IE 9 or earlier using their debugging tool, then spend 3 minutes debugging in Chrome, and you will reach the same conclusion that Microsoft's internal development teams reached: don't waste your time in IE.  Firebug is a perfectly acceptable alternative to webkit's debugger.

Some tips from the Quandis team with respect to the webkit debugger:
  • webkit = the baseline open-source code of both Safari and Chrome
  • Find the Console, and play with it. You can experiement with what you want to make QBO 3 do before publishing js changes!
  • When the Console shows a js error, it include a line number on the right. Clicking that line number takes you to exactly the line of code that raised the error
  • Find the Sources tab: it allows you to access included js and css files
  • From the Sources tab, you can highlight any code, right click, and choose Evaluate in Console
  • Put your js in external files whenever possible; it makes troubleshooting substantially easier
  • Use qbo3.reload() from the console followed by a page refresh to force a refresh your js and css include files after you publish changes

Debugging the Network

QBO 3 makes extensive use of AJAX to communicate with the server. Monitoring XmlHttp requests that are transmitted over the wire is essential to effective debugging. Download Fiddler, and review the tutorials if you're not familiar with it. Some tips from the Quandis team:
  • Enable HTTPS debugging from Tools > Fiddler Options > HTTPS tab
  • Find the Inspectors tab, and peruse the various options available: Raw, WebForms, JSON, and XML inspectors are really, really useful
  • Composer will allow you to fake any submission to the server (including XML posts) (this is a great hacking tool - design your security assuming users will try this out!)
  • Statistics will allow you to measure transmission times and troubleshoot multiple simultaneous requests
Fiddler's Composer window allows you to simulate HTTP GETs and POSTs to a website. To effectively use this with QBO, do the following:
  • Login to a QBO site
  • Start Fiddler
  • Navigate to QBO any page
    • from the Inspectors > Raw tab, copy the Cookie: line from the raw request; this contains your QBO Security cookie
  • Click on Fiddler's Composer window
    • enter the URL to submit to
    • copy the Cookie: line from your request above to the Request Headers section
      • you only need the qbo.Security and ASP.NET_SessionId cookies
    • paste your payload into the Request Body section
    • click the Execute button
    • note a new session appears in the left pane
You can review a more robust tutorial on Fiddler's site.

Debugging the Server

All QBO developers should have a local QBO installation running under IIS on their development machine. It's fine to have this site using a connection string to a dev or uat database, but you need to have the server-side (C#) code running locally to effectively debug. If you encounter a server-side error, you need to know where to start debugging. Determine this from the stack trace found in the server-side error logs.

In the stack trace below, note the following:
  • Timestamp says 11:51:28 PM: this is GMT. 7 lines below that, you see 18:51:28: this is local time (EST in this case).
  • Scan for all of the 'Message' lines: this is the summary of errors trapped. In the stack trace below, we have:
    • General Exception (not so useful)
    • Error attempting to read the path '/Templates/Mortgage/CopyTest/CopyTest.Edit.xslt' in file object repository 'Template'. (excellent clue!)
    • Could not find a part of the path 'C:\inetpub\local.quandis.net\Templates\Mortgage\CopyTest\CopyTest.Edit.xslt'. (excellent clue!)
  • If you cannot determine the cause of the error from Message lines, scan again for a line of code that sounds like a culprit
    • the inner-most exception will contain the line of code that actually raised the error; if it's from System.*, it's core Microsoft code
    • consider reading from the bottom up in a stack trace: in this case, the very last line of the inner exception is the crux of the issue (LocalFile.Read method)


----------------------------------------
Timestamp: 3/4/2013 11:51:28 PM

Activity: 00000000-0000-0000-0000-000000000000

Message: HandlingInstanceID: ea85ac7a-9feb-4197-87ee-49549498e9b4
An exception of type 'qbo.Exception.GeneralException' occurred and was caught.
------------------------------------------------------------------------------
03/04/2013 18:51:28
Type : qbo.Exception.GeneralException, qbo.Exception, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null
Message : General Exception
Source : 
Help link : 
Manager : Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.ExceptionManagerImpl
Data : System.Collections.ListDictionaryInternal
TargetSite : 
HResult : -2146233088
ThrowException : True
Stack Trace : The stack trace is unavailable.
Additional Info:

MachineName : MINBAR
TimeStamp : 3/4/2013 11:51:28 PM
FullName : Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=5.0.414.0, Culture=neutral, PublicKeyToken=null
AppDomainName : /LM/W3SVC/1/ROOT-1-130069145369323941
ThreadIdentity : admin@quandis.com
WindowsIdentity : IIS APPPOOL\DefaultAppPool
Inner Exception
---------------
Type : qbo.Exception.GeneralException, qbo.Exception, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null
Message : Error attempting to read the path '/Templates/Mortgage/CopyTest/CopyTest.Edit.xslt' in file object repository 'Template'.
Source : qbo.Attachment
Help link : 
Manager : Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.ExceptionManagerImpl
Data : System.Collections.ListDictionaryInternal
TargetSite : qbo.Attachment.AttachmentInfo Read(System.IO.Stream, System.String)
HResult : -2146233088
ThrowException : True
Stack Trace :    at qbo.Attachment.FileObjects.LocalFile.Read(Stream stream, String relativePath) in C:\Source\Trunk\qbo.3\qbo.Core\Application Tier\qbo.Attachment\FileObjects\LocalFile.cs:line 60
  at qbo.Attachment.AttachmentObject.Read(Stream streamToWriteTo) in C:\Source\Trunk\qbo.3\qbo.Core\Application Tier\qbo.Attachment\Attachment.cs:line 435
  at qbo.Attachment.AttachmentObject.ReadMemory() in C:\Source\Trunk\qbo.3\qbo.Core\Application Tier\qbo.Attachment\Attachment.cs:line 446
  at qbo.Attachment.AttachmentObject.ReadXsl(XsltSettings settings) in C:\Source\Trunk\qbo.3\qbo.Core\Application Tier\qbo.Attachment\Attachment.cs:line 511
  at qbo.Attachment.AttachmentObject.ReadXsl() in C:\Source\Trunk\qbo.3\qbo.Core\Application Tier\qbo.Attachment\Attachment.cs:line 500
  at qbo.Decision.ImportFormTemplateObject.get_EditTransform() in C:\Source\Trunk\qbo.3\qbo.Core\Application Tier\qbo.Decision\ImportFormTemplate.cs:line 248
  at qbo.DecisionWeb.ImportForm.RenderEdit(HttpContext context) in C:\Source\Trunk\qbo.3\qbo.Core\Web Tier\qbo.DecisionWeb\Decision\ImportForm.ashx.cs:line 152
  at qbo.DecisionWeb.ImportForm.ProcessRequest(HttpContext context) in C:\Source\Trunk\qbo.3\qbo.Core\Web Tier\qbo.DecisionWeb\Decision\ImportForm.ashx.cs:line 60

Inner Exception
---------------
Type : System.IO.DirectoryNotFoundException, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Message : Could not find a part of the path 'C:\inetpub\local.quandis.net\Templates\Mortgage\CopyTest\CopyTest.Edit.xslt'.
Source : mscorlib
Help link : 
Data : System.Collections.ListDictionaryInternal
TargetSite : Void WinIOError(Int32, System.String)
HResult : -2147024893
Stack Trace :    at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
  at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)
  at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access)
  at qbo.Attachment.FileObjects.LocalFile.Read(Stream stream, String relativePath) in C:\Source\Trunk\qbo.3\qbo.Core\Application Tier\qbo.Attachment\FileObjects\LocalFile.cs:line 39



Category: General

Priority: 0

EventId: 100

Severity: Error

Title:Enterprise Library Exception Handling

Machine: MINBAR

App Domain: /LM/W3SVC/1/ROOT-1-130069145369323941

ProcessId: 5584

Process Name: c:\windows\system32\inetsrv\w3wp.exe

Thread Name: 

Win32 ThreadId:7172

Extended Properties: 
----------------------------------------

Debugging the Database

Debugging the database should be used when other technique fail. Profiling a server, particularly a PROD server, is 'expensive' in that the act of profiling can degrade the performance of the db all by itself. Thus, you should coordinate with a DBA or QBO architect if you need to profile a database for your debugging efforts. When profiling QBO3, consider:
  • Use the 'TSQL' template
  • Under 'Events Selection':
    • enable RPC:Starting and SQL:BatchStarting (and/or their Ending counterparts)
    • disable all Security Audit and Sessions checkboxes
    • check on Show all columns
    • click on Column Filters and add a filter on the DatabaseName for the database you are troubleshooting
  • Pause the trace until you are ready to replicate the issue
  • Run the trace, replicate the issue, and re-pause the trace

Log Files

All QBO errors are logged to the web server. QBO 3 logging uses the Microsoft Enterprise Library Logging sinks, including a rolling log file that is renamed each day. Some tips include:
  • If both IIS and the Queue Processor are running, they may conflict in their attempts to write to the rolling.log file. If so, you may see a {Guid}rolling.log file on the web server. Look at it's contents if you don't see what you expect in the standard rolling.log.



Chrome Frame Configuration

posted Mar 2, 2013, 11:21 AM by Eric Patrick

Background 

To ensure a QBO website works with Chrome Frame, two key things must happen:
  1. the server must be configured to issue an HTTP Header instructing IE to use Chrome Frame, if installed, and
  2. IE must have Chrome Frame installed and enabled

IIS 7+ Server Configuration

Ensure web.config includes the following:

<system.webServer>
...
<!-- Use Chrome, if available -->
<httpProtocol>
<customHeaders>
<add name="X-UA-Compatible" value="chrome=1" />
</customHeaders>
</httpProtocol>
</system.webServer>

IIS 6 Configuration


From IIS Manager:
  • navigate to the web site Properties page
  • in the HTTP Header tab, click Add
  • Custom header name: X-UA-Compatible
  • Custom header value: chrome=1

IE Configuration

Normally, all one needs to do is download and install Chrome Frame. If this does not work, troubleshooting steps are:
  • In the IE address bar, enter: gcf:about:version and press enter
    • this should render a page that describes the Chrome Frame version number and such
    • if it does not render this page, Chrome Frame was not successfully installed
  • Assuming Chrome Frame is properly installed, if you still don't see 'About Chrome Frame in the context menu"
    • go to Tools > Internet Options > Advanced, scroll down and ensure the 'Enable third-party browser extensions' is checked
    • click okay, close your browser, and restart the browser
  • If that still does not work, go to Tools > Manage Add Ons
    • Under 'Toolbars and Extensions', ensure any extensions containing the words 'Chrome Frame' are enabled (click on it, and an enable / disable button appears in the info window)

IIS and Windows Authentication

posted Mar 30, 2012, 8:44 AM by Eric Patrick

When configuring IIS for windows authentication, you may encounter an "Access Denied" error when logging in with your local machine account.

This is because IIS is configured to prevent certain types of hacking attempts using this method.

You can fix the problem by modifying the registry.

Access Denied: What account is IIS Using?

posted Dec 21, 2011, 2:29 PM by Eric Patrick   [ updated Jun 10, 2015, 12:00 PM ]

IIS 6 typically uses a standard Windows account called 'Network Service' to execute ASP.NET code. This implies that if you want QBO configuration pages to save changes to the file system, the Network Service account needs write permissions to the subject files.

IIS 7.5 and later gets a bit trickier, though more secure. By default, each Application Pool executes under a virtual account (e.g. 'IIS APPPOOL\ASP.NET v4.0'). Just like with Network Service, you will need to explicitly grant permissions to this account to write to the configuration files. However, the subject account is not listed under the list of user accounts. (It's a, ahem, virtual account.) No matter, simply type the name into the Select Users or Groups permissions dialog, and Windows will accept the virtual account name.

For QBO 3 installs, security must be granted to web.config and the Config folder to modify file(s). To configure:
    1. Obtain the name of the application pool used by the website. From IIS, if the name of the website is qbo.localhost.net, look the the Application Pools tab and see which application pools are listed. Chances are there is a matching application pool named qbo.localhost.net. As a result, the virtual account name would be 'IIS APPPOOL\qbo.localhost.net'.
    2. From Windows Explorer, right click on the root folder of the website and choose Properties ->Security Tab
    3. Click Edit, then click Add
    4. Input IIS APPPOOL\qbo.localhost.net into the text box and click OK
    5. The name qbo.localhost.net will now appear as a listed account under the previous security tab
    6. Grant write and modfy permissions
    7. Repeat same steps for Config folder

SQL Error: tempdb Full

posted Dec 13, 2011, 11:50 AM by Eric Patrick   [ updated Dec 13, 2011, 7:31 PM ]

Standard configuration for our production SQL servers includes a dedicated drive for tempdb, SQL server's 'scratchpad' database. Our configuration follows Microsoft's recommended best practices.

With a complex query, DTA encountered the following SQL error:

Could not allocate space for object 'dbo.SORT temporary run storage:  150581025964032' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

The issue was caused by a FROM clause sub-query doing three things:
  • wanted to use an index different than the outer query, 
  • included an ORDER BY clause in the sub-select, and
  • was on a table with 10's of millions of rows (or more)
This essentially forced SQL to do an ORDER of the entire table in tempdb.

The fix is to have the outer query and sub-query use the same index.

Wrong version was:

SELECT ...
FROM Debt (NOLOCK)
INNER JOIN Ledger (nolock)
ON Ledger.LedgerID = ( ... )
INNER JOIN LedgerItem (nolock) -- this uses PK_LedgerItem
  ON LedgerItem.LedgerItemID = ( 
    SELECT  Top 1 LI.LedgerItemID
    FROM  LedgerItem LI (nolock)
    WHERE  LI.LedgerID = Ledger.LedgerID -- this uses IX_LedgerItemByLedgerID 
      AND   ...
            ORDER BY Li.PostedDate DESC, Li.LedgerItemID DESC 
  )

to this:
  
SELECT ...
FROM Debt (NOLOCK)
INNER JOIN Ledger (nolock)
ON Ledger.LedgerID = ( ... )
INNER JOIN LedgerItem (nolock)
ON LedgerItem.LedgerID = Ledger.LedgerID -- this uses IX_LedgerItemByLedgerID
AND LedgerItem.LedgerItemID = (
SELECT  Top 1 LI.LedgerItemID
FROM  LedgerItem LI (nolock)
WHERE  LI.LedgerID = Ledger.LedgerID -- this also uses IX_LedgerItemByLedgerID 
AND ISNULL(LI.LedgerTemplateItemID,1) not in (55,56,57,175) 
AND ...
ORDER BY Li.PostedDate DESC, Li.LedgerItemID DESC
)
WHERE ...

Import Failures: "Cannot insert NULL into column '{Table}ID'"

posted Nov 9, 2011, 2:40 PM by Wes Coulter   [ updated Dec 13, 2011, 7:34 AM by Eric Patrick ]

When attempting to import ContactMethods, a sporadic error was being produced:

Error executing Stored Procedure pContactMethodInsert; Cannot insert NULL into column 'ContactID'

The ultimate cause of this error was orphaned ObjectSubscription (OS) rows. OS rows had been created in the past, then the corresponding ContactMethod rows were deleted, without the OS rows having been deleted. All tables that may be OS-bound (e.g. participate in the Import Framework by existing in the ObjectFactory.config file) should have a SubscriptionDelete trigger. If the table is missing this trigger, it should be created and added to source control.

The following query can be used to locate orphaned ObjectSubscription rows, and delete them:

--DELETE FROM ObjectSubscription WHERE ObjectSubscriptionID IN 
(
SELECT ObjectSubscriptionID
FROM ObjectSubscription WITH (NOLOCK)
WHERE
SubscribedObject = 'ContactMethod'
AND NOT EXISTS (
SELECT ContactMethod.ContactMethodID
FROM ContactMethod WITH (NOLOCK)
WHERE ContactMethodID = ObjectSubscription.SubscribedObjectID
)
)


1-10 of 19