(SQL2K) -2147467259, xp_cmdshell failed to execute because current security context is not sysadmin and proxy account is not setup correctly. For more information, refer to Book Online, search for topic related to xp_sqlagent_proxy_account.
The above error message is generated as a result of Access VBA (below) calling the attached code snippet/SP:
sSQL = "exec spm_create_stand_alone '" & sCase & "'"
rs = gcnCASES.Execute(sSQL).Fie
I could use some help getting past this, as my knowledge of SQL users/roles/proxy agents is nearly nil.
CREATE PROC spm_create_stand_alone(@cases_id varchar(50)) AS
SET NOCOUNT ON
-- TESTING ONLY
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)
SELECT @bail_message = 'Case ' + @cases_id + 'has not been extracted.'
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
-- This code processes if the deletion was unsuccessful based on any of the above criteria.
SELECT @bail_message as RETURN_VALUE