Hello to everybody.

I want to export incrementally some tables from SQL Server to Oracle.
However, because of constraints (FKs, CKCs, etc) I should have to
respecto some particular order. Given that I prefer to load them in
parallel I must disable Oracle's constraints before exporting the data.

I've written the following stored procedure (which works correctly in
Oracle):

CREATE OR REPLACE PROCEDURE dis_constraint as
CURSOR c iS
SELECT CONSTRAINT_NAME, TABLE_NAME
FROM user_constraints
WHERE CONSTRAINT_NAME LIKE 'FK_%';
BEGIN
FOR i IN c LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || i.TABLE_NAME || ' DISABLE
CONSTRAINT ' || i.CONSTRAINT_NAME;
END LOOP;
END;
/


Unfortunately, whenever I try to execute it from an "Execute SQL Task",
SQL Server shows this error:

"HResult of 0x80040e14 (-2147217900) returned. Unexpected error. An
error without error message was returned."

Obviously the Oracle connection works properly since any other query on
the "Execute Sql Task" works fine.

That's how I call the Oracle's stored procedure in the "Execute SQL
Task":
"EXEC DIS_CONSTRAINT;"


Can anybody help me or at least give me a hint about how to solve this
issue?


TIA,
David Grant

Whilst I have never done this have you read this article

http://groups.google.ch/group/micros...ec1c?hl=en&lr=



--



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


<icebold54 (AT) hotmail (DOT) com> wrote