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 in the package designer, 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. Oh, and MDACVer 2.71.
I'm missing something, but what? Anybody have a similar problem? This is the 4th site that I've posted this question.