September 29, 2015 at 5:18 pm
Hi,
I have to use sql cmd and run diagnostic queries.
I need help to run multiple dmvs as a batch file and storing the dmv result to some place.
How to start this?
September 29, 2015 at 6:03 pm
write a stored procedure to insert the results of the DMV into a table in your database and then call it from the command line?
October 5, 2015 at 1:02 pm
No.
They don't want to make any changes to the databases. They need the select query (DMV) out put to the filesystem
October 6, 2015 at 1:08 pm
sqlcmd -S SERVERNAME -d DATABASENAME -W -h -1 -s "|" -i sql_query.txt >output_file.txt
-W remove trailing spaces
-h -1 remove column headers
-s "|" I like to use the vertical pipe as column delimiter.
-i sql_query.txt put your sql query into the file "sql_query.txt"
>output_file.txt this is where your output from your query shows up
October 6, 2015 at 1:10 pm
Are you allowed to use temp tables and xp_cmdshell?
October 6, 2015 at 4:30 pm
I ran like this
sqlcmd -S SERVERNAME -d Master -i sql_query.bat -o output_file.txt
It is working but just I need to change the output format to understand the query result just like in ssms
October 7, 2015 at 6:32 am
Excel would probably do nicely with formatting, that would be one of my choices. Maybe you could also investigate building html from your query and just outputting that?
October 7, 2015 at 10:20 am
I tried excel extension also. Still most of the values going out of area and mixing with next column.
October 7, 2015 at 11:37 am
when you import into excel, do you use a unique column delimiter? I usually am pretty successful when I use a suitable column delimiter.
You could also consider hooking excel up directly with a query if you know any vba.
October 7, 2015 at 2:26 pm
Do you mean first give the extention as .csv in output and then open using excel?
October 7, 2015 at 3:23 pm
ramana3327 (10/7/2015)
Do you mean first give the extention as .csv in output and then open using excel?
Ah thats where I went wrong, thats a good way to do it. Often though, I run the import wizard from "get external data" and spec the column separator during that process.
October 8, 2015 at 1:04 pm
Your option importing from text is working fine.
Thanks
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply