Update

From: Simon (svilliers@hpm.com.au)
Date: 12/19/02 

From: "Simon" <svilliers@hpm.com.au>
Date: Wed, 18 Dec 2002 20:01:38 -0800

Guys,

Seems to have something to do with security. I now get
the following error:

Executed as user: QSECOFR. Remote access not allowed for
Windows NT user activated by SETUSER. [SQLSTATE 42000]
(Error 7410). The step failed.

I have been attempting variations on users. The attempt
above was to impersonate the AS400 QSECOFR User. A
Windows domain user and a SQL user, both mapped to remote
users and not mapped, with a default security context, all
seem to have failed. I gather that the SQL Agent Job
attempts to log on with the "Owner" account, and this
account needs to be mapped to an AS400/DB2 account.
However nothing I've tried seems to work. Am I missing
something really simple??

Thanks,
Simon.

>-----Original Message-----
>Guys,
>
>I have a linked server, a DB2 database on AS400 using
ODBC
>with a connection string.
>
>I also have several stored procedures that reference the
>linked server, and these work via Query Analyzer. They
>upload data from the DB2 database into the SQL database.
>
>
>I *want* to automate the process by creating a Server
>Agent job that executes the stored procedures each
night.
>Problem is that it doesn't work. I suspect it may be the
>security login's, or perhaps Server Agent doesn't just
>like Linked Servers (though I've found nothing to support
>this.)
>
>Thanks,
>Simon.
>
>
>P.S. For those who may need this info the error I get is:
>
>SQL Server Scheduled Job 'TestUploads'
>(0xD282E2D556F5D14177C991D2FA4CCF9E) - Status: Failed -
>Invoked on: 2002-12-19 08:40:33 - Message: The job
>failed. The Job was invoked by User SimonV. The last
>step to run was step 3 (UploadTheStates). The job was
>requested to start at step 1 (ResetTables).
>
>
>And the error occurs on the following line:
>
>DECLARE curStates CURSOR FOR
>SELECT * FROM OPENQUERY(<Linked Server>, 'SELECT
>StateCode, StateName FROM States')
>
>
>I have determined that it's not the cursor. I also have
a
>view defined with the OPENQUERY statement, and this
>likewise dosen't work.
>
>.
>