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

  • 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  ?

  • 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

  • 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

  • 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?

  • 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) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • 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

  • 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 ..

  • 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 .

  • 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

  • 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 - Wednesday, May 2, 2018 2:25 AM

    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

    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

  • Apologies... my bad. I took it to some other direction.  Thanks for correcting me Sue.

    =======================================================================

  • IrfanHyd - Thursday, May 3, 2018 6:39 AM

    Apologies... 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