Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention. Check out this how-to article for more information.
CREATE PROC spm_create_stand_alone(@cases_id varchar(50)) AS SET NOCOUNT ON /* -- TESTING ONLY use dbname Declare @cases_id varchar(50) SELECT @cases_id = '15322 FOO-3' */ Declare @bail_message varchar(100) -- Make sure the case was pushed IF NOT EXISTS (SELECT cases_id FROM CLOG_CASE_LOG WHERE cases_id=@cases_id) begin SELECT @bail_message = 'Case ' + @cases_id + 'has not been extracted.' goto bail end DELETE FROM MY_TABLE INSERT INTO MY_TABLE (CASES_ID) VALUES (@cases_id) -- Run the DTS package that writes to the Access database exec master.dbo.xp_cmdshell 'dtsrun /Sserver /Utheuser /Pthepassword /Nthedtspackage, no_output SELECT '1' as RETURN_VALUE goto ex -- This code processes if the deletion was unsuccessful based on any of the above criteria. bail: begin SELECT @bail_message as RETURN_VALUE goto ex end ex: GO
Join the community of 500,000 technology professionals and ask your questions.