none
Force poll of instruction to TSX through t-sql?

    Question

  • Hello,

    I have a master server (MSX) to which I enlist several Target Servers (TSX).

    Now I want to script this enlistment process and automatically push out some jobs from the MSX.

    To verify that the jobs are pushed out to the current TSX, I check if there is a job by that specific name - if that's the case, the script is successfull, otherwise not.

     

    Problem with this is that by default, the TSX has a polling interval of 60seconds. So I would need to pause my script 1min by adding "WAITFOR DELAY '00:01'".

    Now of course this works, but it's not the prettiest.. having to wait a whole minute to finish the script.

    If I change the polling interval to, let's say, 10seconds, It'd still take 1min to get that instruction out.

    I want to know if there is a way to Force Poll to this specific target server through t-sql code?

     

    If I try manually through SSMS, I get an error: This operation requires Q38\B server to be a registered server.  Don't know why, because it really is, and everything else works just fine (push out jobs etc). (this seems to be the case for all my registered servers).

     

    Any help is appreciated!

    Thanks,

    Cloxy

    Thursday, December 22, 2011 1:09 PM

Answers

  • Sorry, but I can't configure my instances for MSX at the moment. It will be at least a week until I can do that. Catching these two events should cut it: RPC:Completed and SQL:BatchCompleted. Then you would want to work with filters if needed. Filtering on things like Machine name, application name, for instance.
    Tibor Karaszi, SQL Server MVP | web | blog
    Friday, January 06, 2012 11:16 AM

All replies

  • Hi Cloxy,

    Thank you for your question. 
    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 
    Thank you for your understanding and support.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, December 28, 2011 3:18 AM
  • Hello Cloxy,

     

    You can change the polling interval as mentioned in following KB article.

     

    How to configure the polling interval that is used by SQL Server Management Studio in SQL Server 2008

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;955647

     

     

    Thank You

     

    Jayaprakash JO - MSFT

    Wednesday, December 28, 2011 8:26 PM
  • Thank you for your response.

    However, I cannot find the path specified by the article.

    I can locate HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools

    But not the last "\Shell". There are no such existing directory.

     

    Another problem is that my SQL Server service account is not local administrator on the machine, which means that I get an access denied when trying to use xp_regwrite to create or update an object in the MSSQL hierarchy which resides outside of the instance directory..

     

    There doesn't seem to exist any real solution to my problem at the moment.

    Does anyone know what t-sql command SQL Server uses when we in the GUI for managing Target Servers press "Force Poll" ?

     

    Regards,

    Cloxy

     

    Thursday, December 29, 2011 3:46 PM
  • I don't have an MSX environment here to play with. But if you do, then just start Profiler and trace what command is used. Make sure you trace both master and target server, since the command can be egeinst any of the servers. Target server is the most likely, of course.
    Tibor Karaszi, SQL Server MVP | web | blog
    Wednesday, January 04, 2012 9:59 AM
  • Thank you for the tip, Tibor!

    However, I have already tried this approach but without success.

    I am completely new to this SQL Profiler trace thing. I tried it this once but didn't get anything usable.

    Could you, or anyone else, point me in the right direction on what I should choose to trace? There are a lot of activity on the server all the time so just choose everything and quickly press the Force poll button won't work.. (I've tried, got drowned in messages).

     

    Any help is appreciated.. I will try and play around with it a bit more..

     

    Cloxy

    Thursday, January 05, 2012 5:45 PM
  • Sorry, but I can't configure my instances for MSX at the moment. It will be at least a week until I can do that. Catching these two events should cut it: RPC:Completed and SQL:BatchCompleted. Then you would want to work with filters if needed. Filtering on things like Machine name, application name, for instance.
    Tibor Karaszi, SQL Server MVP | web | blog
    Friday, January 06, 2012 11:16 AM