save query result in a file

emp is a table name in sql2000

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

showing error: incorrect syntex near q

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.

Sorry Wrong posting...


MohammedU.
Moderator
SQL-Server-Performance.com

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

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.

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.

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.

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

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

Like the following...

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

In Query Analyzer:
exec master..xp-cmdshell '"Crogram 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.

Hi ,

I ran the following query in query analyser:

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

result:

'Crogram' 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.

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

AKTHAR

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 crog...
use this instead
exec master..xp_cmdshell 'OSQL.exe -E -dnorthwind -q"select * from employees'

AKTHAR

in command prompt:If I run the query "Crogram 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.

try ISQL rather that OSQL.

Run the following command in Query Analyzer...you should see "Crogram FilesMicrosoft SQL Server80ToolsBinn" in the output...
If you don't see "Crogram 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.

I have set the path variable as Crogram FilesMicrosoft SQL Server80ToolsBinn
but neither osql nor isql working.

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

PATH=" Crogram 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 Crogram FilesMicrosoft SQL Server80ToolsBinn
but neither osql nor isql working.

Make sure OSQL.EXE AND ISQL.EXE exists in Crogram 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.

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

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.

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.

what edition of SQL are you running?

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

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

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

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