Problems with GO in string in script

greyforrest commented 1 year ago

So I tried this in Powershell 7.3.6 and Powershell 5.1, using dbatools 2.1.0.

I'm exporting the SqlAgentJobs with dbatools (Export-DbaScript). We have jobs that include the word GO in the step-command. Now when i'm trying to read those files back into a sql, using Invoke-DbaTools, I get an error because of a unclosed quotation mark. I tested a bit and found out that the GOs we have in our steps are read as normal GOs, hence the unclosed quotation marks.

Quick example:

Invoke-DbaQuery -SqlInstance $sqlInstance -Query "EXEC procedure_to_add_job @job_name=N'Job', 
@category_name=N'category', 
@anothersetting, 
@athirdsetting, 
@command=N'SELECT * FROM TableOne; 
GO;
SELECT * FROM TableTwo;'"

The error I get is always the same: "Unclosed quotation mark near: 'SELECT * FROM TableOne; '.

I tried making a connection with "Connect-DbaInstance" and using another batchseparator (specifically I tried NEXT, BLA and ///), I also tried to export the script using another batchseparator (///) but it still threw the same error. I tried changing the separator in SSMS to /// as well, still got the same error.

$server = Connect-DbaInstance -SqlInstance $sqlInstance -BatchSeparator "NEXT"
Invoke-DbaQuery -SqlInstance $server -Query $queryfromabove

When I put the exported file into SSMS and run it there, I get no error at all and all the jobs and steps are created without any problems.

We used Invoke-Sqlcmd to import the jobs before, exporting the jobs with Export-DbaScript, and we never had a problem until I refactored the script and used Invoke-DbaQuery. I can't use Invoke-DbaQuery in some scripts and Invoke-Sqlcmd in others as they both use the same assembly and I cant have them both loaded/installed at once.

Is there any other way to do this without using New-DbaAgentJobStep? We have hundreds of jobs and probably about a thousand steps, so its not like I could just write a few New-DbaAgentJobStep-Statements. Or is there any way to loop through the jobs and steps in a sql script file and generate New-DbaAgentJobStep-Statements?

niphlod commented 1 year ago

The problem is that you're trying to execute a script rather than a query (and a rather complex one, as it needs to discern nested commands, and "GO" doesn't mean anything in a query, other than "separate this command"). Invoke-DbaQuery isn't going to parse your script completely and it's totally not equivalent, feature-wise, to invoke-sqlcmd, which supports nested GOs, nested comments, :setvar parameters, and so on. Though, invoke-sqlcmd doesn't work with parameters as in "user supplied ones", while invoke-dbaquery does.

That being said, use $server.Query(statement) or $db.Query(statement) that support the "full might" of SQL parsers, just as SSMS and Invoke-Sqlcmd do.

greyforrest commented 1 year ago

Thanks! Is there a documentation on Query() and other methods available on the $server/$db objects or any other examples? I just get the error "Execute with results failed for Database msdb".

niphlod commented 1 year ago

mmhh, try ExecuteNonQuery since I think your script doesn't return results. I don't think those are documented (except if you look at the help from Connect-DbaInstance) as they're not managed by dbatools but rather "resurfaced" from SMO.

greyforrest commented 1 year ago

Yeah I thought about using ExecuteNonQuery as well (saw it in the Connect-DbaInstance documentation) but I get an error there as well, "ExecuteNonQuery failed for Database 'msdb'".

Just did some testing and simple commands like creating a table with ExecuteNonQuery() and doing a select with Query() work but as soon as I add a GO (Example command from above with "Select from OneTable; GO; Select from AnotherTable;") to the Query() it wont work anymore. Same with ExecuteNonQuery().

niphlod commented 1 year ago

hum . wasn't your error related to executing something like

EXEC procedure_to_add_job @job_name=N'Job', 
@category_name=N'category', 
@anothersetting, 
@athirdsetting, 
@command=N'SELECT * FROM TableOne; 
GO;
SELECT * FROM TableTwo;'"

that shouldn't be a problem for .Query or .ExecuteNonQuery as the "GO" there is quoted.

niphlod commented 1 year ago

BTW, just for fun, did you try $server.ConnectionContext.ExecuteNonQuery() ?

greyforrest commented 1 year ago

Yeah my problem was (and still is) that I cannot execute the script created by exporting jobs with dbatools because the quoted GOs aren't read as quoted GOs. When I tried to execute that script with .Query() and .ExecuteNonQuery(), it still threw an error.

When I try to do it with $server.ConnectionContext.ExecuteNonQuery() I get another error, telling me that "an exception occurred while execution a Transact-SQL statement or batch".

niphlod commented 1 year ago

I remember that SMO supported GOs . can you by any chance paste the script you're trying to execute ?

greyforrest commented 1 year ago

I changed the names and the login because of confidentiality and stuff but here's the script. I can assure you that every db that would be in the file exists, its generated by dbatools after all.

USE [msdb]

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Job_forSomething', 
        @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'Category', 
        @owner_login_name=N'Company\ownerlogin', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Primary Node Check', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'DECLARE @role as int

SELECT @role = hadrState.role
FROM sys.dm_hadr_database_replica_states hadrDBState
    join sys.availability_replicas AG on hadrDBState.replica_id = AG.replica_id
    join sys.dm_hadr_availability_replica_states hadrState on hadrState.replica_id = AG.replica_id 
    join sys.databases DBs on hadrDBState.database_id = DBs.database_id
WHERE AG.replica_server_name = @@SERVERNAME 
    AND DBs.name = DB_NAME()

IF  @@ROWCOUNT = 1 AND @role <> 1 -- 1 = DBs that are PRIMARY on actual replica Server
    EXEC msdb.dbo.sp_stop_job JobForSomething', 
        @database_name=N'DbForJob', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'JobForSomething Prepare', 
        @step_id=2, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'/****************************************************************
  Datasource - NextDb -  Prepare
****************************************************************/

DECLARE @p_return_msg VARCHAR(512);
DECLARE @p_status INTEGER;
DECLARE @p_LogID INTEGER = 0;
DECLARE @startDate DATETIME2;
DECLARE @endDate DATETIME2;
SET @startDate = GETDATE();
SET @endDate = GETDATE();

SET @p_return_msg = FORMAT(@startDate,''yyyy-MM-dd HH:mm:ss'') + ''   starting Task ...'';
   RAISERROR(@p_return_msg, 10, 1) WITH NOWAIT;

 EXEC [SomeotherDb].[dbo].[update_data_a0e0671d]
      @p_sequence = 1,
      @p_job_name = ''DataSource~NextDb~Prepare'', 
      @p_task_name = ''Loading~Task'', 
      @p_job_id = 0, 
      @p_task_id = 0, 
      @p_return_msg = @p_return_msg OUTPUT, 
      @p_status = @p_status OUTPUT; 
SET @endDate = GETDATE();

 IF (@p_status <> 1) 
      BEGIN
        SET @p_return_msg = FORMAT(GETDATE(),''yyyy-MM-dd HH:mm:ss'') + ''  '' +  @p_return_msg;
        RAISERROR(@p_return_msg, 16, 1) WITH NOWAIT;
      END
 ELSE
      BEGIN
        SET @p_return_msg = FORMAT(GETDATE(), ''yyyy-MM-dd HH:mm:ss'') + ''   Task complete!'';
        RAISERROR(@p_return_msg, 10, 1) WITH NOWAIT;
      END 

 GO
 WAITFOR DELAY ''00:00:05'';
 GO 

DECLARE @p_return_msg VARCHAR(512);
DECLARE @p_status INTEGER;
DECLARE @p_LogID INTEGER = 0;
DECLARE @startDate DATETIME2;
DECLARE @endDate DATETIME2;
SET @startDate = GETDATE();
SET @endDate = GETDATE();

SET @p_return_msg = FORMAT(@startDate,''yyyy-MM-dd HH:mm:ss'') + ''   starting next Task ...'';
   RAISERROR(@p_return_msg, 10, 1) WITH NOWAIT;

 EXEC [SomeotherDb].[dbo].[update_data_4a51e5a7]
      @p_sequence = 2,
      @p_job_name = ''DataSource~NextDb~Prepare'', 
      @p_task_name = ''Loading~nextTask'', 
      @p_job_id = 0, 
      @p_task_id = 0, 
      @p_return_msg = @p_return_msg OUTPUT, 
      @p_status = @p_status OUTPUT; 
SET @endDate = GETDATE();

 IF (@p_status <> 1) 
      BEGIN
        SET @p_return_msg = FORMAT(GETDATE(),''yyyy-MM-dd HH:mm:ss'') + ''  '' +  @p_return_msg;
        RAISERROR(@p_return_msg, 16, 1) WITH NOWAIT;
      END
 ELSE
      BEGIN
        SET @p_return_msg = FORMAT(GETDATE(), ''yyyy-MM-dd HH:mm:ss'') + ''   next Task complete!'';
        RAISERROR(@p_return_msg, 10, 1) WITH NOWAIT;
      END 

 GO
 WAITFOR DELAY ''00:00:05'';
 GO 

DECLARE @p_return_msg VARCHAR(512);
DECLARE @p_status INTEGER;
DECLARE @p_LogID INTEGER = 0;
DECLARE @startDate DATETIME2;
DECLARE @endDate DATETIME2;
SET @startDate = GETDATE();
SET @endDate = GETDATE();

SET @p_return_msg = FORMAT(@startDate,''yyyy-MM-dd HH:mm:ss'') + ''   starting another Task ...'';
   RAISERROR(@p_return_msg, 10, 1) WITH NOWAIT;

 EXEC [SomeotherDb].[dbo].[update_data_f8a9188b]
      @p_sequence = 3,
      @p_job_name = ''DataSource~NextDb~Prepare'', 
      @p_task_name = ''Loading~anotherTask'', 
      @p_job_id = 0, 
      @p_task_id = 0, 
      @p_return_msg = @p_return_msg OUTPUT, 
      @p_status = @p_status OUTPUT; 
SET @endDate = GETDATE();

 IF (@p_status <> 1) 
      BEGIN
        SET @p_return_msg = FORMAT(GETDATE(),''yyyy-MM-dd HH:mm:ss'') + ''  '' +  @p_return_msg;
        RAISERROR(@p_return_msg, 16, 1) WITH NOWAIT;
      END
 ELSE
      BEGIN
        SET @p_return_msg = FORMAT(GETDATE(), ''yyyy-MM-dd HH:mm:ss'') + ''   another Task complete!'';
        RAISERROR(@p_return_msg, 10, 1) WITH NOWAIT;
      END 

 GO
 WAITFOR DELAY ''00:00:05'';
 GO 

GO
', 
        @database_name=N'SomeotherDb', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Trigger next Job', 
        @step_id=3, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'EXEC dbo.sp_start_job N''nextJob''', 
        @database_name=N'msdb', 
        @flags=0
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

niphlod commented 1 year ago

are you missing a declare for some reason ? even in SSMS, this doesn't work.

that being fixed, I don't remember if "gotos" are fine for SMO (such as QuitWithRollback:)

Also, for your own sanity (unrelated to this issue):

use stored procedures, not scripts stored in sql steps ^_^
to check for primaries, there's sys.fn_hadr_is_primary_replica (@dbname) = 1

greyforrest commented 1 year ago

Not sure about the missing declare, for me it worked in SSMS but I will try to fix it and see if it would work then. Though I believe the gotos would probably be it then. I'll have to experiment a bit and talk to the team about possible different ways to do it.

Thanks for the tips and the help in general!

niphlod commented 1 year ago

let's put it this way. If it doesn't work in SSMS or invoke-sqlcmd, it won't work in dbatools (which, just exposes what SMO has). Once you get to have it working in SSMS, we can find out what is the specialty SMO doesn't like.

greyforrest commented 1 year ago

I have a declare at the top, I guess I didn't select all code when I pasted the script.

It should be this:

USE [msdb]
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Job_forSomething', 
        @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'Category', 
        @owner_login_name=N'Company\ownerlogin', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Primary Node Check', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'DECLARE @role as int

SELECT @role = hadrState.role
FROM sys.dm_hadr_database_replica_states hadrDBState
    join sys.availability_replicas AG on hadrDBState.replica_id = AG.replica_id
    join sys.dm_hadr_availability_replica_states hadrState on hadrState.replica_id = AG.replica_id 
    join sys.databases DBs on hadrDBState.database_id = DBs.database_id
WHERE AG.replica_server_name = @@SERVERNAME 
    AND DBs.name = DB_NAME()

IF  @@ROWCOUNT = 1 AND @role <> 1 -- 1 = DBs that are PRIMARY on actual replica Server
    EXEC msdb.dbo.sp_stop_job JobForSomething', 
        @database_name=N'DbForJob', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'JobForSomething Prepare', 
        @step_id=2, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'/****************************************************************
  Datasource - NextDb -  Prepare
****************************************************************/

DECLARE @p_return_msg VARCHAR(512);
DECLARE @p_status INTEGER;
DECLARE @p_LogID INTEGER = 0;
DECLARE @startDate DATETIME2;
DECLARE @endDate DATETIME2;
SET @startDate = GETDATE();
SET @endDate = GETDATE();

SET @p_return_msg = FORMAT(@startDate,''yyyy-MM-dd HH:mm:ss'') + ''   starting Task ...'';
   RAISERROR(@p_return_msg, 10, 1) WITH NOWAIT;

 EXEC [SomeotherDb].[dbo].[update_data_a0e0671d]
      @p_sequence = 1,
      @p_job_name = ''DataSource~NextDb~Prepare'', 
      @p_task_name = ''Loading~Task'', 
      @p_job_id = 0, 
      @p_task_id = 0, 
      @p_return_msg = @p_return_msg OUTPUT, 
      @p_status = @p_status OUTPUT; 
SET @endDate = GETDATE();

 IF (@p_status <> 1) 
      BEGIN
        SET @p_return_msg = FORMAT(GETDATE(),''yyyy-MM-dd HH:mm:ss'') + ''  '' +  @p_return_msg;
        RAISERROR(@p_return_msg, 16, 1) WITH NOWAIT;
      END
 ELSE
      BEGIN
        SET @p_return_msg = FORMAT(GETDATE(), ''yyyy-MM-dd HH:mm:ss'') + ''   Task complete!'';
        RAISERROR(@p_return_msg, 10, 1) WITH NOWAIT;
      END 

 GO
 WAITFOR DELAY ''00:00:05'';
 GO 

DECLARE @p_return_msg VARCHAR(512);
DECLARE @p_status INTEGER;
DECLARE @p_LogID INTEGER = 0;
DECLARE @startDate DATETIME2;
DECLARE @endDate DATETIME2;
SET @startDate = GETDATE();
SET @endDate = GETDATE();

SET @p_return_msg = FORMAT(@startDate,''yyyy-MM-dd HH:mm:ss'') + ''   starting next Task ...'';
   RAISERROR(@p_return_msg, 10, 1) WITH NOWAIT;

 EXEC [SomeotherDb].[dbo].[update_data_4a51e5a7]
      @p_sequence = 2,
      @p_job_name = ''DataSource~NextDb~Prepare'', 
      @p_task_name = ''Loading~nextTask'', 
      @p_job_id = 0, 
      @p_task_id = 0, 
      @p_return_msg = @p_return_msg OUTPUT, 
      @p_status = @p_status OUTPUT; 
SET @endDate = GETDATE();

 IF (@p_status <> 1) 
      BEGIN
        SET @p_return_msg = FORMAT(GETDATE(),''yyyy-MM-dd HH:mm:ss'') + ''  '' +  @p_return_msg;
        RAISERROR(@p_return_msg, 16, 1) WITH NOWAIT;
      END
 ELSE
      BEGIN
        SET @p_return_msg = FORMAT(GETDATE(), ''yyyy-MM-dd HH:mm:ss'') + ''   next Task complete!'';
        RAISERROR(@p_return_msg, 10, 1) WITH NOWAIT;
      END 

 GO
 WAITFOR DELAY ''00:00:05'';
 GO 

DECLARE @p_return_msg VARCHAR(512);
DECLARE @p_status INTEGER;
DECLARE @p_LogID INTEGER = 0;
DECLARE @startDate DATETIME2;
DECLARE @endDate DATETIME2;
SET @startDate = GETDATE();
SET @endDate = GETDATE();

SET @p_return_msg = FORMAT(@startDate,''yyyy-MM-dd HH:mm:ss'') + ''   starting another Task ...'';
   RAISERROR(@p_return_msg, 10, 1) WITH NOWAIT;

 EXEC [SomeotherDb].[dbo].[update_data_f8a9188b]
      @p_sequence = 3,
      @p_job_name = ''DataSource~NextDb~Prepare'', 
      @p_task_name = ''Loading~anotherTask'', 
      @p_job_id = 0, 
      @p_task_id = 0, 
      @p_return_msg = @p_return_msg OUTPUT, 
      @p_status = @p_status OUTPUT; 
SET @endDate = GETDATE();

 IF (@p_status <> 1) 
      BEGIN
        SET @p_return_msg = FORMAT(GETDATE(),''yyyy-MM-dd HH:mm:ss'') + ''  '' +  @p_return_msg;
        RAISERROR(@p_return_msg, 16, 1) WITH NOWAIT;
      END
 ELSE
      BEGIN
        SET @p_return_msg = FORMAT(GETDATE(), ''yyyy-MM-dd HH:mm:ss'') + ''   another Task complete!'';
        RAISERROR(@p_return_msg, 10, 1) WITH NOWAIT;
      END 

 GO
 WAITFOR DELAY ''00:00:05'';
 GO 

GO
', 
        @database_name=N'SomeotherDb', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Trigger next Job', 
        @step_id=3, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'EXEC dbo.sp_start_job N''nextJob''', 
        @database_name=N'msdb', 
        @flags=0
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

GO

dataplat / dbatools

Problems with GO in string in script #9120