server with IBM DB2 UDB for iSeries IDMDASQL OLE Provider Errors

Hello Everyone

I’m having problems retrieving data from IBM DB2 AS/400 linked server

I have managed to connect a linked server using the IBM DB2 UDB for iSeries "IBMDASQL" OLE DB Provider.

But when I run a query to the database I get only the field names and the following errors:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "IBMDASQL" for linked server "iseries" reported an error. Access denied.

Msg 7301, Level 16, State 2, Line 1

Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "IBMDASQL" for linked server "iseries".

My Server is Standard 64bit edition on cluster with CTP 2 installed .

I don’t have the right to use Microsoft OLE DB provider …..?

Thank you.

Firstly check to correct the Access Denied error and ensure the user you are using to COnnect to IBM DB2 should have access.

Also check this http://www-128.ibm.com/developerworks/forums/dw_thread.jsp?forum=292&thread=143817&cat=5 link on IBM forums.

|||

Hello Satya

Thank you for your time.

I had my DB2 user tested with a non cluster 32bit installation.

I already posted my case to mentioned IBM link.

My problem is that I can’t use a Local system account for my 64bit clustered environment.

After executing a simple select query against the linked server I get an empty result set at the result tab with all table fields included and the errors

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "IBMDASQL" for linked server "GRATHD1" reported an error. Access denied.

Msg 7301, Level 16, State 2, Line 1

Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "IBMDASQL" for linked server "GRATHD1".

at the messages tab.

Thank you

|||

I would like to know how you setup the link. I have tried repeatedly to get a link for AS/400 to work, but can't seem to find the proper combination of parameters. Searching the internet has provided no help to me.

Thanks

|||

Hello Lee

I'm posting the script produced by my linked server

Notice that if you set the SQL Server service account to "local system" the linked server works.

/****** Object: LinkedServer [GRATHD1] Script Date: 01/30/2007 11:15:54 ******/
EXEC master.dbo.sp_addlinkedserver @.server = N'GRATHD1', @.srvproduct=N'IBM DB2 UDB for GRATHD1', @.provider=N'IBMDASQL', @.datasrc=N'GRATHD1'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'GRATHD1',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'as400username',@.rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'GRATHD1',@.useself=N'False',@.locallogin=N'DomainName\UserName',@.rmtuser=N'AS400username',@.rmtpassword='########'

GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'collation compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'data access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'dist', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'pub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'rpc', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'rpc out', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'sub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'connect timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'collation name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'lazy schema validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'query timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'use remote collation', @.optvalue=N'true'

|||

you musto add the options

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO

|||

PS to the Microsoft people

When the server is running under the local system account and not a domain user this option is not necessary.