Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Generating output to text file from SP via SQLCMD Expand / Collapse
Author
Message
Posted Thursday, April 25, 2013 2:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 4, 2014 2:35 AM
Points: 130, Visits: 142
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
Post #1446355
Posted Thursday, April 25, 2013 2:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:05 AM
Points: 1,058, Visits: 2,697
Have you tried SSIS?

Regards
Durai Nagarajan
Post #1446358
Posted Thursday, April 25, 2013 3:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 4, 2014 2:35 AM
Points: 130, Visits: 142
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.
Post #1446368
Posted Thursday, April 25, 2013 3:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:05 AM
Points: 1,058, Visits: 2,697
why dont you schedule it with the help of SSIS and Agent services.

Regards
Durai Nagarajan
Post #1446373
Posted Thursday, April 25, 2013 4:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 4, 2014 2:35 AM
Points: 130, Visits: 142
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.
Post #1446386
Posted Thursday, April 25, 2013 4:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:05 AM
Points: 1,058, Visits: 2,697
try this
osql -E -S server_name -d database_name -Q "EXEC schema.spname param1, param2">C:\Filename.txt




Regards
Durai Nagarajan
Post #1446399
Posted Thursday, April 25, 2013 4:41 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:05 AM
Points: 1,058, Visits: 2,697
sorry i am not an expert on command prompt codes.

Regards
Durai Nagarajan
Post #1446402
Posted Thursday, April 25, 2013 4:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 4, 2014 2:35 AM
Points: 130, Visits: 142
Excellent, that's done it. not sure how I missed that one.
Thanks
Post #1446407
Posted Saturday, April 27, 2013 3:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, November 16, 2013 12:10 AM
Points: 1, Visits: 58
How to get query for effected table data from sys...
Post #1447241
Posted Monday, April 29, 2013 1:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:05 AM
Points: 1,058, Visits: 2,697
pavan,

i didnt get your question, can you explain it?



Regards
Durai Nagarajan
Post #1447392
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse