07-27-2009 03:37 PM - edited 07-28-2009 11:12 AM
I would like to perform transact-sql and/or database triggers and would like to take advantage of some SLX provider features (auto-increment, get_id, ect) from a server that can't have the SLX provider installed. I was hoping that the Linked Server feature in SQL server would be an option, but getting held up. Here is how I created the link.
sp_addlinkedserver
@server= 'SLX' ,
@srvproduct= '',
@provider= 'SLXOLEDB.1',
@datasrc= 'Corp-SLX02',
@provstr='Initial Catalog=ESS;Provider=SLXOLEDB.1;
Data Source=Corp-SLX02;
Password=******;
Persist Security Info=True;
User ID=admin;
Extended Properties="PORT=1706;LOG=ON;CASEINSENSITIVEFIND=ON;AUTOINCBATCHSIZE=1;SVRCERT=;"'
This seems to be close to working, but when I run a simple query:
select * from slx.ess.sysdba.account
I get the following error:
OLE DB error trace [Non-interface error: Unable to use qualified table names (schema or catalog) with the provider.].
Msg 7316, Level 16, State 1, Line 1
Could not use qualified table names (schema or catalog) with OLE DB provider 'SLXOLEDB.1' because it does not implement required functionality.
Has anyone had success using the SalesLogix v7.5 provider in the type of situation? If so can you spot the problem in my setup or the way I am querying the table?
Thanks in advance,
Dennis
07-31-2009 05:40 AM
07-31-2009 06:14 AM
Thanks for the suggestion Mike.
Select * from slx.ess.account OR Select * from slx.account
This provides the following error:
Invalid object name 'slx.ess.account'
The object requires 4 parts. If I try
Select * from slx.ess..accounts
I get the same message from the original posting. I also tried associating my domain account with a DB user with the sp_addlinkedsrvlogin procedure and found that Admin/slxpass still provides the above errors, but if I try a non-slx user (sysdba, sysdbaPass) I get authenication error. So it seems to be getting to the provider and database.
I guess at this point we assume that the provider can't be access in this manner. Would be nice to know, or add to a wishlist, if this is a huge undertaking to make it accessable. There are many situations that accessing the database through the provider would be a huge help.
Dennis
07-31-2009 09:58 AM
07-31-2009 10:12 AM
Oh yea. I've done this before and you have to structre the query differently. It's been awhile for me too
11-27-2009 04:25 AM
Has anyone work with linked server over SLXOLEDB?
I try to use OPENROWSET and it work correctly, but i want use construct SELECT * FROM SLX_SERVER.CATALOG_NAME.SCHEMA_NAME.TABLE_NAME.
11-27-2009 07:38 AM
11-30-2009 11:40 AM
From within an SLX script, you should be able manually create a connection to another SLX database and run queries against it. Hovever, I don't know how you would query an SLX database using the Provider from a machine that doesn't have the Provider installed, which is how I read the orginal post.
12-01-2009 10:34 AM
That is right Dan. I am able to use the SLX provider in SLX scripts and in SQL DTS jobs where I can designate the connection string with the SLX Provider info.
However IF I could get the Linked server to work (the SLX Provider is on the SQL Server) then I could use it more efficiently. One of the basic features that would save time is if I could use Auto-Increment feature in a SQL Trigger or Stored Procedure. By pointing the insert to the 'Linked Server" (which is the same DB, just using the SLX Provider instead of Native SQL) I would not have to jump through hoops to get an acceptable SLX ID for my new records.
There are a lot of other uses that are probably solved by using SData or Web Services, but Linked Servers was (I thought) a quick and easy way to connect to a database from SQL Enterprise Manager, using the SLX provider.