Due to database corruption (see my other posts), I am attempting to transfer all objects and data out of one database into another using SSIS. I've encountered and fixed numerous errors, and now I have a new issue.
I have 2 different schemas, dbo and DMUSER. When the DMUSER stored procedures transfer, *some* of them successfully have the DMUSER schema and *some* come over incorrectly as dbo because of how the stored procs were originally scripted:
CREATE PROCEDURE CheckAS400DealerCode
CREATE PROCEDURE DMUSER.CheckAS400DealerCode
As a result, it was my intent to transfer over all objects *except* for the DMUSER stored procs (this part works correctly) - and then, in a second transfer, change my authentication to the SQL DMUSER user (who has a default schema of DMUSER and is a member of sysadmin) to pull them over. However, the second transfer completes *successfully* (turns green and all) but the stored procs are not transferred to the destination database. No errors or anything!
Has anyone else encountered this or have any suggestions?! If you could provide any sort of help, I'd love you forever and be your best friend! :-P