Not all source columns are present when creating an external table

[thuringian]

Issue Type: Bug

When creating an external table from another SQL view as my source I have experienced instances where not all of the source view's columns are present for creation in the external table.

The external table is being created on a SQL 2019 server (upgraded from SQL 2016) and the source view sits on a SQL 2017 server.

Azure Data Studio version: azuredatastudio 1.10.0 (5fdb967ffcea41df0335ec17cfa396a694bb174e, 2019-08-14T18:28:10.311Z) OS version: Windows_NT x64 10.0.17763

[kevcunnane]

Can you explain the issue more? When you say not all are present, do you mean the wizard is not showing all valid columns? Or something in the generated SQL code?

[thuringian]

The wizard does not show all of the valid source columns. As a result both the generated SQL script as well as the created external table is not a true match to the source table/view.

[kevcunnane]

Thanks - could you give an example schema (e.g. what does the view look like in the original SQL database? What are the detected columns vs. missed columns)?

[thuringian]

Here is the column select from a source view. FYI this view is selecting columns from another view.

SELECT sku.ID
, sku.MUID
, sku.VersionName
, sku.VersionNumber
, sku.Version_ID
, sku.VersionFlag
, sku.[Name]
, sku.[Code]
, sku.[ChangeTrackingMask]
, sku.[Allowed Package Weight]
, sku.[Allowed Package Volume]
, sku.[Amount]
, sku.[Amount UOM]
, sku.[Annual Meeting First Sale Tag]
, sku.[Annual Meeting Product Grouping]
, sku.[Approved Batch Record Required]
, sku.[Assign Effect Valuations]
, sku.[Authorization Group]
, sku.[Base Code_Code]
, sku.[Base Code_Name]
, sku.[Base Code_ID]
, sku.[Basic Material]
, sku.[Basic Text Data]
, sku.[Batch Number]
, sku.[Batch Management]
, sku.[CAD Indicator]
, sku.[Catalog Profile]
, sku.[Change Number]
, sku.[Closed]
, sku.[Commercial Price 1]
, sku.[Commercial Price 2]
, sku.[Commercial Price 3]
, sku.[Commercially Available]
, sku.[Competitor]
, sku.[Concentration]
, sku.[Component]
, sku.[Concentration UOM]
, sku.[Container Requirements]
, sku.[Cost Estimate Number]
, sku.[Cost Estimate Number Product Costing]
, sku.[Costing Version Of Current Standard Cost Estimate]
, sku.[Costing Version Of Future Standard Cost Estimate]
, sku.[Costing Version Of Previous Standard Cost Estimate]
, sku.[Cross Distribution Chain Status]
, sku.[Cross Plant Configurable Material]
, sku.[Cross Plant Material Status]
, sku.[Current Cost Estimate]
, sku.[Current Fiscal Year]
, sku.[Current Posting Period]
, sku.[Dangerous Good Indicator Profile]
, sku.[Dangerous Good Packaging Status]
, sku.[Date Approved]
, sku.[Date Of Last Goods Receipt]
, sku.[Deletion Flag At Client Level]
, sku.[Deletion Flag Valuation Type]
, sku.[Devaluation Indicator]
, sku.[Division]
, sku.[Document]
, sku.[Document Change Number]
, sku.[Document Type]
, sku.[Document Version]
, sku.[EAN Category]
, sku.[EAN UPC]
, sku.[EAN Variant]
, sku.[Environmentally Relevant]
, sku.[Excess Volume Tolerance]
, sku.[Exists in INPUT Sitecore]
, sku.[Excess Weight Tolerance]
, sku.[Exists in Notes]
, sku.[Exists in Salesforce]
, sku.[Exists in SAP]
, sku.[Expiration Date]
, sku.[External Material Group]
, sku.[Family_Code]
, sku.[Family_Name]
, sku.[Family_ID]
, sku.[Future Cost Estimate]
, sku.[Future Fiscal Year]
, sku.[Future Planned Price]
, sku.[Future Price]
, sku.[Generic Material]
, sku.[Global Distribution System Relevant]
, sku.[Gross Weight]
, sku.[Hazardous Material Number]
, sku.[Height]
, sku.[Highly Viscous]
, sku.[In Approved Vendor List]
, sku.[In Bulk Liquid]
, sku.[Industry Sector]
, sku.[Industry Standard Description]
, sku.[Insite Publish Price]
, sku.[International Material Number]
, sku.[Keywords]
, sku.[Label Form]
, sku.[Label Type]
, sku.[Last Price Change Date]
, sku.[Length]
, sku.[LIFO FIFO Relevant]
, sku.[LIFO Pool]
, sku.[Local Currency]
, sku.[Lotus Catalog Number_Code]
, sku.[Lotus Catalog Number_Name]
, sku.[Lotus Catalog Number_ID]
, sku.[Maintenance Status]
, sku.[Maintenance Status Completed Material]
, sku.[Manufacturer]
, sku.[Manufacturer Part Number]
, sku.[Manufacturer Part Profile]
, sku.[Material Completion Level]
, sku.[Material Condition Management]
, sku.[Material Costed With Quantity Structure]
, sku.[Material Description]
, sku.[Material Group]
, sku.[Material Group Packaging Materials]
, sku.[Material Is Configurable]
, sku.[Material Is Locked]
, sku.[Material Item Category Group]
, sku.[Material Ledger Active]
, sku.[Material Price Determination Control]
, sku.[Material Related Origin]
, sku.[Material Type_Code]
, sku.[Material Type_Name]
, sku.[Material Type_ID]
, sku.[Material Usage]
, sku.[Material Valid From]
, sku.[Material Valid To]
, sku.[Maximum Level]
, sku.[Minimum Remaining Shelf Life]
, sku.[Moving Price]
, sku.[Moving Price In Previous Period]
, sku.[Moving Price In Previous Year]
, sku.[Name HTML]
, sku.[Name Text]
, sku.[Name Unicode]
, sku.[NATO Stock Number]
, sku.[Net Weight]
, sku.[Number in Stock]
, sku.[Number Of GR Slips]
, sku.[Number of Sheets]
, sku.[OEM OTC]
, sku.[Old Material Number]
, sku.[Order Type PO]
, sku.[Order Unit]
, sku.[Origin Acceptance]
, sku.[Origin Group]
, sku.[Origin Of The Material]
, sku.[Out of Stock]
, sku.[Overhead Group]
, sku.[Packaging Code]
, sku.[Packaging Material Type]
, sku.[Packaging UOM_Code]
, sku.[Packaging UOM_Name]
, sku.[Packaging UOM_ID]
, sku.[Page Format]
, sku.[Page Format 2]
, sku.[Page Number]
, sku.[Period Indicator For SLED]
, sku.[Period Of Current Standard Cost Estimate]
, sku.[Pivot Material Group]
, sku.[Planned Price 1]
, sku.[Planned Price 2]
, sku.[Planned Price 3]
, sku.[Planned Price Date 1]
, sku.[Planned Price Date 2]
, sku.[Planned Price Date 3]
, sku.[Post To Inspection Stock]
, sku.[Posting Period]
, sku.[Price 2014]
, sku.[Previous Fiscal Year]
, sku.[Price 2015]
, sku.[Previous Period]
, sku.[Price 2016]
, sku.[Previous Planned Price]
, sku.[Price 2017]
, sku.[Price Control In Previous Year]
, sku.[Price Control Indicator]
, sku.[Price Control Indicator In Previous Period]
, sku.[Price Unit]
, sku.[Price Unit For Valuation Prices Based On Law]
, sku.[Price Unit Of Previous Year]
, sku.[Price Unit Or Previous Period]
, sku.[Procurement Rule]
, sku.[Produced In House]
, sku.[Product Allocation]
, sku.[Product Composition]
, sku.[Product Hierarchy]
, sku.[Product Inspection Memo]
, sku.[Product URLs]
, sku.[Purchasing Group PO]
, sku.[Purchasing Organization]
, sku.[Purchasing Value Key]
, sku.[Qualify For Free Goods Discount]
, sku.[Quality Management Procurement Active]
, sku.[Reference Material For Packing]
, sku.[Relevant For CM]
, sku.[Return Code]
, sku.[Return To Logistics Level]
, sku.[Rounding Rule SLED]
, sku.[SAP Entered By]
, sku.[SAP Entry Date]
, sku.[SAP Last Updated By]
, sku.[SAP Last Updated Date]
, sku.[SAP Updated By]
, sku.[Schedule For Delivery]
, sku.[Season]
, sku.[Serialization Level]
, sku.[Shelf Life]
, sku.[Sitecore Discontinued]
, sku.[Shipping Method_Code] AS [Shipping Method]
, sku.[Sitecore Discontinued Date]
, sku.[Size]
, sku.[Sitecore Last Modified]
, sku.[Size Dimensions]
, sku.[Source Database]
, sku.[Source Of Supply]
, sku.[Stackability Factor]
, sku.[Standard Price]
, sku.[Standard Price In Previous Period]
, sku.[Standard Price In Previous Year]
, sku.[Status_Code]
, sku.[Status_Name]
, sku.[Status_ID]
, sku.[Stock Quantity]
, sku.[Storage Conditions]
, sku.[Storage Location]
, sku.[Storage Percentage]
, sku.[Subsidiary]
, sku.[Tax Price 1]
, sku.[Tax Price 2]
, sku.[Tax Price 3]
, sku.[Taxable]
, sku.[Temperature Conditions]
, sku.[Total Net Order Value]
, sku.[Total Stock In Period Before Last]
, sku.[Total Stock In Year Before Last]
, sku.[Total Valuated Stock Period Before Last]
, sku.[Shipping Method_Code] AS [Transportation Group]
, sku.[Type Of Vendor]
, sku.[Unit Of Dimension]
, sku.[Unit Of Measure]
, sku.[Unit Of Weight]
, sku.[Valuation Area]
, sku.[Valuation Category]
, sku.[Valuation Class]
, sku.[Valuation Class For Project Stock]
, sku.[Valuation Class In Previous Period]
, sku.[Valuation Class In Previous Year]
, sku.[Valuation Maintenance Status]
, sku.[Valuation Type]
, sku.[Valuation Valid From]
, sku.[Valuation Variant For Current Standard Cost Estimate]
, sku.[Valuation Variant For Future Standard Cost Estimate]
, sku.[Valuation Variant For Previous Standard Cost Estimate]
, sku.[Variable Key 100 Bytes]
, sku.[Variable Purchase Order Unit]
, sku.[VC Sales Order Stock]
, sku.[Volume]
, sku.[Volume UOM SAP]
, sku.[Volume Unit 2]
, sku.[Volume UOM]
, sku.[Weight Unit]
, sku.[Width]
, sku.[Year Current Period]
, sku.[Core FZ Stock]
, sku.[Shelf Life SAP]
, sku.[Concentration SAP]
, sku.[Website Category List]
, sku.[Concentration UOM SAP]
, sku.[Volume SAP]
, sku.[Temperature Conditions SAP]
, sku.[Storage Conditions SAP]
, sku.[Base Code SAP]
, sku.[Out of Stock Date]
, sku.[Out of Stock Availability Date]
, sku.[Out of Stock Comments]
, sku.EnterDateTime
, sku.EnterUserName
, sku.EnterVersionNumber
, sku.LastChgDateTime
, sku.LastChgUserName
, sku.LastChgVersionNumber
, sku.ValidationStatus
FROM mdm.vw_Product_SKU sku

Here is a screen shot of the column mapping when selecting the view in the wizard. The available columns ends at [Document Version]

And here are the columns included in the generated SQL script which matches what is in the screenshot above and matches the resulting external table that was created. CREATE EXTERNAL TABLE [dbo].[vw_Salesforce_Product_SKU] ( [ID] INT NOT NULL, [MUID] UNIQUEIDENTIFIER NOT NULL, [VersionName] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [VersionNumber] INT NOT NULL, [Version_ID] INT NOT NULL, [VersionFlag] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS, [Name] NVARCHAR(250) COLLATE SQL_Latin1_General_CP1_CI_AS, [Code] NVARCHAR(250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [ChangeTrackingMask] INT NOT NULL, [Allowed Package Weight] DECIMAL(38, 3), [Allowed Package Volume] DECIMAL(38, 3), [Amount] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS, [Amount UOM] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS, [Annual Meeting First Sale Tag] NVARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS, [Annual Meeting Product Grouping] NVARCHAR(25) COLLATE SQL_Latin1_General_CP1_CI_AS, [Approved Batch Record Required] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Assign Effect Valuations] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Authorization Group] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Base Code_Code] NVARCHAR(250) COLLATE SQL_Latin1_General_CP1_CI_AS, [Base Code_Name] NVARCHAR(250) COLLATE SQL_Latin1_General_CP1_CI_AS, [Base Code_ID] INT, [Basic Material] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Basic Text Data] NVARCHAR(150) COLLATE SQL_Latin1_General_CP1_CI_AS, [Batch Number] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Batch Management] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [CAD Indicator] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Catalog Profile] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Change Number] DECIMAL(38), [Closed] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Commercial Price 1] DECIMAL(38, 2), [Commercial Price 2] DECIMAL(38, 2), [Commercial Price 3] DECIMAL(38, 2), [Commercially Available] NVARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS, [Competitor] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Concentration] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS, [Component] NVARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS, [Concentration UOM] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS, [Container Requirements] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Cost Estimate Number] DECIMAL(38), [Cost Estimate Number Product Costing] DECIMAL(38), [Costing Version Of Current Standard Cost Estimate] DECIMAL(38), [Costing Version Of Future Standard Cost Estimate] DECIMAL(38), [Costing Version Of Previous Standard Cost Estimate] DECIMAL(38), [Cross Distribution Chain Status] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Cross Plant Configurable Material] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Cross Plant Material Status] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Current Cost Estimate] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Current Fiscal Year] DECIMAL(38), [Current Posting Period] DECIMAL(38), [Dangerous Good Indicator Profile] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Dangerous Good Packaging Status] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Date Approved] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Date Of Last Goods Receipt] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Deletion Flag At Client Level] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Deletion Flag Valuation Type] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Devaluation Indicator] DECIMAL(38), [Division] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Document] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Document Change Number] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Document Type] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS, [Document Version] NVARCHAR(100) )

[kevcunnane]

Awesome - this should be enough to work on a repro and investigate. Thank you!