Post reply

Generating output to text file from SP via SQLCMD

  • April 25, 2013 at 2:41 am

    #273271

    Hi all,

    I have created a stored procedure to report on login mappings which I want to be able to run from SQLCMD and place the output in a text file on a shared directory. The SP is setup and working but I cannot find a way to output the results to file. Does anyone know if this is even possible and advise on a way to complete this?

    The plan is to hand over this requirement to another team for checking so I wanted to provide some instructions where they could just copy and paste the SQL into a CMD window, adding the Login account variable, which would then produce a text file they could use for auditing requirements.

    Thanks, Mike

  • April 25, 2013 at 2:49 am

    #1609825

    Have you tried SSIS?

    Regards
    Durai Nagarajan

  • April 25, 2013 at 3:26 am

    #1609831

    The problem really is the login ID variable and the people who I need to hand the task over to not having any SQL skills. The theory being : if you see this error, copy this code into a cmd line and check this output file for the login mappings, add to audit report.

  • April 25, 2013 at 3:34 am

    #1609836

    why dont you schedule it with the help of SSIS and Agent services.

    Regards
    Durai Nagarajan

  • April 25, 2013 at 4:03 am

    #1609848

    we could do but we're talking one login issue every few days and to create, manage and schedule this on 150+ servers, reporting on 1000's of login accounts isn't feasible, just to get the login mappings for one user that might try to access the wrong database. So I thought the easiest way with the lowest overhead is just to run the sp to check on the single login as part of the larger audit report.

    I also want to use the same process for another few tasks I wanted to hand over.

  • April 25, 2013 at 4:37 am

    #1609858

    try this

    osql -E -S server_name -d database_name -Q "EXEC schema.spname param1, param2">C:\Filename.txt

    Regards
    Durai Nagarajan

  • April 25, 2013 at 4:41 am

    #1609861

    sorry i am not an expert on command prompt codes.

    Regards
    Durai Nagarajan

  • April 25, 2013 at 4:47 am

    #1609865

    Excellent, that's done it. not sure how I missed that one. :Whistling:

    Thanks

  • April 27, 2013 at 3:41 am

    #1610573

    How to get query for effected table data from sys...

  • April 29, 2013 at 1:30 am

    #1610692

    pavan,

    i didnt get your question, can you explain it?

    Regards
    Durai Nagarajan

Viewing 10 posts - 1 through 9 (of 9 total)

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