ckakara
asked on
DTS Connection to Oracle Error.
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.
Best Regards
C. Kakara
Cincinnati, OH
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.
Best Regards
C. Kakara
Cincinnati, OH
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER