email when SQL/VPN fails

You could schedule a stored procedure to do a one-item query every few minutes.  In an IF block, use IF @@ERROR = ?.  Then you could have your SQL Server send you an e mail (exec sp_send_dbmail) if that is properly set up.

You'd have to look up the proper error code.  You can try select * from sysmessages.

You could have a scheduled job running every x minutes that pings the other SQL and if it fails then it should alert you.  Look at Splunk as it could do all kinds of wonderful things.

Jason,

Thank-you. Please help some more with example of the stored procedure / checking for fail.


Example query could be

SELECT TOP(1) DataDate AS 'Date' FROM dbo.tbPlatformAllocationMsg 121, Level 20, State 0,

There error received if I drop VPN connection is
"A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)"

Thank-you

also, I do not know how a job could be created, I do not have access to create jobs on the database, only read only access to tables

for example if I create in local SQL server instance this doesn't work, it gives

Invalid object name 'clientDatabaseName.dbo.tbPlatformAllocation'.

can't setup SMTP (I do have SMTP on local database, but can't setup on client database)

looks like might not be able to do this after all.....I'll maybe have to write app