Followed steps in Link thanks however, I am getting this error when running any query

I actually only do have a single user licence so what licence do I need for a linked server?

Cheers

Damien

I'm referring to DataFlex 2.3b and 3.01b applications running a Single User DataFlex runtime. I suggest you use VB Demo to see what tables you can query and the ones that need reindexing.

FlexODBC v4 doesn't have single user licenses, FlexODBC is sold in bundles of computer seats.

I suggest you use VB Demo to see what tables you can query
I have done this and tried different tables. I have other linked servers (different products though MS Access) so I am familiar with the syntax. However, FlexODBC linked server is not working. Perhaps I will try a re-index and try again. These tables are fine to access normally through the ODBC though such as VB Demo, SQL 2005 SSIS packages etc so don't think this is a problem.

Cheers

Damien

I followed the steps to set up the linked server, when I click test connection, it tells me that the test connection to linked server succeeded. But when I use select statement as: select * from openquery (dataflex, 'select top 10 * from tablename') it give me following error and the sql server is shutdown.

Try something a little more basic like:

SELECT * FROM dataflex...tablename

Yes, I've tried using select * from dataflex...tablename, same error comming back and sqlservice is shutdown. it seems like the server has no problem sending the query to dataflex but only when receiving data back from dataflex. The system DSN works fine when using VB demo and in IIS. it also works with excel query and MS Access linked table, it's just this SQL Linked Server with which I would like to build some view inside SQL Server to have an extra layer of security and usability.

I'm using SQL server 2005 with SP2 installed. I also tried on SQL 2000, same error message. What could be wrong?

Thanks a lot

When this didn't work, it can be because the Ole Automation Procedures Option is turned off in the SQL Server Surface Area Configuration.

To turn it on with a query, see:
http://msdn2.microsoft.com/en-gb/library/ms191188.aspx

which contains:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

this option doesn't help much. I've tried to turned it both on and off. The behaviour is not consistant on either mode. Sometime it can get result back while other same amount of times I got error message.The performance is not consistant either. sometime it took more than 10 sec. to get 3 records back from dealers table and sometimes it only took a sec...(the data, the odbc driver and the sql server is located on same disk)....the processing message box which shows only on evaluation version also pops up on some cases although I'm using production version...

 I created another linked server linked to network data drive, this linked server never worked.. I always receive error message no matter the configuration. while the System DSN used in remote drive linked server have been tested both in VB demo and in IIS Application.. And the DSN name is unique in the box....

it looks to me that something is wrong in communication level between sql server and flexodbc... maybe you can give me more insight on this issue?..

Thanks a lot.

Louisa