SQL Express creates database with AUTO_CLOSE ON by default. Auto close databases automatically close themself (and thus release the file handles) whenever there are no sessions using the database. This works 'as advertised', you just have to read the small print:
True for all databases when using SQL Server 2000 Desktop Engine or
SQL Server Express, and False for all other editions, regardless of
operating system.
If you don't like the default behavior, simply turn auto-close off on the databases:
ALTER DATABASE [<dbname>] SET AUTO_CLOSE OFF;
Is it safe to assume columns of NVARCHAR, NTEXT, NCHAR, BIT, INT, DECIMAL, FLOAT, and DATETIME all MUST be UNICODE...
Only the XML
and N
-prefixed types (NCHAR
, NVARCHAR
, and NTEXT
[which has been deprecated since SQL Server 2005 was released so please do not use it]) are Unicode. Those other types you mentioned are not strings and are not stored as strings, hence they are not relevant to this question.
... and therefore WILL NOT have any characters unable to be converted from UTF-16LE to UTF-8...
This is not exactly a valid question. Unicode characters are Unicode characters regardless of their encoding, whether it is UTF-8, UTF-16LE, UTF-16BE, UTF-32LE, or UTF-32BE. Now, it is possible for the data itself to contain invalid sequences, such as invalid Surrogate Pairs. But then those aren't valid characters in the UTF-8 or UTF-32 encodings either.
... simply by exporting the values to a .txt file and resaving them with the UTF-8 Encoding prior to importing them to MySQL?
Well, you need to be sure to save the initial export file with a Unicode-encoding. So you would use either the -N
or -w
options with BCP.
Also, make sure that you are doing more than just changing the Byte Order Mark (BOM) of the file and are actually converting the Unicode / UT-16LE characters to UTF-8.
Best Answer
Not natively with BCP, but if you define SQL Server Integration Services as native (after all, it's free in the box with SQL Server), then use SSIS instead. You can even output to compressed files with third party add-ons for SSIS.