1

Topic: Error at usage openrowset on the local server

All kind day.
Decided to adjust here monitoring :
1. a label, filled

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 , we take from sysjobs
[job_name] [nvarchar] (128) NULL, - the name , is possible arbitrary, it is possible from sysjobs, influences only display in 
[max_time] [int] NULL, - a limiting operating time of the job
[operators_notification] [nvarchar] (256) NULL, - - e-e notifications;
[sms_mail] [nvarchar] (256) NULL, - an e-mail of the notification for 
[interrupt] [bit] NULL, - to interrupt the job or not (1 to interrupt, 0 - only the notification)
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. Wrote request about procedure creation as the server 2012, in openrowset msdb.dbo.sp_help_jobactivity described with results? And instead of msdb.dbo.sp_help_job created msdb.dbo.sp_help_job_with_results that, basically, same and is in both cases bypass of a known error openrowset in sql2012 (http://www.sql.ru/forum/1057196/problem … 2-help-plz), launched, procedure successfully formed.
[spoiler]

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 - time on which will be data acquisition, made as a constant, . suddenly in a second ends to work ;
-- And  happens, that in the sending letter to reflect, for what moment worked  long time.
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
---Sending by the cursor on the basis of result of request
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 is checked results
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
--The text of a body of the letter about date formatting in a format of dd/mm/gggg of chch/mm/ss
set @body =' "' + @jname + '" on the server ' + @server + ' begun in ' +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))
+ ' it is fulfilled already ' +convert (varchar (10) ,@dtdiff) + ' minutes (. Check time: ' +
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 = ' the job is long carried out: ';
@body = @body
-- Stop ;
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 + '" on the server ' + @server + ' it is fulfilled more ' +convert (varchar (10) ,@dtdiff) + ' minute'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @mail_profile;
@recipients = @smsmail;
@subject = ' the job is long carried out: ';
@body = @body
--print ' the sending unit '
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 - the procedure end

[/spoiler]
3. Created  with usage by the procedure created on the previous step

exec dbo.adminSP_Jobs_monitoring dbmail (dbmail is a mail profile)

4. And Dzhob falls out with an error

Executed as user: ***\********. Named Pipes Provider: Could not open a connection to A SQL Server [1346].
[SQLSTATE 42000] (Error 1346) AN OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Login timeout expired".
[SQLSTATE 01000] (Error 7412) AN OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to A SQL Server.
Server is not found or not accessible. Check if instance name is correct and if A SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. ".
[SQLSTATE 01000] (Error 7412). The step failed.

Login domain, with the rights of the system administrator
What for connection at it it is impossible to open if he addresses to itself(himself) in this case?

2

Re: Error at usage openrowset on the local server

azmonsterr wrote:

Decided to adjust here monitoring :
2. Wrote request about procedure creation

from
openrowset (' SQLNCLI ', ' Server =>>> (local) <<<; Trusted_Connection=yes; ','

4. And Dzhob falls out with an error

Executed as user: ***\********. Named Pipes Provider: Could not open a connection to A SQL Server [1346].
[SQLSTATE 42000] (Error 1346) AN OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Login timeout expired".
[SQLSTATE 01000] (Error 7412) AN OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to A SQL Server.
Server is not found or not accessible.>>> Check if instance name is correct <<<and if A SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. ".
[SQLSTATE 01000] (Error 7412). The step failed.

What for connection at it it is impossible to open if he addresses to itself(himself) in this case?

3

Re: Error at usage openrowset on the local server

Yes, here it in the enclosed file a picture

4

Re: Error at usage openrowset on the local server

Guf, , ,  I will try to register directly a name

5

Re: Error at usage openrowset on the local server

Registered , the same error

6

Re: Error at usage openrowset on the local server

azmonsterr;

wrote:

and if a SQL Server is configured to allow remote connections.

7

Re: Error at usage openrowset on the local server

wrote:

Correct SQLNCLI on SQLNCLI11

What for it???

8

Re: Error at usage openrowset on the local server

TaPaK, yes

9

Re: Error at usage openrowset on the local server

azmonsterr;
?

10

Re: Error at usage openrowset on the local server

TaPaK, a default

11

Re: Error at usage openrowset on the local server

azmonsterr wrote:

TaPaK, a default

Precisely? @@ SERVERNAME

12

Re: Error at usage openrowset on the local server

TaPaK, well, select @@ SERVERNAME produces a name  coinciding with host name)

13

Re: Error at usage openrowset on the local server

azmonsterr;
That the command returns: telnet localhost 1433?

14

Re: Error at usage openrowset on the local server

WarAnt, the telnet on this port perfectly fails in the black screen)

15

Re: Error at usage openrowset on the local server

azmonsterr;
Try with explicit instructions of the protocol:

openrowset (' SQLNCLI ', ' Server =>>> LPC: <<<(local); Trusted_Connection=yes; '...

16

Re: Error at usage openrowset on the local server

invm;
Exchanged it is a little
Executed as user: ***\******. Shared Memory Provider: The Shared Memory dll used to connect to A SQL Server 2000 was not found [126]. [SQLSTATE 42000] (Error 126) AN OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412) AN OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to A SQL Server. Server is not found or not accessible. Check if instance name is correct and if A SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". [SQLSTATE 01000] (Error 7412). The step failed.

17

Re: Error at usage openrowset on the local server

What protocols and ports are included for the server in Configuration Manager? About what the server unsubscribed in a broad gull, what to them listens?

18

Re: Error at usage openrowset on the local server

wrote:

it is passed...
https://docs.microsoft.com/en-us/sql/t- … ansact-sql
OPENROWSET is OLEDB

You do not see what provider in an error?

19

Re: Error at usage openrowset on the local server

azmonsterr wrote:

TaPaK, well, select @@ SERVERNAME produces a name  coinciding with host name)

I all the same do not trust you smile
That returns

SELECT CONVERT (char (20), SERVERPROPERTY (' InstanceName '))

20

Re: Error at usage openrowset on the local server

TaPaK, NULL. . Did not understand. And how  @@ ?

21

Re: Error at usage openrowset on the local server

azmonsterr wrote:

TaPaK, NULL. . Did not understand. And how  @@ ?

As you it understand a question smile
Well time climbs in Named Pipes in  Enable?

22

Re: Error at usage openrowset on the local server

TaPaK, and, , in the same place , well indeed - it , turns out
Here it
SELECT CONVERT (char (20), SERVERPROPERTY (' servername ')) ServerName;
CONVERT (char (20), SERVERPROPERTY (' InstanceName ')) instancename;
CONVERT (char (20), SERVERPROPERTY (' MachineName '))
as HOSTNAME
Produces result:
ServerName instancename HOSTNAME
MYSERVER NULL MYSERVER

23

Re: Error at usage openrowset on the local server

azmonsterr wrote:

TaPaK, NULL. . Did not understand. And how  @@ ?

null =  a copy;
What you still expected to see?
---
Better the version announce;
And that the client from 2012, and writes that the server 2000

24

Re: Error at usage openrowset on the local server

Yasha123, Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)
Oct 20 2015 3:36:27 PM
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600:) (Hypervisor)

25

Re: Error at usage openrowset on the local server

Gavrilenko Sergey Alekseevich, all protocols are included, the telnet fulfills