I have an external SQL database on a separate server and I have a table from that database linked to Sage.  My question is, when I go to the customization screen for that table there is not a tab to create a view for that table.  I want to create a view that includes the external table as well as the person table so I can combine those two tables in the same lists, screens, and reports.  I've tried creating the view in the Person customization screen but I keep getting errors when using the external table name in the view.  Since it is in a separate database on a different server do I have to enter the table name using a particular syntax?

Hi,

Do you still need help with this thread, it’s a little bit old...

Best Regards,

Ibrahim Younis

iyounis@uba.com.jo

United Business Applications (Jordan)

Yeah, I could still use some help.  The post isn't old though, I just posted this on Friday.

Hi,

My bad reading the thread date, below you can find the solution for your issue, hopefully you are using SQL server 2005 on both machines,

1- On the foreign server create new server link that points to the CRM server.

2-create a new view called testview on the foreign server that join the data from both servers, assuming that the foreign server name is ubaserver01 and the CRM server name is Ammari, for example:

SELECT  company.Comp_CompanyId, company.Comp_Name, person.pers_firstname

FROM ubaserver01.crm.dbo.company as company,ammari.crm.dbo.person as  person

where  person.pers_personid=company. Comp_primarypersonid

3- go to your CRM --> administration --> advanced customization --> tables and databases --> create new table connection (first you will need to create new database connection, unless you did it before) --> in the table name filed write the view name "testview", and complete the other fields in the regular way.

4-now you can use this view in screens and lists and you can filter the data based on the view selected fields.

please let me know if you need more help regarding this subject.

Regards,

Ibrahim younis

iyounis@uba.com.jo

united business applications (Jordan)

Thanks for your reply.  This does help me somewhat because I didn't think about creating the view directly in SQL Server.  However I still have the question of how can I use that view in a Sage report since Reports seem to only be able to use views created in Sage.

Hi,

you can create a new view on the person table that read from the foreign view for example:

create view as foreginreport as select * from 'yourforeighnviewname'

try to change the foreign view name in the above new view by adding/removing segments from servername.dbo.databasename.viewname

Regards,

Ibrahim younis

iyounis@uba.com.jo

united business applications (Jordan)

Ok, I am almost there.  For simplicity's sake I moved the foreign database to the same server as sage.  I added the new database connection, then the new table connection.  I then successfully created a view under Person for the foreigntable.  I even was able to create a report on that view and see all of the fields in the report builder screen.   However, even though I can successfully create the report, when I run it the report shows no data.  I even used the fewest fields and broadest terms that I know should have returned something.  What is odd is it seems Sage has happily accepted all of these modifications and gave me no errors or indications to make me think there is something wrong.

You have been a tremendous help and I really appreciate your assistance.

Hi,

run the SQL profiler before running the report until the end, then save the trace data, then run any regular working CRM report and compare both traces, we can provide some support if you can share the corrupted trace file.

Best Regards,

Ibrahim Younis

iyounis@uba.com.jo

United Business Applications (Jordan)