February 25, 2003 at 12:14 pm
I have an intermittent error with a SQL200 DTS package that makes a connection to an Oracle server:
Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: Oracle error occurred, but error message could not be retrieved from Oracle.
Error source: Microsoft OLE DB Provider for Oracle
The package is executed within a scheduled stored proc.
SET @strSQL = 'master.dbo.xp_cmdshell "dtsrun /S ' + @server + ' /E /N dtspackagename"'
When the package does fail, I can open it up on the server, open up the transformation task and hit the destination tab.. Boom, the same error as above. I then open the connection properties/properties button and test the connection(Test runs fine), and everything works the next couple of times the package runs.. But not for long.
BTW- SQLSERVERAGENT is running under an admin account, SQL 2000 sp-2 and using MS OLE DB Provider for Oracle for a connection/within the package. MDACVer 2.71
I'm missing something, but what? Anybody have a similar problem?
Best Regards
C. Kakara
Cincinnati, OH
February 25, 2003 at 12:40 pm
If you are running the sp that executes the package within a job, then the problem could be SQL Server Agent login account.
Try executing the package from DOS with the DTSRun utility like you did, but logged with a valid NT login account. If it is ok, then there should be the problem
February 25, 2003 at 1:25 pm
Thank You for the quick response, racosta.. It does execute from the command prompt, but it also runs from that same stored procedure about half the time. Also, my agent service runs under a local admin account. Really strange!
Best Regards
C. Kakara
Cincinnati, OH
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply