December 3, 2010 at 8:41 pm
I am trying to find out the SQL Server Availability and DB Availability through SQL job from another server ,so the requirement is the
1)job code/T-SQL would try to connect to remote SQL Server and if it cant then i will insert nonavailability row in one of my local table where from i am running this job
2)job code/T-SQL would try to connect to db of above remote sql server in case sql server is available and then i will insert nonavailability row if DB is not available
is there any way i can achieve the same in sql code , i have already job running for availability but not covering above scenerions ,and in case if SQL not available full job fails saying could not connect to sql ,so possibly we need to get the error code and insert the row for non availability?
Thanks for looking!!
December 4, 2010 at 6:03 pm
Try Catch ,@@Error could not able to go to my insert statement when it was not able to connect to remote sql server,it just throw below and sql job fails
OLE DB provider "SQLNCLI10" for linked server 'XXX' returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server 'XXX'returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 2, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [2].
Is there any way i can catch sql unavailability through t-sql?
December 6, 2010 at 12:30 am
Good question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply