Post reply

sql and stored procedure

  • March 24, 2016 at 9:35 am

    #305856

    How can I return

    select @@servername along with the result of this stored procedure - msdb.dbo.sp_get_sqlagent_properties

    in a single row.

    Result : Servername1,all columns from above store procedure (msdb.dbo.sp_get_sqlagent_properties)

    I cannot create any temp tables or additional stored procedures and I need to run the query on a list of sql servers in an SSIS package inside a for each loopl container.

    Thanks

  • March 24, 2016 at 9:42 am

    #1867493

    Those somewhat contrived requirements make this sound like a homework assignment. Why can't you create any temp tables or new procedures?

    John

  • March 24, 2016 at 9:50 am

    #1867497

    John Mitchell-245523 (3/24/2016)

    Those somewhat contrived requirements make this sound like a homework assignment. Why can't you create any temp tables or new procedures?

    John

    This is actually a common business requirement. The way I like to do it is to:

    1. Get two monitors

    2. Run the select @@servername query on one monitor

    3. Run EXEC msdb.dbo.sp_get_sqlagent_properties on the second monitor

    4. Push the two monitors together so as to appear that the result set is one really wide row

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • March 24, 2016 at 10:49 am

    #1867515

    I can think of 3 possible solutions. The first one is to use a table variable.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • March 24, 2016 at 10:57 am

    #1867520

    Why no temp tables? The normal solution would be to EXEC into the temp table, then query it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 24, 2016 at 1:13 pm

    #1867587

    I like Alan's solution, but I'd never want to move my monitors; I'd move the windows instead. 😛

    Seriously, why the restriction on temp tables?

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • March 24, 2016 at 3:01 pm

    #1867644

    ... that way I can tell my boss that second monitor really is required!

  • March 24, 2016 at 8:54 pm

    #1867678

    Apologies to disappoint people on board for their 'humour'. But that is not the case.

    I need to extract the result from the stored procedure from a bunch of servers along with their respective server names.

    The code will be part of an SSIS package which will loop through the servers in a for each loop and I cannot create any DB objects on any of the servers.

    Temp tables are allowed but I wanted to know if it can be achieved without their use.

    Thanks

  • March 24, 2016 at 8:58 pm

    #1867679

    Thank you

  • March 24, 2016 at 9:29 pm

    #1867681

    sqlnewbie17 (3/24/2016)

    Apologies to disappoint people on board for their 'humour'. But that is not the case.

    I need to extract the result from the stored procedure from a bunch of servers along with their respective server names.

    The code will be part of an SSIS package which will loop through the servers in a for each loop and I cannot create any DB objects on any of the servers.

    Temp tables are allowed but I wanted to know if it can be achieved without their use.

    Thanks

    Getting an extra column into the same resultset the procedure delivers is a pain in T-SQL. You know the server name because you're querying it from SSIS so can you simply add the column to your pipeline in your Data Flow Task using a Derived Column Transformation?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 25, 2016 at 6:47 am

    #1867726

    I like Orlando's idea, but here are some other options:

    Use a Central Management Server. If you're going to do this manually, you could actually just register the servers in SSMS in a group and query the group.

    You could script the procedure and add the column. The procedure shouldn't change unless different versions are queried and even then the probabilities are low.

    USE msdb;

    EXEC sp_helptext sp_get_sqlagent_properties;

    If you script the procedure, remove all the inline comments or convert them to block comments. Otherwise, you'll comment everything after the first comment.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • March 28, 2016 at 4:35 am

    #1867925

    Thanks...That is one of the solutions but was curious to know if I could do using only t-sql.

    Anyways thank you for the response.

  • March 28, 2016 at 8:30 am

    #1867982

    sqlnewbie17 (3/28/2016)

    Thanks...That is one of the solutions but was curious to know if I could do using only t-sql.

    Anyways thank you for the response.

    As I mentioned I would use SSIS here for simplicity but as with most tasks T-SQL can also be used. This technique could help:

    http://www.sqlservercentral.com/articles/T-SQL/138306/[/url]

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 28, 2016 at 10:39 pm

    #1868133

    Noted and thank you again !

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply