UPDATE EFILE SET SHIPPED = 'N' WHERE SHIPPED = 'X'
Update efile
set shipped = 'X' where (efikey = 'F' or efikey = 'O' or efikey = 'P') and shipped = 'N' 

exec master..xp_cmdshell 'dtsrun/NTS05/EFI FEDEX', no_output

quote:

---

exec master..xp_cmdshell 'dtsrun /SNTS05 /Uuser_name /Ppassword /NEFI FEDEX', no_output

---

quote:

---

That should clear things up.

---

It's a little clearer now. Thank you!!!

It's also working now!

I have another question...

I was reading Professional SQL Server 7.0 programming by Robert Vieira. He mentions locking down this XP because of potential security risks. Do I need to do that? If so, how?

Best regards,
Teresa

Edited by - TJ on 05/06/2002 12:10:36

CREATE PROCEDURE [NTS_FEDEXLabels] AS

--create the EFI FEDEX Labels
UPDATE EFILE SET SHIPPED = 'N' WHERE SHIPPED = 'X'
Update efile
set shipped = 'X' 
where (efikey = 'F' or efikey = 'O' or efikey = 'P') and shipped = 'N' 

--run the dts
exec master..xp_cmdshell 'dtsrun /Sserver/Uusername/Ppassword /NEFI FEDEX LABELS'

update efile 
set shipped = 'Y', shipdte = getdate() 
where shipped = 'X'

--create the QIK FEDEX Labels
Update efile 
set shipped = 'X' 
where efikey = 'R' and shipped = 'N'

--run the DTS
exec master..xp_cmdshell 'dtsrun /Sserver /Uusername /Ppassword /NQIK FEDEX LABELS'

update efile
set shipped = 'Y', shipdte = getdate() 
where efikey = 'R' and shipped = 'X'

quote:

---

I was reading Professional SQL Server 7.0 programming by Robert Vieira. He mentions locking down this XP because of potential security risks. Do I need to do that? If so, how?

---

...

CREATE TABLE #cmdshell_out (line_out varchar(1000))

insert into #cmdshell_out
exec master..xp_cmdshell 'dtsrun /Sserver/Uusername/Ppassword /NEFI FEDEX LABELS'

IF EXISTS (select * from #cmdshell_out WHERE line_out LIKE '% error%')
BEGIN 
   RAISERROR ('DTS package failed', 16, 1)
   RETURN 1
END

...

CREATE PROCEDURE [NTS_FEDEXLabels] AS
declare @retval int
set @retval = 1

--create the EFI FEDEX Labels
UPDATE EFILE SET SHIPPED = 'N' WHERE SHIPPED = 'X'
Update efile
set shipped = 'X' 
where (efikey = 'F' or efikey = 'O' or efikey = 'P') and shipped = 'N' 
  --check to see if there are any records to export to the CSV file
  select @@rowcount from efile where Shipped = 'X'
  if @@rowcount > 0 
  begin
    --run the dts
    CREATE TABLE #cmdshell_out2 (line_out varchar(1000))
    insert into #cmdshell_out2    
    exec @retval =  master..xp_cmdshell 'dtsrun /Sserver /Uusername /Ppassword /NEFI FEDEX LABELS'
    -- if @retval = 0 then it was successful, if @retval = 1 then it failed
    if @retval = 0 
    begin
      update efile 
      set shipped = 'Y', shipdte = getdate() 
      where shipped = 'X'
    end
    else
    IF EXISTS (select * from #cmdshell_out2 WHERE line_out LIKE '% error%')
      BEGIN 
        RAISERROR ('DTS package failed', 16, 1)
        RETURN 1
      END
      --print "The FEDEX Label process failed at EFI FEDEX Labels! Contact Teresa with this message information!"
   end
  
--create the QIK FEDEX Labels
Update efile 
set shipped = 'X' 
where efikey = 'R' and shipped = 'N'
  --check to see if there are any records to export to the QIK FEDEX CSV file     
    select @@rowcount from efile where shipped = 'X' 
    if @@rowcount > 0 
    begin
      --run the DTS
      exec @retval = master..xp_cmdshell 'dtsrun /Sserver /Uusername /Ppassword /NQIK FEDEX LABELS'
      if @retval = 0  --successful
      begin      
        update efile
        set shipped = 'Y', shipdte = getdate() 
        where efikey = 'R' and shipped = 'X'  
      end
      else
        Print "The FEDEX Label process failed at QIK FEDEX Labels! Contact Teresa with this message information!"
    end

quote:

---

It raises an error. But the only place I see it is in SQL Query Analyzer. Another person will be running this code from an ASP page. She'll need to see it, if it errors, and be able to tell me.
Can I access the #cmdshell_out2 table?

---

quote:

---

Well, #cmdshell_out2 gets discarded as soon as your stored procedure completes.

---

IF EXISTS (select * from ##cmdshell_out2 WHERE line_out LIKE '% error%')
      BEGIN 
        RAISERROR (50001, 10, 1)
        RETURN 1      
        EXEC xp_sendmail @recipients = 'Teresa J. Bishop;Tammy', 
          @message = 'The EFI FEDEX Label Process failed!',
	  @subject = 'EFI FEDEX LABEL'
      END

quote:

---

I did verify that xp_sendmail is in the master db.

---

IF EXISTS (select * from ##cmdshell_out2 WHERE line_out LIKE '% error%')
      BEGIN 
        RAISERROR (50001, 10, 1)
        EXEC master.dbo.xp_sendmail @recipients = 'Teresa J. Bishop;Tammy', 
          @message = 'The EFI FEDEX Label Process failed!',
	  @subject = 'EFI FEDEX LABEL'
        RETURN 1      
      END

quote:

---

like I said, make sure that you call xp_sendmail BEFORE you issue RETURN command:

---

quote:

---

You're right... I apologize

---

Oh I didn't mean to sound annoyed there at all!!! I just wanted to make sure that you notice the problem. I've had quite a few times when things that were supposed to run -- didn't simply because I overlooked a misplaced RETURN statement somewhere in the code. So I wanted to save you the headache of having to look for it later on!

Cheers!

quote:

---

Cheers!

---

Thanks... I needed that... and when I get home, I'm going to have a few more!

New developments....

They don't want me to use the xp_sendmail. The new directions are to use begin transaction & commit transactions...

Thank you for all of your help!



Edited by - TJ on 05/07/2002 17:51:24

Edited by - TJ on 05/08/2002 09:17:31

declare @retval int
set @retval = 1

begin transaction 
--create the EFI FEDEX Labels
  Update efile
  set shipped = 'X' 
  where (efikey = 'F' or efikey = 'O' or efikey = 'P') and shipped = 'N' 
commit transaction

--check to see if there are any records to export to the CSV file     
select @@rowcount from efile where Shipped = 'X'
if @@rowcount > 0 
begin
  --run the dts   
  exec @retval = master..xp_cmdshell 'dtsrun /Sserver /Uusername /Ppassword /NEFI FEDEX LABELS'
  -- if @retval = 0 then it was successful, if @retval = 1 then it failed
  begin transaction
  if @retval = 0 --successful
    begin
      update efile 
      set shipped = 'Y', shipdte = getdate() 
      where (efikey = 'F' or efikey = 'O' or efikey = 'P') and shipped = 'X'      
    end  
  commit transaction   
  
  if @retval <> 0 --failed        
    begin
      update efile 
      set shipped = 'N', shipdte = getdate() 
      where (efikey = 'F' or efikey = 'O' or efikey = 'P') and shipped = 'X'      
    end
  
end
--create the QIK FEDEX Labels
BEGIN TRANSACTION 
  Update efile 
  set shipped = 'X' 
  where efikey = 'R' and shipped = 'N'
commit transaction

--check to see if there are any records to export to the QIK FEDEX CSV file     
select @@rowcount from efile where shipped = 'X' 
if @@rowcount > 0 
begin
    --run the DTS
    exec @retval = master..xp_cmdshell 'dtsrun /Sserver /Uusername /Ppassword /NQIK FEDEX LABELS'

  begin transaction
  if @retval = 0  --successful
    begin      
      update efile
      set shipped = 'Y', shipdte = getdate() 
      where efikey = 'R' and shipped = 'X'  
    end
  COMMIT TRANSACTION 

  if @retval <> 0 --failed       
    begin
      update efile 
      set shipped = 'N', shipdte = getdate() 
      where efikey = 'R' and shipped = 'X'      
    end
end  

declare @retval int
set @retval = 1

--create the EFI FEDEX Labels
  Update efile
  set shipped = 'X' 
  where (efikey = 'F' or efikey = 'O' or efikey = 'P') and shipped = 'N' 

--check to see if there are any records to export to the CSV file     

if EXISTS (select * from efile where Shipped = 'X')  
begin
  --run the dts   
  exec @retval = master..xp_cmdshell 'dtsrun /Sserver /Uusername /Ppassword /NEFI FEDEX LABELS'
  -- if @retval = 0 then it was successful, if @retval = 1 then it failed
  if @retval = 0 --successful
    begin
      update efile 
      set shipped = 'Y', shipdte = getdate() 
      where (efikey = 'F' or efikey = 'O' or efikey = 'P') and shipped = 'X'   
    end   
  else  --failed        
    begin
      update efile 
      set shipped = 'N', shipdte = getdate() 
      where (efikey = 'F' or efikey = 'O' or efikey = 'P') and shipped = 'X'      
    end
end

--create the QIK FEDEX Labels
  Update efile 
  set shipped = 'X' 
  where efikey = 'R' and shipped = 'N'

--check to see if there are any records to export to the QIK FEDEX CSV file     
if EXISTS (select * from efile where shipped = 'X') 
begin
    --run the DTS
  exec @retval = master..xp_cmdshell 'dtsrun /Sserver /Uusername /Ppassword /NQIK FEDEX LABELS'

  if @retval = 0  --successful
    begin      
      update efile
      set shipped = 'Y', shipdte = getdate() 
      where efikey = 'R' and shipped = 'X'  
    end
  else --failed       
    begin
      update efile 
      set shipped = 'N', shipdte = getdate() 
      where efikey = 'R' and shipped = 'X'      
    end
end  

quote:

---

you aren't doing any error handling in your code, and aren't trying to ROLLBACK anywhere, so you don't need any user-defined transactions at all.

---

Should I be? What is the best practice to employ?

quote:

---

IF EXISTS statement would be more appropriate there.

---

I'll implement the changes you suggested.

Thanks again for sticking with me and lending your expertise!

Teresa