SQL Linked Servers and SLXOLEDB.1 [ Edited ]

[dfoster]

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

Message Edited by dfoster on 07-28-2009 11:12 AM

[mboysen]

Have your tried it without "sysdba"? Normally, this will not work through the provider, which is what that message may be telling you.

[dfoster]

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

[tagersea]

Dennis, it has been a long time since I did this but I believe you have to use OPENROWSET. Look it up in SQL Books Online to get the syntax.

Hope that helps!

[mboysen]

Oh yea. I've done this before and you have to structre the query differently. It's been awhile for me too

[SadJester]

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.

[RJLedger]

This is not supported via the SalesLogix Provider even if it were direct and not linked.

[DanCarvin]

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.

[dfoster]

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.