将查询结果导出到 Excel Microsoft SQL Server 2012 时"200"找不到源数据类型错误

Question

我是Microsoft SQL Server的新手，我正在使用2012 Management Studio。当我尝试使用向导将查询结果导出到excel文件时，我得到了上面的错误。我已经看到解决方案张贴在这个错误的其他地方，但不知道足够弄清楚如何实现建议的解决方案。谁能一步一步地告诉我这些解决方案?

我相信我的问题是SQL Server导入和导出向导不识别Varchar和NVarchar，我认为这是我接收错误的列的数据类型。

SQL Server导入导出向导中的源类型200 ?

http://connect.microsoft.com/SQLServer/feedback/details/775897/sql-server-import-and-export-wizard-does-not-recognise-varchar-and-nvarchar

查询:

SELECT     licenseEntitlement.entID, licenseEntitlement.entStartDate, entEndDate, quote.quoteId, quote.accountId, quote.clientId, quote.clientName, quote.contactName, 
                      quote.contactEmail, quote.extReference, quote.purchaseOrderNumber, quote.linkedTicket
FROM         licenseEntitlement INNER JOIN
                      quote ON quote.quoteId = SUBSTRING(licenseEntitlement.entComments, 12, PATINDEX('% Created%', licenseEntitlement.entComments) - 12)
inner join sophos521.dbo.computersanddeletedcomputers on computersanddeletedcomputers.name = entid and IsNumeric(computersanddeletedcomputers.name) = 1
WHERE     (licenseEntitlement.entType = 'AVS') AND (licenseEntitlement.entComments LIKE 'OV Order + %') and entenddate < '4/1/2014' 
ORDER BY licenseEntitlement.entEndDate

错误:

TITLE: SQL Server Import and Export Wizard
------------------------------
Column information for the source and the destination data could not be retrieved, or the data types of source columns were not mapped correctly to those available on the destination provider.

[Query] -> `Query`:
          - Column "accountId": Source data type "200" was not found in the data type mapping file.
          - Column "clientId": Source data type "200" was not found in the data type mapping file.
          - Column "clientName": Source data type "200" was not found in the data type mapping file.
          - Column "contactName": Source data type "200" was not found in the data type mapping file.
          - Column "contactEmail": Source data type "200" was not found in the data type mapping file.
          - Column "extReference": Source data type "200" was not found in the data type mapping file.
          - Column "purchaseOrderNumber": Source data type "200" was not found in the data type mapping file.
          - Column "linkedTicket": Source data type "200" was not found in the data type mapping file.

如果需要更多的细节，请告诉我

Answer 1

因此，在StackOverflow链接中实现您给出的建议，将查询转换为View，这里有一个示例，它可能看起来像什么(带有一些代码格式;)—

CREATE VIEW [dbo].[test__View_1]
AS
SELECT LIC.entID, LIC.entStartDate, entEndDate, 
    quote.quoteId, quote.accountId, quote.clientId, quote.clientName, 
    quote.contactName, quote.contactEmail, quote.extReference, 
    quote.purchaseOrderNumber, quote.linkedTicket
FROM [dbo].licenseEntitlement  LIC  WITH(NOLOCK)
    INNER JOIN [dbo].quote  WITH(NOLOCK)
        ON quote.quoteId = SUBSTRING(LIC.entComments, 12, 
            PATINDEX('% Created%', LIC.entComments) - 12)
    INNER JOIN sophos521.dbo.computersanddeletedcomputers  COMPS  WITH(NOLOCK)
        ON COMPS.name = entid and IsNumeric(COMPS.name) = 1
WHERE (LIC.entType = 'AVS') 
  AND (LIC.entComments LIKE 'OV Order + %') 
  and (entenddate < '4/1/2014')
ORDER BY LIC.entEndDate
GO

然后，您将从test__View_1(或您为其选择的任何真实名称)导出，就好像test__View_1是表名一样。

供参考，在第一次之后，你已经执行了上面的操作——在你"创建"视图之后——然后从那时起，视图的第一行(在修改期间)发生了变化，从CREATE VIEW到ALTER VIEW。

()而且，除了bug的问题…在你的WHERE条款中，你是想要entComments LIKE 'OV Order + %'，还是真的想要entComments LIKE 'OV Order%' ?我已经在下面的替代示例代码中进行了更改。))

注意:如果您要重复导出(或重用)一次运行的输出，特别是如果您的查询很慢或占用机器…然后，您可能更喜欢SELECT INTO，而不是VIEW，以便在创建一个表，可以快速重用。(在开发用于导出的一次性查询时，我也会选择SELECT INTO而不是CREATE VIEW。)

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_NAME = 'zz_LIC_ENT_DETAIL')
    DROP TABLE [dbo].zz_LIC_ENT_DETAIL
SELECT LIC.entID, LIC.entStartDate, LIC.entEndDate, 
    quote.quoteId, quote.accountId, quote.clientId, quote.clientName, 
    quote.contactName, quote.contactEmail, quote.extReference, 
    quote.purchaseOrderNumber, quote.linkedTicket
INTO [dbo].zz_LIC_ENT_DETAIL
FROM [dbo].licenseEntitlement  LIC  WITH(NOLOCK)
    INNER JOIN [dbo].quote  WITH(NOLOCK)
        ON quote.quoteId = SUBSTRING(LIC.entComments, 12, 
            PATINDEX('% Created%', LIC.entComments) - 12)
    INNER JOIN sophos521.dbo.computersanddeletedcomputers  COMPS  WITH(NOLOCK)
        ON COMPS.name = LIC.entid and IsNumeric(COMPS.name) = 1
WHERE (LIC.entType = 'AVS') 
  AND (LIC.entComments LIKE 'OV Order%') 
  and (LIC.entenddate < '4/1/2014')
ORDER BY LIC.entEndDate

然后，您当然会从表zz_LIC_ENT_DETAIL(或您选择的任何表名)导出。

Answer 2

右键单击查询结果窗口并选择将结果保存为(CSV)可能更容易。

要在第一行添加列名，您还需要以这种方式修改查询(注意int或datetime列的强制转换):

select 'col1', 'col2', 'col3'
union all
select cast(id as varchar(10)), name, cast(someinfo as varchar(28))
from Question1355876