1

Let me start off by saying I know little and less about SQL. I received a .bak file from a client earlier today and they want me to move it to a new database (not SQL). I've created a SQL Server 2012 instance and restored the .bak file to a database and now it is .mdf file.

The last step of this process is getting it into either Excel or a .csv file (the inputs accepted by the new database system). I tried following the basic "right click and then select export and a destination" instructions, but I keep getting an error kicked back to me. I've copy/pasted the report below:

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Copying Rows (Error)
Messages
Error 0xc002f210: Preparation SQL Task 1: Executing the query "CREATE TABLE `aspnet_Profile` (
`UserId` GUID,
`..." failed with the following error: "The Microsoft Jet database engine could not find the object 'aspnet_Profile'.  Make sure the object exists and that you spell its name and the path name correctly.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 (SQL Server Import and Export Wizard)


- Post-execute (Stopped)
Improve this question
2
  • 1
    I've seen this error once when I had the file open in Excel, thus locking it for writing. SSIS couldn't write to the file, and failed to create the table/sheet.
    – db2
    Dec 27, 2013 at 14:50
  • @db2: I have also noted this when the workbook itself is closed, but the instance of EXCEL which recently had it open is still running. It has occasionall been necessary to close both the workbook and the instance of EXCEL which recently had it open.
    – Pieter Geerkens
    Sep 21, 2014 at 14:47

4 Answers 4

Reset to default
3

Seems like more often than not I run into issues trying to export to excel using the SSMS import/export wizard and I've never taken the time to learn and understand the quirks.

This doesn't necessarily answer your question, but might be a viable alternative. Instead of exporting to excel, consider importing into excel. This YouTube video appears to demonstrate it: http://youtu.be/VQrKg0GgZR0

Of course, if you need to export a lot of tables or need to do this often this is a rather tedious approach, but it has worked very well for me with a lot fewer headaches when only exporting a few tables.

Improve this answer
1
  • Also, I've often run into issues copying and pasting from an SSMS query window to excel when there are things like tabs and line breaks. I always follow the approach in that YouTube video to get around it, except in the last step where it asks how you'd like to view the data in your workbook, I click the properties button, change the command to SQL and copy in my query.
    – Eilert Hjelmeseth
    Feb 14, 2015 at 4:10
1

Depending on how large your database is (in terms of number of columns and tables), I don't think you're going to like this particular answer. The failure is likely occurring because you would first need to create a skeleton XLS with all the corresponding columns and sheets predefined (ie; in an XLS file: create sheets that match all the table names and create all the columns predefined with the table column names), unless you create some kind of DTS to dynamically build it all for you - please DBA experts, correct me if I am wrong here.

If you do not wish to do all that, and it is a once off, you may want to consider performing a select statement for each table and then export the results of that select to an excel spreadsheet by using cut and paste in the SQL query window. Be aware that doing a full select * on a table is probably frowned upon by DBAs in a live production environment.

Improve this answer
4
  • If you use the Import/Export wizard, it will typically create the sheets for you.
    – db2
    Dec 27, 2013 at 14:48
  • Interesting, when I tried with 2012, I got the same behavior as the OP. It wasn't until I prefabricated an XLS that it worked.
    – Damien
    Dec 28, 2013 at 0:11
  • Now that's odd. I do exports all the time with 2012, and it's never made me set up the spreadsheet beforehand.
    – db2
    Dec 30, 2013 at 13:14
  • Interesting, I just tried again on a 2008 box, all behaved as expected, and the XLS was generated without any kind of existing destination file. Anyway, perhaps the original poster could use a BCP export per table instead: technet.microsoft.com/en-us/library/aa337544.aspx
    – Damien
    Jan 6, 2014 at 1:55
1

Even though the error message seems to indicate there's something wrong with creating the table, for me the problem was a column of datatype LongBinary that couldn't be exported. After ignoring the column in Column Mappings the table could be exported successfully.

Improve this answer
-3

Make sure the excel file to which you are exporting the data is not open.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.