Wow, I've really let this blogging idea slide. I should have been writing more about my coding adventures in the last years. I don't really know where to pickup. I blame it on having 3 kids keeping me busy. As soon as I get home they're begging me to wrestle or play with them. I try to oblige most of the time. But I'm not complaining, I love my kids.
Life is more important than blogging, right?
I have started working with Oracle and I was recently tasked with importing a data pump export to a different schema. Once all the required roles were created and the correct REMAP_SCHEMA option was sorted out* I was left with one interesting problem. Everything remapped properly except triggers. The trigger name was remapped correctly but the target table still referenced the old schema.
Failing sql is: CREATE TRIGGER "NEW_SCHEMA"."TRIGGER_NAME" BEFORE INSERT ON OLD_SCHEMA.TRIGGER_NAME ...
I'm surprised that something this obvious would go unfixed.
**note Multiple schema remaps are done as follows:
alter table tablename change oldname newname varchar (10);Instead I have to use some sort of SQL Server procedure sp_rename
sp_rename 'TableName.ColumnName', 'NewColumnName', 'COLUMN'
Here's a tip for copying a database in SQL Server 2008 (maybe other versions) via the Copy Database wizard.
When I initially tried the wizard it failed with
Message: Access to the path 'E:DataMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATANEWDATABASENAME.mdf' is denied.
In this case the NETWORK SERVICE was not able to create the new MDF file. I had to do add this user with FULL CONTROL to the MSSQLDATA directory and the wizard ran with no complications.
- Navigate to data folder
- Right click -> Properties
- Click the Security tab
- Click the Edit button to change permissions
- Click the Add button to add a new group or user
- Search for "NETWORK SERVICE" and click Add
- Click the Full Control checkbox for the newly added user
In case you're trying to find the wizard, right click the database, pick tasks, then Copy Database.