SQL xp_cmdshell COPY inside of a loop.

Question

SQL xp_cmdshell COPY inside of a loop.

amiller-888168

SSC Veteran

Points: 235
More actions
February 22, 2011 at 2:37 pm

#235442

This probably belongs in the newbie section, but maybe someone here can help...
I want to Copy and Rename a file to a Customer Account No. I want this file to be created for every customer account that is pulled from the SELECT statement. Here is what I have, but I am having difficulties understanding which loop I should be using, and how I should use it. I am able to use the xp_cmdshell successfully by itself, but once I try to put it into a loop, it seems it is the apostrophes that are getting messed up.
First, i had to enable the xp_cmdshell. You can do this by referring to
Here is the script I want to run successfully...
DECLARE @CustNo VARCHAR(MAX)
SET @CustNo = ''
DECLARE @SQLQUERY VARCHAR(MAX);
SET @SQLQUERY = ''
WHILE Exists (SELECT Cust_No FROM AR_CUST WHERE TERMS_COD = 'CASH')
SET @CustNo = (SELECT cust_no FROM AR_CUST WHERE TERMS_COD = 'CASH')
SET @SQLQUERY =
'xp_cmdshell 'COPY /Y C:\CounterPoint\Edwards\CustomerImages\100-100009.jpg /B C:\CounterPoint\Edwards\CustomerImages\'+@CustNo+'.jpg''
EXEC (@SQLQUERY);
end loop;
Thanks in advance,
Andy

Viewing 5 posts - 1 through 5 (of 5 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Double the single-quote right after xp_cmdshell, right before COPY.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

amiller-888168 SSC Veteran Points: 235 More actions · Answer 2

I did get the xp_cmdshell to work correctly by fixing the apostrophes. But how do I get the @CustNo variable to equal whatever value the loop is currently on?

DECLARE @CustNo VARCHAR(MAX)

SET @CustNo = ''

DECLARE @SQLQUERY VARCHAR(MAX);

SET @SQLQUERY = ''

WHILE Exists (select cust_no from AR_CUST where TERMS_COD = 'CASH')

SET @CustNo = 'whatever Cust_No the loop is currently on'

SET @SQLQUERY =

'xp_cmdshell ''COPY /Y C:\CounterPoint\Edwards\CustomerImages\100-100009.jpg /B C:\CounterPoint\Edwards\CustomerImages\'+@CustNo+'.jpg'''

EXEC (@SQLQUERY);

Lowell SSC Guru Points: 323754 More actions · Answer 3

here's a cursor example that is based on what you posted:

DECLARE @CustNo VARCHAR(MAX)

DECLARE @SQLQUERY VARCHAR(MAX);

SET @SQLQUERY = ''

declare c1 cursor for

select

CustNo

from YourTable

where someCriteria > 0

open c1

fetch next from c1 into @CustNo

While @@fetch_status <> -1

begin

SET @SQLQUERY =

'xp_cmdshell ''COPY /Y C:\CounterPoint\Edwards\CustomerImages\100-100009.jpg /B C:\CounterPoint\Edwards\CustomerImages\'+@CustNo+'.jpg'''

fetch next from c1 into @CustNo

end

close c1

deallocate c1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

amiller-888168 SSC Veteran Points: 235 More actions · Answer 4

Thanks All. Got it working.

DECLARE @CustNo VARCHAR(MAX)

DECLARE @SQLQUERY VARCHAR(MAX);

SET @SQLQUERY = ''

SET @CustNo = ''

declare c1 cursor for

select CUST_NO from AR_CUST where TERMS_COD = 'CASH'

open c1

fetch next from c1 into @CustNo

While @@fetch_status <> -1

begin

SET @SQLQUERY =

'xp_cmdshell ''COPY /Y C:\CounterPoint\Edwards\CustomerImages\100-100001.jpg /B C:\CounterPoint\Edwards\CustomerImages\'+@CustNo+'.jpg'''

EXEC (@SQLQUERY)

fetch next from c1 into @CustNo

end

close c1

deallocate c1