Linked Server problem

Hi,

I have created a linked server with oracle database. Iam using SQL Server 200 and Oracle 8.0 client. I have made the registry settings as mentioned in KB280106. But when i I try to access the tables from the oracle database i get the following error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error. Authentication failed.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Any ideas how this problem can be solved.

http://support.microsoft.com/default.aspx?scid=kb;en-us;280106

"This error message indicates that the linked server does not have correct login mapping. You can execute the sp_helplinkedsrvlogin stored procedure to set the login information correctly. Also, verify that you have specified the correct parameters for the linked server configuration."

HTH

Linked Server problem

Hi,

I have created a linked server with oracle database. Iam using SQL Server 200 and Oracle 8.0 client. I have made the registry settings as mentioned in KB280106. But when i I try to access the tables from the oracle database i get the following error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error. Authentication failed.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Any ideas how this problem can be solved.

http://support.microsoft.com/default.aspx?scid=kb;en-us;280106

"This error message indicates that the linked server does not have correct login mapping. You can execute the sp_helplinkedsrvlogin stored procedure to set the login information correctly. Also, verify that you have specified the correct parameters for the linked server configuration."

HTH

Linked Server Oracle 10g problem

I have followed the examples from MS. to create the link between my MSSQL200
5
and Oracle 10g
I have all of the Oracle client installed on the server.
I have a net service defined.
Here is what I ran to create link:
EXEC sp_addlinkedserver 'OraLinkServer2', 'Oracle', 'MSDAORA', 'HPRD89'
EXEC sp_addlinkedsrvlogin 'OraLinkServer2', 'False' ,Null, 'spwr07', 'spwr07
'
Here is the query:
select count(*) from OraLinkServer2..sysadm.SUN_PUBT
Here is the error:
OLE DB provider "MSDAORA" for linked server "OraLinkServer2" returned
message "ORA-12154: TNS:could not resolve the connect identifier specified
".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDAORA" for
linked server "OraLinkServer2".
What am I doing wrong all the oracle tools connect just fine. I need SQL2005
to connect?
Thanks
GregHob,
I've had the same problem. I used the openquery syntax to work around
the problem. This also has the advantage of pushing the work to the
Oracle Server.
So.. try:
select * from
openquery(OraLinkServer2,
' select count(*) from SUN_PUBT
'
)
No -- you don't need the formating and returns -- I just use them to
make it easy for me to read the Oracle Commands separate from the
MS-SQL commands.
If you find a better solution, please post it.
You also might want to read:
http://www.sqlservercentral.com/col...br />
art3.asp
It has the best set of instructions for getting the Instant Client to
work.
Good luck,
Peter Ryan
Hob_Naggers wrote:
> I have followed the examples from MS. to create the link between my MSSQL2
005
> and Oracle 10g
> I have all of the Oracle client installed on the server.
> I have a net service defined.
> Here is what I ran to create link:
> EXEC sp_addlinkedserver 'OraLinkServer2', 'Oracle', 'MSDAORA', 'HPRD8
9'
> EXEC sp_addlinkedsrvlogin 'OraLinkServer2', 'False' ,Null, 'spwr07', 'spwr
07'
>
> Here is the query:
> select count(*) from OraLinkServer2..sysadm.SUN_PUBT
> Here is the error:
> OLE DB provider "MSDAORA" for linked server "OraLinkServer2" returned
> message "ORA-12154: TNS:could not resolve the connect identifier specified
> ".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDAORA" for
> linked server "OraLinkServer2".
> What am I doing wrong all the oracle tools connect just fine. I need SQL20
05
> to connect?
> Thanks
> Greg|||Hob,
I've had the same problem. I used the openquery syntax to work around
the problem. This also has the advantage of pushing the work to the
Oracle Server.
So.. try:
select * from
openquery(OraLinkServer2,
' select count(*) from SUN_PUBT
'
)
No -- you don't need the formating and returns -- I just use them to
make it easy for me to read the Oracle Commands separate from the
MS-SQL commands.
If you find a better solution, please post it.
You also might want to read:
http://www.sqlservercentral.com/col...br />
art3.asp
It has the best set of instructions for getting the Instant Client to
work.
Good luck,
Peter Ryan
Hob_Naggers wrote:
> I have followed the examples from MS. to create the link between my MSSQL2
005
> and Oracle 10g
> I have all of the Oracle client installed on the server.
> I have a net service defined.
> Here is what I ran to create link:
> EXEC sp_addlinkedserver 'OraLinkServer2', 'Oracle', 'MSDAORA', 'HPRD8
9'
> EXEC sp_addlinkedsrvlogin 'OraLinkServer2', 'False' ,Null, 'spwr07', 'spwr
07'
>
> Here is the query:
> select count(*) from OraLinkServer2..sysadm.SUN_PUBT
> Here is the error:
> OLE DB provider "MSDAORA" for linked server "OraLinkServer2" returned
> message "ORA-12154: TNS:could not resolve the connect identifier specified
> ".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDAORA" for
> linked server "OraLinkServer2".
> What am I doing wrong all the oracle tools connect just fine. I need SQL20
05
> to connect?
> Thanks
> Greg|||Hi Peter,
Thanks for the response, but I finally found the problem yesterday. The real
problem was not with SQL server, but rather it was a problem with the Oracle
config.
Though i had the TNSNames.ora file configured correctly the SQLNET.ora file
was where the problem lay. It was configured for ezconnect instead of
tnsnames. After this was ironed out the linking of the oracle db into sql wa
s
easy. One weird not though I found after getting the config right. When
querying the linked db. It is case sensitive with regards to column and tabl
e
names, even though on the Oracle side it is not case sensitive. I'm guessing
it has something to do with the translation being provided by the providers.
Thanks
Greg
"pryan64" wrote:

> Hob,
> I've had the same problem. I used the openquery syntax to work around
> the problem. This also has the advantage of pushing the work to the
> Oracle Server.
> So.. try:
> select * from
> openquery(OraLinkServer2,
> ' select count(*) from SUN_PUBT
> '
> )
> No -- you don't need the formating and returns -- I just use them to
> make it easy for me to read the Oracle Commands separate from the
> MS-SQL commands.
> If you find a better solution, please post it.
> You also might want to read:
> http://www.sqlservercentral.com/col... />
ypart3.asp
> It has the best set of instructions for getting the Instant Client to
> work.
> Good luck,
> Peter Ryan
> Hob_Naggers wrote:
>|||Hi Peter,
Thanks for the response, but I finally found the problem yesterday. The real
problem was not with SQL server, but rather it was a problem with the Oracle
config.
Though i had the TNSNames.ora file configured correctly the SQLNET.ora file
was where the problem lay. It was configured for ezconnect instead of
tnsnames. After this was ironed out the linking of the oracle db into sql wa
s
easy. One weird not though I found after getting the config right. When
querying the linked db. It is case sensitive with regards to column and tabl
e
names, even though on the Oracle side it is not case sensitive. I'm guessing
it has something to do with the translation being provided by the providers.
Thanks
Greg
"pryan64" wrote:

> Hob,
> I've had the same problem. I used the openquery syntax to work around
> the problem. This also has the advantage of pushing the work to the
> Oracle Server.
> So.. try:
> select * from
> openquery(OraLinkServer2,
> ' select count(*) from SUN_PUBT
> '
> )
> No -- you don't need the formating and returns -- I just use them to
> make it easy for me to read the Oracle Commands separate from the
> MS-SQL commands.
> If you find a better solution, please post it.
> You also might want to read:
> http://www.sqlservercentral.com/col... />
ypart3.asp
> It has the best set of instructions for getting the Instant Client to
> work.
> Good luck,
> Peter Ryan
> Hob_Naggers wrote:
>

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?

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?

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?

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?

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?

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?

Linked Server for Oracle XE Database

Okay, I have gotten around it by installing the Oracle 8i Client on the
server instead of the Oracle XE Client. This is hardly ideal, but it will
have to do until I can find a solution.
-adam
"Adam Byrne" <misterbyrne.nospam@.allforme.gmail.com> wrote in message
news:e3u5JVwjGHA.3440@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I have been able to create a linked server using the MSDAORA provider to
> an Oracle XE Server. I have done so on the same machine as the Oracle XE
> Server (also has a SQL server on it), and on another machine (with SQL
> server and Oracle XE Client installed). The problem happens when I have
> do anything to the Oracle database within a transaction. You see in order
> to get this to work you need to point the MSDTC (Microsoft Distributed
> Transaction Coordinator) to the correct Oracle DLLs. You can do this by
> modifying the registry entries at the following location:
> HKLM\Software\Microsoft\MSDTC\MTxOCI\
> I could do so on the server with Oracle XE Server installed. I entered
> the following values:
> OracleOciLib = oci.dll
> OracleSqlLib = orasql10.dll
> OracleXaLib = oraclient10.dll
> And everything worked fine. But on the server with the Oracle XE Client
> installed, these files are not present, so what am I supposed to do? I
> tried copying the files from the Oracle XE Server PC but it didn't fix
> things. Has anyone had any success in configuring MSDTC for Oracle XE
> Client?
> Any help would be greatly appreciated.
> Thanks.
> -adam
>Hello,
I have been able to create a linked server using the MSDAORA provider to an
Oracle XE Server. I have done so on the same machine as the Oracle XE
Server (also has a SQL server on it), and on another machine (with SQL
server and Oracle XE Client installed). The problem happens when I have do
anything to the Oracle database within a transaction. You see in order to
get this to work you need to point the MSDTC (Microsoft Distributed
Transaction Coordinator) to the correct Oracle DLLs. You can do this by
modifying the registry entries at the following location:
HKLM\Software\Microsoft\MSDTC\MTxOCI\
I could do so on the server with Oracle XE Server installed. I entered the
following values:
OracleOciLib = oci.dll
OracleSqlLib = orasql10.dll
OracleXaLib = oraclient10.dll
And everything worked fine. But on the server with the Oracle XE Client
installed, these files are not present, so what am I supposed to do? I
tried copying the files from the Oracle XE Server PC but it didn't fix
things. Has anyone had any success in configuring MSDTC for Oracle XE
Client?
Any help would be greatly appreciated.
Thanks.
-adam|||Okay, I have gotten around it by installing the Oracle 8i Client on the
server instead of the Oracle XE Client. This is hardly ideal, but it will
have to do until I can find a solution.
-adam
"Adam Byrne" <misterbyrne.nospam@.allforme.gmail.com> wrote in message
news:e3u5JVwjGHA.3440@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I have been able to create a linked server using the MSDAORA provider to
> an Oracle XE Server. I have done so on the same machine as the Oracle XE
> Server (also has a SQL server on it), and on another machine (with SQL
> server and Oracle XE Client installed). The problem happens when I have
> do anything to the Oracle database within a transaction. You see in order
> to get this to work you need to point the MSDTC (Microsoft Distributed
> Transaction Coordinator) to the correct Oracle DLLs. You can do this by
> modifying the registry entries at the following location:
> HKLM\Software\Microsoft\MSDTC\MTxOCI\
> I could do so on the server with Oracle XE Server installed. I entered
> the following values:
> OracleOciLib = oci.dll
> OracleSqlLib = orasql10.dll
> OracleXaLib = oraclient10.dll
> And everything worked fine. But on the server with the Oracle XE Client
> installed, these files are not present, so what am I supposed to do? I
> tried copying the files from the Oracle XE Server PC but it didn't fix
> things. Has anyone had any success in configuring MSDTC for Oracle XE
> Client?
> Any help would be greatly appreciated.
> Thanks.
> -adam
>sql

Linked server for ODBC data source

Hope someone can help with this:
I have an Alpha box running open VMS which has an ODBC client from easysoft
set up as a system dsn in ODBC data sources. I can select the data source
from Excel/Access/VB asp etc with a connection string of "dsn=dsnname"
How can I set this up as a linked server in SQL 2k ?
I have tried using enterprise manager & creating a linked server for
"Microsoft OLE DB Provider for ODBC Drivers" with the data source as
"dsnname" and the provider string as "dsn=dsnname". Is the the correct way
to use a system dsn as a linked server? If so, are there any other
parameters to set up as I can't see any tables, and get an error : 7399,
OLEDB provider 'MSDASQL' reported an error, provider did not give any
information...IDBinitialize returned 0x80004005
Is this a login or security problem? I just want the login to use my current
credentials.
Did you install the ODBC client on the SQL Server box?
You can use a DSN but you need to create the DSN on the
server. You don't need to use a DSN though - you can also
provide the information used by the DSN in the provider
string and data source parameters of sp_addlinkedserver.
In terms of the 7399 error, that's somewhat of a generic
error. Try turning on trace flag 7300 or use SQL Profiler
to capture the OLEDB Errors event. This should give you to
get more information about the error.
-Sue
On Mon, 7 Jun 2004 12:12:03 +0100, "CCA Dave"
<david@.notreally.co.uk> wrote:

>Hope someone can help with this:
>I have an Alpha box running open VMS which has an ODBC client from easysoft
>set up as a system dsn in ODBC data sources. I can select the data source
>from Excel/Access/VB asp etc with a connection string of "dsn=dsnname"
>How can I set this up as a linked server in SQL 2k ?
>I have tried using enterprise manager & creating a linked server for
>"Microsoft OLE DB Provider for ODBC Drivers" with the data source as
>"dsnname" and the provider string as "dsn=dsnname". Is the the correct way
>to use a system dsn as a linked server? If so, are there any other
>parameters to set up as I can't see any tables, and get an error : 7399,
>OLEDB provider 'MSDASQL' reported an error, provider did not give any
>information...IDBinitialize returned 0x80004005
>Is this a login or security problem? I just want the login to use my current
>credentials.
>

Linked server for ODBC data source

Hope someone can help with this:
I have an Alpha box running open VMS which has an ODBC client from easysoft
set up as a system dsn in ODBC data sources. I can select the data source
from Excel/Access/VB asp etc with a connection string of "dsn=dsnname"
How can I set this up as a linked server in SQL 2k ?
I have tried using enterprise manager & creating a linked server for
"Microsoft OLE DB Provider for ODBC Drivers" with the data source as
"dsnname" and the provider string as "dsn=dsnname". Is the the correct way
to use a system dsn as a linked server? If so, are there any other
parameters to set up as I can't see any tables, and get an error : 7399,
OLEDB provider 'MSDASQL' reported an error, provider did not give any
information...IDBinitialize returned 0x80004005
Is this a login or security problem? I just want the login to use my current
credentials.Did you install the ODBC client on the SQL Server box?
You can use a DSN but you need to create the DSN on the
server. You don't need to use a DSN though - you can also
provide the information used by the DSN in the provider
string and data source parameters of sp_addlinkedserver.
In terms of the 7399 error, that's somewhat of a generic
error. Try turning on trace flag 7300 or use SQL Profiler
to capture the OLEDB Errors event. This should give you to
get more information about the error.
-Sue
On Mon, 7 Jun 2004 12:12:03 +0100, "CCA Dave"
<david@.notreally.co.uk> wrote:

>Hope someone can help with this:
>I have an Alpha box running open VMS which has an ODBC client from easysoft
>set up as a system dsn in ODBC data sources. I can select the data source
>from Excel/Access/VB asp etc with a connection string of "dsn=dsnname"
>How can I set this up as a linked server in SQL 2k ?
>I have tried using enterprise manager & creating a linked server for
>"Microsoft OLE DB Provider for ODBC Drivers" with the data source as
>"dsnname" and the provider string as "dsn=dsnname". Is the the correct way
>to use a system dsn as a linked server? If so, are there any other
>parameters to set up as I can't see any tables, and get an error : 7399,
>OLEDB provider 'MSDASQL' reported an error, provider did not give any
>information...IDBinitialize returned 0x80004005
>Is this a login or security problem? I just want the login to use my curren
t
>credentials.
>sql

Linked Server Error

Hello,
I have installed IBM AS/400 Client Access on my machine and then added a new
ODBC Data Source.
I have been working with this ODBC connection on several DTS packages and
everything has worked just fine.
Right now, I need to access some information on my AS/400 server from
several stored procedures on my SQL Server. I already configured a Linked
Server to the AS/400 server using the Microsoft OLE DB Provider for ODBC
Driver.
When I click the Tables folder under the new linked server, I can browse
through all the tables on the 400 Database. But when I execute any query
(using OPENQUERY or 4-part names in a normal query) I get the following erro
r:
****************************************
***+++
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
(32 bits)] Driver not capable.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returned
0x80004005: ].
****************************************
**
Why is this happening? What is the error? Is there anything I am doing
wrong? I have been "fighting" with this problem for several hours.
Thanks a lot, in advance, for all the help you can give me.Gabriel --
See if this thread helps at all:
http://www.mcse.ms/message979460.html
same thread, different format:
http://www.newsarch.com/archive/new...
54.html
Gabriel Giraldo wrote:
> Hello,
> I have installed IBM AS/400 Client Access on my machine and then added a n
ew
> ODBC Data Source.
> I have been working with this ODBC connection on several DTS packages and
> everything has worked just fine.
> Right now, I need to access some information on my AS/400 server from
> several stored procedures on my SQL Server. I already configured a Linked
> Server to the AS/400 server using the Microsoft OLE DB Provider for ODBC
> Driver.
> When I click the Tables folder under the new linked server, I can browse
> through all the tables on the 400 Database. But when I execute any query
> (using OPENQUERY or 4-part names in a normal query) I get the following er
ror:
> ****************************************
***+++
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
> (32 bits)] Driver not capable.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returne
d
> 0x80004005: ].
> ****************************************
**
> Why is this happening? What is the error? Is there anything I am doing
> wrong? I have been "fighting" with this problem for several hours.
> Thanks a lot, in advance, for all the help you can give me.
>|||It has been awhile since I worked with the AS/400. Based on the error
message, I would look at the client software installed on the SQL end. The
second thing to look at is the syntax used to attempt to get data. As long a
s
the SQL is fully ANSI standard, you should be fine on both ends; if not, the
likelihood of error increases.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Gabriel Giraldo" wrote:

> Hello,
> I have installed IBM AS/400 Client Access on my machine and then added a n
ew
> ODBC Data Source.
> I have been working with this ODBC connection on several DTS packages and
> everything has worked just fine.
> Right now, I need to access some information on my AS/400 server from
> several stored procedures on my SQL Server. I already configured a Linked
> Server to the AS/400 server using the Microsoft OLE DB Provider for ODBC
> Driver.
> When I click the Tables folder under the new linked server, I can browse
> through all the tables on the 400 Database. But when I execute any query
> (using OPENQUERY or 4-part names in a normal query) I get the following er
ror:
> ****************************************
***+++
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
> (32 bits)] Driver not capable.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returne
d
> 0x80004005: ].
> ****************************************
**
> Why is this happening? What is the error? Is there anything I am doing
> wrong? I have been "fighting" with this problem for several hours.
> Thanks a lot, in advance, for all the help you can give me.
>

Linked server AS400/DB2 trouble again

Hi !
I'm trying to create Linked Server to AS400 DB2 on SQL Server 2000.
Using IBMDA400 (IBM AS400 OLEDB Provider installed from Client Access).

Got an error 7399: OLEDB Provider returned UNKNOWN error.

exec SP_AddLinkedServer
@.Server=AS_SERVER,
@.SrvProduct='IBM AS400 OLEDB Provider',
@.Catalog='QGPL',
@.Location='AS_SERVER',
@.Provider=IBMDA400,
@.Datasrc=AS_SERVER,
@.Provstr='Provider=IBMDA400.1;Password="";User ID=AS_USER;Data Source=AS_SERVER;Transport Product=Client Access;SSL=DEFAULT;Libraries=QGPL;Connect Timeout=90;Host Code Page=037'

Attempted different combinations of theese params...

After that created remote login on AS400.

Please help or correct my LinkedServerProperties...

Regards,
PVHave you looked at this old posting:

dbForums - BD2OLEDB (http://dbforums.com/t477702.html)

Linked server - Calling AS400 SP

Hi,
I have created a linked server to AS400 DB2 database on my sqlserver
2000 server.
I am using IBM iSeries Client Access drivers on the server.
The connection has been made properly and to confirm that i ran a query
to select a table from AS400 using openquery syntax.
I am trying to call a stored procedure in AS400 that either returns 0 or
1. The stored procedre executes from vb through a dsn without any problem.
However calling the stored proc using the linked server is a nightmare.
If I use the openquery syntax which is ...
select * from openquery(as400serv1,'CALL QGPL.CCVALX ( ''1'' ,
''123343453454'' , ''0205'' , ''CC'') ' )
I get error ...
Server: Msg 7357, Level 16, State 2, Line 2
Could not process object 'CALL QGPL.CCVALX ( '1' , '123343453454' , '0205' ,
'CC') '. The OLE DB provider 'IBMDA400' indicates that the object has no
columns.
If I use the execute method as in ...
exec as400serv1.wima400.QGPL.CCVALX '1','123343453454','0205','CC'
I get err..
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'CCVALX' on remote server 'as400serv1'.
[OLE/DB provider returned message: SQL0104: Token { was not valid. Valid
tokens: ( END SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN CLOSE.
Cause . . . . . : A syntax error was detected at token {. Token { is not
a valid token. A partial list of valid tokens is ( END SET CALL DROP FREE
HOLD LOCK OPEN WITH ALTER BEGIN CLOSE. This list assumes that the statement
is correct up to the token. The error may be earlier in the statement, but
the syntax of the statement appears to be valid up to this point. Recovery
.. . . : Do one or more of the following and try the request again: --
Verify the SQL statement in the area of the token {. Correct the statement.
The error could be a missing comma or quotation mark, it could be a
misspelled word, or it could be related to the order of clauses. -- If the
error token is <END-OF-STATEMENT>, correct the SQL statement because it does
not end with a valid clause.]
Please, can anyone help me understanding what is going wrong... it very
crucial i get this working from the linked server.
Thanks in advance
Remy De Almeida
> Hi,
> I have created a linked server to AS400 DB2 database on my sqlserver
> 2000 server.
> I am using IBM iSeries Client Access drivers on the server.
> The connection has been made properly and to confirm that i ran a
query
> to select a table from AS400 using openquery syntax.
> I am trying to call a stored procedure in AS400 that either returns 0
or
> 1. The stored procedre executes from vb through a dsn without any problem.
> However calling the stored proc using the linked server is a nightmare.
> If I use the openquery syntax which is ...
> select * from openquery(as400serv1,'CALL QGPL.CCVALX ( ''1'' ,
> ''123343453454'' , ''0205'' , ''CC'') ' )
> I get error ...
> Server: Msg 7357, Level 16, State 2, Line 2
> Could not process object 'CALL QGPL.CCVALX ( '1' , '123343453454' ,
'0205' ,
> 'CC') '. The OLE DB provider 'IBMDA400' indicates that the object has no
> columns.
>
> If I use the execute method as in ...
> exec as400serv1.wima400.QGPL.CCVALX '1','123343453454','0205','CC'
> I get err..
> Server: Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'CCVALX' on remote server 'as400serv1'.
> [OLE/DB provider returned message: SQL0104: Token { was not valid. Valid
> tokens: ( END SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN CLOSE.
> Cause . . . . . : A syntax error was detected at token {. Token { is
not
> a valid token. A partial list of valid tokens is ( END SET CALL DROP FREE
> HOLD LOCK OPEN WITH ALTER BEGIN CLOSE. This list assumes that the
statement
> is correct up to the token. The error may be earlier in the statement,
but
> the syntax of the statement appears to be valid up to this point. Recovery
> . . . : Do one or more of the following and try the request again: --
> Verify the SQL statement in the area of the token {. Correct the
statement.
> The error could be a missing comma or quotation mark, it could be a
> misspelled word, or it could be related to the order of clauses. -- If the
> error token is <END-OF-STATEMENT>, correct the SQL statement because it
does
> not end with a valid clause.]
> Please, can anyone help me understanding what is going wrong... it very
> crucial i get this working from the linked server.
> Thanks in advance
> Remy De Almeida
You need to determine if the third-party OLE-DB provider supports the
calling of stored procedures from the remote server. Speak to your OLE-DB
provider vendor about this.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Linked server - Calling AS400 SP

Hi,
I have created a linked server to AS400 DB2 database on my sqlserver
2000 server.
I am using IBM iSeries Client Access drivers on the server.
The connection has been made properly and to confirm that i ran a query
to select a table from AS400 using openquery syntax.
I am trying to call a stored procedure in AS400 that either returns 0 or
1. The stored procedre executes from vb through a dsn without any problem.
However calling the stored proc using the linked server is a nightmare.
If I use the openquery syntax which is ...
select * from openquery(as400serv1,'CALL QGPL.CCVALX ( ''1'' ,
''123343453454'' , ''0205'' , ''CC'') ' )
I get error ...
Server: Msg 7357, Level 16, State 2, Line 2
Could not process object 'CALL QGPL.CCVALX ( '1' , '123343453454' , '0205' ,
'CC') '. The OLE DB provider 'IBMDA400' indicates that the object has no
columns.
If I use the execute method as in ...
exec as400serv1.wima400.QGPL.CCVALX '1','123343453454','0205','CC'
I get err..
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'CCVALX' on remote server 'as400serv1'.
[OLE/DB provider returned message: SQL0104: Token { was not valid. Valid
tokens: ( END SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN CLOSE.
Cause . . . . . : A syntax error was detected at token {. Token { is not
a valid token. A partial list of valid tokens is ( END SET CALL DROP FREE
HOLD LOCK OPEN WITH ALTER BEGIN CLOSE. This list assumes that the statement
is correct up to the token. The error may be earlier in the statement, but
the syntax of the statement appears to be valid up to this point. Recovery
. . . : Do one or more of the following and try the request again: --
Verify the SQL statement in the area of the token {. Correct the statement.
The error could be a missing comma or quotation mark, it could be a
misspelled word, or it could be related to the order of clauses. -- If the
error token is <END-OF-STATEMENT>, correct the SQL statement because it does
not end with a valid clause.]
Please, can anyone help me understanding what is going wrong... it very
crucial i get this working from the linked server.
Thanks in advance
Remy De Almeida> Hi,
> I have created a linked server to AS400 DB2 database on my sqlserver
> 2000 server.
> I am using IBM iSeries Client Access drivers on the server.
> The connection has been made properly and to confirm that i ran a
query
> to select a table from AS400 using openquery syntax.
> I am trying to call a stored procedure in AS400 that either returns 0
or
> 1. The stored procedre executes from vb through a dsn without any problem.
> However calling the stored proc using the linked server is a nightmare.
> If I use the openquery syntax which is ...
> select * from openquery(as400serv1,'CALL QGPL.CCVALX ( ''1'' ,
> ''123343453454'' , ''0205'' , ''CC'') ' )
> I get error ...
> Server: Msg 7357, Level 16, State 2, Line 2
> Could not process object 'CALL QGPL.CCVALX ( '1' , '123343453454' ,
'0205' ,
> 'CC') '. The OLE DB provider 'IBMDA400' indicates that the object has no
> columns.
>
> If I use the execute method as in ...
> exec as400serv1.wima400.QGPL.CCVALX '1','123343453454','0205','CC'
> I get err..
> Server: Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'CCVALX' on remote server 'as400serv1'.
> [OLE/DB provider returned message: SQL0104: Token { was not valid. Valid
> tokens: ( END SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN CLOSE.
> Cause . . . . . : A syntax error was detected at token {. Token { is
not
> a valid token. A partial list of valid tokens is ( END SET CALL DROP FREE
> HOLD LOCK OPEN WITH ALTER BEGIN CLOSE. This list assumes that the
statement
> is correct up to the token. The error may be earlier in the statement,
but
> the syntax of the statement appears to be valid up to this point. Recovery
> . . . : Do one or more of the following and try the request again: --
> Verify the SQL statement in the area of the token {. Correct the
statement.
> The error could be a missing comma or quotation mark, it could be a
> misspelled word, or it could be related to the order of clauses. -- If the
> error token is <END-OF-STATEMENT>, correct the SQL statement because it
does
> not end with a valid clause.]
> Please, can anyone help me understanding what is going wrong... it very
> crucial i get this working from the linked server.
> Thanks in advance
> Remy De Almeida
--
You need to determine if the third-party OLE-DB provider supports the
calling of stored procedures from the remote server. Speak to your OLE-DB
provider vendor about this.
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.