October 21, 2002 at 10:52 pm
I'm new to SQL and would like to create a text file from a sql query. I understand that xp_cmdshell could be used:
exec master..xp_cmdshell 'osql -Q "select batnbr from xtemptable" -O"c:\test.txt"'
Unfortunately, I get the error msg:
Password:
Msg 208, Level 16, State 1:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'xtemptable'.
(4 row(s) affected)
The xtemptable is located at a different db and not in the master one. I even tried exec master..xpcmdshell. Appreicate any help.
October 22, 2002 at 2:08 am
Fully qualify your tablename
exec master..xp_cmdshell 'osql -Q "select batnbr from yourOtherDb..xtemptable" -O"c:\test.txt"'
or use the -d switch to specify the database to connect to.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 22, 2002 at 6:57 pm
Is there any way to have the output using xp_cmdshell to be a csv file? comma delimited?
October 23, 2002 at 4:27 am
Using bcp will produce csv files
bcp "select batnbr from xtemptable" queryout c:\test.txt -t, -c
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 24, 2002 at 4:42 pm
Check the script in http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=148
This one import data into sql, check the final line and change the i for I to o for Out like your command.
October 30, 2002 at 7:20 pm
Thanks guys! Appreicate the Help
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply