2000 > 2005 and xp_cmdshell stopped working!

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 2005 Forums

SQL Server Administration (2005)

2000 > 2005 and xp_cmdshell stopped working!

Author

Topic

holster100
Starting Member

13 Posts

Posted - 2008-06-18 : 17:15:24

Hi,

I'm just moving over one of our databases from SQL2000 to 2005. Everything is working fine, and all web sites are working, however I just can't seem to get xp_cmdshell to function on the 2005 server.

I have enabled xp_cmdshell, and when I run a stored procedure, which writes the contents of one column to a text file, it says "invalid object name". This table is definitely there, and is actually updated earlier on in the same SP, so permissions for that are fine.

This is the line causing the problem (if I take it out, the rest of the SP works no problems):

--write the text file
EXEC master.sys.xp_cmdshell 'bcp "SELECT newslettertext FROM mydatabase.dbo.newsletters" queryout D:\newslettertext\textbody.txt -U -P -c'

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'mydatabase.dbo.newsletters'.

SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
NULL

Could it be to do with permissions on the master.sys.xp_cmdshell? If so, wouldn't it give me a "permission denied" error?

Any help would be greatly appreciated!

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-18 : 17:18:46

Where is the S switch in the bcp command? You need to specify which server to connect to.

bcp ... -S server1\instance1 -T -c

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

Subscribe to my blog

holster100
Starting Member

13 Posts

Posted - 2008-06-18 : 17:30:42

You're bang on, and I just worked it out just after I posted. Was just about to reply to say I sorted it. Thanks for the speedy answer though - I wish I'd posted sooner, instead of spending 2 hours messing about!

Cheers

matthewphillips
Starting Member

6 Posts

Posted - 2009-01-21 : 09:14:55

Hi, I'm having the same problem but don't know what my server name or instance names are. How would I find these?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-21 : 10:20:39

[code]
Select @@Servername
[/code]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-21 : 12:22:00

How are you connected to the SQL Server if you don't even know the name?

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

Subscribe to my blog