Subset: Build MS SQL Server scrambled extract is failing

Document ID : KB000009175
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

While trying to use the Subset function \"build ms sql server scrambled extract\", the resulting script is executed and fails with an error:

Incorrect syntax near the keyword 'from'.

Environment:
TDM 4.1 SQL Server 2008 R2 SP3
Cause:

The supplied file maskviews.sql had malformed SQL.


Error Message:
******************************

Changed database context to 'Scramble'.
Msg 156, Level 15, State 1, Server <<SERVER_NAME>>, Procedure ORDERS_v, Line 8
Incorrect syntax near the keyword 'from'.

Please review the following script that was generated from Subset:

use [Scramble] 

GO 
IF EXISTS(SELECT 1 FROM sys.views 
WHERE object_id = OBJECT_ID('[dbo].[ORDERS_v]')) BEGIN 
DROP VIEW [dbo].[ORDERS_v] 
END 
GO 
CREATE VIEW [dbo].[ORDERS_v] as select 
[ord_num] [ord_num], 
[cust_id] [cust_id], 
[prod_id] [prod_id], 
[qty] [qty], 
[order_date] [order_date], 
dbo.selectCAR_PARTS(200,CAST(description AS VARCHAR(1)),'') [description] from [Sales].[dbo].[ORDERS] L0 where 1 = 1 GO 

Please note this line fragment: 

dbo.selectCAR_PARTS(200,CAST(description AS VARCHAR(1)),'') [description] 

The double quotes are incorrect. It should be a single and not doubles. 

Resolution:

This is another expression of the error of double quotes instead of single quotes. Please see TEC1116381 for more information. Please get the Subset that has been created after June 22, 2017 by opening a Support Case (if needed, please review the video to refresh how to open a Support case) and request a patch after June 22, 2017.  

If a GA version has come out after August 30, 2017, open a Support case and ask if this patch is included in the GA after this date. 

Additional Information:

You can download the latest version of TDM by following the directions in this document: https://support.ca.com/us/knowledge-base-articles.TEC1903942.html.   

To contact support, go to https://www.ca.com/us/services-support/ca-support/contact-support.html.