SQL Server Performance

save query result in a file

Discussion in 'General Developer Questions' started by litu.deb, Apr 19, 2007.

  1. litu.deb Member

    emp is a table name in sql2000

    sqlcmd -q "select * from emp" -o" C:c.txt"

    showing error: incorrect syntex near q
  2. MohammedU New Member

    If you running in regular query window you get this error...
    Try running in command window or in new query window in SQL CMDMODE....

    Check BOL topic "SQLCMD scripts"

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  3. MohammedU New Member

    Sorry Wrong posting...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  4. litu.deb Member

    Dear All,

    I want to save a query result in my specified location.so I ran the following query
    in a sql2000 query analyser.but i got error msg.

    sqlcmd -q "select * from emp" -o" C:c.txt" --here emp is tab name.

    Is there any way to save query result in a user defined location.It should be query.

    thnks,
    Litudeb



    quote:Originally posted by MohammedU

    Sorry Wrong posting...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  5. MohammedU New Member

    SQLCMD utility is introduced in sql server 2005, if you are using sql 2000 you have to use OSQL.

    You posted this question in sql 2005 thread so you get incorrect answers...
    I am moving this to sql 2000....

    If you want to run OSQL in query analyzer you have run in through xp-cmdshell because OSQL is command line utility...

    In command window:
    OSQL -Sservername -E -Q"select query" -o"c:
    esults.txt"

    In Query Analyzer:
    exec master..xp-cmdshell 'OSQL -Sservername -E -Q"select query" -o"c:
    esults.txt"'

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  6. litu.deb Member

    showing the following error:

    'OSQL' is not recognized as an internal or external command,
    operable program or batch file.
    NULL



    quote:Originally posted by MohammedU

    SQLCMD utility is introduced in sql server 2005, if you are using sql 2000 you have to use OSQL.

    You posted this question in sql 2005 thread so you get incorrect answers...
    I am moving this to sql 2000....

    If you want to run OSQL in query analyzer you have run in through xp-cmdshell because OSQL is command line utility...

    In command window:
    OSQL -Sservername -E -Q"select query" -o"c:
    esults.txt"

    In Query Analyzer:
    exec master..xp-cmdshell 'OSQL -Sservername -E -Q"select query" -o"c:
    esults.txt"'

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  7. MohammedU New Member

    Make sure osql.exe utility exists on your machine...generally it should present in "C:program FilesMicrosoft SQL Server80ToolsBinn" folder...

    You can use filename along with path when you are executing osql...

    Like the following...

    In command window:
    C:program FilesMicrosoft SQL Server80ToolsBinnOSQL -Sservername -E -Q"select query" -o"c:
    esults.txt"

    In Query Analyzer:
    exec master..xp-cmdshell '"C:program FilesMicrosoft SQL Server80ToolsBinnOSQL" -Sservername -E -Q"select query" -o"c:
    esults.txt"'


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  8. satya Moderator

    Do you have SQL 2005 and 2k tools on the same machine?>
    As suggested SQLCMD works in SQL 2k5 only and not in SQL 2k, if you are getting invalid path then ensure SQL binaries path is stored on system default path.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  9. litu.deb Member

    Hi ,

    I ran the following query in query analyser:

    exec master..xp_cmdshell '"C:program FilesMicrosoft SQL Server80ToolsBinnOSQL" -Stras1 -E -Q"select * from emp" -o"c:
    esults.txt"'--tras1 is sever name & emp is tab.

    result:

    'C:program' is not recognized as an internal or external command,
    operable program or batch file.
    NULL

    but from commnd prompt I was able to get the output in a result.txt file.

    I need to run from query analyser

    thanks,
    Ashis






    quote:Originally posted by satya

    Do you have SQL 2005 and 2k tools on the same machine?>
    As suggested SQLCMD works in SQL 2k5 only and not in SQL 2k, if you are getting invalid path then ensure SQL binaries path is stored on system default path.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  10. Akthar New Member

    hi ,
    no need to put c:prog...
    use this instead
    exec master..xp_cmdshell 'OSQL.exe -E -dnorthwind -q"select * from employees'

    AKTHAR
  11. litu.deb Member

    showing the following error msg:

    'OSQL' is not recognized as an internal or external command,
    operable program or batch file.
    NULL




    quote:Originally posted by Akthar

    hi ,
    no need to put c:prog...
    use this instead
    exec master..xp_cmdshell 'OSQL.exe -E -dnorthwind -q"select * from employees'

    AKTHAR
  12. satya Moderator

    I have copied Akthar's code to Query analyzer and it works perfectly.
    Goto command prompt and run OSQL to see if you get any restuls back, I believe it may be the path (mssql) problem

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  13. litu.deb Member

    in command prompt:If I run the query "C:program FilesMicrosoft SQL Server80ToolsBinnOSQL -Sservername -E -Q"select query" -o"c:
    esults.txt""

    it is working fine.

    but if I put the query:
    "exec master..xp_cmdshell 'OSQL.exe -E -dnorthwind -q"select * from employees'"

    getting error msg."'OSQL' is not recognized as an internal or external command,
    operable program or batch file.
    NULL
    "

    I set path and classpath variable.but still if I put OSQL in command prompt ,getting d following msg :

    'osql' is not recognized as an internal or external command,
    operable program or batch file.


  14. satya Moderator

    That is nothing but a path problem for SQL tools, then continue with the path specification in this case

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  15. monusharma New Member

    try ISQL rather that OSQL.
  16. satya Moderator

    If the path is a problem ISQL or OSQL will not work without specifying the root of that file.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  17. MohammedU New Member

    Run the following command in Query Analyzer...you should see "C:program FilesMicrosoft SQL Server80ToolsBinn" in the output...
    If you don't see "C:program FilesMicrosoft SQL Server80ToolsBinn" in your output you need to set the path....

    EXEC xp_cmdshell 'PATH'




    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  18. litu.deb Member

    I have set the path variable as C:program FilesMicrosoft SQL Server80ToolsBinn
    but neither osql nor isql working.
  19. litu.deb Member

    I ran the query: master..xp_cmdshell in sql analyser
    result:

    PATH=" C:program FilesMicrosoft SQL Server80ToolsBinn"
    NULL
    THEN
    ran: exec master..xp_cmdshell 'OSQL -Stras1 -E -Q"select * from emp" -o"c:
    esults.txt"'
    --tras1 is server name and emp table name.

    result: 'OSQL' is not recognized as an internal or external command,
    operable program or batch file.
    NULL

    plz help!



    quote:Originally posted by litu.deb

    I have set the path variable as C:program FilesMicrosoft SQL Server80ToolsBinn
    but neither osql nor isql working.
  20. satya Moderator

    Can you check the system variables under COntrol panel --> System andsee whether this is listed or not.

    If the statment is unable to recognise then its better you wrap up the path and mention that when calling XP_CMDSHELL statement.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  21. MohammedU New Member

    Make sure OSQL.EXE AND ISQL.EXE exists in C:program FilesMicrosoft SQL Server80ToolsBinn folder...if your installation is path is default one...
    Otherwise check the installation path and give the right path when you are setting PATH...

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  22. litu.deb Member

    I ran the following query: getting null result.

    exec master..xp_cmdshell 'OSQL.exe -E -dnorthwind -q"select * from sysobjects" -o"c:
    esults.txt"'

    Output:null.

    Thanks,
    Litu
  23. satya Moderator

    Are you running from the server or from a client's machine?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  24. litu.deb Member

    both server and client installed in the same m/c.
    running from the server.



    quote:Originally posted by satya

    Are you running from the server or from a client's machine?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  25. satya Moderator

    I'm getting desired results at my end, hoping you have SQL 2000 Sp4 level to test this statement.
    Also ensure you have proper privileges on C: in order to create the file, sometims I have seen due to the access rights the results will not be written to the file properly.

    Also try using ISQL in thsi case.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  26. litu.deb Member

    Yeah,I have SP4 and I have access on the c Drive.


    quote:Originally posted by satya

    I'm getting desired results at my end, hoping you have SQL 2000 Sp4 level to test this statement.
    Also ensure you have proper privileges on C: in order to create the file, sometims I have seen due to the access rights the results will not be written to the file properly.

    Also try using ISQL in thsi case.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  27. Akthar New Member

    what edition of SQL are you running?
  28. missfie_9 New Member

    i try Akhtar's code also but i modified it like i wrote below :
    exec master..xp_cmdshell 'OSQL.exe -dnorthwind -E -Q"select quantity from DATA... [data_log#txt]" -o"d: eshasil.txt"'
    but the result is NULL...
    and the data in hasil.txt that i was made is :
    [Shared Memory]SQL Server does not exist or access denied.
    [Shared Memory]ConnectionOpen (Connect()).
    Help me... i'm a new user
    thx
  29. satya Moderator

  30. missfie_9 New Member

    would you explain to me what should i do exactly,,because i dont understand with the blog explaination that you suggest to me to visit,,;pthx
  31. satya Moderator

    Welcome to the forums.
    The blog referred actually suggest the solution to resolve SQL Server Does not exist.... error. As you are getting initial connection as failed, so you have to see whether suggested protocols are enabled as per that blog post.
  32. lele New Member

    hello everybody
    i need to know how to save Sql query results in a suitable format to be able to reuse the results through another SQL query
    Thanks in Advace
    Laila
  33. satya Moderator

    Laila
    Welcome to the forums.
    As per you requriement it is unsure that which version of SQL you are using or wanted to know.
    Using SQL 2005 onwards with Management studio you can do Tools - Options - Query Results - Sql Server - Results to Grid - Include column headers when copying or saving results.
  34. lele New Member

    Thanks for your answer
    but i wanna know if there are a comparable method to save my query results using SQL 2000
    Thanks In Advance
    Laila

Share This Page