if exists (select * from sysobjects where id = object_id(N'[dbo].[usp_checksqlserver]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[usp_checksqlserver]

go



create procedure [dbo].[usp_checksqlserver]

	@server_name	varchar(100)='(local)',

	@sql_login	varchar(100)=NULL,

	@sql_password	varchar(100)=NULL,

	@login_timeout	int=60



AS

declare @retcode int, @sqlobj int

declare @err_source varchar(8000), @err_desc varchar(8000)



exec @retcode = sp_OACreate 'SQLDMO.SqlServer', @sqlobj OUT

IF @retcode <> 0 

BEGIN

	PRINT 'sp_OACreate failed. Object - SQLDMO.SqlServer'

	GOTO Error_Handler

END



IF @login_timeout < -1 SET @login_timeout = 60

exec @retcode = sp_OASetProperty @sqlobj, 'LoginTimeout', @login_timeout

if @retcode<>0 

BEGIN

	PRINT 'sp_OASetProperty failed. Property - LoginTimeout'

	GOTO Error_Handler

END





/*for sql authentication*/

IF @sql_login IS NOT NULL

	exec @retcode = sp_OASetProperty @sqlobj, 'LoginSecure', 'False'

ELSE

/*for windows authentication*/

	exec @retcode = sp_OASetProperty @sqlobj, 'LoginSecure', 'True'



if @retcode<>0 

BEGIN

	PRINT 'sp_OASetProperty failed. Property - LoginSecure'

	GOTO Error_Handler

END





/*for sql authentication*/

IF @sql_login IS NOT NULL

	EXEC @retcode = sp_OAMethod @sqlobj, 'Connect', NULL, @server_name, @sql_login, @sql_password

ELSE

/*for windows authentication*/

	EXEC @retcode = sp_OAMethod @sqlobj, 'Connect', NULL, @server_name



if @retcode<>0 

BEGIN

	PRINT 'sp_OAMethod failed. Method - Connect'

	GOTO Error_Handler

END



exec sp_OADestroy @sqlobj

print 'Successfully connected to server ' + @server_name

return 0



Error_Handler: 

PRINT '*** ERROR ***'

EXEC @retcode = sp_OAGetErrorInfo @sqlobj, @err_source OUT, @err_desc OUT

exec sp_OADestroy @sqlobj



IF @retcode = 0

BEGIN

    print 'Error Source: ' + @err_source

    print 'Error Description: ' + @err_desc

end

else

BEGIN

    PRINT 'sp_OAGetErrorInfo failed.'

    return -2

END



return -1

go

sp_OAMethod failed. Method - Connect
*** ERROR ***
Error Source: ODSOLE Extended Procedure
Error Description: [Microsoft][ODBC SQL Server Driver][DBNETLIB]Указанный SQL-сервер не найден.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).