Message --"Configuration option 'show advanced options' changed from 0 to 1. Run the RECO

i am working on storedprocedures and after the Succesful parsing of the procedure .. on executing its giving me 3 msgs ....

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

Configuration option 'xp_cmdshell' changed from 1 to 1. Run the RECONFIGURE statement to install.

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

and not displaying me the output.......

The Procedure i am executing is as follows

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE PROCEDURE [dbo].[uspRcopiaExport12] AS

DROP TABLE Skagway..RCOPIA_EXPORT

-- Get Patients' Home Addresses

/*

SELECT pa.PersonID, pa.Street1, pa.Street2, pa.City, pa.State, Skagway.dbo.StripNonDigits(pa.ZipCode) AS Zip

INTO #ADDRESS

FROM Person..PHYSICAL_ADDRESS pa

INNER JOIN Person..PHYSICAL_ADDRESS_DESC pad ON (pa.PhysicalAddrDescID = pad.PhysicalAddrDescID)

WHERE pad.PhysicalAddrDesc = 'Home'

-- Get Patients' Home Phone Numbers

SELECT tn.PersonID, tn.TelecomNumber

INTO #HOME_PHONE

FROM Person..TELECOM_NUMBER tn

INNER JOIN Person..TELECOM_NUMBER_DESC tnd ON (tn.TelecomNumDescID = tnd.TelecomNumDescID)

INNER JOIN Person..TELECOM_DEVICE_TYPE tdt ON (tn.TelecomDevTypeID = tdt.TelecomDevTypeID)

WHERE tnd.TelecomNumDesc = 'Home'

ORDER BY tdt.TelecomDevTypeID

-- Get Patients' Work Phone Numbers

SELECT tn.PersonID, tn.TelecomNumber

INTO #WORK_PHONE

FROM Person..TELECOM_NUMBER tn

INNER JOIN Person..TELECOM_NUMBER_DESC tnd ON (tn.TelecomNumDescID = tnd.TelecomNumDescID)

INNER JOIN Person..TELECOM_DEVICE_TYPE tdt ON (tn.TelecomDevTypeID = tdt.TelecomDevTypeID)

WHERE tnd.TelecomNumDesc = 'Work'

ORDER BY tdt.TelecomDevTypeID

*/

-- Get SCP patient demographic data

SELECT 'update_patient_4' AS DataType,

p.FirstName AS FIRST_NAME,

p.MiddleInit AS MIDDLE_INITIAL,

p.LastName AS LAST_NAME,

CASE p.Gender

WHEN 'm' THEN 'M'

WHEN 'f' THEN 'F'

WHEN 'M' THEN 'M'

WHEN 'F' THEN 'F'

ELSE 'U'

END AS SEX,

-- (SELECT TOP 1 Street1 FROM #ADDRESS WHERE PersonID = p.PersonID) AS ADDRESS_LINE_1,

-- (SELECT TOP 1 Street2 FROM #ADDRESS WHERE PersonID = p.PersonID) AS ADDRESS_LINE_2,

-- (SELECT TOP 1 City FROM #ADDRESS WHERE PersonID = p.PersonID) AS CITY,

-- (SELECT TOP 1 State FROM #ADDRESS WHERE PersonID = p.PersonID) AS STATE_CODE,

-- (SELECT TOP 1 Zip FROM #ADDRESS WHERE PersonID = p.PersonID) AS ZIP,

-- (SELECT TOP 1 TelecomNumber FROM #HOME_PHONE WHERE PersonID = p.PersonID) AS PHONE,

-- (SELECT TOP 1 TelecomNumber FROM #WORK_PHONE WHERE PersonID = p.PersonID) AS WORK_PHONE,

' 'AS ADDRESS_LINE_1,

' 'AS ADDRESS_LINE_2,

' 'AS CITY,

' 'AS STATE_CODE,

' 'AS ZIP,

' 'AS PHONE,

' 'AS WORK_PHONE,

' ' AS OTHER_PHONE,

-- ea.EmailAddress AS EMAIL,

' ' AS EMAIL,

p.BirthDate AS BIRTHDATE,

p.SocSecNum AS SOCIAL_SECURITY,

' ' AS INSURANCE,

' ' AS LAST_VISIT_DATE,

'PHMS-SCP-' + CAST(p.PersonID AS VARCHAR(8)) AS EXTERNAL_ID,

' ' AS PROVIDER_FIRST_NAME,

' ' AS PROVIDER_MIDDLE_NAME,

' ' AS PROVIDER_LAST_NAME

INTO Skagway..RCOPIA_EXPORT

FROM Skagway..PERSON p

INNER JOIN Skagway..EMAIL_ADDRESS ea ON (p.PersonID=ea.PersonID)

INNER JOIN Skagway..CARE_TEAM_ASSIGNMENT cta ON (p.PersonID=cta.PersonID)

INNER JOIN Skagway..SHARED_CARE_PLAN scp ON (cta.CarePlanID=scp.CarePlanID)

WHERE scp.SCPInactiveFlag <> 1

AND cta.CareTeamRoleID = 23

AND p.BirthDate IS NOT NULL

AND p.SocSecNum IS NOT NULL

AND NOT p.SocSecNum IN ('999999999', '')

-- Replace all NULLs with single spaces (required by Rcopia for import)

UPDATE Skagway..RCOPIA_EXPORT

SET MIDDLE_INITIAL = ' '

WHERE MIDDLE_INITIAL IS NULL OR MIDDLE_INITIAL = ''

--UPDATE Skagway..RCOPIA_EXPORT

-- SET PHONE = ' '

-- WHERE PHONE IS NULL OR PHONE = ''

--UPDATE Skagway..RCOPIA_EXPORT

-- SET WORK_PHONE = ' '

-- WHERE WORK_PHONE IS NULL OR WORK_PHONE = ''

--UPDATE Skagway..RCOPIA_EXPORT

-- SET ADDRESS_LINE_1 = ' '

-- WHERE ADDRESS_LINE_1 IS NULL OR ADDRESS_LINE_1 = ''

--UPDATE Skagway..RCOPIA_EXPORT

-- SET ADDRESS_LINE_2 = ' '

-- WHERE ADDRESS_LINE_2 IS NULL OR ADDRESS_LINE_2 = ''

--UPDATE Skagway..RCOPIA_EXPORT

-- SET CITY = ' '

-- WHERE CITY IS NULL OR CITY = ''

--UPDATE Skagway..RCOPIA_EXPORT

-- SET STATE_CODE = ' '

-- WHERE STATE_CODE IS NULL OR STATE_CODE = ''

--UPDATE Skagway..RCOPIA_EXPORT

-- SET ZIP = ' '

-- WHERE ZIP IS NULL OR ZIP = ''

--UPDATE Skagway..RCOPIA_EXPORT

-- SET EMAIL = ' '

-- WHERE EMAIL IS NULL OR EMAIL = ''

--UPDATE Skagway..RCOPIA_EXPORT

-- SET SOCIAL_SECURITY = ' '

-- WHERE SOCIAL_SECURITY IS NULL OR SOCIAL_SECURITY = ''

-- Create the pipe-delimited ascii file on the HiNet server

DECLARE @.cmd varchar(1000)

--DECLARE @.pwd varchar(30)

--SELECT @.pwd = (SELECT Password FROM master..Passwords WHERE UserName = 'DBO_HelpDesk')

DECLARE @.FileName varchar(100)

--SET @.FileName = '"\\phins\d$\scppatients.txt"' -- Can't get to HiNet at the moment, create it on PHINS

-- SET @.FileName = '"\\x.xx.x.x\phms\phmspatients.txt"'

SET @.FileName = '"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\1.txt"'

--SELECT @.cmd = 'master..xp_cmdshell ' + char(39) + 'bcp Skagway..RCOPIA_EXPORT out ' + @.FileName + ' -c -t"|" -UDBO_HelpDesk -P' + @.pwd + ' -S' + @.@.SERVERNAME + char(39)

SELECT @.cmd = 'master..xp_cmdshell ' + char(39) + 'bcp Skagway..RCOPIA_EXPORT out ' + @.FileName + ' -c -t"|" -T -S' + @.@.SERVERNAME + char(39)

EXEC (@.cmd)

--DROP TABLE #ADDRESS

--DROP TABLE #HOME_PHONE

--DROP TABLE #WORK_PHONE

-

The Query i am executing after this is

USE master

GO

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sp_configure 'xp_cmdshell', 1

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sp_configure 'show advanced options', 0

GO

and the output i m looking for is like this ......

-- <Fst name><mid name><lst name> < DOB><SSN> <ID>

update_patient_4|Dawn|M|Gauthier|F| | | | | | | | | |08/30/1974|370845156| | |SCP-2| | |
update_patient_4|Keith| |Robinson|M| | | | | | | | | |09/28/1948|537523557| | |SCP-9| | |

I moved this thread from SSIS forum; I think the T-SQL one is a better place for this