11/21/2024 4:50:37 PM
slxdeveloper.com
Now Live!
|
|
| |
| 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 24439 times
|
Rating:
 - 5.0 out of 5 by 5 users |
|
|
|
fiogf49gjkf0d
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.
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 )
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.
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.
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 )
Drop the custom SalesLogix table from the SalesLogix database using Enterprise Manager.
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
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
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.
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
|
|
|
|
|
|
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]
|
|
|
- subject is missing.
- comment text is missing.
|
|
|
| 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. | |
|
| 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 | |
|
| 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?
| |
|
| 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 | |
|
| 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
| |
|
| 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 | |
|
| 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
| |
|
| 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 | |
|
| 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 | |
|
| 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! | |
|
| 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. | |
|
| 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 | |
|
| 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. | |
|
| 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...
|
|
|
|
|
|