x

linked-in server ODBC DSN connection Table not found

I am trying to create a linked server in sql server 2005 to a clarion table. In a test box with sql server installed it works fine. When I move it to a live sql server it gives the following message when I run a openquery against it. the linked-server connection tests fine "[SoftVelocity Inc.][TopSpeed ODBC Driver][ISAM]ISAM Table Not Found" everything matches (well obviously something doesn't). Could there be a setting in sql server that might cause this?

more ▼

asked Mar 18, 2010 at 10:11 PM in Default

mhj96813 gravatar image

mhj96813
12 2 2 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

is there any difference in the network - domain / workgroup - between the test and the production boxes? Are you logged on as the same user? can you create a ODBC DSN on the server via Windows that is successful?

Quick tip - create a file on the desktop of your prod SQL Server called "clarion.udl" and then double click it. This will open a dialog that allows you to enter the connection details and then test it is successful. This works regardless of installed software so can be used to diagnose issues from ANY hardware...

more ▼

answered Mar 19, 2010 at 08:00 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

thanks for the response.

I created a udl file and the test connection button returns test connection succeeded. Same user in all cases. same network. same domain, same sub domain.

I remember somewhere reading about a sql setting to allow adhoc queries or connections or something. I'm leaning towards this being some setting in sql. The prod server probably has more recent security patches.
Mar 19, 2010 at 10:11 AM mhj96813
Also, I am using select * from openquery(Linked-server-name, 'Select * from tablename') to test
Mar 19, 2010 at 10:52 AM mhj96813
It may be worth reviewing the setting in the SQL Server Surface Area Configuration Manager to make sure that's not affecting this but the error saying "Table Not Found" suggests to me it's permissions within SQL rather than connectivity to the server/database.
Mar 20, 2010 at 07:39 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x111
x37

asked: Mar 18, 2010 at 10:11 PM

Seen: 2925 times

Last Updated: Mar 18, 2010 at 10:11 PM