September 21, 2018 at 7:16 am
I tried using the Import and Export Wizard to copy table/views from one database server to another and I'm getting the following error and it is not doing all the create of new views:
[Destination 9 - dataExtract_AwardDisbursement_View [528]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Does anyone know how to get pass this error or if there is an easier way to Copy Table/Views for all tables in a database?
September 21, 2018 at 7:22 am
Are you not able to back up and restore the database, assuming you want the data as well?
Alternatively you could generate scripts for objects and then execute the script against the other database server.
September 21, 2018 at 7:35 am
mark.humphreys - Friday, September 21, 2018 7:22 AMAre you not able to back up and restore the database, assuming you want the data as well?Alternatively you could generate scripts for objects and then execute the script against the other database server.
When you say scripts are you saying where I create a script for the drop and then one for the create table? There are 122 dataviews and using the import and export wizard was so easy because it allowed me to select all.
September 21, 2018 at 7:41 am
maria.lindquist - Friday, September 21, 2018 7:35 AMmark.humphreys - Friday, September 21, 2018 7:22 AMAre you not able to back up and restore the database, assuming you want the data as well?Alternatively you could generate scripts for objects and then execute the script against the other database server.
When you say scripts are you saying where I create a script for the drop and then one for the create table? There are 122 dataviews and using the import and export wizard was so easy because it allowed me to select all.
Not sure what a 'dataview' is, but backup/restore is the easiest solution here.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 21, 2018 at 7:43 am
If you right click on the database name and choose
Tasks
then
Generate Scripts
You can export all the tables, etc into one script or individual scripts.
You can then execute on the new server. You will need to check the script before executing as it put the USE database command into each script.
This will create the objects you should need.
September 21, 2018 at 10:31 am
maria.lindquist - Friday, September 21, 2018 7:16 AMI tried using the Import and Export Wizard to copy table/views from one database server to another and I'm getting the following error and it is not doing all the create of new views:[Destination 9 - dataExtract_AwardDisbursement_View [528]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".Does anyone know how to get pass this error or if there is an easier way to Copy Table/Views for all tables in a database?
This looks like a data source error when you are setting up the import/export through the wizard. What are you setting it to? Is this SQL server to SQL server? I typically go with SQL Server Native Client if SQL Server to SQL Server.
Check your PKs and Indexes after the import! (You might have to script those out separately)
Also, in the mappings for each table, make sure your data types match.
Hope this helps.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply