Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
robertnzana
Starting Member
42 Posts |
 Posted - 2008-06-02 : 19:17:32
|
I have a 2005 db. I want to export all tables (including all pk, fk and data), views and sp to a 2000 db (yes downgrade). Seems easy enough, but...I run the Export wizard in 2005 Management Studio. I selected all tables, UNCHECKED "Optimize for many tables", went to "Edit Mappings..." and selected "Enable identity insert". Ran it with no errors. But, when I go to my SQL 2000 Enterprise Mgr and view the design of the tables (1) all primary keys are gone and (2) all default formulas, like newuser(), are completely missing. (When I select all 3 checkboxes in "Edit Mappings" alot of errors occur. Forget that!)Then I tried the "Script approach"...I first deleted the tables in the 2000 db, went to the Script Wizard in 2005 and chose script options for "SQL Server 2000" version. I selected Tables and chose all tables. I let it generate all the scripts. I then went to 2000 Query Analyzer and ran it. I analyzed the tables and that seemed to work better. But, when I went back to 2005 Mgmt Studio and tried to export the data to the 2000 tables I got a slew of errors in Pre-execute...- Pre-execute (Error)MessagesError 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". (SQL Server Import and Export Wizard) Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify that the column metadata is valid. (SQL Server Import and Export Wizard) Error 0xc004701a: Data Flow Task: component "Destination 2 - Appointments" (152) failed the pre-execute phase and returned error code 0xC0202025. (SQL Server Import and Export Wizard) This is all very frustrating!How can I get this working like it should?Thanks. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-02 : 20:46:10
|
| You should copy db objects in ssis. |
 |
|
|
|
|
|
Primary Keys missing on export from 2005 db to 2k