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!
|
|
Can you create a linked server using SalesLogix OLEDB Provider
Posted: 26 Jan 10 11:49 AM
|
| I want to run inserts and updates into SLX from external sources and I want to run them completely form SQL code. To do this I would need to be able to use the SalesLogix OLEDB Provider. So I'm wondering if it is possible to setup a linked server using this provider? Or is there a better way of doing this? |
|
|
| |
| |
|
Re: Can you create a linked server using SalesLogix OLEDB Provider
Posted: 03 Feb 10 9:47 AM
|
| We do it 'all' the time in SQL Server 2005. We do a lot of non Provider stuff (we aren't synching) and the linked server allows T-SQL, Stored Procedures, et al to access the SLX Stored Procedures that are only available through the SLX OLE DB Provider......Getting/generating SLX ID's being the #1 reason. |
|
|
|
Re: Can you create a linked server using SalesLogix OLEDB Provider
Posted: 03 Feb 10 1:30 PM
|
What SQL Server Version (2000, 2005, or 2008) are you using? How do you setup the Linked server? When I setup the linked server the link works but running s simple SQL statement like 'Select * From Account' I get the following error:
Cannot use qualified table names (schema or catalog) with the OLE DB provider "SLXOLEDB" for linked server "slx_ole_test" because it does not implement required functionality.
Any thoughts?
Thanks |
|
|
| |
|
Re: Can you create a linked server using SalesLogix OLEDB Provider
Posted: 04 Feb 10 9:46 AM
|
Select * From SLX_OLE_TEST.PRODUCTION.sysdba.ACCOUNT
The error is:
Cannot use qualified table names (schema or catalog) with the OLE DB provider "SLXOLEDB" for linked server "SLX_OLE_TEST" because it does not implement required functionality.
I'm using SQL 2005.
I can see all the tables and views through SQL Server Management Studio so it appears that the linked server is working but I cannot do a simple select statement. Any thoughts? |
|
|
| |
|
Re: Can you create a linked server using SalesLogix OLEDB Provider
Posted: 04 Feb 10 10:02 AM
|
I guess I don't understand what is being said by:
"and the linked server allows T-SQL, Stored Procedures, et al to access the SLX Stored Procedures that are only available through the SLX OLE DB Provider......Getting/generating SLX ID's being the #1 reason."
Can we obtain ID's without connecting to the SLX using the SLX OLEDB connection/provider? |
|
|
|
Re: Can you create a linked server using SalesLogix OLEDB Provider
Posted: 04 Feb 10 1:20 PM
|
Might be easier to create a view in the db under sysdba and reference that (not into a slx grid directly since not in sectabledef), the view can reference anything at that point.
Or issue a "exec getmydata" sp. This can do it all and through the provider as well (even create and drop the link).
Be careful what you do here, the thrashing can be nasty if tables are large or trying to join over the link. There are special cmds to assist with limiting thrashing. |
|
|
|
Re: Can you create a linked server using SalesLogix OLEDB Provider
Posted: 05 Feb 10 11:10 AM
|
I am running SLX in an Oracle environment, not SQL Server. Is there a way to generate SLX ids (accountid, contactid, addressid) through a stored procedure in PL/SQL? If so, how? If not, is there any other way to do this via Oracle? I am going to be building a fairly complex PL/SQL script to process a file containing account and contact information and import it into our SLX 7.5.2 database, and would like to automate the process of generating the ids.
Any advice would be appreciated.
Karen |
|
|
|
Re: Can you create a linked server using SalesLogix OLEDB Provider
Posted: 05 Feb 10 11:44 AM
|
Hi Dean.
Yes, you definitely can. I was able to get my linked server to work yesterday. I too, ran into the same error as you ... Here's how I ran a simple select statement against my linked server:
EXECUTE ('SELECT * FROM sysdba.Account') AT [Linked Server Name]
GO
Let me know if this works for you or not.... |
|
|
| |
| |
|
Re: Can you create a linked server using SalesLogix OLEDB Provider
Posted: 05 Feb 10 12:41 PM
|
How can you connect to a linked server in SSMS using the SLX OLEDB Provider? I can see no way of choosing provider when connecting.
edit - ah, I see what you mean - just testing
edit 2 - yes indeed, that statement works. So how do we use this to generate new SLX IDs? Getting too late in the day for my brain to engage those thoughts. |
|
|
|
Re: Can you create a linked server using SalesLogix OLEDB Provider
Posted: 05 Feb 10 12:56 PM
|
| Yes, you will not see SLX OLE DB Provider as an option to connect to in SSMS. Once you setup your linked server, when running queries, ie: a simple select statement, you either need to explicitly define the linked server name as part of the statement (SELECT * FROM [LINKEDSERVERNAME].[DBNAME].[SCHEMA].[TABLENAME]) or you can use the EXECUTE statement similar to what I provided earlier. |
|
|
|
Re: Can you create a linked server using SalesLogix OLEDB Provider
Posted: 05 Feb 10 1:19 PM
|
Did not work... See error below:
A severe error occurred on the current command. The results, if any, should be discarded.
My real intent of doing a linked server using SLX OLEDB is not to query the database... but rather to do Inserts, updates, delete, obtain new IDs.... Which all needs to be done through the SLX OLEDB...
Thanks for your help. |
|
|
|
Re: Can you create a linked server using SalesLogix OLEDB Provider
Posted: 05 Feb 10 1:44 PM
|
What is the command you are trying to execute? I understand that a simple select wasn't what you were looking for. It was merely an example to prove that it is possible to execute against a linked server using the SLXOLEDB provider.
That being said, I did perform an update against the linked server for one of our custom tables...
EXECUTE ('UPDATE sysdba.Mappings SET AccountID = ''Something'' WHERE AccountID IS NULL') AT SLX_TEST
GO
-- SLX_TEST is the name of the linked server
-- Mappings is our custom table.
Do you mind providing me your linked server configuration? |
|
|
|
Re: Can you create a linked server using SalesLogix OLEDB Provider
Posted: 05 Feb 10 2:01 PM
|
The query I ran is: execute ('Select * From sysdba.Account') at SLX
There are a couple of questions though... under my linked server it shows the Catalogs... I have 3 different Catalogs or SLX Databases... So even if I did not get the error how would it know which database to connect against?
If I specify the database in the query I get:
execute ('Select * From PRODUCTION.sysdba.Account') at SLX
A severe error occurred on the current command. The results, if any, should be discarded.
|
|
|
|
Re: Can you create a linked server using SalesLogix OLEDB Provider
Posted: 05 Feb 10 2:10 PM
|
-If using the provider try using the slxDbIDs("table',1) stored procedure.
-you can turn on the auto generation flag on the primary table first in dbmanager and the provider will do it for you, this can be a bit problematic at times.
-Or write a compliant sp to do it external.
-Last option Create a remotedb and then retire it. Take the sitecode and and append a counter to it and you have a unique id never to be duplicated. If you want to really follow the rules set the first change of the id to the table specific key (look at the table for what it is, its hardcoded). format would be: tableCode+sitecode+7-DigitCounter. If you dont have remotes, make up a sitecode, use you initials + counter. |
|
|
|
Re: Can you create a linked server using SalesLogix OLEDB Provider
Posted: 05 Feb 10 2:16 PM
|
Hi Dean
The value for "Catalog" in the linked server should equal to one of the "Connection Names" setup in SalesLogix Connection Manager of the server you are connecting to.
As you've noticed, when you launch the SLX Client, it asks which "Data Link" you want to Log On To. When you click "Add" it provides the user with, "SalesLogix OLE DB Provider", Server, Database Name. The "Database Name" is actually the name of the connection set up within the Connection Manager. It is this same principle that will allow your linked server to connect to your db. No need to explicitly define the db in your query because the Connection Manager already does it for you. You just need to configure your linked server similar to how you would configure the SLX Client. 
I hope this helps. |
|
|
|
Re: Can you create a linked server using SalesLogix OLEDB Provider
Posted: 05 Feb 10 2:19 PM
|
based on what you have here you can't be going through the provider.
a SLX Provider LINK will specify the SLX Server and slx Database you are connecting to, it'll also require the slx provider to be installed locally on the host sql server.
a linked sql server process under the provider means a slx client connection to the host sql server, and that sql server connecting to a linked sql server under sql, not under slx.
I hope this helps |
|
|
|
Re: Can you create a linked server using SalesLogix OLEDB Provider
Posted: 05 Feb 10 2:26 PM
|
Hi Lane,
"a SLX Provider LINK will specify the SLX Server and slx Database you are connecting to, it'll also require the slx provider to be installed locally on the host sql server." -- yes, that is correct....
"a linked sql server process under the provider means a slx client connection to the host sql server, and that sql server connecting to a linked sql server under sql, not under slx. " -- I don't understand what you mean by this?
The slx provider must be installed on both the remote server and the server you are connecting from... |
|
|
|
Re: Can you create a linked server using SalesLogix OLEDB Provider
Posted: 05 Feb 10 2:42 PM
|
The slx provider is only only needed to display info in the client obviously or to distribute data to remotes via slx synchronization. Otherwise it isn't needed. So;
1) the slx client user needs to see combined info from separate sql servers
Under this option the slx user connects using slx connection to host sql server and this server uses a linked sql connection to the other data. The provider pulls from the slx database and other data is exposed through the linked server (typically as views or sp's, these need to exist in the slx database under the sysdba user). If data needs to be pushed or manipulated a sp is best, called from slx provider exec cmd.
2) I want to pull remote sql data into the SLX database in a process and distribute it to slx remotes. Like a nightly process. I'd typically use a script to do this and generate two connections then cycle the data accross. A DTS can do it, but you need to;
a) install slx provider on the server
b) form a slx connection to the slx data - target connection - reference only slx connection names
c) generate the source data set in SQL (outside the SLX provider but it might contain slx data from a sql connection)
d) process the dts |
|
|
|
Re: Can you create a linked server using SalesLogix OLEDB Provider
Posted: 05 Feb 10 3:28 PM
|
apparently I don't have the slx provider installed locally????
When I went to the SLX sql server and setup a linked server everything works... when I did it on my local SQL Server it let me setup the linked server but would not let me run the SQL statements....
I have the SLX client installed... so I thought by default I would have the provider installed.
How do I install the provider locally? |
|
|
|
Re: Can you create a linked server using SalesLogix OLEDB Provider
Posted: 05 Feb 10 3:50 PM
|
Hi Dean
What kind of error are you getting when running the SQL Statements? Can you also verify if "SLXOLEDB" is present under the list of Linked Server in SQL Management Studio? If you need to install the provider, you'll have to get your hands on the Installation CD for SLX. There's a "Client OLE DB Provider" installation in there.... |
|
|
|