0

Starting the same job using SSMS on the machine directly always works. Can't figure out why. Is there some firewall rule I need to allow?

It works via SSMS when logged into the box directly. It does not work via SSMS when the SSMS is on a separate box outside the AWS sub-network. We have a private line, but this problem has been happening since even before we activated the private line and just had a tunnel.

Pretty annoying the help links forward to ads to buy more Microsoft stuff.

TITLE: Microsoft.SqlServer.Smo

Start failed for Job 'XXXXXXXXXXXXXXXXXX'.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17177.0+((SSMS_Rel).170803-0429)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Start+Job&LinkId=20476

------------------------------ ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.) (Microsoft SQL Server, Error: 121)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=121&LinkId=20476


The semaphore timeout period has expired

------------------------------ BUTTONS:

OK

Update based on feedback from a Microsoft employee

Here is a reproducible .sql script file:

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'XXXXXXXXXXXXXXXXXX', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=2, 
        @notify_level_page=2, 
        @delete_level=0, 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'XXXXXXXXXXXXXXXXXX', @server_name = N'DEVSQL1'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'XXXXXXXXXXXXXXXXXX', 
@step_name=N'Hello, World', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_fail_action=2, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'print ''Hello, World''', 
        @database_name=N'master', 
        @flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'XXXXXXXXXXXXXXXXXX', 
        @enabled=1, 
        @start_step_id=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=2, 
        @notify_level_page=2, 
        @delete_level=0, 
        @description=N'', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', 
        @notify_email_operator_name=N'', 
        @notify_page_operator_name=N''
GO

EXEC msdb.dbo.sp_start_job @job_name = 'XXXXXXXXXXXXXXXXXX'
GO
  • Can you go into a little more detail on what you're doing, when it works, and when it doesn't? – John Eisbrener Oct 19 '17 at 15:18
  • For the failure case, remotely, are you connecting via SSMS, or invoking the Job another way? – Robert Gannon Oct 19 '17 at 15:33
  • It works via SSMS when logged into the box directly. It does not work via SSMS when the SSMS is on a separate box outside the AWS sub-network. We have a private line, but this problem has been happening since even before we activated the private line and just had a tunnel. – John Zabroski Oct 19 '17 at 16:41
  • The error is about receiving data from the server... sooooo... have you tried starting a job from TSQL through something like SQLCMD or SSMS query window and see if it works? – Sean Gallardy Oct 19 '17 at 23:41
  • EXEC msdb.dbo.sp_start_job @job_name = 'XXXXXXXXXXXXXXXXXX' fails remotely when DB is in AWS; succeeds locally. Error is Msg 121, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.) Since you're a MSFT employee - I am sure you are aware how difficult it is for third parties to debug Tabular Data Stream protocol errors! – John Zabroski Oct 20 '17 at 23:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.