|
subhi
Starting Member
9 Posts |
 Posted - 2010-02-25 : 14:56:15
|
| I am new to sql programming but i know pl/sql .in oracle can use utl_file function to create the output of the query or pl/sql processing to a flat file. how can i do it in sql. I have pasted the code below for the example i tried . which said query was executed successfully but the file is not created.Declare @scheduledJobStage2ID bigint;declare @scheduledJobStage1ID bigint; DECLARE @ERRMSG NVARCHAR(MAX);DECLARE @ERRSEVERITY INT;DECLARE @ERRORNUMBER INT;DECLARE @ERRORPROCEDURE VARCHAR(1000);DECLARE @ERRORSTATE INT;DECLARE @ERRORLINE INT;DECLARE @errorCount AS BIGINT;DECLARE @IS_MAJOR_ERROR INT;DECLARE @SCHEDULED_JOB_ID BIGINT;DECLARE @INTERNAL_KNOWN_ERROR_CODE_ID INT;DECLARE @EXTERNAL_KNOWN_ERROR_CODE_ID INT; DECLARE @LINE_NUMBER BIGINT;DECLARE @ERROR_SUMMARY VARCHAR(MAX);DECLARE @DETAIL_ERROR VARCHAR(MAX);DECLARE @ERROR_LINE_CONTENTS VARCHAR(MAX);DECLARE @STAGE2_TABLE_NAME_ID BIGINT;DECLARE @STG2_ID INT;DECLARE @isNullable INT; DECLARE @nullCheck VARCHAR(MAX);DECLARE @strCONTENTS AS VARCHAR(MAX);DECLARE @reqCheck VARCHAR(MAX);DECLARE @reqCheckLoopCount int;DECLARE @ParmDefinition NVARCHAR(MAX)DECLARE @reqColName VARCHAR(100);Declare @data_file varchar(100);DECLARE @SQLqueryStr VARCHAR(max);declare @sql varchar(max);declare @bcpcommand varchar(max);--*********************************************************************************************************-- 1. Select [EXTERNAL_DATA_SOURCE_ID] from the table "[SCHEDULED_JOB]" based on "@scheduledJobStage2ID" -- received from the parameters passed (For Scheduled Job in Stage2 Outbound). -- select the columns from the inbound stage 1 table to create the file --*********************************************************************************************************SET @errorCount = 0;set @scheduledJobStage2ID =14074;set @scheduledJobStage1ID=14075;BEGIN TRY SET @nullCheck = NULL; SET @reqCheck=NULL; SET @reqCheckLoopCount = 0;set @data_file =REPLACE('c:\authors_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-'); /* set @sqlquerystr= ' SELECT -- @EDSID = [EXTERNAL_DATA_SOURCE_ID] HDR, FTR, RS_REFERRAL_ID, RS_BUDGET_GROUP_NO_FAMILY_NO, RS_MEDICAID_RECIPIENTS_COUNTY, RS_CSES_CASE_ID, RS_CSES_CASE_STATUS, RS_CASEWORKER_LAST_NAME, RS_CASEWORKER_FIRST_NAME, RS_CASEWORKER_MIDDLE_NAME, RS_CASEWORKER_SUFFIX, RS_CASEWORKER_PHONE_NUMBER, RS_CASEWORKER_COUNTY, RS_DATE_REFERRED_TO_CSES, RS_PAYMENT_CATEGORY, RS_MEDICAID_CASE_STATUS, RS_GOOD_CAUSE_EXISTS, RS_GOOD_CAUSE_APPROVED, RS_ASSIGNMENT_OF_RIGHTS_DATE, CPS_MEDICAID_CP_ID, CPS_CSES_MEMBER_ID, CPS_LAST_NAME, CPS_FIRST_NAME, CPS_MIDDLE_NAME, CPS_SUFFIX, CPS_MAIDEN_NAME, CPS_SSN, CPS_DATE_OF_BIRTH, CPS_GENDER, CPS_RACE, CPS_CP_AND_NCP_LIVING_TOGETHER, CPS_CP_NON_COOPERATIVE, CPS_RES_ADDRESS_LINE_1, CPS_RES_ADDRESS_LINE_2, CPS_RES_ADDRESS_LINE_3, CPS_RES_CITY, CPS_RES_STATE, CPS_RES_ZIP, CPS_HOME_PHONE, CPS_MAIL_ADDRESS_LINE_1, CPS_MAIL_ADDRESS_LINE_2, CPS_MAIL_ADDRESS_LINE_3, CPS_MAIL_CITY, CPS_MAIL_STATE, CPS_MAIL_ZIP, CPS_EMPLOYER_NAME, CPS_EMPLOYER_ADDRESS_LINE_1, CPS_EMPLOYER_ADDRESS_LINE_2, CPS_EMPLOYER_CITY, CPS_EMPLOYER_STATE, CPS_EMPLOYER_ZIP, CPS_WORK_PHONE, CPS_WORK_SHIFT, CPS_CURRENT_MARITAL_STATUS, CPS_SPOUSE_LAST_NAME, CPS_SPOUSE_FIRST_NAME, CPS_SPOUSE_MIDDLE_NAME, CPS_SPOUSE_SUFFIX, CPS_DATE_OF_MARRIAGE, CPS_MARRIAGE_COUNTRY, CPS_MARRIAGE_CITY, CPS_MARRIAGE_STATE, CPS_DATE_OF_DIVORCE, CPS_DATE_OF_DEATH, CPS_ATTORNEYS_LAST_NAME, CPS_ATTORNEYS_FIRST_NAME, CPS_ATTORNEYS_MIDDLE_NAME, CPS_ATTORNEYS_SUFFIX, CPS_ELIGIBILITY_START_DATE, CPS_ELIGIBILITY_END_DATE, CS01, CS02, CS03, CS04, CS05, CS06, CS07, CS08, CS09, CS10, CS11, CS12, CS13, CS14, CS15, CS16, CS17, CS18, CS19, CS20, CS21, CS22, CS23, CS24, CS25, NCPS_REFERRAL_ID, NCPS_BUDGET_GROUP_NUMBER, NCPS_CSES_CASE_ID, NCPS_CSES_MEMBER_ID, NCPS_LAST_NAME, NCPS_FIRST_NAME, NCPS_MIDDLE_NAME, NCPS_SUFFIX, NCPS_ALIAS, NCPS_NICKNAME, NCPS_MAIDEN_NAME, NCPS_SSN, NCPS_DATE_OF_BIRTH, NCPS_DATE_OF_DEATH, NCPS_COUNTRY_OF_BIRTH, NCPS_CITY_OF_BIRTH, NCPS_STATE_OF_BIRTH, NCPS_GENDER, NCPS_RACE, NCPS_DRIVERS_LICENSE_NUMBER, NCPS_ISSUE_DATE, NCPS_EXPIRATION_DATE, NCPS_STATE, NCPS_CURRENT_MARITAL_STATUS, NCPS_HEIGHT_FT, NCPS_HEIGHT_IN, NCPS_WEIGHT_LBS, NCPS_HAIR_COLOR, NCPS_EYE_COLOR, NCPS_IDENTIFYING_MARK_SCARS, NCPS_RESIDENTIAL_ADDRESS_LINE_1, NCPS_RESIDENTIAL_ADDRESS_LINE_2, NCPS_RESIDENTIAL_ADDRESS_LINE_3, NCPS_RESIDENTIAL_ADDRESS_CITY, NCPS_RESIDENTIAL_ADDRESS_STATE, NCPS_RESIDENTIAL_ADDRESS_ZIP, NCPS_RESIDENTIAL_ADDRESS_DATE_LAST_LIVED, NCPS_RESIDENTIAL_ADDRESS_HOME_PHONE, NCPS_MAILING_ADDRESS_LINE_1, NCPS_MAILING_ADDRESS_LINE_2, NCPS_MAILING_ADDRESS_LINE_3, NCPS_MAILING_CITY, NCPS_MAILING_STATE, NCPS_MAILING_ZIP, NCPS_PREVIOUS_ADDRESS_ADDRESS_LINE_1, NCPS_PREVIOUS_ADDRESS_ADDRESS_LINE_2, NCPS_PREVIOUS_ADDRESS_ADDRESS_LINE_3, NCPS_PREVIOUS_ADDRESS_CITY, NCPS_PREVIOUS_ADDRESS_STATE, NCPS_PREVIOUS_ADDRESS_ZIP, NCPS_LAST_EMPLOYER_NAME, NCPS_LAST_EMPLOYER_COUNTRY, NCPS_LAST_EMPLOYER_ADDRESS_LINE_1, NCPS_LAST_EMPLOYER_ADDRESS_LINE_2, NCPS_LAST_EMPLOYER_CITY, NCPS_LAST_EMPLOYER_STATE, NCPS_LAST_EMPLOYER_ZIP, NCPS_LAST_EMPLOYER_WORK_PHONE, NCPS_LAST_EMPLOYER_DATE_LAST_WORKED, NCPS_LAST_EMPLOYER_MONTHLY_SALARY, NCPS_LAST_EMPLOYER_USUAL_OCCUPATION, NCPS_PAST_EMPLOYER_NAME, NCPS_PAST_EMPLOYER_DATE_LAST_WORKED, NCPS_PAST_EMPLOYER_COUNTRY, NCPS_PAST_EMPLOYER_ADDRESS_LINE_1, NCPS_PAST_EMPLOYER_ADDRESS_LINE_2, NCPS_PAST_EMPLOYER_CITY, NCPS_PAST_EMPLOYER_STATE, NCPS_PAST_EMPLOYER_ZIP, NCPS_FATHERS_LAST_NAME, NCPS_FATHERS_FIRST_NAME, NCPS_FATHERS_MIDDLE_NAME, NCPS_FATHERS_SUFFIX, NCPS_FATHERS_ADDRESS_LINE_1, NCPS_FATHERS_ADDRESS_LINE_2, NCPS_FATHERS_ADDRESS_LINE_3, NCPS_FATHERS_CITY, NCPS_FATHERS_STATE, NCPS_FATHERS_ZIP, NCPS_FATHERS_PHONE_NUMBER, NCPS_MOTHERS_LAST_NAME, NCPS_MOTHERS_FIRST_NAME, NCPS_MOTHERS_MIDDLE_NAME, NCPS_MOTHERS_SUFFIX, NCPS_MOTHERS_MAIDEN_NAME, NCPS_MOTHERS_ADDRESS_LINE_1, NCPS_MOTHERS_ADDRESS_LINE_2, NCPS_MOTHERS_ADDRESS_LINE_3, NCPS_MOTHERS_CITY, NCPS_MOTHERS_STATE, NCPS_MOTHERS_ZIP, NCPS_MOTHERS_PHONE_NUMBER, NCPS_LAST_SCHOOL_ATTENDED_NAME, NCPS_LAST_SCHOOL_COUNTRY, NCPS_LAST_SCHOOL_ADDRESS_LINE_1, NCPS_LAST_SCHOOL_ADDRESS_LINE_2, NCPS_LAST_SCHOOL_CITY, NCPS_LAST_SCHOOL_STATE, NCPS_LAST_SCHOOL_ZIP, NCPS_ARREST_HAS_A_POLICE_RECORD, NCPS_ARREST_DATE, NCPS_ARREST_OFFENSE, NCPS_ARREST_CITY, NCPS_ARREST_STATE, NCPS_INCARCERATION_DATE, NCPS_INCARCERATION_RELEASE_DATE, NCPS_INCARCERATION_FACILITY_NAME, NCPS_INCARCERATION_ADDRESS_LINE_1, NCPS_INCARCERATION_ADDRESS_LINE_2, NCPS_INCARCERATION_CITY, NCPS_INCARCERATION_STATE, NCPS_INCARCERATION_ZIP, NCPS_ARMED_FORCES_STATUS, NCPS_ARMED_FORCES_VA_SERVICE_NUMBER, NCPS_ARMED_FORCES_BRANCH, NCPS_ARMED_FORCES_ENTRY_DATE, NCPS_ARMED_FORCES_DISCHARGE_DATE, NCP_PAYS_VOLUNTARILY, SUPPORT_TYPE, SS1_DOCKET_NO, SS1_NAME_OF_COURT, SS1_ADDRESS_LINE_1, SS1_ADDRESS_LINE_2, SS1_CITY, SS1_STATE, SS1_ZIP, SS1_AMOUNT_OF_SUPPORT, SS1_AMOUNT_OF_ARREARAGE, SS1_AMOUNT_OF_LAST_PAYMENT_RECEIVED, SS1_FREQUENCY_OF_SUPPORT, SS1_DATE_OF_LAST_PAYMENT, SS1_SUPPORT_METHOD, SS1_ORDER_EFFECTIVE_DATE, SS2_DOCKET_NO, SS2_NAME_OF_COURT, SS2_ADDRESS_LINE_1, SS2_ADDRESS_LINE_2, SS2_CITY, SS2_STATE, SS2_ZIP, SS2_AMOUNT_OF_SUPPORT, SS2_AMOUNT_OF_ARREARAGE, SS2_AMOUNT_OF_LAST_PAYMENT_RECEIVED, SS2_FREQUENCY_OF_SUPPORT, SS2_DATE_OF_LAST_PAYMENT, SS2_SUPPORT_METHOD, SS2_ORDER_EFFECTIVE_DATE, SS3_DOCKET_NO, SS3_NAME_OF_COURT, SS3_ADDRESS_LINE_1, SS3_ADDRESS_LINE_2, SS3_CITY, SS3_STATE, SS3_ZIP, SS3_AMOUNT_OF_SUPPORT, SS3_AMOUNT_OF_ARREARAGE, SS3_AMOUNT_OF_LAST_PAYMENT_RECEIVED, SS3_FREQUENCY_OF_SUPPORT, SS3_DATE_OF_LAST_PAYMENT, SS3_SUPPORT_METHOD, SS3_ORDER_EFFECTIVE_DATE, SS4_DOCKET_NO, SS4_NAME_OF_COURT, SS4_ADDRESS_LINE_1, SS4_ADDRESS_LINE_2, SS4_CITY, SS4_STATE, SS4_ZIP, SS4_AMOUNT_OF_SUPPORT, SS4_AMOUNT_OF_ARREARAGE, SS4_AMOUNT_OF_LAST_PAYMENT_RECEIVED, SS4_FREQUENCY_OF_SUPPORT, SS4_DATE_OF_LAST_PAYMENT, SS4_SUPPORT_METHOD, SS4_ORDER_EFFECTIVE_DATE, SS5_DOCKET_NO, SS5_NAME_OF_COURT, SS5_ADDRESS_LINE_1, SS5_ADDRESS_LINE_2, SS5_CITY, SS5_STATE, SS5_ZIP, SS5_AMOUNT_OF_SUPPORT, SS5_AMOUNT_OF_ARREARAGE, SS5_AMOUNT_OF_LAST_PAYMENT_RECEIVED, SS5_FREQUENCY_OF_SUPPORT, SS5_DATE_OF_LAST_PAYMENT, SS5_SUPPORT_METHOD, SS5_ORDER_EFFECTIVE_DATE FROM dbo.interfacedev.stg1_meds_initial_referrals_inbound -- [SCHEDULED_JOB] WHERE -- [ID]=@scheduledJobStage2ID AND -- [STAGE2_IS_ON_HOLD]=0 AND -- [IS_DELETED]=0; SCHEDULED_JOB_STAGE1_ID= @scheduledJobStage2ID and SCHEDULED_JOB_STAGE2_ID =@scheduledJobStage2ID' ;*//*set @sqlquerystr = 'select * from dbo.interfacedev.stg1_meds_initial_referrals_inbound where scheduled_job_stage1_id = 14074';print @sqlquerystr; -- set @sql='exec master..xp_cmdshell ''bcp '+@sqlquerystr+'" queryout "'+@data_file+'" -c''';--print @sql; -- exec(@sql);--set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''set @sql ='exec master..xp_cmdshell ''bcp "select * from dbo.interfacedev.stg1_meds_initial_referrals_inbound where scheduled_job_stage1_id = 14074 '+'" queryout "'+@data_file+'" -c''' ;print @sql;exec(@sql);*/SET @bcpCommand = 'bcp "SELECT * FROM dbo.case_file " queryout "'print @bcpcommandSET @bcpCommand = @bcpCommand + @data_file + '" -U garth -P pw -c'print @bcpcommandEXEC master..xp_cmdshell @bcpCommand END TRYBEGIN CATCH -- Whoops, there was an error SET @ERRSEVERITY = ERROR_SEVERITY(); SET @ERRORNUMBER = ERROR_NUMBER(); SET @ERRORPROCEDURE = ERROR_PROCEDURE(); SET @ERRORSTATE = ERROR_STATE(); SET @ERRORLINE = ERROR_LINE(); SET @ErrMsg = ERROR_MESSAGE(); SET @IS_MAJOR_ERROR = 1; SET @SCHEDULED_JOB_ID = @scheduledJobStage2ID; SET @INTERNAL_KNOWN_ERROR_CODE_ID = 51; SET @EXTERNAL_KNOWN_ERROR_CODE_ID = 0; SET @LINE_NUMBER = ERROR_LINE(); SET @ERROR_SUMMARY = 'Error Severity :- ' + CONVERT(VARCHAR,@ERRSEVERITY) + ' ; Error number :- ' + CONVERT(VARCHAR,@ERRORNUMBER) + ' ; Error Procedure :-' + ISNULL(@ERRORPROCEDURE,'') + ' ; Error State :-' + CONVERT(VARCHAR,@ERRORSTATE) + ' ; Error Line :- ' + CONVERT(VARCHAR,@ERRORLINE) + ' ; Error in IntStg2Out_LoadStage2ToStage1Outbound store procedure' SET @DETAIL_ERROR = ERROR_MESSAGE(); SET @ERROR_LINE_CONTENTS = NULL; SET @STAGE2_TABLE_NAME_ID = NULL; SET @STG2_ID = NULL;--Exec Int_PopulateError 0, 2, @IS_MAJOR_ERROR, @SCHEDULED_JOB_ID, null, @INTERNAL_KNOWN_ERROR_CODE_ID, @LINE_NUMBER, @ERROR_SUMMARY, @DETAIL_ERROR, @ERROR_LINE_CONTENTS, @STAGE2_TABLE_NAME_ID, @STG2_ID --RAISERROR (@ErrMsg, @ERRSEVERITY, 1) RETURNEND CATCHany help is much appreciated |
|
how to output the data to file