Generating output to text file from SP via SQLCMD

  • 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

  • Have you tried SSIS?

    Regards
    Durai Nagarajan

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

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

    Regards
    Durai Nagarajan

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

  • try this

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

    Regards
    Durai Nagarajan

  • sorry i am not an expert on command prompt codes.

    Regards
    Durai Nagarajan

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

    Thanks

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

  • 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