Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Tuesday, May 21, 2019 
 
How to Create a View in a SalesLogix Database that Points a Table in a Different Database on a Different Server  
Description:  This article shows you how to create a view in your SalesLogix database that looks and acts like a normal SalesLogix table from within SalesLogix applications, but is actually accessing data on another server.

Category:  Architect How To Articles
Author:  David Nelson
Submitted:  6/13/2003
   
Stats: 
Article has been read 21260 times

Rating: - 5.0 out of 5 by 5 users
 

fiogf49gjkf0d
How to Create a View in a SalesLogix Database that Points a Table in a Different Database on a Different Server

This article shows you how to create a view in your SalesLogix database that looks and acts like a normal SalesLogix table from within SalesLogix applications, but is actually accessing data on another server.

Scenario

The table I want to access is the customer table in the dntest database on server2. The SalesLogix database is not on server2.

My customer table looks like this:

Create table customer (
    custid int identity (1, 1) not null ,
    custname char(25) null ,
    custemail char(128) null ,
    custamtdue money null )


STEP 1: Link the servers (if necessary)

If the table is on a different SQL server than SalesLogix, as mine is, link the servers. If you don’t know how to do it, see SQL Server Books Online under the title "Configuring Linked Servers". If your table is on the same SQL server as SalesLogix, linking servers is not necessary.

STEP 2: Verify access to the customer table

Make sure you can query the customer table from within Query Analyzer connected to the SalesLogix SQL server as the sysdba user with a query like: select * from server2.dntest.dbo.customer

While you’re at it, unless you have other ideas, I would make sure the sysdba user has read only access to the table.

STEP 3: Create a similar custom table in SalesLogix

The customer table will be represented in the SalesLogix database by a view named c_customer. Create a table in SalesLogix using the Administrator or Architect Database Manager as close as possible to the customer table columns. I suggest you turn off the “Sync this table…” options while creating the table. Examine and document the details of the SalesLogix table, after you create it, using Enterprise Manager. My custom SalesLogix table end up looks like this:

Create table c_customer (
    c_customerid char(12),
    createuser char(12),
    createdate datetime null ,
    modifyuser char(12),
    modifydate datetime null ,
    custname varchar(25) null ,
    custemail varchar(128) null ,
    custamtdue numeric(15, 2) null )


STEP 4: Drop the the SalesLogix custom table

Drop the custom SalesLogix table from the SalesLogix database using Enterprise Manager.

STEP 5: Create the view in the SalesLogix database

Create a view that exactly duplicates the original SalesLogix table with column names and data types being the same. Hopefully, there is a column in the external table that can be converted into a char(12) and represent a unique value per row like SalesLogix keys do. If not, you might have a problem and might need to create one (I don't know since I have tried linking a table without that). Here is what my view looks like:

Create view saleslogix.sysdba.c_customer as 
    Select 
    Cast(custid as char(12)) as c_customerid, 
    Cast('admin' as char(12)) as createuser, 
    Cast('2003-01-01' as datetime) as createdate, 
    Cast('admin' as char(12)) as modifyuser, 
    Cast('2003-01-01' as datetime) as modifydate, 
    Cast(custname as varchar(25)) as custname, 
    Cast(custemail as varchar(128)) as custemail, 
    Cast(custamtdue as numeric(15,2)) as custamtdue 
    From server2.dntest.dbo.customer


STEP 6: Test the view in Query Analyzer

Make sure you can query the c_customer table using Query Analyzer connected as the sysdba user with the default database set to the SalesLogix database with a query like: select * from sysdba.c_customer

STEP 7: Test the view in SalesLogix

Make sure you can query the table from within SalesLogix using Administrator Tools / ExecuteSQL using something like: Select * from c_customer

The external table should now be ready for use from within SalesLogix just like a normal SalesLogix table. If the external table is on a different server, access to the table may be slower than normal. This table is fine for read only access by network clients. If you are going beyond that, you have issues to deal with.


Editor's Note

An alternative approach to creating an identical table first in SalesLogix and deleting it in SQL Enterprise Manager is to simply create your view normally and then add an entry to the RESYNCTABLEDEFS table in the SalesLogix database for your view. Your view will need to have a unique field that can be considered it's primary key, but by adding the entry for your view in the RESYNCTABLEDEFS table the view will show up as a standard SalesLogix table.
-Ryan

 

About the Author

David Nelson
(SalesLogix Business Partner)
Accel Solutions group, Seattle, Washington

fiogf49gjkf0d


View online profile for David Nelson
 

[ back to top] [ send to a friend]  

Rate This Article you must log-in to rate articles. [login here] 
 
Please log in to rate article.
 

Comments & Discussion you must log-in to add comments. [login here] 
 
Author Article Comments and Discussion
Ted Sturr



Also there is an issue with having the views work in Support
Posted: 6/13/2003 1:01:25 AM
fiogf49gjkf0d
I documented this on the newsgroups, but if you are doing customizations within support 5.2 (haven't checked the behaviour in 6.x yet) the views will not work properly. Same view that worked in the sales client would bomb on me in the support client. Just an FYI.
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Excellent...
Posted: 6/13/2003 11:50:01 PM
fiogf49gjkf0d
Excellent article David. I know that this is something that many SalesLogix implementations would benefit from. Probably not done more often because people are unsure how to accomplish it. It is great to have it so clearly documented here.

-Ryan
 
Ramiro S. Justet
 

with Sales Logix v 6.0 SP 1
Posted: 7/25/2003 12:16:57 PM
fiogf49gjkf0d
I to try to create a view in different server with linked server; but when I deleting the table in Enterprise Manager, the view in Database Manager of SLX don't show the fields, only shows the name of the table (view) on the left panel, the right panel is empty and the view doesn't work.
I was sure that I have follow all the steps in this article, but doesn't work in SLX 6.01.

Any sugestions about that?
 
Paul Neth
 

Views dont show up in SLX 6.1
Posted: 7/28/2003 12:37:48 PM
fiogf49gjkf0d
I made the table in Architect, dropped table and added view. This works in SLX Execute SQL. But the view names do not show up in any table lists (Edit query for datagrid, join manager or "Manage Database") Is there some setting for table to make the view show up again? I did not change anything in RESYNCTABLEDEFS.

Thanks
P
 
Enrico Chiesa
 

SLX 6.1
Posted: 7/28/2003 1:24:45 PM
fiogf49gjkf0d
Remember that in SLX 6.1 they use the view OA_TABLES to list the tables, so if you modify this view adding

when 'VIEW' THEN 'TABLE'

all work rights.

-Enrico
Here the complete alter view.

ALTER VIEW OA_TABLES AS
select CAST(TABLE_CATALOG as VARCHAR) TABLE_QUALIFIER, CAST( TABLE_SCHEMA as varchar) TABLE_OWNER, CAST( TABLE_NAME as VARCHAR) TABLE_NAME,
case TABLE_TYPE
when 'BASE TABLE' THEN 'TABLE'
when 'VIEW' THEN 'TABLE'
else TABLE_TYPE
end TABLE_TYPE
, '' TABLE_STRUCT,
'' TABLE_PATH, '' OA_USERDATA, '' OA_SUPPORT, '' REMARKS
from INFORMATION_SCHEMA.TABLES
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: SLX 6.1...
Posted: 7/30/2003 7:50:44 PM
fiogf49gjkf0d
Enrico, Hey that is cool info. I just tried this and it worked great. Pretty cool.

-Ryan
 
Dhany



Oracle Problem
Posted: 8/13/2003 2:10:31 AM
fiogf49gjkf0d
I've tried the above example on Saleslogix 6.1 and Oracle 8.1.7
Here are the steps I took :
1. Create a DBLINK to another database on another server
2. Create a view that warp up a table in the DBLINK
3. Alter the OA_TABLES view query to include my newly created view as a TABLE instead of VIEW
4. Add a record to RESYNCTABLEDEFS table to point to my view.

- I was successfully query the view in SQL Plus
- The View registered successfully in OA_TABLES as a TABLE
- My newly created view appears as a table in SalesLogix Manage Database screen

The problem is : I cannot see any field in my table !! (either from Manage Database, or manage Join)
And since i cannot see the field, i cannot make any Data Form based on that table.

Any help to overcome this trouble is really appreciated.

Regards,

Dhany
 
Dhany



Re: Oracle Problem
Posted: 8/13/2003 4:44:06 AM
fiogf49gjkf0d
Whoa...
After some trial and error and a lot help from Google, this is how to workaround this issue.
1. Edit the registry value of HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OLEDB\DistribTX to 0.
By default this value will be set to True (1). Some articles mention about setting the property in the connection string, right when you're about to begin a new connection. But since I don't know where to change the OLE DB Connection string in SalesLogix OLEDB Provider, I think modifying the registry would be the best way. Any help on this issue is welcomed.

2. Restart your Saleslogix OLEDB Provider :)
I spent almost two hours figuring this out until I realize that the Saleslogix OLEDB Provider has to be restarted before the change in the registry can be effevtive. You can do this by simply restart the service in your Service MMC.

And voila... your table and your fields from DBLINK appears in the Manage Database...:)
Hope this help,

Dhany
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Warning about the OA_TABLES view
Posted: 10/6/2003 1:08:18 AM
fiogf49gjkf0d
I thought I should mention, the method of altering the OA_TABLES view that was brought up in some of these comments may introduce some issues with SLX 6.2. The OA_TABLES view will no longer be used in 6.2 which means it will no longer be created on new installs.

So, don't grow to rely on that method too much as you'll be stuck when you start seeing new 6.2 installs and it doesn't exist.

-Ryan
 
Alissa Gibbs
 

Alternate Fix for version 6.2?
Posted: 3/16/2004 10:52:52 AM
fiogf49gjkf0d
All this information is great but I'm hesitant to work around this by changing the OA_Tables data if it isn't going to help in v6.2. I followed the process to that point (Adding the table through the datbase manager, dropping it and then adding the view) The data is successfully obtained when I use the Execute SQL tool in the Administrator.

Still my problem is that the table doesn't show up in the db manager and it does show up in the Join manager (but without any fields). There is definitely an entry in the resyncTableDefs table for it. Anyone know of any other fixes for this?

Thanks!
 
rich solomon
 

Re: How to Delete a View from a SalesLogix Database that Points a Table in a Different Database on a Different Server
Posted: 9/15/2004 5:46:44 PM
fiogf49gjkf0d
I finally tried this and it's cool and potentially useful. Only one question: how do you get rid of the view from the database manager? I'm using 6.1.1a with SQL 2000.
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: How to Create a View in a SalesLogix Database that Points a Table in a Different Database on a Different Server
Posted: 9/15/2004 5:49:08 PM
fiogf49gjkf0d
Hi Rich,

Good question. It should work to simply delete the entry from the RESYNCTABLEDEFS table. You'd want to do it in a way that allows it to sync out since the original entry would have synched out to remotes as well.

Let us know if that works (or doesn't work)

-Ryan
 
Craig Herder
 

Re: How to Create a View in a SalesLogix Database that Points a Table in a Different Database on a Different Server
Posted: 11/10/2005 3:08:09 PM
fiogf49gjkf0d
Hi.

I would really be interested in knowing how to work this in 6.2. This provides a possible way of viewing realtime data from the backoffice, instead of waiting for a scheduled sync. I might possibly include this as part of a current project, so I would really appreciate it if someone has the workaround.

Thanks.
 
Brett Burch
 

Re: How to Create a View in a SalesLogix Database that Points a Table in a Different Database on a Different Server
Posted: 8/22/2006 5:51:59 PM
fiogf49gjkf0d
Just an FYI for 6.2, I just tried this and had no problem. You just need to (1) create the view, (2) add a row to the RESYNCTABLEDEFS table, and (3) run the OA_TABLES alter statement mentioned here.
 
 

       Visit the slxdeveloper.com Community Forums!
Not finding the information you need here? Try the forums! Get help from others in the community, share your expertise, get what you need from the slxdeveloper.com community. Go to the forums...
 



 slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation.
 This site, and all contents herein, are Copyright © 2019 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.

code of conduct | Subscribe to the slxdeveloper.com Latest Article RSS feed
   
 
page cache (param): 5/21/2019 2:40:10 PM