SQL 2000 accessing DB2

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mrdj - 02 Jul 2008 17:24 GMT

I have a stored procedure that calls remote query using a linked server. It
works fine when I run it manually through the Query analyzer window, but when
ever I try to schedule it as a job it fails with:
Could not create an instance of OLE DB provider 'IBMDASQL'. [SQLSTATE
42000] (Error 7302) (Error 7302) OLE DB error trace [Non-interface error:
CoCreate of DSO for IBMDASQL returned 0x80040154]. [SQLSTATE 01000] (Error
7300). The step failed.

I had to code it to write my query out to a flat file and then execute using
isql.

any suggestions/thoughts would be highly appreciated.

Thanks.

Reply to this Message

Linchi Shea - 02 Jul 2008 18:28 GMT

What if you sechule to run it via a job that calls osql.exe?

Linchi

> I have a stored procedure that calls remote query using a linked server. It
> works fine when I run it manually through the Query analyzer window, but when
[quoted text clipped - 10 lines]
>
> Thanks.

Reply to this Message

David Hay - 02 Jul 2008 19:41 GMT

Look at your linked server properties, especially the login mappings.
Make sure that you have it set to a valid user on DB2 box. Then make
sure the job owner follows that mapping. When I used to do that on
the AS400 we had one login on the AS400 that we used for all ODBC
connections. Also check your DSN settings. Looks like a permissions
issue.

David Hay

Reply to this Message

mrdj - 02 Jul 2008 20:02 GMT

Thanks, but that all checks out. The only difference is that I am using
exec(@sql) inside the stored proc. I know sp_executesql is better practice,
but bad habits die hard, should I try that? I know it has something to do
with the way SQL is interacting with the IBM drivers and how it accepts calls
from a stored procedure but just can't figure out why.

> Look at your linked server properties, especially the login mappings.
> Make sure that you have it set to a valid user on DB2 box. Then make
[quoted text clipped - 4 lines]
>
> David Hay

Reply to this Message

TheSQLGuru - 02 Jul 2008 20:19 GMT

Surely you don't need to ask if you should try an obvious change to see if
something broken then works! :-)

Have you verified that a DIRT SIMPLE sproc works correctly? Something like
SELECT 1 as a??

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

> Thanks, but that all checks out. The only difference is that I am using
> exec(@sql) inside the stored proc. I know sp_executesql is better
[quoted text clipped - 12 lines]
>>
>> David Hay

Reply to this Message

mrdj - 03 Jul 2008 15:34 GMT

good one ;-) ...still no worky

> Surely you don't need to ask if you should try an obvious change to see if
> something broken then works! :-)
[quoted text clipped - 18 lines]
> >>
> >> David Hay

Reply to this Message

David Hay - 03 Jul 2008 20:16 GMT

It might help if you post the code if it doesn't give too much away.
Linked servers are tricky with the double ticks and how you pass a
literal string to the open query portion. I'll see if I can find some
old examples of mine and post them.

If I recall I had to build the whole open query string as a literal,
then use sp_executesql @sql

declare @sql varchar(4000)
declare @mytable varchar(50)
set @mytable='master..sysobjects'

set @sql='select * from openquery(LinkedServer,''select * from ' +
@mytable + ''')'

print @sql

sp_executesql @sql

Reply to this Message