SQL query to retrieve notes from Microsoft CRM database

With Microsoft CRM version 3.0, there was a "bug" in that you could not build a view in CRM that would properly pull all information on the notes you have put in the system. You could go to "Advanced Find" and build a partial query on notes, but there was a limitation in that you couldn't pull in the name of the account or contact that the note was associated with. This was just a system limitation in Microsoft CRM version 3.0.

The workaround is to pull the notes directly from the Microsoft CRM 3.0 version sql server database.

To retrieve from the Microsoft CRM database all notes for accounts and contacts, use the following sql:

select isnull(accountbase.name, contactbase.fullname) as RecordName,

n.objecttypecodename, n.createdbyname, n.createdon,

replace(replace(cast(n.notetext as nvarchar(max)),char(10),''), char(13),'') as Note,

n.subject

from filteredannotation as n

left join accountbase on n.objectid = accountbase.accountid

left join contactbase on n.objectid = contactbase.contactid

where (objecttypecodename='Contact' or objecttypecodename='Account')

order by isnull(accountbase.name, contactbase.fullname)

NOTE: the above sql includes a couple of replace commands on the notetext field in order to replace

carriage returns and line feeds that exist in the note.

NOTE2: we have run into issues with really long notes. Apparently, a text field in Excel/CSV file can only contain so many characters (some number over 1,0000). So at times we add a filter to filter out really long notes:

" and len(replace(replace(cast(n.notetext as nvarchar(max)),char(10),''), char(13),'')) <1000 "

Note: built using CRM 3.0 on-premise. Will probably work but has not been tested with later versions of CRM.