| Author |
Topic |
|
sentinelace
Starting Member
34 Posts |
 Posted - 2011-12-13 : 14:31:05
|
| I have two databases. I want to schedule a dump from one database but only copy over a few tables each day without having to do it manually. I thought you could do this in 2005 with import/export. What is the best way of doing this? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sentinelace
Starting Member
34 Posts |
Posted - 2011-12-13 : 15:03:57
|
| I ran it but never got to that point. I create the SSIS package but then at the end it just says, hit finish to perform these actions. What did I miss? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sentinelace
Starting Member
34 Posts |
Posted - 2011-12-13 : 15:12:12
|
| Great! When I try and create the job and run it manually it says sa login failed? Why? I can connect to the DB fine with SA. The log shows:Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>] |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sentinelace
Starting Member
34 Posts |
Posted - 2011-12-13 : 16:41:12
|
| I am not 100% I am looking at the right log either. How do you edit the package once it's been created?Started over with same error. I am logged on to the server as "administrator" and connected to the database as "SA" and tried "Administrator" with same result. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sentinelace
Starting Member
34 Posts |
Posted - 2011-12-13 : 17:04:12
|
| I saved it to the file system and I get a failed on step 2 every time. I have recreated the package now 5 times with the same result. I have the same username and password on both databases. Where is the log? I'm not seeing much to help troubleshoot the error? That is the error located under sql logs. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sentinelace
Starting Member
34 Posts |
Posted - 2011-12-14 : 07:22:28
|
| I have enabled logging with visual stuido and I see this error when running the job:An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The statement has been terminated.".An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'TEST_GRP_PK'. Cannot insert duplicate key in object 'dbo.TEST_GRP'.". |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sentinelace
Starting Member
34 Posts |
Posted - 2011-12-14 : 13:02:44
|
| Perhaps. This is one step process now. How do you split it up to delete first, then copy?When I try to delete from studio I get referenced by a foreign key constraint. And it won't delete there. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-12-14 : 13:25:49
|
| If you've got foreign key constraints and you are refreshing parent tables, then this just got a bit more complex. You are going to need to refresh all tables involved in the foreign keys. You'll start by deleting from all of them and then doing the insert. As for how to do this, well you'll need to edit the SSIS package to add a SQL step in there with your delete code. I'd highly recommend abandoning the SSIS package at this point and instead just using T-SQL. Can you get away with a backup/restore instead?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sentinelace
Starting Member
34 Posts |
Posted - 2011-12-14 : 13:29:55
|
| I don't care what process honestly as long as I can restore paticular tables to this second database. The SSIS package seemed to be the easiest method but obviously not. How do we proceed now? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-12-14 : 13:34:02
|
| If you can't use backup/restore (would give you a total refresh and not just particular tables), then you'll need to track down all of your foreign key constraints for the particular tables. Then you'll need to spend some time ordering them. For the deletes that you'll need, the order would be child tables first and then parent tables. For the inserts, the order would be parent tables first and then child tables.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sentinelace
Starting Member
34 Posts |
Posted - 2011-12-14 : 13:45:16
|
| So I can just restore paticular tables from a backup? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sentinelace
Starting Member
34 Posts |
Posted - 2011-12-14 : 14:41:48
|
| Okay. If found a script that gives me the PK's and FK between all the tables and the relation, but I only see one of the tables I need. Maybe the other ones don't have FKs? Now what is needed to move from here? |
|
|
|
sentinelace
Starting Member
34 Posts |
Posted - 2011-12-15 : 10:58:05
|
| bump. ;) |
|
|
|
sentinelace
Starting Member
34 Posts |
Posted - 2011-12-19 : 08:28:25
|
| I am contacting the company for that makes this software to see what they say about the keys. unlessy ou have another idea? |
|
|
|
Next Page
|
Import/Export From database to another Automated?