DataSource Controls :: Sql Server 2008 Import/export Wizard... Completely Useless
Apr 13, 2010
import/export wizard in 2000 had lots of options.
I could copy databases without a care in the world. Including constraints, stored proc., AND the data, etc.
Now, with v2008, the wizard is dumbed down too much. It's a "stupid" copy function that fails on the first constraint it meets.
"Copy database.." is not an option here: it needs sysadmin on both source and destination server.
Re-adding the constraints manually is not an option either: the database is WAY too large.
I'm actually hoping there is a temporary bug in the import/export wizard. Each option (and you have THREE...) returns with a new error.
Googling, I found I am not alone:
http://itknowledgeexchange.techtarget.com/itanswers/sql-server-2008-export-data-problem/
This post is exactly my situation. Here's what his post says: (bolded key parts)
I'm trying to get used to sql server 2008 having been forced to upgrade by my isp. I need to back up my key db on a weekly remote schedule, so have been trying to use ssms to export data from my remote db to a local copy.
Firstly I created a new empty db on the local machine. Then I scripted the structure of the db on the remote machine and ran the resulting query on the local machine to copy the table structure into the new db. This gave me empty target tables to map to (see
below).
I used the export data wizard and selected my remote tables, then highlighted the tables to be copied and used the Edit Mappings option. Here I need to do two things, firstly checking'Enable Identity Insert' (because I have a unique id in most tables which is identity seeded, and I've read that if this is not checked sql server will helpfully renumber your entire table, rendering all your foreign key references useless). Secondly I need to decide what to do with the data already in the destination table. SQL 2k DTS simply dropped the destination table and recreated it. SQL 2008 will either keep the structure and delete the rows therein, or drop the table and recreate it. Because I intend to save this package and call it on a schedule in the future, I need to choose one of these options, because next time the target db won't be empty.
If I choose the delete rows option, when the package runs I get the following error when the process gets ot the first table with an identity ID which is used elsewhere in the db as a foreign key.
Error 0xc002f210: Preparation SQL Task 4: Executing the query "Truncate TABLE [dbo][tblCompany] failed with the following error 'Cannot truncate table tblCompany because it is being referenced by a foreign key constraint. Possible failure reasons: Problems with the query, "Result Set" property not set correctly, parameters not set correctly or connection not established correctly'
Half a dozen preceding tables did seem to transfer fine, but the process falls down on this one table. What I want is for the process to ignore constraints whilst it does the backup.
Undeterred I tried the other option, dropping each table and creating a new version. This time I get the error:
Error 0cx0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code 0x80004005. An OLE DB record is available. Source MS SQL Server Native Client 10.0. Hresult 0x80004005 Description "The statement has been terminated".
An OLE DB record is available. Source MS SQL Server Native Client 10.0. Hresult 0x80004005 Description "Violation of Primary Key constraint 'PK_LoginClasses'. Cannot insert duplicate key in object 'dbo.LoginClasses'.
Then I try unchecking the Enable Identity Insert option to see what happens. This happens:
- Validating (Error)Messages
Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column "UserID".
(SQL Server Import and Export Wizard)
Error 0xc0202045: Data Flow Task 1: Column metadata validation failed.
(SQL Server Import and Export Wizard)
Error 0xc004706b: Data Flow Task
1: "component "Destination - Login" (40)" failed validation and returned validation status "VS_ISBROKEN".
(SQL Server Import and Export Wizard)Error 0xc004700c: Data Flow Task 1: One or more component failed validation.
(SQL Server Import and Export Wizard)
Error 0xc0024107: Data Flow Task 1: There were errors during task validation.
(SQL Server Import and Export Wizard)
I can't see any more options to try. So how do I get SSMS 2008 to do what Enterprise Manager 2k did with no drama?
View 11 Replies
Similar Messages:
Feb 23, 2010
I have to export some data from excel to sql server. So, I just used simply Import/Export wizard from Management Studio.
Fill up and set the all necessary according to this wizard, after that I saved as SSIS Package.
Because, I have to run this package several time, but unfortunately, I can't find it where she saved it.
That may be funny, but really i can't find it out So, How to i save this package?
View 2 Replies
Aug 19, 2010
I have recently upgraded my system from Visual Studio 2008 to Visual Studio 2010. I have MS SQL Server 2008 RS installed as well as IIS7 and I am able to connect to my web application on my local server (IIS7).
View 1 Replies
Apr 11, 2010
I created and populated a database inside Visual Studio 2008 instead of using Management Studio.I need to run the script to create the schema for users and roles.The database does not show up in ASP.NET SQL Server Setup Wizard or in Management StudioThe only databases that do show up are master, model, msdb, and tempdb
Is there anything I can do to fix this or would it be easier for me to just create a new database starting in Management Studio and then connect to it from within Visual Studio?
View 4 Replies
Feb 7, 2010
i have different tables for product details, images, category mapping
is there any way to import/export products by excel file in database along with category mappying and pictures in one go rather that importing just the products and then categorizing each product and adding image one by one ?
View 4 Replies
Feb 9, 2010
I have to programmatically import a table from a database "A" in Sql Server 2000 to a database "B" in another server 2005.
View 1 Replies
Jul 1, 2010
In my .net 2.0 application, i have used export / import excel option. In local, its working perfectly but in server i am getting the below errors,
Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005.Description: An unhandled exception occurred during the execution of the current web request. review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.UnauthorizedAccessException: Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005.
ASP.NET is not authorized to access the requested resource. Consider granting access rights to the resource to the ASP.NET request identity. ASP.NET has a base process identity (typically {MACHINE}ASPNET on IIS 5 or Network Service on IIS 6) that is used if the application is not impersonating. If the application is impersonating via <identity impersonate="true"/>, the identity will be the anonymous user (typically IUSR_MACHINENAME) or the authenticated request user.
To grant ASP.NET access to a file, right-click the file in Explorer, choose "Properties" and select the Security tab. Click "Add" to add the appropriate user or group. Highlight the ASP.NET account, and check the boxes for the desired access.
[UnauthorizedAccessException: Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005.]
repClaim.Button3_Click(Object sender, EventArgs e) +337
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565
I searched solutions for google and even in this forum, the following link also i applied but no use, still same error
[URL]
My application is running in IIS 7.0 with DefaultAppPool. In my web.config, i have used this authentication,
<authentication mode="Windows"/>
View 3 Replies
Jun 2, 2010
i have excel sheet i need it to be imported entire sheet into sql server 2005 i need the query to complete the task..
View 3 Replies
Jun 29, 2010
how can i transfer complete database from sql server 2008 to sql server 2008 without loosing relationship intigrity.
View 9 Replies
Feb 20, 2010
I have an application which imports data from csv to sql server 2005. It is taking long time to import data from csv to sql server 2005. it has a timer. on tick of timer, I am checking if current rown in csv exists in database. If it exists, It is not added to sql server otherwise, It is added that row to sql server 2005. Also, In page load, It checks on each tick, whether user is logged in or not. it adds value to log file that user is logged in. How can I optimize this ?
View 1 Replies
Feb 26, 2010
How do you import database diagram at SQL Server Management Studio Express (SSMSE)? Using SSMSE in checking my database through a shared server, the "Database Diagrams" folder has no import functionality.
View 1 Replies
May 19, 2010
is there any difference between sql express and sql client. Actually I have been told to install a sql client using which i will connect to the sql server installed remotely. Also I have sql express edition 2008 installed on my machine. So is it the same. Will I be able to connect to the sql server using the sql server express edition.?
View 2 Replies
Feb 2, 2010
For protect against XSS we should make all input from textboxes thoht Server.Htmlencode function.
1) If i let a input go thorgh Server.Htmlencode and save it in database. But what happen if i letter show this input data from database on browerser ...if database input data have <script> it will then make Xss!!!!.
2) I use Server.Htmlencode.. and the user write <b>ss<b>... (label.text = userinput.text;) and i WANT to show ss in browser. What shuld i do for make this happen ??
View 4 Replies
Apr 29, 2010
When i tried to attach 2008 database in sql server 2008 it is throwing the error-
"The database 'actitle' cannot be opened because it is version 655. This server supports version 611 and earlier. A downgrade path is not supported.Could not open new database 'actitle'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 948)"
When i execute 'select @@version' it gives 'Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Workgroup Edition on Windows NT 5.1 (Build 2600: Service Pack 3) 'How the version still be 2005 when im in 2008 ..How can i attach my 2008 database back up.
View 2 Replies
Sep 23, 2010
I want to import a DLL (built on VC) in asp.net, and I have a major problem in PRODUCTION Box (IIS 7 and Windows 2008 server). First: I want to say that this DLL works fine in my DEV Box with web application written in C#, and my development server (Win XP SP 3, .NET Framework 3.5 and IIS6). But when I upload my code (web application code + DLL) in PRODUCTION Box (IIS 7 and Windows 2008 server). Web application doesn't work with DLL any more. I am sure that Asp.net knows this DLL well, and if it did'nt recognize it would give me an error, but my problem is that my DLL does'nt works proprely when I run it in IIS 7 under Windows 2008 server. Do you know any solution ? Can it be something with the IIS 7 permissions?
View 2 Replies
Jul 15, 2010
I've written a desktop application using VB.NET and SQLEXPRESS. Now I am trying to deploy it to WS 2k3. But getting positive results wiht SQLE is proving difficult. Here is the connection string I am using:
connectionString = "Data Source=.SQLEXPRESS;" + "AttachDbFilename=" + DBPath + ";" + "Integrated Security=True;" + "Connect Timeout=30;" + "User Instance=True"
The problem is the results I get from the setting of 'User Instance'. Like it is, it loads a unique stance of SQLE, which is fine. But the instance just keeps eating memory. I tried limiting memory with sp_configure, but nothing happened. It just keeps growing. If I exclude 'User Instance', it uses the SQLE service running on my machine. It ignores the memory limitation, but eventually settles and stops growing.
Those results came from working on my local machine.When I put it on 2k3, I get the same results with User Instance = True; runaway memory. With User Instance = False, I start getting the 'attempt to attach an auto-named database failed' message. So now I am stuck. I would like to go with the 'User Instance = true' option, but I don't know how to deal with the runaway memory. If all else fails, I could use the =False option, but I just can't get 2k3 to allow.
View 1 Replies
Jan 13, 2010
I am developing a Standalone Application where i have to exprot data from sqltable to excelSheet..
View 6 Replies
May 7, 2010
I am trying to Export data from SQL server table to a text file using OpenRowSet.
View 3 Replies
Jun 17, 2010
Migrating SSIS package from SQL Server 2005 to SQL Server 2008
View 1 Replies
Feb 25, 2010
Previous developers have developed a .SSISDeploymentManifest file (SQL Server 2005) to install SSIS packages which prompts a Package Installation wizard. I tried to install it on SQL Sever 2008 (it works on SQL Server 2005), but it failed with the error: "Storing or modifying packages in SQL Server requires the SSIS runtime and database to be the same version. Storing packages in earlier versions is not supported".
How do I install 2005 SSIS packages on SQL Server 2008? I have all the .dtsx files, not sure how to install them?
View 2 Replies
Apr 2, 2010
I have 5 GB data, when I move from SQL server 2000 to 2008 with Import and Export wizard, my LDF size is increased to 30 GB, Can I reduce my LDF file or move. In my one of the table contain around 20 Lakh data which i have normalized in 2008 and create 4 tables, so my data is entering in normalized form in 2008. But due to size by hard disk is full. I have used shrnik command but it reduce to only 1 GB.
View 5 Replies
Mar 24, 2010
Does anyone know, what is difference between SQL Server 2000, 2005 and 2008?
View 3 Replies
Apr 26, 2010
I have SQL Server Standard 2008 installed on my machine, alongside Visual Studio 2008.
My current hosting provider (not a huge fan) requires me to access my db's remotely via SQL Server Management Utility. To date, I've been doing it by connecting remotely in VS2008. Their troubleshooting documentation/steps, though, are for the SQL Server Management Express 2008, and i'm having a lot of connectivity issues with them.
I followed the steps here: [URL] to "add additional features to an existing installation" - but when I did so, the setup wizard indicated that the Management Tools-Basic were already installed.
View 1 Replies
Apr 26, 2010
The sites that I've got are still in classic ASP. I know this is a good reason to move to .net but I don't want to have to redo all the sites yet.
So here is the problem:
[Code]....
When I have "On Error Resume Next" no erros get posted and everything runs just fine, the pulls from the database work perfectly. But as soon as we take On error resume next we start getting the following erros:
err#424err desc:Object requiredsql:SELECT Name,URL,Title FROM tblMenu WHERE Parent_ID=1 AND Deleted=0 ORDER BY SortOrderScript Name: /index2.asp
View 3 Replies
Sep 9, 2010
I recently bought a new computer, so I want to copy the projects into current system which has visual studio. I have a project developed in VWD 2008. I copied the folder into a flash drive and copied it back into the new system.
Knowing the location, how I do import the project into new system?
View 1 Replies