none
Dynamic Linked Server Database System Database TSQL Query

    Question


  • Hello,

    I have a Dynamic SQL Query that I am trying to create to retrieve information from multiple linked servers. I am getting an error message, and not certain what the problem is. Here is my Query:

    declare @server varchar(25)
    set @server='ABC'
    
    --Drop table #ResultSet 
    
    Create Table #ResultSet (
    	  Servername varchar(255) null,
    	  DBName  varchar(255) null,
    	  Is_Encrypted INT null,
    	  encryption_state int null,
    	  percent_complete int null,
    	  key_algorithm varchar(25) null,
    	  Key_Length varchar(25) null
    	  )
    	  
    declare @query nvarchar(max) 
    
        set @query = --@query + 
          '''EXEC(''''SELECT db.[name], 
    	db.[is_encrypted], 
    	dm.[encryption_state], 
    	dm.[percent_complete], 
    	dm.[key_algorithm], 
    	dm.[key_length]
    	FROM [sys].[databases] db
    	LEFT OUTER JOIN [sys].[dm_database_encryption_keys] dm
    	ON db.[database_id] = dm.[database_id]''''
    	) AT ' + '['+@server+']'+''''
            
     
    Insert into #ResultSet
    (
    	  Servername,
    	  DBName,
    	  Is_Encrypted ,
    	  encryption_state ,
    	  percent_complete ,
    	  key_algorithm ,
    	  Key_Length 
    )
    exec(@query) 
    print @query    
    --Print(@query)  
    --Select * from #ResultSet
    
    Drop table #ResultSet 

    Here are my Errors/Results:

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'EXEC('SELECT db.[name], 
    	db.[is_encrypted], 
    	dm.[encryption_state], 
    	dm.[percent_complete], 
    	dm.[key_algorithm], 
    	dm.[k'.
    'EXEC(''SELECT db.[name], 
    	db.[is_encrypted], 
    	dm.[encryption_state], 
    	dm.[percent_complete], 
    	dm.[key_algorithm], 
    	dm.[key_length]
    	FROM [sys].[databases] db
    	LEFT OUTER JOIN [sys].[dm_database_encryption_keys] dm
    	ON db.[database_id] = dm.[database_id]''
    	) AT [ABC]'

    Thursday, October 03, 2013 3:43 PM

Answers

  • Try this.. this worked for me..

    declare @server varchar(25)
    set @server='ABC'

    --Drop table #ResultSet

    Create Table #ResultSet (
       Servername varchar(255) null,
       DBName  varchar(255) null,
       Is_Encrypted INT null,
       encryption_state int null,
       percent_complete int null,
       key_algorithm varchar(25) null,
       Key_Length varchar(25) null
       )
      
    declare @query nvarchar(max)

    set @query = 'EXEC(''SELECT db.[name],
     db.[is_encrypted],
     dm.[encryption_state],
     dm.[percent_complete],
     dm.[key_algorithm],
     dm.[key_length]
     FROM [sys].[databases] db
     LEFT OUTER JOIN [sys].[dm_database_encryption_keys] dm
     ON db.[database_id] = dm.[database_id]''
     ) AT ' + '['+@server+']'+''
           
     
    Insert into #ResultSet
    (
       Servername,
       DBName,
       Is_Encrypted ,
       encryption_state ,
       percent_complete ,
       key_algorithm ,
       Key_Length
    )
    exec(@query)
    print @query   
    --Print(@query) 
    --Select * from #ResultSet

    Drop table #ResultSet

    • Marked as answer by jmcpsd Thursday, October 03, 2013 5:37 PM
    Thursday, October 03, 2013 4:56 PM

All replies

  • The EXEC itself does not need to be enclosed in parantheses.
    Thursday, October 03, 2013 4:14 PM
  • I'm sorry, Stefan, I'm not certain what you mean, I thought it was required since I am using the "AT" and it does not appear to work otherwise - can you please elaborate with my code?

    Many thanks

    Thursday, October 03, 2013 4:51 PM
  • Try this.. this worked for me..

    declare @server varchar(25)
    set @server='ABC'

    --Drop table #ResultSet

    Create Table #ResultSet (
       Servername varchar(255) null,
       DBName  varchar(255) null,
       Is_Encrypted INT null,
       encryption_state int null,
       percent_complete int null,
       key_algorithm varchar(25) null,
       Key_Length varchar(25) null
       )
      
    declare @query nvarchar(max)

    set @query = 'EXEC(''SELECT db.[name],
     db.[is_encrypted],
     dm.[encryption_state],
     dm.[percent_complete],
     dm.[key_algorithm],
     dm.[key_length]
     FROM [sys].[databases] db
     LEFT OUTER JOIN [sys].[dm_database_encryption_keys] dm
     ON db.[database_id] = dm.[database_id]''
     ) AT ' + '['+@server+']'+''
           
     
    Insert into #ResultSet
    (
       Servername,
       DBName,
       Is_Encrypted ,
       encryption_state ,
       percent_complete ,
       key_algorithm ,
       Key_Length
    )
    exec(@query)
    print @query   
    --Print(@query) 
    --Select * from #ResultSet

    Drop table #ResultSet

    • Marked as answer by jmcpsd Thursday, October 03, 2013 5:37 PM
    Thursday, October 03, 2013 4:56 PM
  • Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
    Thursday, October 03, 2013 5:29 PM
  • Awesome - that works, I'm just getting a distributed transaction error message

    OLE DB provider "SQLNCLI10"

    for linked server "ABC" returned message "The transaction manager has disabled its support for remote/network transactions.".


    Msg 7391

    , Level 16, State 2, Line 1

    The operation could

    not be performed because OLE DB provider "SQLNCLI10" for linked server "ABC" was unable to begin a distributed transaction.

    Thursday, October 03, 2013 5:38 PM
  • Great --got this resolved by:

    EXEC sp_serveroption @server = 'ABC',@optname = 'remote proc transaction promotion', @optvalue = 'false' ;

    Thank you so much for your help, All!!

    Thursday, October 03, 2013 5:44 PM
  • Almost done with this - but still have a couple of hiccups that need to be addressed - hoping this will also help additional people -

    The following code works great - except It fails if the linked server is Oracle or if the Linked Server is spelled correctly and offline (it times out). If the linked server is spelled incorrectly, the following code still works. How can I check to disregard if the linked server is oracle and if the server is offline --(It times out if offline).

    Create  PROCEDURE [dbo].[uspLinkedServerEncryptionListing]
    
    AS
    BEGIN
    
    SET NOCOUNT ON;
    
    declare @ConnectTest int;
    
    declare @servers table (
    servername sysname,
    ConnectTest int,
    Processed bit
    )
    
    declare @query varchar(max)
    
    
    declare @ResultSet table  (
    	  Servername varchar(255) null,
    	  DBName  varchar(255) null,
    	  Is_Encrypted INT null,
    	  encryption_state int null,
    	  percent_complete int null,
    	  key_algorithm varchar(25) null,
    	  Key_Length varchar(25) null
    )
    
    insert  @servers
            (
            servername
            )
    
    select  name
    from    sys.servers
    where   server_id > 0 
    
    
    
    
    while 1=1
        begin
        declare @server sysname
    
        select  top 1 @server = servername 
        from    @servers where Processed is null
    
        if @@rowcount = 0
            break
    
    --Do Insert Table Looping Here:
    begin try
    Exec @ConnectTest=sys.sp_testlinkedserver @server
    Update @servers
    Set ConnectTest=@ConnectTest
    Where @server=servername
    
    
    If @ConnectTest=0
    Begin
    exec sp_serveroption @server=@server, @optname='rpc', @optvalue='true'
    exec sp_serveroption @server=@server, @optname='rpc out', @optvalue='true' 
    EXEC sp_serveroption @server =@server,@optname = 'remote proc transaction promotion', @optvalue = 'false' ;
    
    
    
    set @query = 'EXEC(''SELECT db.[name], 
     db.[is_encrypted], 
     dm.[encryption_state], 
     dm.[percent_complete], 
     dm.[key_algorithm], 
     dm.[key_length]
      FROM [sys].[databases] db
      LEFT OUTER JOIN [sys].[dm_database_encryption_keys] dm
      ON db.[database_id] = dm.[database_id]''
      ) AT ' + '['+@server+']'+''
    
    
     Insert into @ResultSet
     (
       
        DBName,
        Is_Encrypted ,
        encryption_state ,
        percent_complete ,
        key_algorithm ,
        Key_Length 
    )
    
     exec(@query) 
    End
    
     Update @ResultSet
     set Servername=@server where Servername is null
     
    
    End try
    begin catch
    set @ConnectTest = sign(@@error);
    Update @servers
    Set ConnectTest=@ConnectTest
    Where @server=servername
    
    end catch;
    
    
    
    
    
    
    --End of Loop
        Update  @servers
        Set Processed=1
        where   servername = @server and Processed is null
        end
    --New End
    
    
    --Select * from @servers
    Select
        R.DBName,
        R.Is_Encrypted ,
        R.encryption_state ,
        R.percent_complete ,
        R.key_algorithm ,
        R.Key_Length, 
        S.Servername,
        S.Processed,
        s.ConnectTest
    from  @ResultSet R
    inner join @servers  s on 
    R.Servername=s.servername
    where R.DBName is not null
    Order by S.servername, R.DBName, R.Is_Encrypted
    
    
    END
    

    Thursday, October 03, 2013 9:42 PM