email when SQL/VPN fails

I have server connected over vpn to second server so it can read Sql data (required as second server is different company)
The vpn sometimes fails /times out
How can I monitor this and send email when vpn /sql fails ?
Thanks
rwallacejAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jason SchlueterIT ManagerCommented:
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.
0
Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
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.
0
rwallacejAuthor Commented:
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
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

rwallacejAuthor Commented:
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'.
0
Jason SchlueterIT ManagerCommented:
If you can't create a job then you probably can't set up the SMTP stuff (assuming that it's not set up already).

Without a job, my solution isn't going to work.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rwallacejAuthor Commented:
can't setup SMTP (I do have SMTP on local database, but can't setup on client database)
0
rwallacejAuthor Commented:
looks like might not be able to do this after all.....I'll maybe have to write app
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.