Task is simple: I want to export all SQL Server database tables using SQL Server Management Studio 2008 R2 to MySQL (or any other format that MySQL can take, e.g Excel, CSV).

I have tried the following:

  • MySQL Workbench Successful in creating everything but did not copy the data for some tables
  • Import/Export Wizard in SQL Server Management Studio. This fails for all cases (of various destination options) with a useful error message when exporting to Excel (see below).

While Googling my way around this, the data export is failing due to invalid length of data that have some encoding. My guess is that a certain type of encoding that is not supposed to be more than 4000 bytes is having data stored more than 4000 bytes.

I confirmed this while attempting to export to Excel. It showed the following error:

Error 0xc0204016: SSIS.Pipeline:
The "output column "Subject" (83)" has a length that is not valid.
The length must be between 0 and 4000. (SQL Server Import and Export Wizard)

Exception from HRESULT: 0xC0204016 (Microsoft.SqlServer.DTSPipelineWrap)

The above error shows on some of the tables. This is evident in the fact that MySQL Workbench actually exported data for tables that are not affected, and then simply created tables for those affected (without exporting data to them).

How can I solve this and be able to export this data? (either by a query that will correct all the rows in all the tables that have this bad data; or completely change the encoding of the data in all the rows of all tables that have this bad data).

I cannot use the Flat File option because I have up a lot of tables. The Import/Export Wizard in my SQL Server Management Studio only allows for backing up on table at a time to Flat File.

When I try to export one of the tables to a flat file, I get these errors:

Error 0xc00470d4: Data Flow Task 1:
The code page on input column "FederalGovSponsored" (225) is 1252 and is required to be 65001.
(SQL Server Import and Export Wizard)

Error 0xc004706b: Data Flow Task 1:
"component "Destination - newflatfile_txt" (139)" 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)

How to remove this error?

I really don't use SQL Server, I use MySQL. The database was being maintained by someone else who is unwilling to provide support.

  • Stop using point-and-click wizards, roll up your sleeves, and write a BCP command. You can loop over tables etc. also, isn't cp 65001 UTF-8? How are you using UTF-8 in SQL Server? – Aaron Bertrand Mar 6 '16 at 16:24
  • @AaronBertrand I really don't use SQL Server. I use MySQL. The database was being maintained by someone else who is unwilling to provide support. Please provide insights into how to "write a BCP command...loop over tables etc." Thanks – Damilola Olowookere Mar 6 '16 at 16:49
up vote 4 down vote accepted

This will work for simple cases (e.g. no funky Unicode characters in object/column names or in the data).

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
EXEC master..xp_cmdshell ''bcp ' 
  + QUOTENAME(DB_NAME()) + '.'
  + QUOTENAME(s.name) + '.' 
  + QUOTENAME(t.name) 
  + ' out "C:\temp\x_' + s.name + N'_' + t.name + '.txt" -c ' -- may need -w/-N/-n
  + ' -S server\instance -Uusername -Ppassword'';'
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id];

PRINT @sql;
EXEC sys.sp_executesql @sql;

If you can't use xp_cmdshell then you can just output the bcp commands and run those from any command line window on any server or workstation that (a) has SQL Server client tools installed and (b) can connect to the database server. So run this query on the database server and then stuff the output into a batch file and run that batch file from wherever.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'PRINT ''bcp ' 
  + QUOTENAME(DB_NAME()) + '.'
  + QUOTENAME(s.name) + '.' 
  + QUOTENAME(t.name) 
  + ' out "C:\temp\x_' + s.name + N'_' + t.name + '.txt" -c ' -- may need -w/-N/-n
  + ' -S server\instance -Uusername -Ppassword'';'
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id];

EXEC sys.sp_executesql @sql;

More documentation here (including how to use format files and work with Unicode data):

  • Error: Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. I do not have admin privilege on the system – Damilola Olowookere Mar 6 '16 at 19:40
  • @emmanuel You can run those commands from any instance of SQL Server - e.g. one where you can turn on xp_cmdshell, or you can simply just print out the bcp commands and run those yourself from a batch file. All that needs to happen is the client machine where it runs needs at least bcp installed and obviously needs to be able to connect to the database. I'm just trying to offer you potential workarounds because it's very hard for someone on the Internet to troubleshoot your pointing and clicking in some wizard. – Aaron Bertrand Mar 6 '16 at 21:50
  • Thanks a lot! You are a life saver! I was able to generate for all then tables. I noticed that the output is a clean and nice tab (\t) separated file which is cool for import. Now the only issue left is how to export table schema (by that, I mean name and data type for each column) – Damilola Olowookere Mar 7 '16 at 11:32

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.