###RFC 4180:
RFC 4180 defines Common Format and MIME Type for Comma-Separated Values (CSV) Files. One of the requirements of the RFC 4180 is stated as below. This is the point #7 in the RFC link.
If double-quotes are used to enclose fields, then a double-quote
appearing inside a field must be escaped by preceding it with
another double quote. For example:
"aaa","b""bb","ccc"
###SQL Server 2000:
DTS Export/Import Wizard in SQL Server 2000 seems to conform to the above mentioned standards even though the RFC 4180 itself seem to have been published only on October 2005. I am using the below stated SQL Server 2000 version.
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
###SQL Server 2012:
SQL Server Import and Export Wizard in SQL Server 2012 does not export the data from table to CSV file according to the standard defined in RFC 4180. I am using the below stated SQL Server 2012 version.
Microsoft SQL Server 2012 - 11.0.2316.0 (X64)
Apr 6 2012 03:20:55
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
###Issue Simulation:
Here is a sample that I ran in both SQL Server 2000 and SQL Server 2012. I ran the below query to create a table and insert few records. The ItemDesc column has data with double-quotes in it. My intention is to export the data from both these SQL