SQL Server Performance

Query output into a text file

Discussion in 'General DBA Questions' started by mahi, Sep 5, 2006.

  1. mahi New Member

    Hello All,

    I am trying to write the output of query in to a text file with the following script. its working fine in my local but not on a dev machine. In my local machine it is executing in 1 or 2 seconds but on dev mechaine it is continously executing for more than 5 mins. i cancled the query after 5 mins and for cancelation also it took more than 6 mins. Can any body please help me why this is happening? your inputs would be greatly appreciated.

    DECLARE @isqlString varchar(255)
    SELECT @isqlString = 'isql -Q "select * from sysprocesses" -E -o c:sp_blocks_log.txt'
    EXEC master..xp_cmdshell @isqlString

  2. RockyLee New Member

    replace the "-E" switch with "-U" and "-P" switchs ,have a try?

    Life is simple~~~
  3. deepakontheweb New Member

    just specified master database name, another i hope you have proper connectivity else use -U and -P parametres in query.

    DECLARE @isqlString varchar(255)
    SELECT @isqlString = 'isql -Q "select * from master..sysprocesses" -E -o c:sp_blocks_log.txt'
    EXEC master..xp_cmdshell @isqlString

    if you need exact output related to blocks.. you can modify your query like:

    select * from master..sysprocesses where blocked<>0

    Deepak Kumar
    MVP, MCDBA - SQL Server

    Disclaimer: This post is provided as is with no rights & warranty for accuracy, for the sake of knowledge sharing only.
  4. mahi New Member

    Yeah.... Yesterday i cancled the queries those processess are showing still running in Process Info.<br />yesterday even after cancled & closing the query analyzer those processes are still there in Current activity--&gt<img src='/community/emoticons/emotion-4.gif' alt=';P' />rocess info, even after killing them in process info they were not released, i thought they may go after some time but when i logon today i checked that it is still running. I wonder why this is happening, how can i kill those processes?<br /><br />Thanks,<br />Mahi<br /><br />Mahi
  5. mahi New Member

    Hi RockleeDeepak,

    I have tried in my local machine by giving U and P instead of E but the output file is showing login failed.

    Please some one help me about my previous message..how can i kill those processes.

    Thank you,

  6. RockyLee New Member

    Login failed ?

    Fisrt,refer to the BOL for more information about the param of the "isql".
    if u dont use -S to specify the server u want to connect to,this will connect the server on your local machine.
    try this first.

    Second,make sure that the account u use has enouth privilege to logon

    Life is simple~~~

Share This Page