MS SQL Server DTS - DTS package using ODBC fails when scheduled as a job

Author

DTS package using ODBC fails when scheduled as a job

Brian Beck

2005-07-18, 8:24 pm

I have a simple DTS package that downloads one table from an ODBC source
(CA/Datacom database). I can run the package with no problems. When I
schedule the package as a job it fails. The owner of the package and job is
the same as the SQLServerAgent. I can run and schedule packages with this
account that copy data locally with no errors, so I think it is a rights
issue with the account I am using ? If that is the case, then what rights do
I need to give this account to run a job that accesses an ODBC source?

Thanks,
Brian.

Allan Mitchell

2005-07-19, 7:24 am

Have a read here

http://support.microsoft.com/?kbid=269074

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know

"Brian Beck" <Brian Beck@discussions.microsoft.com> wrote in message
news:284DE1CF-773C-42FB-8CA5- 02F90C660948@microso
ft.com...
>I have a simple DTS package that downloads one table from an ODBC source
> (CA/Datacom database). I can run the package with no problems. When I
> schedule the package as a job it fails. The owner of the package and job
> is
> the same as the SQLServerAgent. I can run and schedule packages with
> this
> account that copy data locally with no errors, so I think it is a rights
> issue with the account I am using ? If that is the case, then what rights
> do
> I need to give this account to run a job that accesses an ODBC source?
>
> Thanks,
> Brian.

Brian Beck

2005-07-20, 1:23 pm

Alan, I read the article http://support.microsoft.com/?kbid=269074

So I created a domain account called DTSSchedule, Signed on to SQLServer as
DTSSchedule, Signed on to SqlServerAgent as DTSSchedule, set the DTS package
owner to DTSSchedule, set the job owner to DTSSchedule and scheduled the job.
It failed with a different error now:

"SQL Server Scheduled Job 'EVA mod_CR_CYR_INSTR' - Status: Failed - Invoked
on: 2005-07-19 18:01:28 - Message: The job failed. Unable to determine if
the owner (MyDomain\DTSSchedul
e) of job EVA mod_CR_CYR_INSTR has server
access (reason: Could not obtain information about Windows NT group/user
'MyDomain\DTSSchedul
er'. [SQLSTATE 42000] (Error 8198))."

I read a number of documents about 8198 errors. I am on a Windows 2003
Server. One of the articles mentioned upgrading to SP4 ( I am on SP3). That
is a possibility, but is there anything else I should try before I do that?
I am still not sure what SQLSTATE 42000 means.

Thanks for you help,
Brian.

Sponsored Links