Xp_CmdShell - 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 2000 Forums

SQL Server Development (2000)

Xp_CmdShell

Author

Topic

Maysam
Starting Member

8 Posts

Posted - 2009-07-02 : 16:56:56

Hey guys,

I want to create an Excel file for each record inserted into a table. Therefore, I decided to use Xp_cmdShell extended stored procedure. However, I faced a very strange problem .

It seems that Xp_cmdshell does not work with variables.
This is my code :

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

set @l_param = 'Execute master..xp_cmdshell' + char(39)+' bcp "Select * from TblName where integrationID='+ rtrim(@l_ID) +'" queryout '+rtrim(@l_genFileName)+' -U username -P password -c '+ char(39)
execute sp_executesql @l_param
--select @l_param
--------------------------------------------------------------------

This command does not work , I do not recieve any error message, it just takes for ever. but when I retrieve the command (@l_param) , copy the command and execute it , it works.

Any ideas how to fix this problem ?

Thank you.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-03 : 01:38:56

set @l_param = ' bcp "Select * from TblName where integrationID='+ rtrim(@l_ID) +'" queryout '+rtrim(@l_genFileName)+' -U username -P password -c '+ char(39)
Execute master..xp_cmdshell @l_param

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-03 : 02:32:24

First print @l_param and see the result

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-03 : 02:45:51

quote:
Originally posted by madhivanan

First print @l_param and see the result

Madhivanan

Failing to plan is Planning to fail

quote:
but when I retrieve the command (@l_param) , copy the command and execute it , it works.

It is already done.

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-03 : 02:51:50

quote:
Originally posted by webfred

quote:
Originally posted by madhivanan

First print @l_param and see the result

Madhivanan

Failing to plan is Planning to fail

quote:
but when I retrieve the command (@l_param) , copy the command and execute it , it works.

It is already done.

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks. I forgot to note that

Madhivanan

Failing to plan is Planning to fail

Maysam
Starting Member

8 Posts

Posted - 2009-07-03 : 12:07:58

@webfred :
Xp_CmdShell does not accept any variables therefore I have to use sp_ExecuteSQL

@Madhivana : First print @l_param and see the result
I did that , the command works correctly.

Maysam
Starting Member

8 Posts

Posted - 2009-07-03 : 12:10:48

It seems there are some sort of permission problem .

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-03 : 15:16:58

quote:
Originally posted by Maysam

@webfred :
Xp_CmdShell does not accept any variables therefore I have to use sp_ExecuteSQL

Execute master..xp_cmdshell @l_param
Something like this above I have already used and it worked very well.
Why do you mean that xp_cmdshell does not accept any variables?

No, you're never too old to Yak'n'Roll if you're too young to die.

Maysam
Starting Member

8 Posts

Posted - 2009-07-03 : 15:31:50

quote:
Originally posted by webfred

quote:
Originally posted by Maysam

@webfred :
Xp_CmdShell does not accept any variables therefore I have to use sp_ExecuteSQL

Execute master..xp_cmdshell @l_param
Something like this above I have already used and it worked very well.
Why do you mean that xp_cmdshell does not accept any variables?

No, you're never too old to Yak'n'Roll if you're too young to die.

I mean
Execute master..xp_cmdshell ' bcp "Select * from TblName where integrationID='+ rtrim(@l_ID) +'" queryout '+rtrim(@l_genFileName)+' -U username -P password -c '

will not work , I mean variable in the body of command , @l_param is one parameter , that's fine but you can not create the command using prameters , that will not work.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-03 : 15:55:19

Hello Maysam,
maybe it is the heat and my brain is cooked.
But one time again.
I have recently used:
declare a variable
Built command string in that variable
Use it: Execute master..xp_cmdshell @reay_built_variable

Also see:
http://weblogs.sqlteam.com/tarad/archive/2004/03/29/1129.aspx

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

Maysam
Starting Member

8 Posts

Posted - 2009-07-03 : 16:09:57

:) Ok try these two commands

1 -
declare @param varchar(100)
Set @param = 'I love 4th of July'
execute master..xp_CmdShell 'echo '+@param

2 -
execute master..xp_CmdShell 'echo I love 4th of July'

The first coomand will not work

Maysam
Starting Member

8 Posts

Posted - 2009-07-03 : 16:10:53

Sorry, I checked the link late :D
That's what I mean :D

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-03 : 16:28:23

I cannot see the problem in building the complete string in a variable and passing it to the xp_cmdshell