Query on linked server (DB2 / AS400)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Query on linked server (DB2 / AS400)

Author

Topic

hephie
Starting Member

15 Posts

Posted - 2010-01-11 : 11:34:50

Hi all,

First of all I hope i'm posting my question in the correct subforum. It's .NET related but probably also sql server administration related, so I had to chose one :)

Let me first describe the server specs and sql edition
Server specs:
Windows Server 2003 R2 SP2 Enterprise Edition
SQL Server 2005 Enterprise (Version 9.00.4053.00)

the problem:
I've created a linked server to an AS400 (DB2) machine. The linked server will alwaysuse the same credentials, the one I specified in the security settings from the linked server.
No problem so far.

When i now pop open a new query window in sql server and just typ in a random correct query, i get the restuls back from the AS400 machine (through the linked server). Still no problem here.

EG Query: SELECT * FROM AS400.HJL.BEL.REL
In this query:
AS400 = Linked Server Name on SQL
HJL = AS400 Hostname / Machine Name (this could also be the server IP)
BEL = The library from which we want to select data
REL = Table

So far so good.
Now i've created a little web application that will show the data in a asp:gridview.
I've used the basic (System.Data)SqlCommand, specified the correct connectionstring to the sql server and tried to run this same query as I run before (without any problems).
The result --> FAILS

Exception Message:
Cannot create an instance of OLE DB provider "IBMDASQL" for linked server "AS400".

Exception StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader()

I've already tried to put the query in a stored procedure and the call the stored procedure from my c# code = same result = FAIL.

When I execute the stored procedure directly through the SQL Server Management Studio it works without any problem(s) and I get the query result.

That's my problem, I hope somebody can help me figure this out!!
Thanks a lot in advance!

Best Regards,
Thomas

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-11 : 12:02:58

I have goggled this and hope it helps:
http://www.bigresource.com/Tracker/Track-ms_sql-ZqOqI0jc/

No, you're never too old to Yak'n'Roll if you're too young to die.

hephie
Starting Member

15 Posts

Posted - 2010-01-12 : 05:09:46

I've been reading as well and came across the "Allow in Process" in some other articles but I could not find where to configure it.
In your article (above) it's well explained, however it did not resolve my problem.

I've checked the "Allow in process" for each provider, gave the windows domain user that is used sysadmin rights on the sql server database(s) and restarted the database service afterwards.

After doing all that I still get the same error message (see first post).

Any other people with this problems? any other tips / solutions?

Thanks in advance!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-12 : 11:56:02

first of all, if you're querying DB2/400 through your app, don't bother going through the linked server, go straight to the 400. unless your stored procedure is combining that data with data stored on MSSQL.

2nd, make sure rpc is true in the linked server properties

3rd, I'd use either the client access driver or the microsoft oledb provider for db2

hephie
Starting Member

15 Posts

Posted - 2010-01-14 : 06:11:32

russel, thanks for your reply.

1/ I need to select (join) data from AS400 and SQL at the same time, so need to use the linked server
2/ All things are set to true in the linked server > properties > server options, as well as Rpc.
3/ All client acces components are installed on the sql server, however there is no client access driver/provider visible in the linked server providers on sql server.
I've been searching the internet for the micorosoft oledb provider for db2 and download the one from: http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

I'm currently testing this microsoft oledb provider for db2, i'll let you know how this works out.

hephie
Starting Member

15 Posts

Posted - 2010-01-14 : 06:17:46

Concerning the microsft oledb provider for db2.

In the linked server configuration I now also need to specify a Provider String.
Can you tell me what I need to fill in over there? Or how I can generate this string?

Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-14 : 14:10:48

one easy way is to create a udl.

1. right-click an empty space on the desktop
2. click new...text document
3. give the file a .udl extension and say yes when prompted if you're sure
4.double-click the new file
5. fill in the appropriate information

when you're finished, open the file in notepad. this will show you the entire connection string.

that said, i'm not sure why the client access driver isn't showing up in your list of providers. was it installed with an admin account and do you have admin priveleges?

hephie
Starting Member

15 Posts

Posted - 2010-01-15 : 06:02:38

Thanks!
That worked out great :)

About the client access driver:
The client access components were installed as domain admin and I login to the sql server using windows authentication (is also domain admin)

I know have a perfect (and working) connection to AS400. As wel as directly from sql server, as well as from my webpage(s)!
Great!

REMEMBER: When using the Microsoft Ole DB Provider for DB2 all table names on/in the linked server you'd like to query are CASE SENSITIVE!
At first my queries were not working (error table not found myTable), then i changed my query to SELECT ... FROM MYTABLE WHERE ... and that did work :)

Off course this depends on how your table names are specified on the linked server :)

Thanks all for the help, my issue is resolved.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources