SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How can I insert in a single table from different SQL Servers


How can I insert in a single table from different SQL Servers

Author
Message
anusql74
anusql74
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 311
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 ?
Sue_H
Sue_H
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74643 Visits: 15266
anusql74 - Monday, April 16, 2018 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 ?


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



anusql74
anusql74
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 311
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (609K reputation)SSC Guru (609K reputation)SSC Guru (609K reputation)SSC Guru (609K reputation)SSC Guru (609K reputation)SSC Guru (609K reputation)SSC Guru (609K reputation)SSC Guru (609K reputation)

Group: Administrators
Points: 609479 Visits: 21159
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?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
sgmunson
sgmunson
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95604 Visits: 7179
anusql74 - Monday, April 16, 2018 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

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)
Smile Smile Smile
Health & Nutrition
Sue_H
Sue_H
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74643 Visits: 15266
anusql74 - Monday, April 16, 2018 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


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



anusql74
anusql74
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 311
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 ..
anusql74
anusql74
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 311
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 .
Sue_H
Sue_H
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74643 Visits: 15266
anusql74 - Monday, April 16, 2018 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 .


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



IrfanHyd
IrfanHyd
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1462 Visits: 874
anusql74 - Monday, April 16, 2018 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 ?

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
GO

drop table #sqltest_temp


---------------------------------------------------------------------------------------------------------------------------------
IrfanHyd

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search