EXEC xp_cmdshell VS sqlcmd - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

EXEC xp_cmdshell VS sqlcmd

Author

Topic

andreak
Starting Member

4 Posts

Posted - 2011-12-07 : 10:50:20

Hi.

I am getting an error running a statement via sqlcmd when the exact statement runs fine via smms EXEC xp_cmdshell

This is a local db with the default server (computer name) and instance (MSSQLSERVER). I have no usernames or passwords in the db

- what am I missing? Thanks in advance.

--This works perfectly in SSMS Query Window

EXEC xp_cmdshell 'bcp "SELECT * FROM mydb2.dbo.MyTable" queryout "C:\Users\Owner\Documents\Work_NET\SQL\BCP_Test\MyTable.bcp" -E -c -b 1000 -t\¿ -SFRINK -T'

-----------------------------------------------------

--This returns the error "Msg 102, Level 15, State 1, Server FRINK, Line 2 Incorrect syntax near 'queryout'."

--1) The myFile.sql only contains the text:


bcp "SELECT * FROM mydb2.dbo.MyTable" queryout "C:\Users\Owner\Documents\Work_NET\SQL\BCP_Test\MyTable.bcp" -E -c -b 1000 -t\¿ -SFRINK -T

Sachin.Nand

2937 Posts

Posted - 2011-12-07 : 11:55:58

Try this

!!bcp..Your rest of the query

Set the option SQLCMD in the Tools option of the query analyser.

PBUH

andreak
Starting Member

4 Posts

Posted - 2011-12-07 : 12:17:14

Thanks, Sachin.Nand - your help is much appreciated!! The !! part worked! Now to look up what the !! means...

sandy123
Starting Member

14 Posts

Posted - 2011-12-07 : 13:23:25

!! <command>
Executes operating system commands. To execute an operating system command, start a line with two exclamation marks (!!) followed by the operating system command. For example, the following performs a directory listing of the current drive: !! dir

sandy

andreak
Starting Member

4 Posts

Posted - 2011-12-07 : 14:45:28

Thanks, sandy123! I was spinning my wheels looking in MSDN documentation.

Would anyone mind explaining why a Java command is needed? Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-07 : 15:41:40

No Java command is needed. Sandy has added a spam link to the signature. I have edited it out.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

andreak
Starting Member

4 Posts

Posted - 2011-12-07 : 18:03:34

Thanks.