April 16, 2018 at 9:00 am
I am trying to create MSX ans TSX relationship
MSX is SQL1
TSX is
SQL2 and SQL3
created a job in SQL1 that will run on SQL2 and SQL3
job step is :
create table #sqltest_temp (sqlversion nvarchar(max))
use master
go
insert into #sqltest_temp Select @@SERVERNAME
go
insert into [SQL1].[Databasename].[dbo].[sqlversion_anu]
Select * from #sqltest_temp
go
drop table #sqltest_temp
But when I querry this table [SQL1].[Databasename].[dbo].[sqlversion_anu] it shows only one value whereas should show value for both SQL2 and SQL3 , anyone ...where I am missing ?
April 16, 2018 at 11:41 am
anusql74 - Monday, April 16, 2018 9:00 AMI am trying to create MSX ans TSX relationship
MSX is SQL1TSX is
SQL2 and SQL3created a job in SQL1 that will run on SQL2 and SQL3
job step is :
create table #sqltest_temp (sqlversion nvarchar(max))
use master
go
insert into #sqltest_temp Select @@SERVERNAME
go
insert into [SQL1].[Databasename].[dbo].[sqlversion_anu]
Select * from #sqltest_temp
go
drop table #sqltest_tempBut when I querry this table [SQL1].[Databasename].[dbo].[sqlversion_anu] it shows only one value whereas should show value for both SQL2 and SQL3 , anyone ...where I am missing ?
You would want to check the job history when things don't seem to be running correctly. Do you have a linked server on SQL2 and SQL3 for SQL1?
Sue
April 16, 2018 at 12:06 pm
yes there is link server on all servers
Job history is simply saying Success but table ' [SQL1].[Databasename].[dbo].[sqlversion_anu] is not updating the result from both servers SQL2 and SQL3
April 16, 2018 at 1:43 pm
Is that the message from job history?
You should ensure the job creates a log, and examine that. The insert might fail, but that won'd necessarily fail the job. Can you ensure you make it clear what code is in the job?
April 16, 2018 at 2:09 pm
anusql74 - Monday, April 16, 2018 12:06 PMyes there is link server on all servers
Job history is simply saying Success but table ' [SQL1].[Databasename].[dbo].[sqlversion_anu] is not updating the result from both servers SQL2 and SQL3
okay, let's take this step by step.
1.) Why use nvarchar(max) for a server name? That isn't likely to be anywhere near 100 characters, much less 2 billion (max size of the max version of varchar or nvarchar). This is wasteful.
2.) You'll need to verify that none of these job steps are trying to run on one server, but select from another.
3.) What's the true objective here? Server names are usually static. Why not just build a table of them and be done with it?
4.) Check your execution context for each job and be sure that it has the necessary permissions to run any kind of query on each other server involved for that job step.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 16, 2018 at 2:44 pm
anusql74 - Monday, April 16, 2018 12:06 PMyes there is link server on all servers
Job history is simply saying Success but table ' [SQL1].[Databasename].[dbo].[sqlversion_anu] is not updating the result from both servers SQL2 and SQL3
Make sure the target servers are configured as targets - query msdb.dbo.systargetservers on the master.
Then check to make sure the job was downloaded to the targets - query msdb.dbo.sysdownloadlist on the master.
If all of that seems in order, you can try to force a synchronization of the jobs between master and target by executing sp_resync_targetserver on the master in msdb.
Sue
April 16, 2018 at 2:53 pm
What's the true objective here? Server names are usually static. Why not just build a table of them and be done with it?
Want to get all Orphan user on all servers in all databases and collect that report to one table in repository ..
April 16, 2018 at 2:56 pm
Hi Sue ,Thanks for reply ,
Make sure the target servers are configured as targets - query msdb.dbo.systargetservers on the master.
Then check to make sure the job was downloaded to the targets - query msdb.dbo.sysdownloadlist on the master.
If all of that seems in order, you can try to force a synchronization of the jobs between master and target by executing sp_resync_targetserver on the master in msdb.
but yes all this is already done but only issue is only one server is writing to Central repository table whereas it should be populated from output of all servers .
April 16, 2018 at 3:03 pm
anusql74 - Monday, April 16, 2018 2:56 PMHi Sue ,Thanks for reply ,Make sure the target servers are configured as targets - query msdb.dbo.systargetservers on the master.
Then check to make sure the job was downloaded to the targets - query msdb.dbo.sysdownloadlist on the master.
If all of that seems in order, you can try to force a synchronization of the jobs between master and target by executing sp_resync_targetserver on the master in msdb.but yes all this is already done but only issue is only one server is writing to Central repository table whereas it should be populated from output of all servers .
Yes...thanks. I get the issue just wanted to check if everything is configured and that you have resynced.
So what happens when you execute just the query manually on SQL2 and SQL3?
Sue
May 2, 2018 at 2:25 am
anusql74 - Monday, April 16, 2018 9:00 AMI am trying to create MSX ans TSX relationship
MSX is SQL1TSX is
SQL2 and SQL3created a job in SQL1 that will run on SQL2 and SQL3
job step is :
create table #sqltest_temp (sqlversion nvarchar(max))
use master
go
insert into #sqltest_temp Select @@SERVERNAME
go
insert into [SQL1].[Databasename].[dbo].[sqlversion_anu]
Select * from #sqltest_temp
go
drop table #sqltest_tempBut when I querry this table [SQL1].[Databasename].[dbo].[sqlversion_anu] it shows only one value whereas should show value for both SQL2 and SQL3 , anyone ...where I am missing ?
One way to do this is to use OPENQUERY for Linked Servers. For example, creating an Agent Job on SQL1 with the following code might work.
create table #sqltest_temp (sqlversion nvarchar(max))
insert into #sqltest_temp
select * from table
union
select * from openquery(SQL2,'Select @@SERVERNAME')
union
select * from openquery(SQL3,'Select @@SERVERNAME')
insert into [DatabaseName].[dbo].[sqlversion_anu]
Select * from #sqltest_temp
GO
drop table #sqltest_temp
=======================================================================
May 2, 2018 at 11:33 am
IrfanHyd - Wednesday, May 2, 2018 2:25 AManusql74 - Monday, April 16, 2018 9:00 AMI am trying to create MSX ans TSX relationship
MSX is SQL1TSX is
SQL2 and SQL3created a job in SQL1 that will run on SQL2 and SQL3
job step is :
create table #sqltest_temp (sqlversion nvarchar(max))
use master
go
insert into #sqltest_temp Select @@SERVERNAME
go
insert into [SQL1].[Databasename].[dbo].[sqlversion_anu]
Select * from #sqltest_temp
go
drop table #sqltest_tempBut when I querry this table [SQL1].[Databasename].[dbo].[sqlversion_anu] it shows only one value whereas should show value for both SQL2 and SQL3 , anyone ...where I am missing ?
One way to do this is to use OPENQUERY for Linked Servers. For example, creating an Agent Job on SQL1 with the following code might work.
USE TempDB;create table #sqltest_temp (sqlversion nvarchar(max))
insert into #sqltest_temp
select * from table
union
select * from openquery(SQL2,'Select @@SERVERNAME')
union
select * from openquery(SQL3,'Select @@SERVERNAME')insert into [DatabaseName].[dbo].[sqlversion_anu]
Select * from #sqltest_temp
GOdrop table #sqltest_temp
That's totally different. The user is trying to set up multiserver administration for jobs. You create a master server and then one or more target servers. It's not about querying between two servers.
Create a Multiserver Environment
Sue
May 3, 2018 at 6:39 am
Apologies... my bad. I took it to some other direction. Thanks for correcting me Sue.
=======================================================================
May 3, 2018 at 7:12 am
IrfanHyd - Thursday, May 3, 2018 6:39 AMApologies... my bad. I took it to some other direction. Thanks for correcting me Sue.
No worries - just didn't want anyone confused on what the poster is trying to do. A lot of people have no idea what MSX and TSX refer to. Or what multiserver administration is in SQL Server.
Sue
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply