Linked Server issue

I have a problem querying a linked server connected via the OleDB provider for ODBC.

This is the DataDirect 32-BIT SequeLink 5.4 client driver connecting to a FileMaker Pro 8 database. I've tested this in VB.net 2005 by adding an ODBC connection in server explorer and a data grid to a form. I can run the tooty and the data loads in the grid with no errors what so ever.

So using the above ODBC connection I added a linked server called 'DNA' and run the following query on the Stock_DNA table.

SELECT * FROM OPENQUERY(DNA, 'SELECT * FROM Stock_DNA')

The result I get is this;

OLE DB provider "MSDASQL" for linked server "DNA" returned message "Requested conversion is not supported.".

Msg 7341, Level 16, State 2, Line 1

Cannot get the current row value of column "[MSDASQL].Stock_number" from OLE DB provider "MSDASQL" for linked server "DNA".

Now, if I specify field names in the OPENQUERY statement so that no text columns are returned the query runs fine, but anytime I include the text columns it blows up.

I also can't use four part naming with out getting an error as follows;

I give the statement;

SELECT * FROM DNA...Stock_DNA

and get this;

Msg 7313, Level 16, State 1, Line 1

An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "DNA".

Any help or work arounds would be greatly appreciated.

It sounds like the problem is not related to the ODBC connection string, but with the Linked servers that are defined within the SQL server itself. When you invoke the linked server query the actual schema does not match the schema stored by the linked server information.

I have seen this twice - and i had to use a different solution each time:

1) define an index on the table you are selecting from - sometimes SQL can get confused without one.

or

2) define a view for the data you want and select from the view with the ODBC connection string.

Hope this helps - let me know how you get on.

Mike

|||

I am also having the "Cannot get the current row value" problem with text columns from an OPENQUERY call for a MySQL linked server. Does anyone know a fix for this problem?

|||

I am also trying to connect a Filemaker Pro database with .Net. Could you post your code?

Here is my attempt to load a DataGridView with Filemaker Pro data:

string sConnString = "DRIVER={DataDirect 32-BIT SequeLink 5.4};SDSN=Sample10;HST=localHost;PRT=2399;UID=admin;";
odbcConnection1 = new OdbcConnection(sConnString);
try
{
odbcConnection1.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

//connection opens okay

//The following doesn't work:
string strSQL = "select * from FirstName";
OdbcDataAdapter da = new OdbcDataAdapter(strSQL, odbcConnection1);
System.Data.DataSet ds = new System.Data.DataSet();
da.Fill(ds, "FirstName"); //ERROR [HY000] Unable to retrieve error message from backend ODBC Driver.
dataGridView1.DataSource = ds.Tables["FirstName"];

|||Chekc whether the data types between the source and target are similar or matches the requirement such varchar to varchar or nvarchar to nvarchar in thsi case.|||

Thanks Satya. With more testing, I have found that sometimes I can pull a table out, but not always.

Types in table that can be loaded:

VARCHAR

Types in table the cannot be loaded:

DOUBLE, VARCHAR, DATE

I am loaing the data into a dataset. Is DATE a type that a dataset can't handle?

EDIT> Yes, the field that uses type DATE can't be loaded. Thanks for the tip.

|||Glad to know more from your feedback, as explained you might need to check the compatbility of fields between the source and SQL Server table.|||

I'm at the same point as Brandon C...whose question seems to not have been really addressed.

To add to his post, if I try to use 4-part naming to query the linked server (FILEMAKER8), and just make up a schema name (dbschema) and run the query "SELECT Profiles.* FROM FILEMAKER8.dbschema..Profile Profiles", I get the following error:

OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "Unspecified error".

OLE DB provider "MSDASQL" for linked server "FILEMAKER8" returned message "[DataDirect][ODBC SequeLink driver]The qualifier 'dbschema' was larger than SQL_MAX_QUALIFIER_LEN from SQLGetInfo (0).".

Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "FILEMAKER8". The provider supports the interface, but returns a failure code when it is used.

I'm gonna take a guess that the underlying DataDirect 32-BIT SequeLink 5.4 client driver doesn't support schemas? (If that makes sense).

Is it possible to force the MSDASQL provider to interpret all text column types a certain way? (don't know if that makes sense either)

|||Just an FYI,

I never was able to get the LinkedServer to work properly. I wound up creating some DSNs using the DataDirect 32-BIT SequeLink 5.4 driver on my server and a Web Service in VB.NET to access them.

Using the OdbcDataAdapter I am able to populate and return a DataSet regardless of the Data Types in the table.|||I think you are using filemaker as a linked server, if so ensure you relevant compatible drivers to perform the connection.|||

Yes, the DataDirect 32-BIT SequeLink 5.4 driver is the correct one for Filemaker. You should find the installer in your Filemaker folder ->\Extensions\xDBC Support\bin. Thanks for that tip, Brandon, I couldn't pull out some Filemaker data types, like DATE, for example.

|||Just a little more follow up.

I had to create a connection for each table or 'FORM' in FileMaker.

The code I'm using return the data set would be similar to the following;

Code Snippet

<WebMethod(Description:="This method takes a stock number and returns the" & _
" record from the Stock_DNA table.")> _
Public Function ReturnStockDNARecord(ByVal StockNo As String) As DataSet
Dim sSQL As String = ""
Dim sConnect As String = ""
Dim oConnection As New Odbc.OdbcConnection

oConnection.ConnectionString = "DSN=ABRC_DNA;UID=SYSTEM;PWD=SYSTEM;SDSN=Stock_DNA"
Try
oConnection.Open()
Catch e As Exception
LogAppError(e.Message & vbTab & e.Source & vbTab & _
e.StackTrace & vbTab & CStr(Now))
Return Nothing
End Try

sSQL = "SELECT * FROM Stock_DNA WHERE ABRC_stock_number = '" & StockNo.Trim & "'"

Dim DA As Odbc.OdbcDataAdapter = New Odbc.OdbcDataAdapter(sSQL, oConnection)
Dim DS As DataSet = New DataSet
DA.Fill(DS, "Stock_DNA")
oConnection.Close()
oConnection.Dispose()
Return DS
End Function

Simple but it does the job.
|||

Actually, that looks very much like what I have. Does it crash for you if you try to load a table with a DATE column?

|||I have a 3-part integration project:

SQL 2005 -> Filemaker 8 -> SQL 2005

All parts ideally should work seamlessly. Part of my process is to run a query of data form SQL2005 and attempt to join in data from FileMaker. Unfortunately, I've never been able to get this to work via the FABULOUS drivers designed for supposedly just this sort of task. What I've ended up having to do is do all of my joining and subsequent querying in CODE (I'm using .NET as well, works fine). This is a less than ideal situation, especially as both the filemaker server and first sql2k5 server are on the same physical machine.

Anybody else manage to actually get linked servers working?