xp_cmdshell problem - 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 problem

Author

Topic

j_mact
Starting Member

17 Posts

Posted - 2004-01-09 : 15:32:32

All,
Having a very strange problem. I am generating a text file from a cursor in a stored procedure, based on two dates that a user enters (startDate,endDate). The strange thing is that, if they enter in any date before 1/1/04 as the startDate, everything works fine, but if I enter anything after that date for the start or end date, this command does not work:

set @cmd = 'move /y c:\sims\sims' + @schID + '\' + 'bas.* ' + ' \\SomeComputer\shared\folder\file.' + @schID
exec master..xp_cmdshell @cmd

I am looping through a cursor to build the file. I have a waitfor delay command after the loop, to make sure the file is finished writing. It almost seems like it is skipping this command when it does not work. Any suggestions would be a HUGE help.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2004-01-09 : 15:39:52

Your code doesn't reference start or end date at all - tell us how those 2 data points are relevant.

Jonathan
codesmith templates

j_mact
Starting Member

17 Posts

Posted - 2004-01-09 : 15:46:32

The start and end date variables are used in SQL statement that defines the cursor, and they are not used at all in the xp_cmdshell.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-09 : 15:52:19

Could you provide the rest of the code so that we can help out? Does the command work fine in a cmd window?

Tara

j_mact
Starting Member

17 Posts

Posted - 2004-01-09 : 15:56:51

The command does work in a commmand window. The file DOES get created from the cursor, but the if the endDate is after 1/1/04 the xp_cmdshell does NOT work
Below is the code:

CREATE PROCEDURE [dbo].[CreateBas] @schID varchar(50), @beginDate smalldatetime, @endDate smalldatetime AS
declare @outStr varchar(2000), @cmd varchar(2000),@studId varchar(12),
@lName varchar(18),@idLen varchar(20),@fName varchar(35),@gend varchar(1),
@homePh varchar(15),@ssn varchar(12),@bDate datetime, @bDint int,
@grade varchar(2),@bDYear int,@bDMonth int,@bDDay int,@bDStr varchar(8),
@bDStrMonth varchar(2), @bDStrDay varchar(2),@ethn varchar(4),@entryCode varchar(3),@enrollDate datetime,@enrollStr varchar(8),
@homeRoom varchar(4)
declare @nCnt int
--must delete old bas file or file will contain old records when trying to import into sims. creating directory
set @cmd = 'IF NOT EXIST c:\sims\sims' + @schID + ' md ' + 'c:\sims\sims' + @schID
exec master..xp_cmdshell @cmd
set @cmd = 'IF EXIST c:\sims\sims' + @schID + ' del c:\sims\sims' + @schID + '\' + 'bas.' + @schID
exec master..xp_cmdshell @cmd

--begin cursor to load string.
declare basCur cursor for
/*select 'V91',studentid,lastname,firstname, gender = case when gender = 'M' then '0' when gender = 'F' then '1' end,homephone,ssn,'',birthdate,grade,'TRAN',' ','1','20030811','E1' from students*/
--select studentid,lastname,firstname,gender,homephone,ssn,birthdate,grade from students where schoolid = @schID and enrolled = 1 and enrollchgdate = @addDate
select studentid,substring(lastname,1,18),substring(firstname,1,14),gender,homephone,ssn,birthdate,grade,ethnicity,entrywithcode,enrollchgdate,homeroom
from students
where schoolid = @schID and enrolled = 1 and enrollchgdate >= @beginDate and enrollchgdate <= @endDate and enrollchgdate is not null
open basCur
fetch next from basCur
into @studId,@lName,@fName,@gend,@homePh,@ssn,@bDate,@grade,@ethn,@entryCode,@enrollDate,@homeRoom
/*beginning while loop to fill string....*/
while @@FETCH_STATUS = 0
begin
--will have to create several loops to "pad out" fields for the sims file.
--Student number
set @idLen = @studId
if len(@idLen) < 9
begin
while len(@idLen) < 9
begin
set @idLen = '0' + @idLen
end
end

--Gender Field
if @gend = 'M'
begin
set @gend = '0'
end
else
set @gend = '1'
--must manipulate date field so Sims will read it correctly...
set @bDYear = year(@bDate)
set @bDMonth = month(@bDate)
set @bDDay = day(@bDate)
--must test to see if month and day need a "0" in front
set @bDStrMonth = cast(@bDMonth as varchar(2))
set @bDStrDay = cast(@bDDay as varchar(2))
if len(@bDStrMonth) = 1
begin
set @bDStrMonth = '0' + @bDStrMonth
end

if len(@bDStrDay) = 1
begin
set @bDStrDay = '0' + @bDStrDay
end
--creating string for date....
set @bDStr = cast(@bdYear as varchar(4)) + @bDStrMonth
+ @bDStrDay

--must do the same thing for the enrollment date....
set @bDYear = year(@enrollDate)
set @bDMonth = month(@enrollDate)
set @bDDay = day(@enrollDate)

--must test to see if month and day need a "0" in front
set @bDStrMonth = cast(@bDMonth as varchar(2))
set @bDStrDay = cast(@bDDay as varchar(2))
if len(rtrim(ltrim(@bDStrMonth))) = 1
begin
set @bDStrMonth = '0' + @bDStrMonth
end

if len(@bDStrDay) = 1
begin
set @bDStrDay = '0' + @bDStrDay
end

set @enrollStr = cast(@bdYear as varchar(4)) + @bDStrMonth
+ @bDStrDay

--need to pad out first and last name fieds
set @lName = rtrim(@lName)
set @fName = rtrim(@fName)
--reset counter
set @nCnt = 0
set @nCnt = len(@lName)
if @nCnt < 18
begin
set @lName = @lName + space(18-@nCnt)
end

set @nCnt = len(@fName)
if @nCnt < 14
begin
set @fName = @fName + space(14-@nCnt)
end

--will go away, but for testing purposes, must feed something into entrywithcode...
if @entryCode = NULL
begin
set @entryCode = 'E9'
end

--must test to see if homeroom is available. If not, we will hard code to "TRAN"

if @homeRoom = NULL
begin
set @homeRoom = 'TRAN'
end

set @nCnt = len(@homeRoom)
if @nCnt < 4
begin
set @homeRoom = @homeRoom + space(14-@nCnt)
end

--must change format of phone number. they are stored with paran. around area code. (919)....
--set @homeph = substring(@homeph,2,2) + substring(@homeph,5,10)
--set @homeph = substring(@homeph,2,3) + substring(@homeph,6,8) not storing with parens

set @outStr = 'V91' + @idLen + @lName + @fName + @gend + ltrim(rtrim(@homePh))
+ @ssn + ' ' + @bDStr + @grade + @homeRoom + ' ' + '1'
+ @enrollStr + @entryCode

--set @cmd = 'echo ' + @outStr + ' >> c:\sims\sims' + @schID + '\' + 'bas.' + @schID
set @cmd = 'echo ' + @outStr + ' >> c:\sims\sims' + @schID + '\' + 'bas.' + @schID
exec master..xp_cmdshell @cmd

--must try to change code to import different fields....

set @outStr = 'V28' + @idLen +'0000' + @ssn

set @cmd = 'echo ' + @outStr + ' >> c:\sims\sims' + @schID + '\' + 'bas.' + @schID
exec master..xp_cmdshell @cmd

--one more time to get ethnicity....
set @nCnt = 0
set @fName = rtrim(@fName)
set @nCnt = len(@fName)

if @nCnt < 32
begin
set @fName = @fName + space(32-@nCnt)
end

set @outStr = 'V03' + @idLen + @lName + @fName + ' ' + @ethn

set @cmd = 'echo ' + @outStr + ' >> c:\sims\sims' + @schID + '\' + 'bas.' + @schID
exec master..xp_cmdshell @cmd

fetch next from basCur
into @studId,@lName,@fName,@gend,@homePh,@ssn,@bDate,@grade,@ethn,@entryCode,@enrollDate,@homeRoom
insert into testtable (testmsg) values ('looping')
end

insert into testtable (testmsg) values ('out of loop')

waitfor delay '000:00:05'
insert into testtable (testmsg) values ('did wait')
--set @cmd = 'move /y c:\sims\sims' + @schID + '\' + 'bas.* ' + ' \\MyComputer\shared\sims\sims' + @schID
set @cmd = 'move /y c:\sims\sims' + @schID + '\' + 'bas.* ' + ' \\MyComputer\shared\sims\sims' + @schID
--set @cmd ='echo "hello world!" > c:\testsol.txt'
exec master..xp_cmdshell @cmd

close basCur
deallocate basCur

/*set @bcpCommand = 'bcp "select * from students where schoolid =' + '''' + @schID + '''' + '" queryout c:\sims\result.txt -U sisapp -P sisapp -c'*/
/*set @bcpCommand = 'bcp "select'+ '''V91'''+ ',studentid,lastname,firstname, gender = case when gender ='+ '''M''' + ' then'+'''0'''+'when gender ='+'''F'''+'then'+'''1'''+'end,homephone,ssn,'+ '''' + '''' + ',birthdate,grade,'+'''TRAN'''+','+''''+''''+','+'''1'''+','+'''20030811'''+','+'''E1'''+' from students where SchoolId =' + '''' + @schID + '''' + '" queryout c:\sims\result.txt -U sisapp -P sisapp -c'*/

/*exec master..xp_cmdshell @bcpCommand*/
--set @cmd = 'echo " ' + @outStr + ' "> c:\sims\vartest.txt'
--exec master..xp_cmdshell @cmd
/*Print @bcpCommand*/
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-09 : 16:01:04

Well what goes into @schID? You should PRINT out @cmd prior to the EXECution of it that way you can see what the command is.

Tara

j_mact
Starting Member

17 Posts

Posted - 2004-01-09 : 16:03:26

The @schID is another paramater that contains the number of a school.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-09 : 16:06:00

PRINT out @cmd to make sure that the command is being built correctly. Then copy the output of the PRINT statement into a cmd window and see if it runs.

Tara

j_mact
Starting Member

17 Posts

Posted - 2004-01-12 : 08:38:48

The problem is we can't print out the command, because the code stops executing before the command is run. We put some print statements in the code, and the print statements around the xp_cmdshell (after the while loop) are not being executed. All rows are being returned properly, but the program terminates after the while loop executes.

j_mact
Starting Member

17 Posts

Posted - 2004-01-12 : 11:49:40

By the way, this stored procedure works fine if I run it from query analyzer, but when I run it from a call in VB, that's when the SP stops in mid-execution.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-12 : 12:18:36

If it is terminating before completion, then what is the error message? If it works inside Query Analyzer, then you should run SQL Profiler to determine what the difference is between the VB call and the QA call.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources