Linking CRM/SQL Server to External Database

I am looking for information of someone to talk to about how to "link" data 
from an external non-SQL Server d/b to the CRM SQL Server d/b.  In our case 
we need to link our Oracle ERP database to SQL server (CRM database) as it 
contains about half of the info we need in our CRM.  

We can obviously create an interface between the two db's and "load" the 
data periodically but what I would rather do is use ODBC to map / connect the 
data in the Oracle db to the schema in CRM/SQL server so we don't have 
duplicate data (I would really prefer to have the Oracle be our one and only 
ERP solution and load ERP data into SQL server that then must be maintained 
in two locations). 

ODBC may not be the appropriate way to do this although I haven't come 
across anyway to go directly to Oracle so I am open to suggestions.  And - 
maybe what I am trying to do isn't possible and loading may be the only 
option (I hope not).

Ed 
0
Utf
4/18/2007 11:22:02 AM
crm 35858 articles. 1 followers. Follow

3 Replies
673 Views

Similar Articles

[PageSpeed] 56

In a future release Microsoft may support virtual entities which are where 
data appears in Microsoft CRM but is hosted in another solution. Today there 
are a few options

1 - Load the data into Microsoft CRM duplicate the information
2 - Load only the critical pieces of the data and then have a view into 
anything else on the other database.
3 - use a view or asp.net page to view the data within crm but keep it in 
the other system.
4 - maybe load a record from the other db as needed then remove it..

Really need to know more about your scenario to come up with a good answer.


-- 

==============================
John O'Donnell
Microsoft CRM MVP
http://www.crowecrm.com


"Ed K" <EdK@discussions.microsoft.com> wrote in message 
news:762AC72B-B728-4FFC-95A4-04F108FD88C8@microsoft.com...
>I am looking for information of someone to talk to about how to "link" data
> from an external non-SQL Server d/b to the CRM SQL Server d/b.  In our 
> case
> we need to link our Oracle ERP database to SQL server (CRM database) as it
> contains about half of the info we need in our CRM.
>
> We can obviously create an interface between the two db's and "load" the
> data periodically but what I would rather do is use ODBC to map / connect 
> the
> data in the Oracle db to the schema in CRM/SQL server so we don't have
> duplicate data (I would really prefer to have the Oracle be our one and 
> only
> ERP solution and load ERP data into SQL server that then must be 
> maintained
> in two locations).
>
> ODBC may not be the appropriate way to do this although I haven't come
> across anyway to go directly to Oracle so I am open to suggestions.  And -
> maybe what I am trying to do isn't possible and loading may be the only
> option (I hope not).
>
> Ed 


0
John
4/18/2007 10:30:07 PM
John, do yo have a little more information about this kind of virtual 
entities, please?
It will be possible run Ms-CRM without SQL Server, and with Oracle DB?

"John O'Donnell" wrote:

> In a future release Microsoft may support virtual entities which are where 
> data appears in Microsoft CRM but is hosted in another solution. Today there 
> are a few options
> 
> 1 - Load the data into Microsoft CRM duplicate the information
> 2 - Load only the critical pieces of the data and then have a view into 
> anything else on the other database.
> 3 - use a view or asp.net page to view the data within crm but keep it in 
> the other system.
> 4 - maybe load a record from the other db as needed then remove it..
> 
> Really need to know more about your scenario to come up with a good answer.
> 
> 
> -- 
> 
> ==============================
> John O'Donnell
> Microsoft CRM MVP
> http://www.crowecrm.com
> 
> 
> "Ed K" <EdK@discussions.microsoft.com> wrote in message 
> news:762AC72B-B728-4FFC-95A4-04F108FD88C8@microsoft.com...
> >I am looking for information of someone to talk to about how to "link" data
> > from an external non-SQL Server d/b to the CRM SQL Server d/b.  In our 
> > case
> > we need to link our Oracle ERP database to SQL server (CRM database) as it
> > contains about half of the info we need in our CRM.
> >
> > We can obviously create an interface between the two db's and "load" the
> > data periodically but what I would rather do is use ODBC to map / connect 
> > the
> > data in the Oracle db to the schema in CRM/SQL server so we don't have
> > duplicate data (I would really prefer to have the Oracle be our one and 
> > only
> > ERP solution and load ERP data into SQL server that then must be 
> > maintained
> > in two locations).
> >
> > ODBC may not be the appropriate way to do this although I haven't come
> > across anyway to go directly to Oracle so I am open to suggestions.  And -
> > maybe what I am trying to do isn't possible and loading may be the only
> > option (I hope not).
> >
> > Ed 
> 
> 
> 
0
Utf
4/19/2007 3:40:01 PM
John - thank you for your response - I was afraid that was the answer.

The situation I have - about half of the data that I need in the CRM system 
is located in our ERP system Banner (this is student data - we are a 
University).  The Banner data resides in Oracle (highly normalized).  We 
would like to maintain data in only one place (that being Banner) and there 
is enough change in the data that we can't load the CRM once and forget it.  

At this point - my thought is that we will need to extract the data from 
Banner (in the process we can flatten it as we don't need the high degree of 
normalization in the CRM) and then periodically (maybe daily) drop and 
recreate the table data in CRM to refresh it.  Records will never disappear 
in Banner so we shouldn't risk anything in terms of creating orphans in CRM 
by dropping the tables and reloading.   I really don't want to have to get 
into data comparison and selective update if I can avoid it.

I will need to think about how to "select" the records I want (the CRM will 
contain only a subset of the Banner ERP system) - I am almost thinking of 
allowing the user of the CRM system to enter a Student ID, and then have the 
load program match to the Banner key and pull the full set of data set from 
Banner rather than doing a general select on the Banner side ( a "pull" vs. a 
"push") - by doing that I would always be assured of having integrity on the 
CRM side - the worst that I would have have is a record with some blank field 
if the match on the Banner side failed.  That would also prevent a problem in 
the unlike event that somehow someone was able to delete a record on the 
Banner side - without a match on the Banner side - the existing data in CRM 
would be left un-touched. 

What things do I need to know about "loading" data into the SQL (CRM) tables 
- obviously I don't need to worry about the meta data side given that we 
can't map directly - the metadata will be handled by CRM and I assume is 
referential to the SQL server schema which would be in place.

Ed

"John O'Donnell" wrote:

> In a future release Microsoft may support virtual entities which are where 
> data appears in Microsoft CRM but is hosted in another solution. Today there 
> are a few options
> 
> 1 - Load the data into Microsoft CRM duplicate the information
> 2 - Load only the critical pieces of the data and then have a view into 
> anything else on the other database.
> 3 - use a view or asp.net page to view the data within crm but keep it in 
> the other system.
> 4 - maybe load a record from the other db as needed then remove it..
> 
> Really need to know more about your scenario to come up with a good answer.
> 
> 
> -- 
> 
> ==============================
> John O'Donnell
> Microsoft CRM MVP
> http://www.crowecrm.com
> 
> 
> "Ed K" <EdK@discussions.microsoft.com> wrote in message 
> news:762AC72B-B728-4FFC-95A4-04F108FD88C8@microsoft.com...
> >I am looking for information of someone to talk to about how to "link" data
> > from an external non-SQL Server d/b to the CRM SQL Server d/b.  In our 
> > case
> > we need to link our Oracle ERP database to SQL server (CRM database) as it
> > contains about half of the info we need in our CRM.
> >
> > We can obviously create an interface between the two db's and "load" the
> > data periodically but what I would rather do is use ODBC to map / connect 
> > the
> > data in the Oracle db to the schema in CRM/SQL server so we don't have
> > duplicate data (I would really prefer to have the Oracle be our one and 
> > only
> > ERP solution and load ERP data into SQL server that then must be 
> > maintained
> > in two locations).
> >
> > ODBC may not be the appropriate way to do this although I haven't come
> > across anyway to go directly to Oracle so I am open to suggestions.  And -
> > maybe what I am trying to do isn't possible and loading may be the only
> > option (I hope not).
> >
> > Ed 
> 
> 
> 
0
Utf
4/20/2007 12:42:02 PM
Reply:

Similar Artilces:

i am having trouble locating my server
i cannot receive my emails. the error report says that it cannot find a server Have you checked with your ISP's help site? Based on your IP address I think you're in Australia. See if the article here helps: http://www.aapt.com.au/support/55.asp "talkeesha" <talkeesha@discussions.microsoft.com> wrote in message news:78072F02-9CAE-485E-A9B8-03E297727716@microsoft.com... >i cannot receive my emails. the error report says that it cannot find a >server ...

Out of office in exchange server 5.5
Hello, Is there a way to set out of office reply on the email server rather than at the client's machine? Because if the client's machine is off, there will not be any replies sent. I am using the internet mail connector in exchange server 5.5. Thank you See reply to your later post in here. leo wrote: > Hello, > > Is there a way to set out of office reply on the email server rather > than at the client's machine? Because if the client's machine is off, > there will not be any replies sent. I am using the internet mail > connector in exchange server 5.5...

Demoting a 2003 Domain Controller Hosting SQL Server
We have a 2-node Windows Server 2003 cluster, with both nodes as domain controllers and running clustered SQL Server 2003. I realise this goes against all good practice, and that is partly why I am trying to do the following. We additionally have two Windows Server 2008 R2 domain member servers, which we wish to make domain controllers. We then wish to demote the two existing WS2003 DCs. Is there any reason why I wouldn't be able to demote the 2003 DCs whilst they are hosting SQL Server 2003? I can stop the instance service, but I wouldn't want to uninstall SQL Serv...

save e-mail on server
Everytime I sign on to check my e-mail I have discovered the "advanced" setting to save my e-mail for 60 days on the server is turned off. How do I get this setting to permanently stay activated? Hard to say without any configuration details and what exactly you did where. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World Answers ----- "Irish Travelor" <Irish Travelor@discussions.microsoft.com>...

sql server 2000: most frequented tables -> script wanted
hallo, could someone please provide me a script, which throws the most frequented tables used within a sql server 2000 database? i am aware of one (see below) for sql server 2005, but due to new functionalities within sql server 2005 it can not be backported. SELECT TOP 20 db_name(database_ID), object_name(object_id), leaf_insert_count, leaf_delete_Count, leaf_update_count, range_scan_count, row_lock_count FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL) WHERE db_name(database_ID)='<Datenbank-Name>' ORDER BY leaf_update_count desc --oder ...

Many to 1 Relationships in CRM 4.0 #2
Hi I just wondering if there is a suggested way to create multiple many to 1 relationships in CRM 4.0. We have created a custom entity and we need to associate more than 1 type of contact with the custom entity. We have a nominated and a registered contact. We have created two many to 1 relationships with contacts and the custom entity. The problem is that the name of the custom entity now appears twice in the navigation pane for contacts. We can select the 'Do not display option' however the custom entity will not appear in the navigation pane for the 'nominated contacts&...

Error while installing languega pack, CRM 4.0
Hello, I installed succesfully the german language pack for crm. When I try to activate this languega pack over the Webinterface i get an error. The application log tells me this: Source: MSCRMWebService Description: MUI Provisioning failed. Error: Thread was being aborted. Any ideas? I searched in previous entrys here, but wasn't able to find a solution. Thanks! Regards Rene On the crm database run the following command: Delete from MetadataSchema.LocalizedLabel where CustomizationLevel = 2 NB: make sure you have a backup. And ofcourse this is not supported but should work. -...

Saving copy of e-mail on server
How do I stop saving copies of all my e-mails to my server. It's gettoing jammed. Thanks! >-----Original Message----- >How do I stop saving copies of all my e-mails to my >server. It's gettoing jammed. Thanks! >. > What kind of a server do you use? POP3, Hotmail, MSN, or IMAP? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Cent...

Database Model Diagram #2
I have Visio 2003. When I try to load a new Database Model Diagram Visio locks up every time. Every other type of template loads just fine. I have Windows XP Professional x64. Is there something I'm missing? Thanks for any information. ------=_NextPart_0001_4353DD39 Content-Type: text/plain Content-Transfer-Encoding: 7bit This can occur with Visio 2003 on 64-bit Windows systems, and is related to the way that Windows provides the Printer supported paper sizes which Visio needs. Here is a work around that will help you avoid the problem: 1) new blank drawing 2) File | Print 3...

getting rid of linking errors...
hello every1, I am new to VC++. I have assignement in which i am trying to port VC 6 code to VC 2005. Although I removed some of the common errors, I am getting some linking errors while building the sol'n. These are as follows: LINK : warning LNK4098: defaultlib 'MSVCRT' conflicts with use of other libs; use /NODEFAULTLIB:library USBInfo.lib(USBInfoInterface.obj) : error LNK2019: unresolved external symbol "__declspec(dllimport) private: void __thiscall std::basic_string<char,struct std::char_traits<char>,class std::allocator<char> >::_Copy(unsigned int)&...

Multiple Servers?
Can FRX pull data from multiple servers which are physically sitting in different locations? My company has done an acquisition. Does all of the GL data need to be sitting on the same server or can I consolidate financial statements from servers that are sitting in different cities? Thanks. Yes, It is possible to do a consolidation with data coming from different servers. In the System Specific tab of the company setup, you would require different DSNs (Data Source Names) for each server. However you mention that the servers are in different cities, so unless you have a fast link be...

VBA to export large tables from Excel to SQL Server
I have spreadsheets that I distribute to users. I want Excel VBA to export large tables (2000+ rows) into a remote SQL Server 2005. I am able to successfully do this with the following generic code, but it is too slow: Dim con As ADODB.Connection Set con = New ADODB.Connection con.Open "Driver={SQL Server};SERVER=<server>;DATABASE=TestSample;UID=<id>;PWD=<pw>;" con.Execute "INSERT INTO ... SELECT * FROM ..." 'Looping this for each row It takes over 10 minutes because it does it one line at a time. I've also tried l...

Moving CRM to a Differnet Domain
We recently purchased a company that paid for CRM cutomization/design...problem is the server (CRM and SQL only) is on it's own domain. We want to move CRM/SQL to our domain (to use Exchange, Sales Outlook client, etc.) without losing the customization . There's no data on the CRM server.....is there a way to move CRM to a different domain and not lose the customization? And to throw one more kink into the picture the customized CRM is on 1.1 and we'll want to install it on 1.2. Is this possible?? if you have no data you are lucky.. you can open up deployment manager on t...

active directory, exchange, crm
I am trying to figure out the best way to allocate to minimal amount of hardware the following components: active directory, exchange, crm (1+ installations), virtual server, etc. if I had only 2 actual servers, which one should have active directory, exchange, sql server, crm, virtual servers, etc.Should all CRM instances go onto virtual servers? should I use redundant disk on both servers? Also what is the best approach for windows updates in production environment? could they cause problems, crashes, etc.? I recently had a bugcheck x50 when tried running the security updates that were in...

Users of Apple Mail getting meeting requests as web links.
Apple Mail users of my Exchange server get invites as web links to the Exchange Web Access interface instead of .ics files. Can anyone point me to what setting I need to change in Exchange to make them be .ics files again? --Sterling Anderson ...

CRM Appointments and Outlook Meeting Requests
I am running Exchange 2003 w/ CRM 3.0 Customers often send me an Outlook meeting request for appointments, if the time slot is open I accept. If I try to track these in CRM I get a message 'the item type is not valid for promotion to Microsoft CRM'. This happens several times per day, for now I create those appointments manually, track them in CRM and then delete the request without sending back a notification (so they don't think I declined). This starts to get complicated when somebody proposes a new time, etc. Any suggestions? Is this behavior expected? Thanks, Sha...

Sending E-Mail through CRM Web
Hi, I have CRM 3.0 installed on one server, Exchange on another. During setup, I specified my SMTP server as the Exchange server. When I attempt to send an email I get an Invalid Association Error. I set the config file to show more details on the error and all I'm getting is "System.Web.Service.Protocols.SoapException: Server was unable to process request." The source error shows an unhandled exception, and the stack doesn't provide much help either. I found a KB article referencing this error that listed the cause as port 25 being blocked by antivirus software....

Access is Denied in CRM 3.0 after upgrading from 1.2
After an upgrade of Microsoft CRM 1.2 to 3.0 without any errors, I can not log into the Microsoft CRM web page. I get the following error "you do not have enough privileges to access the microsoft crm object or perform the requested operation" I have searched everywhere for a solution for this but i am now at a loss. During the install i recieved two warnings 1. verify domain user account spn for the microsoft crm asp .net application pool account 2. setup was unable to verify that the "trust for delegation" property is enabled for the computer object. If I create a new...

Links Problem
I have a spreadsheet which contains links to other spreadsheets on m PC. When I send the spreadsheet to a friend and they open it and clic "Don't Update" they get errors where the linked formulas are. I thought that as long as you didn't try to update the links th spreadsheet should be OK and look the same as when it left m computer. HELP !!!!!!!!!!! :mad: :mad: :mad: :mad: :mad: :mad -- Hibbste ----------------------------------------------------------------------- Hibbster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3016 View this thre...

Collect data for a SQL Server DB via Infopath
Can anyone direct me toward information that discusses using InfoPath forms as a means to capture data for a SQLServer database? http://office.microsoft.com/en-us/infopath/HP101070601033.aspx -- Susan Ramlet **please reply to the newsgroup so others may benefit** "David C. Holley" <David.C.Holley> wrote in message news:eqFN06IuKHA.652@TK2MSFTNGP05.phx.gbl... > Can anyone direct me toward information that discusses using InfoPath > forms > as a means to capture data for a SQLServer database? > ...

uninstall dpm server, but ntbackup.exe stops detecting tape device
I uninstall dpm server (no protected server) successfullu. However when i run ntbackup, it cannot detect the TAPE device anymore. How shld i approach the problem ? Update the drivers, and possibly firmware. I had a problem where DPM suddendly sent my tape offline. I flashed the firmware to the latest version and updated the drivers, it fixed it. Don't know whether it was a driver of firmware problem, maybe it just reset the drive! "IT STAFF" wrote: > I uninstall dpm server (no protected server) successfullu. > > However when i run ntbackup,...

Importing and assigning ownership to crm user
Hi there, I am importing data into CRM database using Scribe software, source data is also on SQL tables. Importing works perfectly using scribe adaptor. But I want to assign ownership to the imported records. I am not sure where the owner ID and Owner names are stord in the CRM tables. Can anybody help? Regards, Kyaw You need to set the OwningUser filed to the unique identifiier of the system user. You can find the unique identifiier values in the Systemuserbase table under the systemuserid column. Laz "Kyaw" <no@spam.net> wrote in message news:%23zeRN6P0EHA.345...

SQL 2005 Express Error
Has anyone received a fatal error when SQL Express is trying to install during the installation of the Outlook client? If so, can someone please help? ...

I am unable to open links
I am unable to open links in my received messages I keep getting a message up 'this operation has been canceled due to restrictions on this computer please contact administrator' I have checked my security setting and they seem ok, please help!!! This is a problem with IE, its not an Outlook error. You need to reset your internet settings in IE's Tools, Internet Options, Advanced tab. (Or Control panel, Internet options, Advanced tab). See http://www.slipstick.com/problems/link_restrict.htm for the latest information. -- Diane Poremsky [MVP - Outlook] Outlook T...

Now I have MS CRM Installed
Okay, I have finally got MS CRM installed. No when I open my browser and point it to the VS, I the browser simply closes. What next? Has anyone experienced this? Thank you in advance! -- Bob Mixon http://attunesystems.mywsssite.com http://www.sharepointblogs.com/bmixon remove any popup blocker from ur browser. -- Regards Ashfaq Ali "msnews.microsoft.com" <bmixon@bizla.rr.com> wrote in message news:uHqkmAGqEHA.644@tk2msftngp13.phx.gbl... > Okay, I have finally got MS CRM installed. No when I open my browser and > point it to the VS, I the browser simply clos...