Invoke-DbaQuery -File (Incorrect syntax creating SQL Agent job)

[Gincules]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

Exception             : System.Exception: Unclosed quotation mark after the character string 'IF
                        OBJECTPROPERTY(OBJECT_ID(N'sp_MyGreatProcedure'), 'IsProcedure') = 1
                            DROP PROCEDURE sp_MyGreatProcedure;
                        '.
                        Incorrect syntax near 'IF OBJECTPROPERTY(OBJECT_ID(N'sp_MyGreatProcedure'), 'IsProcedure') = 1
                            DROP PROCEDURE sp_MyGreatProcedure;
                        '. ---> Microsoft.Data.SqlClient.SqlException: Unclosed quotation mark after the character
                        string 'IF OBJECTPROPERTY(OBJECT_ID(N'sp_MyGreatProcedure'), 'IsProcedure') = 1
                            DROP PROCEDURE sp_MyGreatProcedure;
                        '.
                        Incorrect syntax near 'IF OBJECTPROPERTY(OBJECT_ID(N'sp_MyGreatProcedure'), 'IsProcedure') = 1
                            DROP PROCEDURE sp_MyGreatProcedure;
                        '.
                           at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
                        breakConnection, Action`1 wrapCloseInAction)
                           at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
                        stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
                           at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand
                        cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler,
                        TdsParserStateObject stateObj, Boolean& dataReady)
                           at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
                           at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
                           at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior
                        runBehavior, String resetOptionsString, Boolean isInternal, Boolean
                        forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
                           at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior,
                        RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task,
                        Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean
                        describeParameterEncryptionRequest)
                           at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,
                        RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1
                        completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
                           at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,
                        RunBehavior runBehavior, Boolean returnStream, String method)
                           at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String
                        method)
                           at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables,
                        Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
                        behavior)
                           at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32
                        maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
                           at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
                           at CallSite.Target(Closure , CallSite , Object , Object )
                           --- End of inner exception stack trace ---

ErrorDetails          : Unclosed quotation mark after the character string 'IF
                        OBJECTPROPERTY(OBJECT_ID(N'sp_MyGreatProcedure'), 'IsProcedure') = 1
                            DROP PROCEDURE sp_MyGreatProcedure;
                        '.
                        Incorrect syntax near 'IF OBJECTPROPERTY(OBJECT_ID(N'sp_MyGreatProcedure'), 'IsProcedure') = 1
                            DROP PROCEDURE sp_MyGreatProcedure;
                        '.

Steps to Reproduce

command executed:

Invoke-DbaQuery -SqlInstance MyComputer.domain.net -File .\Agent_Jobs.sql

Agent_Jobs.sql

USE [msdb]
GO

/****** Object:  Job [dbatools_test]    Script Date: 29.03.2023 14:53:23 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Engine Tuning Advisor]    Script Date: 29.03.2023 14:53:23 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Engine Tuning Advisor' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Engine Tuning Advisor'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'dbatools_test', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'Database Engine Tuning Advisor', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [step I want to create]    Script Date: 29.03.2023 14:53:23 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step I want to create', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=1, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'IF OBJECTPROPERTY(OBJECT_ID(N''sp_MyGreatProcedure''), ''IsProcedure'') = 1 
    DROP PROCEDURE sp_MyGreatProcedure;
GO

CREATE PROCEDURE dbo.sp_MyGreatProcedure
    (
        @Counter NVARCHAR(128) = N''%''
    )
AS 
    BEGIN;
        SELECT *
        FROM [dbo].[PerformanceMonitorData]
        WHERE [Counter] LIKE @Counter
        ORDER BY [Counter], [CaptureDate]
    END;
GO', 
        @database_name=N'AdminDB', 
        @flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Please confirm that you are running the most recent version of dbatools

dbatools version: 1.1.145: syntax error (above)

SSMS: Commands completed successfully. Completion time: 2023-03-29T13:59:50.4673509+02:00

PowerShell Module SqlServer: Invoke-Sqlcmd -ServerInstance MyComputer.domain.net -Database msdb -InputFile .\Agent_Jobs.sql -Verbose OK - created

Other details or mentions

Invoke-DbaQuery -File does not work trying to create the above SQL Agent job from file (reduced to minimum I receive the error).

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

PSVersion: 5.1.20348.1366 PSEdition: Desktop PSCompatibleVersions: {1.0, 2.0, 3.0, 4.0...} BuildVersion: 10.0.20348.1366 CLRVersion: 4.0.30319.42000 WSManStackVersion: 3.0 PSRemotingProtocolVersion: 2.3 SerializationVersion: 1.1.0.1

SQL Server Edition and Build number

Microsoft SQL Server 2022 (RTM-CU2) (KB5023127) - 16.0.4015.1 (X64) Feb 27 2023 15:40:01 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2022 Standard 10.0 (Build 20348: ) (Hypervisor)

.NET Framework Version

Client: 4.8.04161 Full: 4.8.04161 Client: 4.0.0.0

[niphlod]

that's not a Query, it's a batch, and with savepoints also. Invoke-DbaQuery is not the right tool for the job.

[Gincules]

sorry I forgot to mention that this only fails using wildcard '%' for the @command part.

@command=N'IF OBJECTPROPERTY(OBJECT_ID(N''sp_MyGreatProcedure''), ''IsProcedure'') = 1 
    DROP PROCEDURE sp_MyGreatProcedure;
GO

CREATE PROCEDURE dbo.sp_MyGreatProcedure
    (
        @Counter NVARCHAR(128) = N''%''
    )
AS 
    BEGIN;
        SELECT *
        FROM [dbo].[PerformanceMonitorData]
        WHERE [Counter] LIKE @Counter
        ORDER BY [Counter], [CaptureDate]
    END;
GO', 

for other queries (example: @command=N'SELECT @@VERSION', or @command=N'EXEC master.sys.sp_MSforeachdb @command; GO' ) it's executed without any issue.

[mwgevans115]

I had the same issue running a create agent job - If the step command is split over multiple lines the query fails. With

 @command=N'exec usp_SweepStockReservations
Go
', 

I get the following error.

WARNING: [10:50:55][Invoke-DbaQuery] [mnp-kammac-sand] Failed during execution | Unclosed quotation mark after the character string 'exec usp_SweepStockReservationstockReservations
'.
Incorrect syntax near 'exec usp_SweepStockReservations
'.

[mwgevans115]

that's not a Query, it's a batch, and with savepoints also. Invoke-DbaQuery is not the right tool for the job.

What is the right tool for the job?

[wsmelton]

If you are wanting to execute a script file as-is in your example use sqlserver/Invoke-SqlCmd module. dbatools/Invoke-DbaQuery will not support running files with that syntax in them.