Prev: Authentication failure for perfectly good server
Next: SQL2005 profiler filtering
From: mrdj on 2 Jul 2008 12:24
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.

From: Linchi Shea on 2 Jul 2008 13:28
What if you sechule to run it via a job that calls osql.exe?

Linchi

"mrdj" wrote:

> 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.
>
From: David Hay on 2 Jul 2008 14:41
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
From: mrdj on 2 Jul 2008 15:02
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.

"David Hay" wrote:

> 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
>
From: TheSQLGuru on 2 Jul 2008 15:19
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??

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


"mrdj" <mrdj(a)discussions.microsoft.com> wrote in message
news:8C1B3003-1963-410F-BECD-F7E314A00125(a)microsoft.com...
> 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.
>
> "David Hay" wrote:
>
>> 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
>>


 |  Next  |  Last
Pages: 1 2
Prev: Authentication failure for perfectly good server
Next: SQL2005 profiler filtering