variables in stored procs with xp_cmdshell

Question

question

Tom Redd asked Apr 30, '10

variables in stored procs with xp_cmdshell

Here is part of a script that I wrote that moves files from one server to another using sql.

EXEC master..xp_cmdshell 'net use T: \memdsa1\staging_area @pwd /user:@domainuser/persistent:no'
EXEC master..xp_cmdshell 'net use S: \memdev01\stagingarchive @pwd /user:@domainuser/persistent:no'

I want to know a way that you can package this into a stored proc and pass the @pwd variable my password and @domainuser variable my domain\user.

Right now, I am hardcoding it running the script and then changing it back.

stored-procedures xp-cmdshell

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-05-01T15:08:17Z

TimothyAWiseman answered May 1, '10

Sure, just use dynamic SQL. It would look something like

declare @cmd nvarchar(128)

set @cmd = 'net use T: \memdsa1\staging_area ' + @pwd + '/user:' + @domainuser+ '/persistent:no'

exec master..xp_cmdshell @cmd

set @cmd = 'net use S: \memdev01\stagingarchive ' + @pwd + '/user:'+@domainuser'+/persistent:no'
exec master..xp_cmdshell @cmd

If you haven't already, you might want to read The Curse and Blessing of Dynamic SQL by Sommarskog. One of the most important SQL articles written.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question