Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 ошибка при использовании openrowset на локальном сервере  [new]
azmonsterr
Member

Откуда:
Сообщений: 43
Всем доброго дня.
Решил тут настроить мониторинг джобов:

1.создал табличку, заполнил
USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[jobs_monitoring](
    [job_id] [uniqueidentifier] NOT NULL,  --job_id джоба, берем из sysjobs
    [job_name] [nvarchar](128) NULL,   -- имя джоба, можно произвольное,     можно из sysjobs, влияет только на отображение в алерте
    [max_time] [int] NULL,  -- предельное время работы задания
    [operators_notification] [nvarchar](256) NULL,  --- e-mail-ы оповещения,
    [sms_mail] [nvarchar](256) NULL,  -- e-mail оповещения для смс
    [interrupt] [bit] NULL, -- прерывать задание или нет ( 1- прерывать, 0 - только оповещение)
 CONSTRAINT [PK_jobs_monitoring] PRIMARY KEY CLUSTERED
(
    [job_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


2. Написал запрос на создание процедуры, поскольку сервер 2012, в openrowset msdb.dbo.sp_help_jobactivity описал с результатами? а вместо msdb.dbo.sp_help_job создал msdb.dbo.sp_help_job_with_results , что, в принципе, одно и то же и является в обоих случаях обходом известной ошибки openrowset в sql2012 ( https://www.sql.ru/forum/1057196/problema-s-sp-help-job-na-sql2012-help-plz) , запустил, процедура успешно создалась.

+
USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[adminSP_Jobs_monitoring] @mail_profile nvarchar(50)
as
begin
    SET NOCOUNT ON;

declare @dt datetime  -- время на которое будет сбор данных, сделал как константу, т.к вдруг через секунду закончится работать джоб,
-- а рассыка произойдет, чтобы в письме отсылки отразить, на какой момент работал джоб долгое время.
set @dt=GETDATE()

select t2.job_id,t2.name,t2.originating_server,t1.start_execution_date,
        t1.dtdiff, t3.operators_notification,t3.sms_mail,t3.interrupt
into #res
 from
    (
    select job_id,job_name,start_execution_date,DATEDIFF(mi,start_execution_date,GETDATE())  as dtdiff
    from
    openrowset('SQLNCLI', 'Server=(local);Trusted_Connection=yes;', '
	
	DECLARE
	@session_id              int = NULL,              
    @job_id                  uniqueidentifier = NULL, 
    @job_name                sysname = NULL          
    
	
	EXEC msdb.dbo.sp_help_jobactivity 
	@session_id,
	@job_id,
	@job_name               
    
	WITH RESULT SETS
	(
	  (
	session_id              int,          
    job_id                  uniqueidentifier, 
    job_name                sysname,
    run_requested_date      datetime, 
    run_requested_source    sysname, 
    queued_date             datetime,
    start_execution_date    datetime, 
    last_executed_step_id   int, 
    last_exectued_step_date datetime, 
    stop_execution_date     datetime, 
	next_scheduled_run_date datetime, 
    job_history_id          int, 
    message                 nvarchar(1024), 
    run_status              int, 
    operator_id_emailed     int, 
    operator_id_netsent     int,
    operator_id_paged       int
	)
	)
	')
    where  job_id in (select job_id  from msdb.dbo.jobs_monitoring)) as t1
inner join (
    select job_id,name,originating_server --into #works_jobs
    from
    openrowset('SQLNCLI', 'Server=(local);Trusted_Connection=yes;', 'exec msdb.dbo.sp_help_job_with_results @execution_status =0')
    where  job_id in (select job_id  from msdb.dbo.jobs_monitoring)) as t2 on t1.job_id=t2.job_id
inner join msdb.dbo.jobs_monitoring t3 on t1.job_id=t3.job_id
where t1.dtdiff>t3.max_time

---отсылка курсором на основе результата запроса
declare @jname varchar(128)
declare @server varchar(50)
declare @dtstart datetime
declare @dtdiff int
declare @email varchar(100)
declare @body varchar(350)
declare @smsmail varchar(100)
declare @break int
declare @job_id uniqueidentifier
select * from #res  --это проверял результаты
declare cur cursor for select job_id,name,originating_server,start_execution_date,dtdiff,operators_notification,sms_mail,interrupt  from #res
open cur

fetch next from cur into @job_id,@jname,@server,@dtstart,@dtdiff,@email,@smsmail,@break

while @@FETCH_STATUS<>-1
begin
    --текст тела письма с форматирование даты в формат дд/мм/гггг чч/мм/сс
    set @body='Задание "'+@jname+'" на сервере '+@server +' начавшееся в '+convert(varchar(2),datepart(dd,@dtstart))+'/'+convert(varchar(2),datepart(mm,@dtstart))+'/'+convert(varchar(4),datepart(yyyy,@dtstart))+
    ' '+convert(varchar(4),datepart(hh,@dtstart))+':'+convert(varchar(4),datepart(n,@dtstart))+':'+convert(varchar(4),datepart(ss,@dtstart))
    + ' выполняется уже '+convert(varchar(10),@dtdiff)+' минут(ы). Время проверки: '+
convert(varchar(2),datepart(dd,@dt))+'/'+convert(varchar(2),datepart(mm,@dt))+'/'+convert(varchar(4),datepart(yyyy,@dt))+
    ' '+convert(varchar(4),datepart(hh,@dt))+':'+convert(varchar(4),datepart(n,@dt))+':'+convert(varchar(4),datepart(ss,@dt))

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @mail_profile,
         @recipients = @email,
         @subject = 'Долго выполняется задание: ',
         @body = @body
    -- остановка джоба,
    if @break =1
    begin
     exec msdb.dbo.sp_stop_job @job_id=@job_id

    end
    -----
    if (@smsmail is not  null  or @smsmail!='')
    begin
    set @body='Задание "'+@jname+'" на сервере '+@server +'выполняется более '+convert(varchar(10),@dtdiff)+' мин.'
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @mail_profile,
         @recipients = @smsmail,
         @subject = 'Долго выполняется задание: ',
         @body = @body
    --print 'модуль отсылки смс'
    end
   
    set @body=''
    fetch next from cur into @job_id,@jname,@server,@dtstart,@dtdiff,@email,@smsmail,@break
end
drop table #res
close cur
deallocate  cur
end -- конец процедуры


3. Создал джоб с использованием созданной на предыдущем шаге процедурой
exec dbo.adminSP_Jobs_monitoring dbmail ( dbmail - это почтовый профиль)


4. И Джоб вываливается с ошибкой
Executed as user: ***\********. Named Pipes Provider: Could not open a connection to SQL Server [1346]. 
[SQLSTATE 42000] (Error 1346)  OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Login timeout expired". 
[SQLSTATE 01000] (Error 7412)  OLE DB provider "SQLNCLI11" for linked server "(null)" 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.". 
[SQLSTATE 01000] (Error 7412).  The step failed.

Логин доменный, с правами сисадмина
Что за соединение у него не получается открыть если он обращается сам к себе в данном случае?

Сообщение было отредактировано: 13 фев 18, 12:38
13 фев 18, 12:23    [21187408]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
пвап
Guest
Посмотрите в SSMS \Server Objects\Linked Servers\Providers
есть ли там SQLNCLI, который у вас в adminSP_Jobs_monitoring
13 фев 18, 12:46    [21187495]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 641
azmonsterr
Решил тут настроить мониторинг джобов:

2. Написал запрос на создание процедуры
from
    openrowset('SQLNCLI', 'Server=(local);Trusted_Connection=yes;', '

4. И Джоб вываливается с ошибкой
Executed as user: ***\********. Named Pipes Provider: Could not open a connection to SQL Server [1346]. 
[SQLSTATE 42000] (Error 1346)  OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Login timeout expired". 
[SQLSTATE 01000] (Error 7412)  OLE DB provider "SQLNCLI11" for linked server "(null)" 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.". 
[SQLSTATE 01000] (Error 7412).  The step failed.

Что за соединение у него не получается открыть если он обращается сам к себе в данном случае?
13 фев 18, 12:50    [21187511]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
azmonsterr
Member

Откуда:
Сообщений: 43
Да, вот он в приложенном файле картинка

К сообщению приложен файл. Размер - 17Kb
13 фев 18, 12:51    [21187518]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
azmonsterr
Member

Откуда:
Сообщений: 43
Guf, ок, спс, счас попробую прописать напрямую имя инстанса
13 фев 18, 12:53    [21187526]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
azmonsterr
Member

Откуда:
Сообщений: 43
Прописал инстанс, ошибка та же
13 фев 18, 13:08    [21187575]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
azmonsterr,

автор
and if SQL Server is configured to allow remote connections.
13 фев 18, 13:12    [21187597]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
пвап
Guest
Исправьте SQLNCLI на SQLNCLI11
13 фев 18, 13:17    [21187621]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
пвап
Исправьте SQLNCLI на SQLNCLI11

зачем это???
13 фев 18, 13:19    [21187625]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
azmonsterr
Member

Откуда:
Сообщений: 43
TaPaK, да

К сообщению приложен файл. Размер - 17Kb
13 фев 18, 13:19    [21187630]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
azmonsterr,

инстанс именованый?
13 фев 18, 13:22    [21187639]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
azmonsterr
Member

Откуда:
Сообщений: 43
TaPaK, дефолт
13 фев 18, 13:27    [21187661]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
azmonsterr
TaPaK, дефолт

точно? @@SERVERNAME
13 фев 18, 13:38    [21187698]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
azmonsterr
Member

Откуда:
Сообщений: 43
TaPaK, ну да, select @@SERVERNAME выдает имя инстанса совпадающее с именем хоста)
13 фев 18, 13:42    [21187719]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
azmonsterr,

что вернет команда: telnet localhost 1433 ?
13 фев 18, 13:50    [21187743]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
azmonsterr
Member

Откуда:
Сообщений: 43
WarAnt, телнет по этому порту прекрасно проваливается в черный экран)
13 фев 18, 13:58    [21187780]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
azmonsterr,

Попробуйте с явным указанием протокола:
openrowset('SQLNCLI', 'Server=LPC:(local);Trusted_Connection=yes;', ...
13 фев 18, 14:18    [21187850]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
azmonsterr
Member

Откуда:
Сообщений: 43
invm,

Поменялось немного

Executed as user: ***\******. Shared Memory Provider: The Shared Memory dll used to connect to SQL Server 2000 was not found [126]. [SQLSTATE 42000] (Error 126) OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412) OLE DB provider "SQLNCLI11" for linked server "(null)" 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.". [SQLSTATE 01000] (Error 7412). The step failed.
13 фев 18, 14:37    [21187937]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36694
Какие протоколы и порты включены для сервера в Configuration Manager? О каких сервер отписался в логе, что их слушает?
13 фев 18, 14:40    [21187950]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
пвап
Guest
TaPaK
пвап
Исправьте SQLNCLI на SQLNCLI11

зачем это???

https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql

OPENROWSET - это OLEDB
13 фев 18, 14:55    [21188029]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
пвап
TaPaK
пропущено...

зачем это???

https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql

OPENROWSET - это OLEDB

вы не видите какой провайдер в ошибке?
13 фев 18, 15:00    [21188060]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
azmonsterr
TaPaK, ну да, select @@SERVERNAME выдает имя инстанса совпадающее с именем хоста)

я всё равно вам не верю :)
что вернёт
SELECT CONVERT(char(20), SERVERPROPERTY('InstanceName')) 
13 фев 18, 15:06    [21188089]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
azmonsterr
Member

Откуда:
Сообщений: 43
TaPaK, NULL..упс..не понял.. а как же селект @@сервернейм?
13 фев 18, 15:43    [21188249]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
azmonsterr
TaPaK, NULL..упс..не понял.. а как же селект @@сервернейм?

вопрос как вы его понимаете :)

Ну раз лезет в Named Pipes в конфигуграторе Enable?
13 фев 18, 15:50    [21188289]     Ответить | Цитировать Сообщить модератору
 Re: ошибка при использовании openrowset на локальном сервере  [new]
azmonsterr
Member

Откуда:
Сообщений: 43
TaPaK, а, сорри, там же инстанснейм, ну так и есть - он дефолтный, получается

Вот это
SELECT CONVERT(char(20), SERVERPROPERTY('servername')) ServerName,
CONVERT(char(20), SERVERPROPERTY('InstanceName')) instancename,
CONVERT(char(20), SERVERPROPERTY('MachineName'))
as HOSTNAME

Выдает результат:
ServerName instancename HOSTNAME
MYSERVER NULL MYSERVER
13 фев 18, 15:55    [21188313]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить