Exporting CRM data/cases

Hi...I'd like to export my data/cases from CRM and need help in identifying a 
table (SQL) that would contain all data, including notes and attachments.  I 
know there's an AnnotationsBase table and assume that's where the 
notes/attachments are but cannot find a way to link them back to the actual 
case.  Thoughts?
0
Utf
7/13/2006 6:50:02 PM
crm 35858 articles. 1 followers. Follow

4 Replies
813 Views

Similar Articles

[PageSpeed] 6

For each entity you have a so called "filtered view" in the <company
name>_MSCRM database. This views are "supported" way of getting data
from CRM.


KTF wrote:
> Hi...I'd like to export my data/cases from CRM and need help in identifying a
> table (SQL) that would contain all data, including notes and attachments.  I
> know there's an AnnotationsBase table and assume that's where the
> notes/attachments are but cannot find a way to link them back to the actual
> case.  Thoughts?

0
dejan
7/14/2006 9:04:01 AM
Thanks Dejan.  I'll double-check to make sure that's the view we're using.  
Can you confirm whether that view will have a direct reference/link between 
notes/attachments and the related case? That's the issue we keep running 
into, can't find a way to link them up... 

"dejan.dular@gmail.com" wrote:

> For each entity you have a so called "filtered view" in the <company
> name>_MSCRM database. This views are "supported" way of getting data
> from CRM.
> 
> 
> KTF wrote:
> > Hi...I'd like to export my data/cases from CRM and need help in identifying a
> > table (SQL) that would contain all data, including notes and attachments.  I
> > know there's an AnnotationsBase table and assume that's where the
> > notes/attachments are but cannot find a way to link them back to the actual
> > case.  Thoughts?
> 
> 
0
Utf
7/14/2006 2:03:02 PM
Sorry for late response.

If you want to get Annotations for an object, you have to join the
object's view and FileterdAnnotation view. Join on the objectid
columns.

Example of getting annotations for contacts:
SELECT *
FROM FilteredContact FC
LEFT JOIN FilteredAnnotation FA on FA.objectid = FC.contactid

Unfortunately the FilteredAnnotation does not contain all attachment
data. So if you want to get attachemnts then you'll have to join
AnnotationBase table istead of FilteredAnnotation view.

Example:
SELECT *
FROM FilteredContact FC
LEFT JOIN AnnotationBase AB on AB.objectid = FC.contactid AND
AB.DeletionStateCode IN (0, NULL)

Note the DeletionStateCode column, that must be 0 (or NULL in join).
Otherwise the object is deleted.

Oh. And please don't use SELECT * in views. Please state the columns
you want to use.

---------
Dejan Dular




KTF wrote:
> Thanks Dejan.  I'll double-check to make sure that's the view we're using.
> Can you confirm whether that view will have a direct reference/link between
> notes/attachments and the related case? That's the issue we keep running
> into, can't find a way to link them up...
>
> "dejan.dular@gmail.com" wrote:
>
> > For each entity you have a so called "filtered view" in the <company
> > name>_MSCRM database. This views are "supported" way of getting data
> > from CRM.
> >
> >
> > KTF wrote:
> > > Hi...I'd like to export my data/cases from CRM and need help in identifying a
> > > table (SQL) that would contain all data, including notes and attachments.  I
> > > know there's an AnnotationsBase table and assume that's where the
> > > notes/attachments are but cannot find a way to link them back to the actual
> > > case.  Thoughts?
> > 
> >

0
dejan
7/19/2006 1:19:28 PM
Argh.. You can use Annotation view instead of AnnotationBase table. You
must still watch for DeletionStateCode thoung.


dejan.dular@gmail.com wrote:
> Sorry for late response.
>
> If you want to get Annotations for an object, you have to join the
> object's view and FileterdAnnotation view. Join on the objectid
> columns.
>
> Example of getting annotations for contacts:
> SELECT *
> FROM FilteredContact FC
> LEFT JOIN FilteredAnnotation FA on FA.objectid = FC.contactid
>
> Unfortunately the FilteredAnnotation does not contain all attachment
> data. So if you want to get attachemnts then you'll have to join
> AnnotationBase table istead of FilteredAnnotation view.
>
> Example:
> SELECT *
> FROM FilteredContact FC
> LEFT JOIN AnnotationBase AB on AB.objectid = FC.contactid AND
> AB.DeletionStateCode IN (0, NULL)
>
> Note the DeletionStateCode column, that must be 0 (or NULL in join).
> Otherwise the object is deleted.
>
> Oh. And please don't use SELECT * in views. Please state the columns
> you want to use.
>
> ---------
> Dejan Dular
>
>
>
>
> KTF wrote:
> > Thanks Dejan.  I'll double-check to make sure that's the view we're using.
> > Can you confirm whether that view will have a direct reference/link between
> > notes/attachments and the related case? That's the issue we keep running
> > into, can't find a way to link them up...
> >
> > "dejan.dular@gmail.com" wrote:
> >
> > > For each entity you have a so called "filtered view" in the <company
> > > name>_MSCRM database. This views are "supported" way of getting data
> > > from CRM.
> > >
> > >
> > > KTF wrote:
> > > > Hi...I'd like to export my data/cases from CRM and need help in identifying a
> > > > table (SQL) that would contain all data, including notes and attachments.  I
> > > > know there's an AnnotationsBase table and assume that's where the
> > > > notes/attachments are but cannot find a way to link them back to the actual
> > > > case.  Thoughts?
> > > 
> > >

0
dejan
7/19/2006 1:41:53 PM
Reply:

Similar Artilces:

CRM ERROR 1053 when trying to start MSCRM Security Service.
WE have hust installed CRM 1.2 on Windows 2003. When we try to start the CRM Security Service we get the following error ERROR 1053: The service did not responf to the start or control request in a timely fashion. Any help on this will be much appreciated. what OS are your domain controllers running? -- John O'Donnell Microsoft CRM MVP http://www.microsoft.com/BusinessSolutions/Community/CRMFaqLanding.aspx "CRM ERROR 1053" <CRMERROR1053@discussions.microsoft.com> wrote in message news:263F0B66-427E-48E0-A3A7-D2658F1C7B8B@microsoft.com... > WE have hust insta...

Inporting data to cell that exceeds the size of the cell.
When I import data to the excel-document that exceed the size of the cell, the cell will expand vertical. I want the text to be horizontal. How can I do that? I use text-boxes for import. When I use the fuction display "full screen" in some of my excel documents, I dont see the full screen. It is problably someting in the setup, but what Can somebody help please? SOSK, Prior to the import, set the column width of the cell that will be receiving the data to be as wide as you want it to be after the import. That will allow Excel to spread the data out rather than expanding the row ...

Contacts export display X400 not SMTP
Hi all, I'm not sure if this behavior is by default or not, but when my users try to export their contacts to an Excel file, any internal (domain) contacts show the X400 address rather than the SMTP address. Is there a way to change this? I've read some about VB scripts with CDO or something, but hoping there is an alternate way. Thanks Mike -- what version Exchange? -- Susan Conkey [MVP] "Mike W." <wallmr_snowpam@gmailDOTC0M> wrote in message news:iQ72h.553254$Pi2.240974@fe08.news.easynews.com... > Hi all, > > I'm not sure if this behavio...

Export to XML exports single row only
What I did: On XML source panel of Excel 2007 added XML map, mapped to columns using ranges (i. e. A1:A1825), selected “Export” from “Developer” tab in the ribbon. Result: Output XML file contains only one record (first row from the worksheet) I’ve done this numerous times in Excel 2003 with no problem. What happens?? Please help! Thanks, Eugene ...

Accessing CRM 4.0 from another machine
Thanks for any help in advance... I have successfully installed CRM on the server using port 5555. I can access it from the local machine using HTTP://localhost:5555 without issues. For some reason, I am unable to connect to that instance from any other machine on the network. Ultimately I would like to access the instance over the internet so our disconnected sales team can work with it, but for now I'd be happy to access it from the network, or for VPN clients. I have tried http://<MACHINENAME>:5555, as well as the IP address approach. Our network engineer has opened port...

UPDATE OF DATA
I have a problem, one of the stores is sale data that occurred a problem in servant HQ and it did not synchronize the information well, it is to say in the store this the complete data and in HQ this a partial data, since I can make to update in HQ what the store has in UNDER? this happened day 09 of February. Thanks. ...

Copying Data between Workbooks...
Firstly Hi there folks...complete newbie to Excel here and in desperat need of help in many matters...!!! My biggest headache so far is trying to handle my data betwee Workbooks... The scenario: End of everyday I have a worksheet mailed over containing all th details of deals that have been processed etc... Alot of the data is the same day-2-day : Rep Name, Builder Name etc... Now I have a number of reps that I keep track of deals for in my are alone, what I have been doing is opening the worksheet i receive ever night and copying only the relevant entries (By rep name) and the pasting in...

How CRM categorize opportunities into "This Record Only", "Related" and "All Affiliated" Records
In Account or Contact windows, with opportunity tab clicked, a list of opportunities is displayed under the above 3 different categories. I believe it depends on how you create an opportunity. Want to know how opportunities are being ended up under 3 different categories and what are the significances or uses in differentiating the opportunities into these categories. ...

Data Consolidation across workbooks
I am using data consolidation across workbooks using row labels and linked to source data. If I change numbers in my original workbooks, the consolidated workbook reflects those changes; however, if I change a row label, the consolidated workbook does not update. Please does anyone know if there is a way round this? ...

Export / Import Security
Ok, so I have setup my test environment again. Now I have to create security all over from scratch to define what will be used in production. Is there a way to export from the test environment and then import it into the newly upgraded installation? In both cases security has been/will be completely removed from the 8.0 install (restored on new server) before being upgraded to version 10. Dear, Security will be upgraded upon upgrading GP from GP 8.0 SP5 to GP 10.0. Converting the Security structure from GP 8.0 to 10.0 will not be an easy task. Regards, -- Mohammad R. Daoud MCP, MCBM...

lost Excel data!
I've just deleted some important data from an Excel sheet having moved it to another workbook. Unfortunately I closed the new workbook without saving and I can't find the data! I've not got a backup of the old workbook and the data is not on the clipboard. Is there any way of recovering it? thanks MrBen AFAIK, NO. We all gotta learn sometime (to make backups) -- Don Guillett SalesAid Software donaldb@281.com "MrBen" <anonymous@discussions.microsoft.com> wrote in message news:2305c01c45e8d$7842a610$a601280a@phx.gbl... > I've just deleted some importa...

Exporting Sent Email Addresses from Entourage
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC Hello! I'm trying to export email addresses from an employee's Entourage, but would like to include email addresses in his "Sent Items" folder. Is there a way to export these email addresses aside from exporting the "MBOX" file, which has all the email content as well? Thanks in advance! On 3/12/08 8:49 AM, in article ee9381d.-1@webcrossing.caR9absDaxw, "Quinlisk@officeformac.com" <Quinlisk@officeformac.com> wrote: > I'm trying to export email addresses from an emplo...

Quickly moving down one row on pasted data from another worksheet.
Ok, this should be fairly simple but I'm a excel novice. I have two worksheets. One contains all data with each row representing a different store. I then have another worksheet that is a form. I need to fill out a form for each store. The information maps the same every time, just one row down. It looks like this... WORKSHEET1 A B C D E 1 A1 B1 C1 D1 E1 2 A2 B2 C2 D2 E2 WORKSHEET2 Name: =Worksheet1!A1 Company: Worksheet1!B1 Parts: Worksheet1!...

CRM 4.0 entity view column properties
Has anyone run into this? Using the Web Client, in customizations open an entity and one of the views. Click on a column heading and try to change its properties (the width of the column). I get an alert that says "Select the column whose properties you want to change". Clearly not recognizing that I've clicked on a column. Using the Outlook Client this works fine. Sort of strange... Hi Bob, You can know if a column is selected if it has green border surrounding it. Can you see this before you click on "Change Properties"? When you launch any View page (s...

Export item ID?
Hi there. Having resolved my previous EMS Data import issues, I have now moved on to more complicated things (importing supplier lists, aliases etc). However, these operations require the item ID for each item - something I don't have. Is there some way I can export these from the database so I know what item ID corresponds to each item. I have run a query in Administrator (select itemlookupcode, ID from item) which tells me this, but I haven't worked out how to export this out to a spreadsheet. Any help would be appreciated, cheers After running your query goto File menu and Export....

Exporting email in Outlook Express
I am unable to export all emails in Outlook Express. First it had an error message mentioning MAPI so i followed the instructions on Microsoft Knowledge Base to install Windows Messaging - now when i try to export the following error message is: MAPISP32 caused an invalid page fault in module KERNEL32.DLL at 0187:bff7b9a6. Does anyone know what this means? tania jackson <tania@realtime-bs.com> wrote: > I am unable to export all emails in Outlook Express. Ask in an Outlook Express newsgroup. -- Brian Tillman Smiths Aerospace 3290 Patterson Ave. SE, MS 1B3 Grand Rapid...

Can CRM Integrate with MYOB?
I want to have MS CRM integrate with MYOB (Accounting Plus) so that any customer records that are updated in one, will automatically update in the other, and vice versa. Also for things like orders and invoices, if they are created in one program, that they automatically update the other. Can this be done?? MSCRM will not provide this integration for you, but you could develop your own integration. You might also try Scribe, they offer a lot of different integration adapters, but I do not know if they have one for MYOB. On Tue, 2 Mar 2004 20:10:58 -0800, <anonymous@discussions.mic...

CRM on a DC ?
Is it accurate that MS does NOT support CRM on a domain controller other than SBS ? Thanks Jon Epstein MCSE/MSCA InfoVision, Inc. Charlotte, NC, USA That is correct. It will work, but you can hit some support issues. With v1.2 there were also some isolated instances where it didn't work right in an established domain when installed on the DC. -- Matt Parks MVP - Microsoft CRM "InfoVision" <infovision1@gmail.com> wrote in message news:1135094163.922375.9580@z14g2000cwz.googlegroups.com... Is it accurate that MS does NOT support CRM on a domain controller other than...

Community Group user of Ms CRM
Hi We have a client who wishes to implement MS CRM. They have asked us to find a reference site who is a community, or not-for-profit group who has at least started an implementation. If nyou are such an orgnaisation, or have worked with one, and are willing to be such a reference site, please contact me via the newsgroup or on gillwalker@ozemail.com.au Thanks Gill ...

Microsoft CRM Router
The Problem: No E-Mails will be shown at Supportqueue. I followed the Implementation Guide from Microsoft. EMails at my personal adress will be shown at CRM. Sending Mails via CRM to some contacts are also ok. System: 1. Server SBS 2003 mit Exchange 2. Server MS Server 2003 Standard Edition +SQL Server 2003 +CRM 1.2 All OS running under German. Wich hook i didn't activated? ...

Export Image Package
I have an access application that has stored .JPG images within the database as a "Package". How can I export the image back to a normal .jpg file so that it can be read by a normal (non ole server) imaging application? ...

Office 2007
I have been working with Office 2007 beta. In prior versions I was able to use the mouse to highlight the cells that were to be added to a chart. I cannot highlight the cells that I need, but have to manually type in the beginning and ending cell range. ...

Exporting to Powerpoint
I have a small table in Excel that I am trying to export to PowerPoint, for some inexplicable reason one column was always missing even though it was specified in the range which the Macro is supposed to export. My manager is the only was able to see the value in that column and no other can replicate his success. Do you know any reason for this? We are all looking at the exact same file, why could he see something that the rest of us cannot? Tao, Why don't you simply copy and paste the whole table into a Powerpoin slide? Joh -- Message posted from http://www.ExcelForum.com...

Looking for experience CRM integration consultants.
I am operating a new Tech startup, called TekSpecs. We are a consulting services company who looks to grow into a complete managed services provider. Is their a programmer out their who would be interested in helping us enhance the MS CRM product and build a completely integrated system to manage our business? I am open to creative compensation and such. Email me privately: jleschorn@tekspecs.com I hope to hear from you soon, John T. Leschorn I did alot in ms crm, i am available for this customization and integration of the projects.. I make integrate of marketing tool with ms crm, al...

Export from Excel to Word
I have a 20 page Excel document that has a LOT of formating involved, it is text not data. Is there a way that I get it into Word without having to redo all the formatting? It is 11x17, landscape. Yes: Select the area you wish to copy. While holding the SHIFT key pull-down: Edit > Copy Picture... In the Word document, just paste. Because you have pasted a picture, the formatting will be preserved. Also because its a picture, yo can re-size it as you choose. -- Gary's Student "kath0178" wrote: > I have a 20 page Excel document that has a LOT of formating inv...