Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
guna_dev
Starting Member
2 Posts |
 Posted - 2007-09-17 : 15:31:54
|
| Hi,I want to query a table and save the recordset into a text file. I used bcp and xp_cmdshell. Query gets executed without any error but text file is not generated (using sql server 2000). But when i run the bcp command in command prompt, text file is generated. Here is the query i tried.declare @query varchar(1000)set @query = 'bcp.exe "select employeeid,firstname from northwind.dbo.employees" queryout "D:/nw_employees.txt" -P "sa" -cT -t -T -k'exec master..xp_cmdshell @query |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-17 : 15:38:32
|
| Is the SQL Server on the same box where you are running this query? If not, then the file is being created on the database server and not on your client machine. xp_cmdshell runs from the server. When you run bcp.exe from your own command prompt, it runs from the client.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-17 : 15:40:57
|
It works for me copied verbatim so I am not sure. You say you were able to copy and paste the exact output as below:bcp.exe "select employeeid,firstname from northwind.dbo.employees" queryout "d:/nw_employees.txt" -P "sa" -cT -t -T -kinto the command line and it works? What does the results pane say when you run it in QA? This is what I get:output --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- NULLStarting copy...NULL9 rows copied.Network packet size (bytes): 4096Clock Time (ms.): total 1NULL(7 row(s) affected)
Future guru in the making. |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-17 : 15:41:48
|
quote:
Originally posted by tkizer
Is the SQL Server on the same box where you are running this query? If not, then the file is being created on the database server and not on your client machine. xp_cmdshell runs from the server. When you run bcp.exe from your own command prompt, it runs from the client.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/
Ahh.. good point, I bet that's it.
Future guru in the making. |
|
|
|
guna_dev
Starting Member
2 Posts |
Posted - 2007-09-18 : 02:45:54
|
| This is what i too get, but text file is not createdoutput --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- NULLStarting copy...NULL9 rows copied.Network packet size (bytes): 4096Clock Time (ms.): total 1NULL(7 row(s) affected)could some one help me out. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-18 : 12:05:57
|
| guna_dev, did you see my post?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
unable to copy table into text file