strange xp_cmdshell behaviour - 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

General SQL Server Forums

New to SQL Server Programming

strange xp_cmdshell behaviour

Author

Topic

CSnoek
Starting Member

3 Posts

Posted - 2011-09-20 : 12:25:55

Hi,

I hope you can help me with this, because I am quite baffled with what is happening right now. (Maybe I am overlooking th obvious as well, so don't discount anything).

What I am doing is the following:
declare @backup nvarchar(20)
set @backup = 'backup20_09_2011' --> this is the name of the folder I created at some point and I want the system to check if this folder exits by importing the directory list into a table. (Which is functioning).
select @backup as test --> extra statement just to see for myself that the folder name is set correctly

declare @sql nvarchar(4000)
select @sql = 'SELECT * FROM dirList where line like ''%<DIR>%'' and line like ''%'+ @backup + '%''' --> this is the query formed with the dynamic data

select @sql as test_query --> once again to see if the query is actually created correctly

exec master..xp_cmdshell @sql --> this is where things go wrong... :-S

The printout of the full query is "SELECT * FROM dirList where line like '%<DIR>%' and line like '%backup20_09_2011%'" and this query executes correctly from a normal query window.
This query is not executing correctly from the xp_cmdshell, not even if I put it in directly via the variable @sql.

What am I missing or doing wrong here? Is there anybody who can shed a light on this?

Thanks a lot in advance for your help.

Cheers,
Corne

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 12:32:28

you're trying to execute sql query from command shell?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-20 : 12:35:01

I don't understand the purpose of this code, but to call it from command line you'd need to pass the query through sqlcmd or similar. You can't directly query via xp_cmdshell as that's just Windows commands from there.

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

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 12:36:36

i think OP got confused between EXEC/sp_executesql and xp_cmdshell. I dont think there's any need of xp_cmdshell here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

CSnoek
Starting Member

3 Posts

Posted - 2011-09-20 : 12:39:42

quote:
Originally posted by visakh16

you're trying to execute sql query from command shell?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

OOOOPS, sorry guys, thanks for pointing this out.
Been so knee deep in other troubles that I stopped thinking logically.

Man, do I feel stupid.

Corrected the code and now it is working.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 12:57:02

ok thats fine. Glad that you sorted it out.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources