Passing variable into xp_cmdshell

Question

Passing variable into xp_cmdshell

guido1971

SSC Enthusiast

Points: 187
More actions
January 22, 2020 at 10:10 am
Go to Answer
#3717291
Hi all, I need your help.
I need passing variable into xp_cmdshell.
This is my code :
```
DECLARE @cmd VARCHAR (100)
SET @cmd = 'D:\inetpub\wwwroot\myFile\myFile_' + CONVERT (VARCHAR, YEAR(GETDATE()), 23) + '.txt' PRINT @cmd


EXEC master.dbo.sp_configure 'show advanced options',
1 RECONFIGURE EXEC master.dbo.sp_configure 'xp_cmdshell',
1 RECONFIGURE EXEC xp_cmdshell 'bcp "SELECT * FROM mytable WHERE YEAR (myDate) = YEAR (GETDATE());" queryout @cmd -T -c -t;'
```
But I have error :
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
How to do resolve this ?
Please can you help me ?
Thank you in advance for any help.

John Mitchell-245523

SSC Guru

Points: 148809
More actions
January 22, 2020 at 11:44 am
Answer
#3717323
Oh, I see what's happened. The xp_cmdshell process can't see your @cmd variable, so you need to insert it into your string before you run xp_cmdshell.
```
SET @cmd = 'bcp "SELECT * FROM mytable WHERE YEAR (myDate) = YEAR (GETDATE());" queryout ' + @cmd + ' -T -c -t;'
```
John

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

As long as the bcp command was actually executed, you're fine. You always get that "Configuration option..." informational message when you change a setting with sp_configure.

John

guido1971 SSC Enthusiast Points: 187 More actions · Answer 2

Thank you fo reply.

But the output file :

'D:\inetpub\wwwroot\myFile\myFile_' + CONVERT (VARCHAR, YEAR(GETDATE()), 23) + '.txt'

it was not generated

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP host data-file

This reply was modified 4 years, 3 months ago by guido1971.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

When you run xp_cmdshell, the command is shelled out to the SQL Server service account. It doesn't run in the context of your own login. Therefore you need to make sure that the service account has access to create files in the D:\inetpub\wwwroot\myFile folder.

John

guido1971 SSC Enthusiast Points: 187 More actions · Answer 4

I have access on folder because if try this code the output is correctly generated.

EXEC master.dbo.sp_configure 'show advanced options',
1 RECONFIGURE EXEC master.dbo.sp_configure 'xp_cmdshell',
1 RECONFIGURE EXEC xp_cmdshell 'bcp "SELECT * FROM mytable WHERE YEAR (myDate) = YEAR (GETDATE());" queryout "D:\inetpub\wwwroot\myFile\myFile_2020.txt" -T -c -t;'

guido1971 SSC Enthusiast Points: 187 More actions · Answer 5

guido1971

SSC Enthusiast

Points: 187

January 22, 2020 at 11:46 am

#3717325

thank you!