CODEWITHSUNDEEP
sql-servert-sqlazure-synapse
Vinicio Steven Flores Hernndez
Vinicio Steven Flores Hernndez

Reputation: 3

The below dynamic SQL fails but works fine when I run it manually

I'm running this SQL statement built into a string using sp_executesql but it always fails with this error:

Msg 103010, Level 16, State 1, Line 1
Parse error at line: 4, column: 1: Incorrect syntax near 'GO '.

Here's the code:

DECLARE @sql NVARCHAR(MAX) = '';
SET @sql = N'BEGIN TRY
             IF object_id(N''[DW.myview]'', ''V'') IS NOT NULL
                DROP VIEW [DW.myview]
             GO
             CREATE VIEW [DW.myview] 
             AS
                 SELECT *
                 FROM
                     (SELECT
                          field_name, field_value, ww, id, tenant,
                          revision, rls_group_id, is_current,
                          updated_date, system_updated_date
                      FROM
                          dbo.myview
                      WHERE
                          field_name IS NOT NULL
                          AND field_name <> '''') t 
               PIVOT(MAX(field_value) 
                     FOR field_name IN ([topic.ar_gen]
                                                   , [topic.build]
                                                   , [topic.build_found]
                                                   , [topic.ccb_change_scope]
                                                   , [topic.ccb_order]
                                                   , [topic.ccb_por]
                                                   , [topic.ccb_prq_gating]
                                                   , [topic.ccb_questionnaire]
                                                   , [topic.ccb_status]
                                                   , [topic.ccb_wontfix_action]
                                                   , [topic.ccb_wontfix_reason]
                                                   , [topic.ccb_workaround]
                                                   , [layer.defined_date]
                                                   , [layer.drop_fix_plan]
                                                   , [layer.drop_found]
                                                   , [layer.env_found]
                                                   , [layer.errata_info]
                                                   , [layer.errata_info_owner]
                                                   , [layer.errata_status]
                                                   , [layer.failure_signature]
                                                   , [layer.forum]
                                                   , [layer.merge_id]
                                                   , [layer.open_date]
                                                   , [layer.por]
                                                   , [layer.release_found]
                                                   , [layer.repo_modified_date]
                                                   , [layer.root_cause]
                                                   , [layer.status_mode]
                                                   , [layer.status_reopen]
                                                   , [layer.team_found]
                                                   , [layer.test_found]
                                                   , [layer.to_reproduce]
                                                   , [layer.type]
                                                   , [layer.ww_defined_date]
                                                   , [layer.ww_open_date]
                                                   , [layer.ww_repo_modified_date]
                                                   , [classification]
                                                   , [closed_by]
                                                   , [closed_date]
                                                   , [comments]
                                                   , [component]
                                                   , [component_affected]
                                                   , [concat_read_grps_id]
                                                   , [concat_write_grps_id]
                                                   , [context]
                                                   , [description]
                                                   , [domain]
                                                   , [domain_affected]
                                                   , [eid]
                                                   , [family]
                                                   , [from_id]
                                                   , [from_subject]
                                                   , [from_tenant]
                                                   , [hierarchy_path]
                                                   , [subtopic.layer.affected_domain]
                                                   , [subtopic.layer.affecting_BAT]
                                                   , [subtopic.layer.affecting_certification]
                                                   , [subtopic.layer.affecting_compliance]
                                                   , [subtopic.layer.affecting_DPMO]
                                                   , [subtopic.layer.affecting_regression]
                                                   , [subtopic.layer.affecting_WiAMT]
                                                   , [subtopic.layer.applicable_derivatives]
                                                   , [subtopic.layer.applicable_projects_in_OTM]
                                                   , [subtopic.layer.AR_owner]
                                                   , [subtopic.layer.ATransition]
                                                   , [subtopic.layer.boot_device]
                                                   , [subtopic.layer.branch]
                                                   , [subtopic.layer.bug_communication]
                                                   , [subtopic.layer.bug_escape_category]
                                                   , [subtopic.layer.bug_escape_resolution]
                                                   , [subtopic.layer.bug_escape_stage]
                                                   , [subtopic.layer.bug_escape_status]
                                                   , [subtopic.layer.CCE_status]
                                                   , [subtopic.layer.changelist]
                                                   , [subtopic.layer.customer]
                                                   , [subtopic.layer.customer_impact]
                                                   , [subtopic.layer.date_moved_to_assigned]
                                                   , [higher_level.layer..layer.dcg_exposure]
                                                   , [higher_level.layer.dcg_priority]
                                                   , [higher_level.layer.duplicate_date]
                                                   , [higher_level.layer.exposure]
                                                   , [higher_level.layer.families_affected]
                                                   , [higher_level.layer.fix_stage]
                                                   , [higher_level.layer.fixed_by]
                                                   , [higher_level.layer.fixed_in_project_build]
                                                   , [higher_level.layer.found_in_derivatives]
                                                   , [higher_level.layer.future_cloned_bug_id]
                                                   , [higher_level.layer.FW_release]
                                                   , [higher_level.layer.hh_current_user]
                                                   , [higher_level.layer.HSD_Classic_DB]
                                                   , [higher_level.layer.HSD_Classic_Focus]
                                                   , [higher_level.layer.HSD_closed_by]
                                                   , [higher_level.layer.HSD_submitted_by]
                                                   , [higher_level.layer.intel_plarform]
                                                   , [higher_level.layer.intel_Platfrom_affected]
                                                   , [higher_level.layer.internal_record_update]
                                                   , [higher_level.layer.IP]
                                                   , [higher_level.layer.is_DevOps_user]
                                                   , [higher_level.layer.is_FIT_user]
                                                   , [higher_level.layer.is_OK_to_forward]
                                                   , [higher_level.layer.is_security_group_user]
                                                   , [higher_level.layer.NP_sysdebug_status]
                                                   , [higher_level.layer.operating_system]
                                                   , [higher_level.layer.pch_list]
                                                   , [higher_level.layer.platform]
                                                   , [higher_level.layer.platform_type]
                                                   , [higher_level.layer.PO_sysdebug_status]
                                                   , [higher_level.layer.prev_component]
                                                   , [higher_level.layer.prev_component_affected]
                                                   , [higher_level.layer.product]
                                                   , [higher_level.layer.project]
                                                   , [higher_level.layer.rejected_approved]
                                                   , [higher_level.layer.rejected_approved_notes]
                                                   , [higher_level.layer.rejected_date]
                                                   , [higher_level.layer.related_issue_id]
                                                   , [higher_level.layer.release_trigger]
                                                   , [higher_level.layer.release_type]
                                                   , [higher_level.layer.reproducibility]
                                                   , [higher_level.layer.reviewed_by]
                                                   , [higher_level.layer.reviewed_by_CCE]
                                                   , [higher_level.layer.root_cause_analysis]
                                                   , [higher_level.layer.security_legal_impact]
                                                   , [higher_level.layer.security_level]
                                                   , [higher_level.layer.sku_affected]
                                                   , [higher_level.layer.sku_list]
                                                   , [higher_level.layer.submitted_for_tracking_only]
                                                   , [higher_level.layer.submitter_org]
                                                   , [higher_level.layer.sysdebug_notes]
                                                   , [higher_level.layer.sysdebug_review_status]
                                                   , [higher_level.layer.target_MS]
                                                   , [higher_level.layer.target_QSR_release]
                                                   , [higher_level.layer.target_release]
                                                   , [higher_level.layer.validator]
                                                   , [higher_level.layer.version_defect_verified]
                                                   , [higher_level.layer.ww_date_moved_to_assigned]
                                                   , [higher_level.layer.ww_duplicate_date]
                                                   , [higher_level.layer.ww_rejected_date]
                                                   , [kinship]
                                                   , [lineage]
                                                   , [link_direction]
                                                   , [link_type]
                                                   , [native]
                                                   , [nickname]
                                                   , [notify]
                                                   , [owner]
                                                   , [parent_id]
                                                   , [permission_id_list]
                                                   , [permission_override]
                                                   , [priority]
                                                   , [read_grps]
                                                   , [reason]
                                                   , [reason_other]
                                                   , [relationship]
                                                   , [relationship_action]
                                                   , [release]
                                                   , [release_affected]
                                                   , [sampletime]
                                                   , [send_mail]
                                                   , [sets]
                                                   , [source_read_grps]
                                                   , [source_read_grps_id]
                                                   , [source_write_grps]
                                                   , [source_write_grps_id]
                                                   , [status]
                                                   , [status_reason]
                                                   , [subject]
                                                   , [submitted_by]
                                                   , [submitted_date]
                                                   , [subsystem]
                                                   , [sync_action]
                                                   , [tag]
                                                   , [tenant_affected]
                                                   , [title]
                                                   , [updated_by]
                                                   , [updated_reason]
                                                   , [write_grps]
                                                   , [write_grps_id]
                                                   , [week_closed_date]
                                                   , [week_submitted_date]
                                                   , [week_system_updated_date]
                                                   , [week_updated_date]) ) AS pivot_table
    ;

    END TRY
BEGIN CATCH
    EXEC usp_GetErrorInfo
END CATCH'

EXECUTE sp_executesql @sql;

The interesting thing is, if I extract the SQL out of the string and run it as usual (F5) it works fine it creates the intended view with the intended SQL behind it.

I'm using Azure SQL Data Warehouse (now known as Synapse)

What's wrong in the SQL above? Is there something I'm not escaping properly?

Upvotes: 0

Views: 3154

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89121

sp_executesql can only execute single TSQL batches. So you'll have to execute each batch seperately, eg:

DECLARE @sql1 NVARCHAR(MAX);
DECLARE @sql2 NVARCHAR(MAX);

SET @sql1 = N'IF object_id(N''[DW.myview]'', ''V'') IS NOT NULL
                DROP VIEW [DW.myview]';

set @sql2 = N'CREATE VIEW [DW.myview]  . . .'

EXECUTE sp_executesql @sql1;
EXECUTE sp_executesql @sql2;

Upvotes: 3

Related Questions