MAXDB ODBC driver and MS SQL SERVER

[Former Member]

Dear SAP Community

I need to set up a linked server from MS Sql Server to a MAXDB instance.

Well, after installing the ODBC driver, I've set up a data source and then I could successfully establish a connection to my MAXDB from a sample Excel file using that datasource.

Then I moved to Sql server in order to define a linked server with this data source, likewise.

Therefore, in the first option as a provider I've selected "Microsoft OLE DB provider for ODBC drivers" then I specified that datasource for both Product Name and Data Source (same setting), however I got the following error.

Could you kindly help me? I have almost spent 2 days on this problem without figuring it out, and any help or hint would be extremely appreciated.

Many many thanks in advance

Alberto

-

---

The OLE DB provider "MSDASQL" for linked server "MAXDB2" reported an error. The provider did not give any information about the error.

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MAXDB2". (Microsoft SQL Server, Error: 7399)

[Former Member]

Dear Lars (and all the readers),

is getting better and better! Although I cannot see anything when I expand the tree under my linked server, I can query my tables in MaxDB, with this syntax:

SELECT * from LINKED_SERVER...TABLE_NAME

The problem occurs when there is at least a column in UNICODE, which gives me the following error:

I also tried to use OPENQUERY:

SELECT * FROM OPENQUERY(MAXDBFINAL, 'SELECT * FROM ROOTS ')

but same result.

OLE DB provider 'MSDASQL' for linked server 'MAXDBFINAL' returned data that does not match expected data length for column '[MAXDBFINAL]...[ROOTS].SCHEMANAME'. The (maximum) expected data length is 64, while the returned data length is 14.

And googling it, seems to be unfortunately, quite common when dealing with Unicode db like Oracle or DB2.

So, the whole situation is getting better, although not completely solved.

Thanks in advance

Alberto Della Santina

[Former Member]

Hi Alberto, I know this is al old post but thing is you experienced the same issue I'm dealing with right now.

I just created the ODBC connection correctly but there is no way for me to successfully create the linked connection to that ODBC data source. I've tried several things but there is no way for me to get this working, I got same error like you, the #7399 one.

Please what did you meant with:

I have the connection tested and running, without errors: I had to force the authentication before actually creating it.

Looks like someway you forced the authentication for the ODBC datasource..how did you do that ?

Thanks in advance for any help

[Former Member]

Just for your information,

I have the connection tested and running, without errors: I had to force the authentication before actually creating it.

However, I don't see any table at the moment, so it would be extremely appreciate if anybody could provide me with some hints, perhaps I do have to put a connection string, as I kept blank those settings (just set only Product Name and Data Source).

Many thanks in advance!

Alberto

/****** Object: LinkedServer [MAXDB3] Script Date: 07/18/2011 17:10:29 ******/

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'MAXDB3')EXEC master.dbo.sp_dropserver @server=N'MAXDB3', @droplogins='droplogins'

GO

/****** Object: LinkedServer [MAXDB3] Script Date: 07/18/2011 17:10:29 ******/

EXEC master.dbo.sp_addlinkedserver @server = N'MAXDB3', @srvproduct=N'MAXDB3', @provider=N'MSDASQL', @datasrc=N'MAXDB3'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MAXDB3',@useself=N'False',@locallogin=NULL,@rmtuser=N'DBADMIN',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'MAXDB3', @optname=N'collation compatible', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MAXDB3', @optname=N'data access', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'MAXDB3', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MAXDB3', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MAXDB3', @optname=N'rpc', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MAXDB3', @optname=N'rpc out', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MAXDB3', @optname=N'sub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MAXDB3', @optname=N'connect timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'MAXDB3', @optname=N'collation name', @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N'MAXDB3', @optname=N'lazy schema validation', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MAXDB3', @optname=N'query timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'MAXDB3', @optname=N'use remote collation', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'MAXDB3', @optname=N'remote proc transaction promotion', @optvalue=N'true'

GO

[Former Member]

Dear Lars,

thanks for your reply, first of all.

Then, at this stage as I cannot solve this ODBC issue, how would you recommend to link MaxDB with Sql Server, in order to fetch data from the latter?

Sorry, I though ODBC was the quickest as I thought it was a "standard".

By the way, googling my topic in German (I know a little bit of it) there was a german forum with somebody having the same problem as in 2008....without any answer.

I apologise I may have caused any offense on you or towards to SAP when I said "SAP is working on", it was not my intention at all.

Alberto Della Santina

[Former Member]

Dear Lars,

I hope you would excuse me but I wouldn't say that my question is totally unrelated to MaxDB as nowdays, as far as I understood, SAP is working to have their BW solution on Sql Server as well, so a proper ODBC connection would be helpful not only for myself whenever such sort of porting will be necessary.

By the way, the lead I am working with has a big move to Sql Server 2008 in plan, from their current infrastructure.

However, thank you for your reply, I was going through all the tons of links spilled by google but all of theme were referring to a particular error, like wrong authentication rather than lack of memory rather than missing write permission, where in my case there is a total lack of information

Many thanks for your attention.

Alberto

[lbreddemann]

Dear Lars,
>   I hope you would excuse me but I wouldn't say that my question is totally unrelated to MaxDB as nowdays, as far as I understood, SAP is working to have their BW solution on Sql Server as well, so a proper ODBC connection would be helpful not only for myself whenever such sort of porting will be necessary.

so what?

There are many ways to integrate MaxDB databases into SAP BW running on any other DBMS including MS SQL Server.

The easiest would be to simply use the secondary database connection to the MaxDB.

By the way, the SAP BW solution is running on MS SQL, Oracle, DB2/4/6 for a very long time now - so saying we're "working on that" doesn't really nails it...

Apart from that, what I can tell you about the technical aspect of this error: it really isn't MaxDB related. As it seems, the request doesn't even get to the MaxDB ODBC driver or MaxDB client software. That is the reason why I wrote: "not MaxDB related".

The error is based in another part of the software stack you're using-

regards,

Lars

[lbreddemann]

Hmm... totally not a MaxDB issue!

But googling your last error message spills out tons of pages.

E.g. this one http://support.microsoft.com/kb/814398

regards,

Lars