slxdeveloper.com Community Forums

The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!

External Development Forums - SalesLogix OLEDB Provider
Forum to discuss using the SalesLogix OLE DB Provider from external applications (including usage of built-in provider procedures). View the code of conduct for posting guidelines.

Forums RSS Feed

Back to Forum List | Back to SalesLogix OLEDB Provider | New Thread

View: Search:

Author

Thread: SLX OLEDB Provider vs Linked Server

Vladimir
Posts: 93

SLX OLEDB Provider vs Linked Server Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 26 Apr 07 10:17 AM

How to create linked server using SLX OLEDB Provider?

I use this options:
Linked Server = SalesLogix_Server
Data Source = HSLX2
Provider String = SLXOLEDB.1;Data Source=HSLX2;Initial Catalog=SLX_DB_PILOT_LINKED;User Id=Admin

assword=password

ersist Security Info=True;
Catalog = SLX_DB_PILOT_LINKED

Error occured when I trying execute any query. For example: select * from saleslogix_server...account

Error: OLE DB provider 'SLXOLEDB.1' returned an invalid schema definition.
OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid schema definition.].

I need linked server for synchronization. Changes does not synchronize if query executes in stored procedures.

Any suggestions?

[Reply][Quote]

Vladimir
Posts: 93

Re: SLX OLEDB Provider vs Linked Server Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 26 Apr 07 12:18 PM

SalesLogix 6.2.5
MS SQL 2000 sp4

[Reply][Quote]

Phil Parkin
Posts: 819

Re: SLX OLEDB Provider vs Linked Server Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 26 Apr 07 11:33 PM

Hi Vladimir - it's obvious that your 1st language isn't English - and I'm doing my best, but I can't quite understand what's going on here. Why are you trying to create a 'linked' server. I'm not even sure I know what you mean - are you talking about 'linked server' in the SQL Server sense?

Can you explain a bit more about the problem that you are facing that led you to think that a linked server was the answer? Maybe there is another solution.

Phil

[Reply][Quote]

Vladimir
Posts: 93

Re: SLX OLEDB Provider vs Linked Server Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 27 Apr 07 2:30 AM

Yes, you are right. My first language is not English

Our client has remote offices: 1 Main Office and ~50 Remote Offices. There are jobs in the SQL server at the Main Office. Jobs execute stored procedures. "Insert", "Update" and "Delete" queries in stored procedures does not synchronize to remote offices. If queries will execute using SLXOLEDB Provider the changes at main office will syncronize to remote office.
I am using MSSQL DTS.

Function Main()

Dim objConn

on error resume next

set objConn = CreateObject("ADODB.Connection")
objConn.Open "Provider=SLXOLEDB.1

assword=password

ersist Security Info=True;User ID=admin;Initial Catalog=SLX_DB_PILOT_LINKED;Data Source=HSLX2;Extended Properties=""PORT=1706;LOG=ON;CASEINSENSITIVEFIND=ON;"""
objConn.Execute( DTSGlobalVariables("SQL").Value )

if err.number <> 0 then
objConn.Execute("INSERT INTO DTS_TEST(STR) VALUES(' " & err.description & replace(DTSGlobalVariables("SQL").Value,"'","''") & " ')")
end if

objConn.Close

Main = DTSTaskExecResult_Success
End Function

In stored procedure:
set @SQL = ''
set @CMD = 'dtsrun /S "HSLX2" /U "sa" /P "password" /N "SLXSync" /A SQL:8="'+@SQL+'"'
exec master..xp_cmdshell @CMD,NO_OUTPUT

All work properly but very long time (1 query = 1 second)

Now I trying to configure linked server using SLXOLEDB Provider.

P.S. Sorry for my English

[Reply][Quote]

Jeff Crawford
Posts: 52

Re: SLX OLEDB Provider vs Linked Server Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 27 Apr 07 11:24 AM

May I ask your need for using stored procedures on a remote enabled implementation of SalesLogix?

Assuming that all database update requests are instigated by the SalesLogix application and not an external application (i.e. Company Web Site), one should not use stored procedures on a remote enabled implementation of SalesLogix, but rather use SalesLogix internal coding solutions. If an external application is instigating the update requests, there are other ways to accomplish the updates, within the parameters of the SalesLogix provider. Such as writing the information to a temporary table, which SalesLogix could scan and process. Another way would be to enable to the external application to use the SalesLogix provider and write directly to the database.

It just sounds like you may be trying to reinvent the wheel, by creating your own synchronization method and it may be more headache than it’s worth.

[Reply][Quote]

Phil Parkin
Posts: 819

Re: SLX OLEDB Provider vs Linked Server Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 28 Apr 07 1:46 AM

One way to do this in DTS is to perform all of your INSERTS, UPDATES and DELETES explicitly, via an SLX OLEDB Provider connection.

These SQL statements will run on the main database and then synchronise out to your remotes.

Bear in mind that you need to be careful if you are using data that exists only on the main SQL Server to update the SLX database. A (pseudo) statement such as

update account
set userfield1 = externaltable.field1
from account
join externaltable on account.accountid = externaltable.accountid

may run fine on the main db. But when it is synced out to remotes, it will not run, because the external table does not exist on the remotes' SQL Server instances.

In this case, what you need to do in your DTS is create a recordset containing the data that you want to update in SLX and then loop round the recordset, performing the updates explicitly:

create recordset
do until recordset.bof or recordset.eof
update account
set userfield1 = recordset.fields("field1").value
where accountid = recordset.fields("ID").value
next record
loop

etc. Obviously, this is not actual code.

When these statements get synced out to remotes, they execute fine, because there is no dependence on external data.

But if there's a lot of updates to be done, the length of time it takes to sync could increase hugely.

Good luck.

[Reply][Quote]

Sergey
Posts: 1

Re: SLX OLEDB Provider vs Linked Server Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 25 Jul 07 12:13 PM

Hi, Vladimir!
I hope you was able to solve your problem with linked server....

But any way I want to share with you my solution:
1.select "SalesLogix OLE DB Provider" as "Provider Name:" for linked server;
2.click on "Provider Options" and make sure that only "Allow InProcess" is only option checked;
3.Enter "Provider String:" as
Provider=SLXOLEDB.1

assword=pwd

ersist Security Info=True;User ID=admin;Initial Catalog=saleslogix;Data Source=SLXPROD;Extended Properties="PORT=1706;LOG=ON;CASEINSENSITIVEFIND=ON;AUTOINCBATCHSIZE=1"
where "Initial Catalog" is equal your SLX alias name;
"Data Source" is equal name of your SLX server....
4.On security tab select "Be made using this security context" and enter Saleslogix username and password;
5.On the "Server Options" tab I have "Data Access"/"RPC"/"Use Remote Collation" checked.

--------------------------------------------------------
I was able delete/update SLX data with remote syncronization thought this server (using OpenQuery). One problem I found with access to [text] fields.

Thank you,
Sergey

[Reply][Quote]

Page 1 of 1

You can subscribe to receive a daily forum digest in your user profile. View the site code of conduct for posting guidelines.

Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!

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

page cache (param): 11/26/2024 8:32:03 AM