从视图导出数据 [英] Exporting data from a view

问题描述

我正在将 EXPORT WIZARD 运行到 SQL Server 2008 上的平面文件.

I am running the EXPORT WIZARD to a flat file on SQL Server 2008.

我正在为此过程提供一个从视图中获取数据的查询:

I am providing a query for this process which grabs data from a view:

SELECT [Full Date]
      ,[Year Entered]
      ,[Month Entered]
      ,[Day Entered]
      ,[DOW]
      ,[Week Ending]
      ,[Accession]
      ,[Sales Rep]
      ,[MLNPI]
      ,[IMSNPI]
      ,[IMS Specialty Primary Code]
      ,[Specialty Primary Description]
      ,[IMS Specialty Secondary Code]
      ,[Specialty Secondary Description]
      ,[IMS Specialty Tertiary Code]
      ,[Specialty Tertiary Description]
      ,[IMS Professional ID 1]
      ,[Physician]
      ,[Practice Code]
      ,[MLIS Code]
      ,[Practice Name]
      ,[Date Established]
      ,[Address]
      ,[Address2]
      ,[City]
      ,[State]
      ,[Status]
      ,[order count]
      ,[Order Comments]
      ,[Release Status]
  FROM [SalesDWH].[dbo].[BySpecimenWITHOUTLOMDATA]
  where [Year Entered]=2009

我收到这些错误:

- Validating (Error)
Messages
Error 0xc00470d4: Data Flow Task 1: The code page on input column "Full Date" (142) is 1252 and is required to be 65001.
 (SQL Server Import and Export Wizard)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Error 0xc0024107: Data Flow Task 1: There were errors during task validation.
 (SQL Server Import and Export Wizard)

有谁知道我做错了什么以及如何解决这个问题?

Does anyone know what I am doing wrong and how I can fix this?

我试图用 CAST 包装列:

I tried to wrap the columns in CAST:

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT cast( [Full Date] as nvarchar) as [Full Date],
cast([Year Entered] as nvarchar) as [Year Entered],
cast([Month Entered] as nvarchar) as [Month Entered],
cast([Day Entered] as nvarchar) as [Day Entered],
cast([DOW] as nvarchar) as [DOW],
cast([Week Ending] as nvarchar) as [Week Ending],
cast([Accession] as nvarchar) as [Accession],
cast([Sales Rep] as nvarchar) as [Sales Rep],
cast([MLNPI] as nvarchar) as [MLNPI],
cast([IMSNPI] as nvarchar) as [IMSNPI],
cast([IMS Specialty Primary Code] as nvarchar) as [IMS Specialty Primary Code],
cast([Specialty Primary Description] as nvarchar) as [Specialty Primary Description],
cast([IMS Specialty Secondary Code] as nvarchar) as [IMS Specialty Secondary Code],
cast([Specialty Secondary Description] as nvarchar) as [Specialty Secondary Description],
cast([IMS Specialty Tertiary Code] as nvarchar) as [IMS Specialty Tertiary Code],
cast([Specialty Tertiary Description] as nvarchar) as [Specialty Tertiary Description],
cast([IMS Professional ID 1] as nvarchar) as [IMS Professional ID 1],
cast([Physician] as nvarchar) as [Physician],
cast([Practice Code] as nvarchar) as [Practice Code],
cast([MLIS Code] as nvarchar) as [MLIS Code],
cast([Practice Name] as nvarchar) as [Practice Name],
cast([Date Established] as nvarchar) as [Date Established],
cast([Address] as nvarchar) as [Address],
cast([Address2] as nvarchar) as [Address2],
cast([City] as nvarchar) as [City],
cast([State] as nvarchar) as [State],
cast([Status] as nvarchar) as [Status],
cast([order count] as nvarchar) as [order count],
cast([Order Comments] as nvarchar) as [Order Comments],
cast([Release Status] as nvarchar) as [Release Status]


  FROM [SalesDWH].[dbo].[BySpecimenWITHOUTLOMDATA]
  where [Year Entered]=2009

并收到错误消息:

- Setting Source Connection (Error)
Messages
Error 0xc0207016: Source - Query [1]: There is a data source column with no name.  Each data source column must have a name.
 (SQL Server Import and Export Wizard)

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

推荐答案

编辑

错误消息中列的数据类型需要更改为NVARCHAR.由于您要求不修改视图，请使用 CAST 或 CONVERT 在查询中修改相应的列.我仍然会确保为目标文件指定 UTF-8 代码页只是为了一致性.

Datatypes for the columns in the error message need to be changed to NVARCHAR. Since you have a requirement to not modify the view, use CAST or CONVERT in the query to modify the appropriate columns. I would still ensure the UTF-8 code page is specified for the destination file just for consistency.

原创

在导入/导出向导中，将文件目标的代码页更改为 65001 (UTF-8).

In the Import/Export wizard, change the code page to 65001 (UTF-8) for the file destination.

这篇关于从视图导出数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！