Solved

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Posted on 2004-09-10
10
6,044 Views
Last Modified: 2008-01-09
Ok i am trying to build a rather large dynamic stored procedure. The jist of this procedure is to create a history table and tag each line with a particular value so that if it needs to be updated it will only update the correct lines. I am basically duplicating an existing table, however becuase of the extra field i can not just do a simple    ---- insert into select (field names) where ---- statement. I have copied the procedure over here for you to see the exact procedure. According to query analyzier the statement is a valid statement and the syntax is correct as well. however when i try and run it i get the following error (Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. )

bare with it, because it is a large stored procedure.


CREATE Procedure usp_createShadowRecord3
      (
            @strPubID varchar(2) = '',
            @strMatchKey char(20) = '',
            @strLDLCurCycle char(5)
      )
As

declare @stupdateSQL nvarchar(4000)
declare @strSQL nvarchar(4000)
declare @strSQL2 nvarchar(200)
declare @newLDL varchar(10)
declare @lastLDL varchar(10)
declare @strTempInfo varchar(8000)
set @lastLDL = (select top 1 LDL from tblShadowRecords where Mag = @strPubID and Matchkey = @strMatchKey order by ldl_ycc desc)

Set @strSQL2 = N'select @newLDL = LDL from [' + @strPubID + ']' +
      N'where MatchKey = ''' + @strmatchKey + ''''
      EXEC sp_ExecuteSQL @strSQL2,
            N'@newLDL varchar(10) output', @newLDL output

Begin
      declare @lngSubscriptionID int
      declare @Mag varchar(2)
      declare @Matchkey2 varchar(14)
      declare @DomFor smallint
      declare @SubName varchar(35)
      declare @PreName varchar(35)
      declare @FirstName varchar(35)
      declare @LastName varchar(35)
      declare @Title varchar(35)
      declare @Company varchar(35)
      declare @Division varchar(35)
      declare @Address varchar(35)
      declare @City varchar(20)
      declare @State varchar(2)
      declare @Zip varchar(6)
      declare @Zip4 varchar(4)
      declare @Country varchar(30)
      declare @LDL varchar(10)
      declare @LDL_YCC char(3)
      declare @LDL_Batch char(3)
      declare @LDL_TranCode char(2)
      declare @PDL varchar(10)
      declare @TypeSub varchar(1)
      declare @StartDate varchar(6)
      declare @ExpireDate varchar(6)
      declare @Term varchar(3)
      declare @Price varchar(3)
      declare @ItmDolVal money
      declare @StTax money
      declare @Postage money
      declare @AmtDue money
      declare @AmtPd money
      declare @DepositDate varchar(6)
      declare @AuditTrlNum varchar(4)
      declare @PayDocLoc varchar(10)
      declare @PayDoc_YCC char(3)
      declare @PayDoc_Batch char(3)
      declare @PayDoc_TranCode char(2)
      declare @OrdDocLoc varchar(10)
      declare @OrdDoc_YCC char(3)
      declare @OrdDoc_Batch char(3)
      declare @OrdDoc_TranCode char(2)
      declare @NumCopies smallint
      declare @MailClass varchar(2)
      declare @AdCl varchar(1)
      declare @TypeSubTrl varchar(10)
      declare @StartDateTrl varchar(6)
      declare @ExpireDateTrl varchar(6)
      declare @TermTrl varchar(3)
      declare @PriceTrl varchar(2)
      declare @ItmDolValTrl money
      declare @StTaxTrl money
      declare @PostageTrl money
      declare @AmtDueTrl money
      declare @AmtPdTrl money
      declare @DepositDateTrl varchar(6)
      declare @AuditTrlNumTrl varchar(4)
      declare @PayDocLocTrl varchar(10)
      declare @PayDocTrl_YCC char(3)
      declare @PayDocTrl_Batch char(3)
      declare @PayDocTrl_TranCode char(2)
      declare @OrdDocLocTrl varchar(10)
      declare @OrdDocTrl_YCC char(3)
      declare @OrdDocTrl_Batch char(3)
      declare @OrdDocTrl_TranCode char(2)
      declare @NumCopiesTrl smallint
      declare @MailClassTrl varchar(2)
      declare @AdClTrl varchar(1)
      declare @VDate varchar(6)
      declare @List varchar(1)
      declare @Home varchar(1)
      declare @PrimaryIndustryCode varchar(2)
      declare @PIGroup varchar(1)
      declare @SecondaryIndustryCode varchar(2)
      declare @BPASource varchar(2)
      declare @SourceID varchar(2)
      declare @TitleCode varchar(3)
      declare @TitleGroup varchar(1)
      declare @Channel varchar(1)
      declare @EffortSource varchar(1)
      declare @EffortKey varchar(6)
      declare @PaymentKey varchar(5)
      declare @GroupNum varchar(5)
      declare @RenewInd varchar(1)
      declare @BillSeriesInd varchar(1)
      declare @RenewCounter smallint
      declare @DateofLastIssue varchar(6)
      declare @RemainingIssues smallint
      declare @ForeignStateCode varchar(3)
      declare @SuspendCode smallint
      declare @Split varchar(3)
      declare @ClCounter smallint
      declare @OldPC varchar(2)
      declare @OldEffs varchar(1)
      declare @ReasonCode varchar(1)
      declare @PreviousStartDate varchar(6)
      declare @PreviousExpireDate varchar(6)
      declare @SubAccount varchar(9)
      declare @WebAccount int
      declare @Phone varchar(10)
      declare @GiftType varchar(2)
      declare @PONum varchar(8)
      declare @AgeingDate varchar(6)
      declare @CycleID varchar(1)
      declare @OldDF varchar(1)
      declare @OldMatchkey varchar(14)
      declare @PurgeDate varchar(6)
      declare @MailInhibitor varchar(1)
      declare @SpecialFlag varchar(2)
      declare @OrderNo varchar(20)
      declare @FinishOps varchar(25)
      declare @SecondAlpha varchar(12)
      declare @ExpDemo1 smallint
      declare @ExpDemo2 smallint
      declare @ExpDemo3 smallint
      declare @ExpDemo4 smallint
      declare @ExpDemo5 smallint
      declare @MultDemos varchar(220)
      declare @FaxNumber varchar(10)
      declare @CurrCode varchar(1)
      declare @UnitNum varchar(6)
      declare @BFiller varchar(13)
      declare @UnitType varchar(2)
      declare @QDate varchar(6)
      declare @ADate varchar(6)
      declare @EmailAddress varchar(65)
      declare @ZipExt2 varchar(4)
      declare @AddInd varchar(1)
      declare @TitleLiteral varchar(35)
      declare @ADate2 varchar(6)
      declare @AMoney money
      declare @CarrierRoute varchar(4)
      declare @DeliveryPoint varchar(3)
      declare @FinalIssue char(4)
      declare @AuxiliaryDemos varchar(78)
      declare @SecondaryDemos varchar(80)
      declare @ABSplit char(1)      
      declare @Misc1Lit10A varchar(10)
      declare @Misc1Lit10B varchar(10)
      declare @Misc1Lit10C varchar(10)
      declare @Misc1Lit10D varchar(10)
      declare @OldEffort varchar(60)
      declare @Misc2Lit10A varchar(10)
      declare @Misc2Lit10B varchar(10)
      declare @Misc2Lit10C varchar(10)
      declare @Misc2Lit10D varchar(10)
      declare @Misc2Lit10E varchar(10)
      declare @Misc2Lit10F varchar(10)
      declare @Misc2Lit10G varchar(10)
      declare @Misc2Lit10H varchar(10)
      declare @Misc2Lit10I varchar(10)
      declare @Misc2Lit10J varchar(10)
      declare @GroupDate varchar(4)
      declare @Opt1 char(1)
      declare @Opt2 char(1)
      declare @Opt3 char(1)
      declare @Opt4 char(1)
      declare @Opt5 char(1)
      declare @Opt6 char(1)
      declare @Opt7 char(1)
      declare @Opt8 char(1)
      declare @Opt9 char(1)
      declare @Misc3Num1 int
      declare @RenewalCounter smallint
      declare @StateTaxNew money
      declare @PostageNew money
      declare @StateTaxTrlNew money
      declare @PostageTrlNew money
      declare @ForeignPhone varchar(15)
      declare @ForeignFax varchar(15)
      declare @ForeignPostalCode varchar(10)
      declare @Address2 varchar(35)
      declare @CheckNumber varchar(10)
      declare @CCType varchar(2)
      declare @CCNumber varchar(20)
      declare @CCExpDate varchar(6)
      declare @PostalCarrier varchar(3)
      declare @PostalCarrierAcct varchar(20)
      declare @TaxExempt varchar(1)
      declare @TaxExemptNumber varchar(9)
      declare @PONumber varchar(12)
      declare @OrdDocLocTrl2 varchar(10)
      declare @EDate varchar(4)
      declare @blnUpdated bit
      declare @datUpdatedOn datetime
      declare @Agency char(5)

      set @strTempInfo = 'select @lngSubscriptionID = lngSubscriptionID, @Mag = Mag, @Matchkey2 = Matchkey, @DomFor = DomFor, @SubName = SubName, @PreName = PreName,
            @FirstName = FirstName, @LastName = LastName, @Title = Title, @Company = Company, @Division = Division, @Address = Address, @City = City, @State = State,
            @Zip = Zip, @Zip4 = Zip4, @Country = Country, @LDL = LDL, @LDL_YCC = LDL_YCC, @LDL_Batch = LDL_Batch, @LDL_TranCode = LDL_TranCode, @PDL = PDL, @TypeSub = TypeSub,
            @StartDate = StartDate, @ExpireDate = ExpireDate, @Term = Term, @Price = Price, @ItmDolVal = ItmDolVal, @StTax = StTax, @Postage = Postage, @AmtDue = AmtDue,
            @AmtPd = AmtPd, @DepositDate = DepositDate, @AuditTrlNum = AuditTrlNum, @PayDocLoc = PayDocLoc, @PayDoc_YCC = PayDoc_YCC, @PayDoc_Batch = PayDoc_Batch,
            @PayDoc_TranCode = PayDoc_TranCode, @OrdDocLoc = OrdDocLoc, @OrdDoc_YCC = OrdDoc_YCC, @OrdDoc_Batch = OrdDoc_Batch, @OrdDoc_TranCode = OrdDoc_TranCode,
            @NumCopies = NumCopies, @MailClass = MailClass, @AdCl = AdCl, @TypeSubTrl = TypeSubTrl, @StartDateTrl = StartDateTrl, @ExpireDateTrl = ExpireDateTrl,
            @TermTrl = TermTrl, @PriceTrl = PriceTrl, @ItmDolValTrl = ItmDolValTrl, @StTaxTrl = StTaxTrl, @PostageTrl = PostageTrl, @AmtDueTrl = AmtDueTrl, @AmtPdTrl = AmtPdTrl,
            @DepositDateTrl = DepositDateTrl, @AuditTrlNumTrl = AuditTrlNumTrl, @PayDocLocTrl = PayDocLocTrl, @PayDocTrl_YCC = PayDocTrl_YCC, @PayDocTrl_Batch = PayDocTrl_Batch,
            @PayDocTrl_TranCode = PayDocTrl_TranCode, @OrdDocLocTrl = OrdDocLocTrl, @OrdDocTrl_YCC = OrdDocTrl_YCC, @OrdDocTrl_Batch = OrdDocTrl_Batch, @OrdDocTrl_TranCode = OrdDocTrl_TranCode,
            @NumCopiesTrl = NumCopiesTrl, @MailClassTrl = MailClassTrl, @AdClTrl = AdClTrl, @VDate = VDate, @List = List, @Home = Home, @PrimaryIndustryCode = PrimaryIndustryCode,
            @PIGroup = PIGroup, @SecondaryIndustryCode = SecondaryIndustryCode, @BPASource = BPASource, @SourceID = SourceID, @TitleCode = TitleCode, @TitleGroup = TitleGroup,
            @Channel = Channel, @EffortSource = EffortSource, @EffortKey = EffortKey, @PaymentKey = PaymentKey, @GroupNum = GroupNum, @RenewInd = RenewInd, @BillSeriesInd = BillSeriesInd,
            @RenewCounter = RenewCounter, @DateofLastIssue = DateofLastIssue, @RemainingIssues = RemainingIssues, @ForeignStateCode = ForeignStateCode, @SuspendCode = SuspendCode,
            @Split = Split, @ClCounter = ClCounter, @OldPC = OldPC, @OldEffs = OldEffs, @ReasonCode = ReasonCode, @PreviousStartDate = PreviousStartDate, @PreviousExpireDate = PreviousExpireDate,
            @SubAccount = SubAccount, @WebAccount = WebAccount, @Phone = Phone, @GiftType = GiftType, @PONum = PONum, @AgeingDate = AgeingDate, @CycleID = CycleID, @OldDF = OldDF,
            @OldMatchkey = OldMatchkey, @PurgeDate = PurgeDate, @MailInhibitor = MailInhibitor, @SpecialFlag = SpecialFlag, @OrderNo = OrderNo, @FinishOps = FinishOps,
            @SecondAlpha = SecondAlpha, @ExpDemo1 = ExpDemo1, @ExpDemo2 = ExpDemo2, @ExpDemo3 = ExpDemo3, @ExpDemo4 = ExpDemo4, @ExpDemo5 = ExpDemo5, @MultDemos = MultDemos,
            @FaxNumber = FaxNumber, @CurrCode = CurrCode, @UnitNum = UnitNum, @BFiller = BFiller, @UnitType = UnitType, @QDate = QDate, @ADate = ADate, @EmailAddress = EmailAddress,
            @ZipExt2 = ZipExt2, @AddInd = AddInd, @TitleLiteral = TitleLiteral, @ADate2 = ADate2, @AMoney = AMoney, @CarrierRoute = CarrierRoute, @DeliveryPoint = DeliveryPoint,  
            @FinalIssue = FinalIssue, @AuxiliaryDemos = AuxiliaryDemos, @SecondaryDemos = SecondaryDemos, @ABSplit = ABSplit, @Misc1Lit10A = Misc1Lit10A, @Misc1Lit10B = Misc1Lit10B,
            @Misc1Lit10C = Misc1Lit10C, @Misc1Lit10D = Misc1Lit10D, @OldEffort = OldEffort, @Misc2Lit10A = Misc2Lit10A, @Misc2Lit10B = Misc2Lit10B, @Misc2Lit10C = Misc2Lit10C,
            @Misc2Lit10D = Misc2Lit10D, @Misc2Lit10E = Misc2Lit10E, @Misc2Lit10F = Misc2Lit10F, @Misc2Lit10G = Misc2Lit10G, @Misc2Lit10H = Misc2Lit10H, @Misc2Lit10I = Misc2Lit10I,
            @Misc2Lit10J = Misc2Lit10J, @GroupDate = GroupDate, @Opt1 = Opt1, @Opt2 = Opt2, @Opt3 = Opt3, @Opt4 = Opt4, @Opt5 = Opt5, @Opt6 = Opt6, @Opt7 = Opt7, @Opt8 = Opt8,
            @Opt9 = Opt9, @Misc3Num1 = Misc3Num1, @RenewalCounter = RenewalCounter, @StateTaxNew = StateTaxNew, @PostageNew = PostageNew, @StateTaxTrlNew = StateTaxTrlNew,
            @PostageTrlNew = PostageTrlNew, @ForeignPhone = ForeignPhone, @ForeignFax = ForeignFax, @ForeignPostalCode = ForeignPostalCode, @Address2 = Address2, @CheckNumber = CheckNumber,
            @CCType = CCType, @CCNumber = CCNumber, @CCExpDate = CCExpDate, @PostalCarrier = PostalCarrier, @PostalCarrierAcct = PostalCarrierAcct, @TaxExempt = TaxExempt,
            @TaxExemptNumber = TaxExemptNumber, @PONumber = PONumber, @OrdDocLocTrl2 = OrdDocLocTrl2, @EDate = EDate, @blnUpdated = blnUpdated, @datUpdatedOn = datUpdatedOn,
            @Agency = Agency from [' + @strPubID + '] where MatchKey = ' + @strmatchKey + ''

            EXEC sp_ExecuteSQL @strTempInfo,
                  '@lngSubscriptionID int output, @Mag varchar(2) output, @Matchkey2 varchar(14) output, @DomFor smallint output, @SubName varchar(35) output, @PreName varchar(35) output, @FirstName varchar(35) output, @LastName varchar(35) output, @Title varchar(35) output, @Company varchar(35) output, @Division varchar(35) output, @Address varchar(35) output, @City varchar(20) output,@State varchar(2) output, @Zip varchar(6) output, @Zip4 varchar(4) output, @Country varchar(30) output, @LDL varchar(10) output, @LDL_YCC char(3) output, @LDL_Batch char(3) output, @LDL_TranCode char(2) output, @PDL varchar(10) output, @TypeSub varchar(1) output, @StartDate varchar(6) output, @ExpireDate varchar(6) output, @Term varchar(3) output, @Price varchar(3) output, @ItmDolVal money output, @StTax money output, @Postage money output, @AmtDue money output, @AmtPd money output, @DepositDate varchar(6) output, @AuditTrlNum varchar(4) output, @PayDocLoc varchar(10) output, @PayDoc_YCC char(3) output, @PayDoc_Batch char(3) output, @PayDoc_TranCode char(2) output, @OrdDocLoc varchar(10) output, @OrdDoc_YCC char(3) output, @OrdDoc_Batch char(3) output, @OrdDoc_TranCode char(2) output, @NumCopies smallint output, @MailClass varchar(2) output, @AdCl varchar(1) output, @TypeSubTrl varchar(10) output, @StartDateTrl varchar(6) output, @ExpireDateTrl varchar(6) output, @TermTrl varchar(3) output, @PriceTrl varchar(2) output, @ItmDolValTrl money output, @StTaxTrl money output, @PostageTrl money output, @AmtDueTrl money output, @AmtPdTrl money output, @DepositDateTrl varchar(6) output, @AuditTrlNumTrl varchar(4) output, @PayDocLocTrl varchar(10) output, @PayDocTrl_YCC char(3) output, @PayDocTrl_Batch char(3) output, @PayDocTrl_TranCode char(2) output, @OrdDocLocTrl varchar(10) output, @OrdDocTrl_YCC char(3) output,  @OrdDocTrl_Batch char(3) output, @OrdDocTrl_TranCode char(2) output, @NumCopiesTrl smallint output, @MailClassTrl varchar(2) output, @AdClTrl varchar(1) output, @VDate varchar(6) output, @List varchar(1) output, @Home varchar(1) output, @PrimaryIndustryCode varchar(2) output, @PIGroup varchar(1) output, @SecondaryIndustryCode varchar(2) output, @BPASource varchar(2) output, @SourceID varchar(2) output, @TitleCode varchar(3) output, @TitleGroup varchar(1) output, @Channel varchar(1) output, @EffortSource varchar(1) output, @EffortKey varchar(6) output, @PaymentKey varchar(5) output, @GroupNum varchar(5) output, @RenewInd varchar(1) output, @BillSeriesInd varchar(1) output, @RenewCounter smallint output, @DateofLastIssue varchar(6) output, @RemainingIssues smallint output, @ForeignStateCode varchar(3) output, @SuspendCode smallint output, @Split varchar(3) output, @ClCounter smallint output, @OldPC varchar(2) output, @OldEffs varchar(1) output, @ReasonCode varchar(1) output, @PreviousStartDate varchar(6) output, @PreviousExpireDate varchar(6) output, @SubAccount varchar(9) output, @WebAccount int output, @Phone varchar(10) output, @GiftType varchar(2) output, @PONum varchar(8) output, @AgeingDate varchar(6) output, @CycleID varchar(1) output, @OldDF varchar(1) output, @OldMatchkey varchar(14) output, @PurgeDate varchar(6) output, @MailInhibitor varchar(1) output, @SpecialFlag varchar(2) output, @OrderNo varchar(20) output, @FinishOps varchar(25) output, @SecondAlpha varchar(12) output, @ExpDemo1 smallint output, @ExpDemo2 smallint output, @ExpDemo3 smallint output, @ExpDemo4 smallint output, @ExpDemo5 smallint output, @MultDemos varchar(220) output, @FaxNumber varchar(10) output, @CurrCode varchar(1) output, @UnitNum varchar(6) output, @BFiller varchar(13) output, @UnitType varchar(2) output, @QDate varchar(6) output, @ADate varchar(6) output, @EmailAddress varchar(65) output, @ZipExt2 varchar(4) output, @AddInd varchar(1) output, @TitleLiteral varchar(35) output, @ADate2 varchar(6) output, @AMoney money output, @CarrierRoute varchar(4) output, @DeliveryPoint varchar(3) output, @FinalIssue char(4) output, @AuxiliaryDemos varchar(78) output, @SecondaryDemos varchar(80) output, @ABSplit char(1) output, @Misc1Lit10A varchar(10) output, @Misc1Lit10B varchar(10) output, @Misc1Lit10C varchar(10) output, @Misc1Lit10D varchar(10) output, @OldEffort varchar(60) output, @Misc2Lit10A varchar(10) output, @Misc2Lit10B varchar(10) output, @Misc2Lit10C varchar(10) output, @Misc2Lit10D varchar(10) output, @Misc2Lit10E varchar(10) output, @Misc2Lit10F varchar(10) output, @Misc2Lit10G varchar(10) output, @Misc2Lit10H varchar(10) output, @Misc2Lit10I varchar(10) output, @Misc2Lit10J varchar(10) output, @GroupDate varchar(4) output, @Opt1 char(1) output, @Opt2 char(1) output, @Opt3 char(1) output, @Opt4 char(1) output, @Opt5 char(1) output, @Opt6 char(1) output, @Opt7 char(1) output, @Opt8 char(1) output, @Opt9 char(1) output, @Misc3Num1 int output, @RenewalCounter smallint output, @StateTaxNew money output, @PostageNew money output, @StateTaxTrlNew money output, @PostageTrlNew money output, @ForeignPhone varchar(15) output, @ForeignFax varchar(15) output, @ForeignPostalCode varchar(10) output, @Address2 varchar(35) output, @CheckNumber varchar(10) output, @CCType varchar(2) output, @CCNumber varchar(20) output, @CCExpDate varchar(6) output, @PostalCarrier varchar(3) output, @PostalCarrierAcct varchar(20) output, @TaxExempt varchar(1) output, @TaxExemptNumber varchar(9) output, @PONumber varchar(12) output, @OrdDocLocTrl2 varchar(10) output, @EDate varchar(4) output, @blnUpdated bit output, @datUpdatedOn datetime output, @Agency char(5) output',
                  
                  @lngSubscriptionID output, @Mag output, @Matchkey2 output, @DomFor output, @SubName output, @PreName output, @FirstName output, @LastName output, @Title output, @Company output, @Division output, @Address output, @City output,@State output, @Zip output, @Zip4 output, @Country output, @LDL output, @LDL_YCC output, @LDL_Batch output, @LDL_TranCode output, @PDL output, @TypeSub output, @StartDate output, @ExpireDate output, @Term output, @Price output, @ItmDolVal output, @StTax output, @Postage output, @AmtDue output, @AmtPd output, @DepositDate output, @AuditTrlNum output, @PayDocLoc output, @PayDoc_YCC output, @PayDoc_Batch output, @PayDoc_TranCode output, @OrdDocLoc output, @OrdDoc_YCC output, @OrdDoc_Batch output, @OrdDoc_TranCode output, @NumCopies output, @MailClass output, @AdCl output, @TypeSubTrl output, @StartDateTrl output, @ExpireDateTrl output, @TermTrl output, @PriceTrl output, @ItmDolValTrl output, @StTaxTrl output, @PostageTrl output, @AmtDueTrl output, @AmtPdTrl output, @DepositDateTrl output, @AuditTrlNumTrl output, @PayDocLocTrl output, @PayDocTrl_YCC output, @PayDocTrl_Batch output, @PayDocTrl_TranCode output, @OrdDocLocTrl output, @OrdDocTrl_YCC output,  @OrdDocTrl_Batch output, @OrdDocTrl_TranCode output, @NumCopiesTrl output, @MailClassTrl output, @AdClTrl output, @VDate output, @List output, @Home output, @PrimaryIndustryCode output, @PIGroup output, @SecondaryIndustryCode output, @BPASource output, @SourceID output, @TitleCode output, @TitleGroup output, @Channel output, @EffortSource output, @EffortKey output, @PaymentKey output, @GroupNum output, @RenewInd output, @BillSeriesInd output, @RenewCounter output, @DateofLastIssue output, @RemainingIssues output, @ForeignStateCode output, @SuspendCode output, @Split output, @ClCounter output, @OldPC output, @OldEffs output, @ReasonCode output, @PreviousStartDate output, @PreviousExpireDate output, @SubAccount output, @WebAccount output, @Phone output, @GiftType output, @PONum output, @AgeingDate output, @CycleID output, @OldDF output, @OldMatchkey output, @PurgeDate output, @MailInhibitor output, @SpecialFlag output, @OrderNo output, @FinishOps output, @SecondAlpha output, @ExpDemo1 output, @ExpDemo2 output, @ExpDemo3 output, @ExpDemo4 output, @ExpDemo5 output, @MultDemos output, @FaxNumber output, @CurrCode output, @UnitNum output, @BFiller output, @UnitType output, @QDate output, @ADate output, @EmailAddress output, @ZipExt2 output, @AddInd output, @TitleLiteral output, @ADate2 output, @Amoney output, @CarrierRoute output, @DeliveryPoint, @FinalIssue output, @AuxiliaryDemos output, @SecondaryDemos output, @ABSplit output, @Misc1Lit10A output, @Misc1Lit10B output, @Misc1Lit10C output, @Misc1Lit10D output, @OldEffort output, @Misc2Lit10A output, @Misc2Lit10B output, @Misc2Lit10C output, @Misc2Lit10D output, @Misc2Lit10E output, @Misc2Lit10F output, @Misc2Lit10G output, @Misc2Lit10H output, @Misc2Lit10I output, @Misc2Lit10J output, @GroupDate output, @Opt1 output, @Opt2 output, @Opt3 output, @Opt4 output, @Opt5 output, @Opt6 output, @Opt7 output, @Opt8 output, @Opt9 output, @Misc3Num1 output, @RenewalCounter output, @StateTaxNew output, @PostageNew output, @StateTaxTrlNew output, @PostageTrlNew output, @ForeignPhone output, @ForeignFax output, @ForeignPostalCode output, @Address2 output, @CheckNumber output, @CCType output, @CCNumber output, @CCExpDate output, @PostalCarrier output, @PostalCarrierAcct output, @TaxExempt output, @TaxExemptNumber output, @PONumber output, @OrdDocLocTrl2 output, @EDate output, @blnUpdated output, @datUpdatedOn output, @Agency output

      insert into tblShadowRecords2
      (strLDLCurCycle, lngSubscriptionID, Mag, Matchkey, DomFor, SubName, PreName, FirstName, LastName, Title, Company, Division, Address, City,State, Zip, Zip4, Country, LDL, LDL_YCC, LDL_Batch, LDL_TranCode, PDL, TypeSub, StartDate, ExpireDate, Term, Price, ItmDolVal, StTax, Postage, AmtDue, AmtPd, DepositDate, AuditTrlNum, PayDocLoc, PayDoc_YCC, PayDoc_Batch, PayDoc_TranCode, OrdDocLoc, OrdDoc_YCC, OrdDoc_Batch, OrdDoc_TranCode, NumCopies, MailClass, AdCl, TypeSubTrl, StartDateTrl, ExpireDateTrl, TermTrl, PriceTrl, ItmDolValTrl, StTaxTrl, PostageTrl, AmtDueTrl, AmtPdTrl, DepositDateTrl, AuditTrlNumTrl, PayDocLocTrl, PayDocTrl_YCC, PayDocTrl_Batch, PayDocTrl_TranCode, OrdDocLocTrl, OrdDocTrl_YCC,  OrdDocTrl_Batch, OrdDocTrl_TranCode, NumCopiesTrl, MailClassTrl, AdClTrl, VDate, List, Home, PrimaryIndustryCode, PIGroup, SecondaryIndustryCode, BPASource, SourceID, TitleCode, TitleGroup, Channel, EffortSource, EffortKey, PaymentKey, GroupNum, RenewInd, BillSeriesInd, RenewCounter, DateofLastIssue, RemainingIssues, ForeignStateCode, SuspendCode, Split, ClCounter, OldPC, OldEffs, ReasonCode, PreviousStartDate, PreviousExpireDate, SubAccount, WebAccount, Phone, GiftType, PONum, AgeingDate, CycleID, OldDF, OldMatchkey, PurgeDate, MailInhibitor, SpecialFlag, OrderNo, FinishOps, SecondAlpha, ExpDemo1, ExpDemo2, ExpDemo3, ExpDemo4, ExpDemo5, MultDemos, FaxNumber, CurrCode, UnitNum, BFiller, UnitType, QDate, ADate, EmailAddress, ZipExt2, AddInd, TitleLiteral, ADate2, Amoney, CarrierRoute, DeliveryPoint, FinalIssue, AuxiliaryDemos, SecondaryDemos, ABSplit, Misc1Lit10A, Misc1Lit10B, Misc1Lit10C, Misc1Lit10D, OldEffort, Misc2Lit10A, Misc2Lit10B, Misc2Lit10C, Misc2Lit10D, Misc2Lit10E, Misc2Lit10F, Misc2Lit10G, Misc2Lit10H, Misc2Lit10I, Misc2Lit10J, GroupDate, Opt1, Opt2, Opt3, Opt4, Opt5, Opt6, Opt7, Opt8, Opt9, Misc3Num1, Agency)
      values
      (@strLDLCurCycle, @lngSubscriptionID, @Mag, @Matchkey2, @DomFor, @SubName, @PreName, @FirstName, @LastName, @Title, @Company, @Division, @Address, @City,@State, @Zip, @Zip4, @Country, @LDL, @LDL_YCC, @LDL_Batch, @LDL_TranCode, @PDL, @TypeSub, @StartDate, @ExpireDate, @Term, @Price, @ItmDolVal, @StTax, @Postage, @AmtDue, @AmtPd, @DepositDate, @AuditTrlNum, @PayDocLoc, @PayDoc_YCC, @PayDoc_Batch, @PayDoc_TranCode, @OrdDocLoc, @OrdDoc_YCC, @OrdDoc_Batch, @OrdDoc_TranCode, @NumCopies, @MailClass, @AdCl, @TypeSubTrl, @StartDateTrl, @ExpireDateTrl, @TermTrl, @PriceTrl, @ItmDolValTrl, @StTaxTrl, @PostageTrl, @AmtDueTrl, @AmtPdTrl, @DepositDateTrl, @AuditTrlNumTrl, @PayDocLocTrl, @PayDocTrl_YCC, @PayDocTrl_Batch, @PayDocTrl_TranCode, @OrdDocLocTrl, @OrdDocTrl_YCC,  @OrdDocTrl_Batch, @OrdDocTrl_TranCode, @NumCopiesTrl, @MailClassTrl, @AdClTrl, @VDate, @List, @Home, @PrimaryIndustryCode, @PIGroup, @SecondaryIndustryCode, @BPASource, @SourceID, @TitleCode, @TitleGroup, @Channel, @EffortSource, @EffortKey, @PaymentKey, @GroupNum, @RenewInd, @BillSeriesInd, @RenewCounter, @DateofLastIssue, @RemainingIssues, @ForeignStateCode, @SuspendCode, @Split, @ClCounter, @OldPC, @OldEffs, @ReasonCode, @PreviousStartDate, @PreviousExpireDate, @SubAccount, @WebAccount, @Phone, @GiftType, @PONum, @AgeingDate, @CycleID, @OldDF, @OldMatchkey, @PurgeDate, @MailInhibitor, @SpecialFlag, @OrderNo, @FinishOps, @SecondAlpha, @ExpDemo1, @ExpDemo2, @ExpDemo3, @ExpDemo4, @ExpDemo5, @MultDemos, @FaxNumber, @CurrCode, @UnitNum, @BFiller, @UnitType, @QDate, @ADate, @EmailAddress, @ZipExt2, @AddInd, @TitleLiteral, @ADate2, @Amoney, @CarrierRoute, @DeliveryPoint, @FinalIssue, @AuxiliaryDemos, @SecondaryDemos, @ABSplit, @Misc1Lit10A, @Misc1Lit10B, @Misc1Lit10C, @Misc1Lit10D, @OldEffort, @Misc2Lit10A, @Misc2Lit10B, @Misc2Lit10C, @Misc2Lit10D, @Misc2Lit10E, @Misc2Lit10F, @Misc2Lit10G, @Misc2Lit10H, @Misc2Lit10I, @Misc2Lit10J, @GroupDate, @Opt1, @Opt2, @Opt3, @Opt4, @Opt5, @Opt6, @Opt7, @Opt8, @Opt9, @Misc3Num1, @Agency)

End
return
GO
0
Comment
Question by:cntrymannj
  • 5
  • 3
  • 2
10 Comments
 
LVL 34

Expert Comment

by:arbert
Comment Utility
"however becuase of the extra field i can not just do a simple    ---- insert into select (field names) where ---- statement"

Why can't you?  Sure you can....You just have to explicitly list the columns on the insert statement.

0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
Insert into test (col1,col2,col3)
select col1,col2,col3 from yourtable
0
 

Author Comment

by:cntrymannj
Comment Utility
I attempted that yesterday and it wouldnt work. will revisit that code again and see if i can figure out why it did not work. might have just over looked something.
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
Yep, it should work--maybe you just got a column out of whack or something.  Something I find useful, to get a column list to "build" your insert statement, use the information_schema views:


select column_name + ',' from information_schema.columns
where table_name='yourtablename'
order by ordinal_position
0
 

Author Comment

by:cntrymannj
Comment Utility
it will not allow me to dynamically set the table which it is doing the select statement from.

sql sees this as correct syntax but when run it has an error at the from

      insert into tblShadowRecords2
      (lngSubscriptionID, Mag, Matchkey2, DomFor, SubName, PreName, FirstName, LastName, Title, Company, Division, Address, City,State, Zip, Zip4, Country, LDL, LDL_YCC, LDL_Batch, LDL_TranCode, PDL, TypeSub, StartDate, ExpireDate, Term, Price, ItmDolVal, StTax, Postage, AmtDue, AmtPd, DepositDate, AuditTrlNum, PayDocLoc, PayDoc_YCC, PayDoc_Batch, PayDoc_TranCode, OrdDocLoc, OrdDoc_YCC, OrdDoc_Batch, OrdDoc_TranCode, NumCopies, MailClass, AdCl, TypeSubTrl, StartDateTrl, ExpireDateTrl, TermTrl, PriceTrl, ItmDolValTrl, StTaxTrl, PostageTrl, AmtDueTrl, AmtPdTrl, DepositDateTrl, AuditTrlNumTrl, PayDocLocTrl, PayDocTrl_YCC, PayDocTrl_Batch, PayDocTrl_TranCode, OrdDocLocTrl, OrdDocTrl_YCC,  OrdDocTrl_Batch, OrdDocTrl_TranCode, NumCopiesTrl, MailClassTrl, AdClTrl, VDate, List, Home, PrimaryIndustryCode, PIGroup, SecondaryIndustryCode, BPASource, SourceID, TitleCode, TitleGroup, Channel, EffortSource, EffortKey, PaymentKey, GroupNum, RenewInd, BillSeriesInd, RenewCounter, DateofLastIssue, RemainingIssues, ForeignStateCode, SuspendCode, Split, ClCounter, OldPC, OldEffs, ReasonCode, PreviousStartDate, PreviousExpireDate, SubAccount, WebAccount, Phone, GiftType, PONum, AgeingDate, CycleID, OldDF, OldMatchkey, PurgeDate, MailInhibitor, SpecialFlag, OrderNo, FinishOps, SecondAlpha, ExpDemo1, ExpDemo2, ExpDemo3, ExpDemo4, ExpDemo5, MultDemos, FaxNumber, CurrCode, UnitNum, BFiller, UnitType, QDate, ADate, EmailAddress, ZipExt2, AddInd, TitleLiteral, ADate2, Amoney, CarrierRoute, DeliveryPoint, FinalIssue, AuxiliaryDemos, SecondaryDemos, ABSplit, Misc1Lit10A, Misc1Lit10B, Misc1Lit10C, Misc1Lit10D, OldEffort, Misc2Lit10A, Misc2Lit10B, Misc2Lit10C, Misc2Lit10D, Misc2Lit10E, Misc2Lit10F, Misc2Lit10G, Misc2Lit10H, Misc2Lit10I, Misc2Lit10J, GroupDate, Opt1, Opt2, Opt3, Opt4, Opt5, Opt6, Opt7, Opt8, Opt9, Misc3Num1, RenewalCounter, StateTaxNew, PostageNew, StateTaxTrlNew, PostageTrlNew, ForeignPhone, ForeignFax, ForeignPostalCode, Address2, CheckNumber, CCType, CCNumber, CCExpDate, PostalCarrier, PostalCarrierAcct, TaxExempt, TaxExemptNumber, PONumber, OrdDocLocTrl2, EDate, blnUpdated, datUpdatedOn, Agency)
      select lngSubscriptionID, Mag, Matchkey2, DomFor, SubName, PreName, FirstName, LastName, Title, Company, Division, Address, City,State, Zip, Zip4, Country, LDL, LDL_YCC, LDL_Batch, LDL_TranCode, PDL, TypeSub, StartDate, ExpireDate, Term, Price, ItmDolVal, StTax, Postage, AmtDue, AmtPd, DepositDate, AuditTrlNum, PayDocLoc, PayDoc_YCC, PayDoc_Batch, PayDoc_TranCode, OrdDocLoc, OrdDoc_YCC, OrdDoc_Batch, OrdDoc_TranCode, NumCopies, MailClass, AdCl, TypeSubTrl, StartDateTrl, ExpireDateTrl, TermTrl, PriceTrl, ItmDolValTrl, StTaxTrl, PostageTrl, AmtDueTrl, AmtPdTrl, DepositDateTrl, AuditTrlNumTrl, PayDocLocTrl, PayDocTrl_YCC, PayDocTrl_Batch, PayDocTrl_TranCode, OrdDocLocTrl, OrdDocTrl_YCC,  OrdDocTrl_Batch, OrdDocTrl_TranCode, NumCopiesTrl, MailClassTrl, AdClTrl, VDate, List, Home, PrimaryIndustryCode, PIGroup, SecondaryIndustryCode, BPASource, SourceID, TitleCode, TitleGroup, Channel, EffortSource, EffortKey, PaymentKey, GroupNum, RenewInd, BillSeriesInd, RenewCounter, DateofLastIssue, RemainingIssues, ForeignStateCode, SuspendCode, Split, ClCounter, OldPC, OldEffs, ReasonCode, PreviousStartDate, PreviousExpireDate, SubAccount, WebAccount, Phone, GiftType, PONum, AgeingDate, CycleID, OldDF, OldMatchkey, PurgeDate, MailInhibitor, SpecialFlag, OrderNo, FinishOps, SecondAlpha, ExpDemo1, ExpDemo2, ExpDemo3, ExpDemo4, ExpDemo5, MultDemos, FaxNumber, CurrCode, UnitNum, BFiller, UnitType, QDate, ADate, EmailAddress, ZipExt2, AddInd, TitleLiteral, ADate2, Amoney, CarrierRoute, DeliveryPoint, FinalIssue, AuxiliaryDemos, SecondaryDemos, ABSplit, Misc1Lit10A, Misc1Lit10B, Misc1Lit10C, Misc1Lit10D, OldEffort, Misc2Lit10A, Misc2Lit10B, Misc2Lit10C, Misc2Lit10D, Misc2Lit10E, Misc2Lit10F, Misc2Lit10G, Misc2Lit10H, Misc2Lit10I, Misc2Lit10J, GroupDate, Opt1, Opt2, Opt3, Opt4, Opt5, Opt6, Opt7, Opt8, Opt9, Misc3Num1, RenewalCounter, StateTaxNew, PostageNew, StateTaxTrlNew, PostageTrlNew, ForeignPhone, ForeignFax, ForeignPostalCode, Address2, CheckNumber, CCType, CCNumber, CCExpDate, PostalCarrier, PostalCarrierAcct, TaxExempt, TaxExemptNumber, PONumber, OrdDocLocTrl2, EDate, blnUpdated, datUpdatedOn, Agency from [' + @strPubID + '] where MatchKey =  @strmatchKey
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

Expert Comment

by:arbert
Comment Utility
Ah, ok, you need to stup a dynamic statement and exec that like this (if you're sting is longer than 8000, you'll have to setup multiple strings):

Declare @sql varchar(8000)

select @sql=' insert into tblShadowRecords2
     (lngSubscriptionID, Mag, Matchkey2, DomFor, SubName, PreName, FirstName, LastName, Title, Company, Division, Address, City,State, Zip, Zip4, Country, LDL, LDL_YCC, LDL_Batch, LDL_TranCode, PDL, TypeSub, StartDate, ExpireDate, Term, Price, ItmDolVal, StTax, Postage, AmtDue, AmtPd, DepositDate, AuditTrlNum, PayDocLoc, PayDoc_YCC, PayDoc_Batch, PayDoc_TranCode, OrdDocLoc, OrdDoc_YCC, OrdDoc_Batch, OrdDoc_TranCode, NumCopies, MailClass, AdCl, TypeSubTrl, StartDateTrl, ExpireDateTrl, TermTrl, PriceTrl, ItmDolValTrl, StTaxTrl, PostageTrl, AmtDueTrl, AmtPdTrl, DepositDateTrl, AuditTrlNumTrl, PayDocLocTrl, PayDocTrl_YCC, PayDocTrl_Batch, PayDocTrl_TranCode, OrdDocLocTrl, OrdDocTrl_YCC,  OrdDocTrl_Batch, OrdDocTrl_TranCode, NumCopiesTrl, MailClassTrl, AdClTrl, VDate, List, Home, PrimaryIndustryCode, PIGroup, SecondaryIndustryCode, BPASource, SourceID, TitleCode, TitleGroup, Channel, EffortSource, EffortKey, PaymentKey, GroupNum, RenewInd, BillSeriesInd, RenewCounter, DateofLastIssue, RemainingIssues, ForeignStateCode, SuspendCode, Split, ClCounter, OldPC, OldEffs, ReasonCode, PreviousStartDate, PreviousExpireDate, SubAccount, WebAccount, Phone, GiftType, PONum, AgeingDate, CycleID, OldDF, OldMatchkey, PurgeDate, MailInhibitor, SpecialFlag, OrderNo, FinishOps, SecondAlpha, ExpDemo1, ExpDemo2, ExpDemo3, ExpDemo4, ExpDemo5, MultDemos, FaxNumber, CurrCode, UnitNum, BFiller, UnitType, QDate, ADate, EmailAddress, ZipExt2, AddInd, TitleLiteral, ADate2, Amoney, CarrierRoute, DeliveryPoint, FinalIssue, AuxiliaryDemos, SecondaryDemos, ABSplit, Misc1Lit10A, Misc1Lit10B, Misc1Lit10C, Misc1Lit10D, OldEffort, Misc2Lit10A, Misc2Lit10B, Misc2Lit10C, Misc2Lit10D, Misc2Lit10E, Misc2Lit10F, Misc2Lit10G, Misc2Lit10H, Misc2Lit10I, Misc2Lit10J, GroupDate, Opt1, Opt2, Opt3, Opt4, Opt5, Opt6, Opt7, Opt8, Opt9, Misc3Num1, RenewalCounter, StateTaxNew, PostageNew, StateTaxTrlNew, PostageTrlNew, ForeignPhone, ForeignFax, ForeignPostalCode, Address2, CheckNumber, CCType, CCNumber, CCExpDate, PostalCarrier, PostalCarrierAcct, TaxExempt, TaxExemptNumber, PONumber, OrdDocLocTrl2, EDate, blnUpdated, datUpdatedOn, Agency)
     select lngSubscriptionID, Mag, Matchkey2, DomFor, SubName, PreName, FirstName, LastName, Title, Company, Division, Address, City,State, Zip, Zip4, Country, LDL, LDL_YCC, LDL_Batch, LDL_TranCode, PDL, TypeSub, StartDate, ExpireDate, Term, Price, ItmDolVal, StTax, Postage, AmtDue, AmtPd, DepositDate, AuditTrlNum, PayDocLoc, PayDoc_YCC, PayDoc_Batch, PayDoc_TranCode, OrdDocLoc, OrdDoc_YCC, OrdDoc_Batch, OrdDoc_TranCode, NumCopies, MailClass, AdCl, TypeSubTrl, StartDateTrl, ExpireDateTrl, TermTrl, PriceTrl, ItmDolValTrl, StTaxTrl, PostageTrl, AmtDueTrl, AmtPdTrl, DepositDateTrl, AuditTrlNumTrl, PayDocLocTrl, PayDocTrl_YCC, PayDocTrl_Batch, PayDocTrl_TranCode, OrdDocLocTrl, OrdDocTrl_YCC,  OrdDocTrl_Batch, OrdDocTrl_TranCode, NumCopiesTrl, MailClassTrl, AdClTrl, VDate, List, Home, PrimaryIndustryCode, PIGroup, SecondaryIndustryCode, BPASource, SourceID, TitleCode, TitleGroup, Channel, EffortSource, EffortKey, PaymentKey, GroupNum, RenewInd, BillSeriesInd, RenewCounter, DateofLastIssue, RemainingIssues, ForeignStateCode, SuspendCode, Split, ClCounter, OldPC, OldEffs, ReasonCode, PreviousStartDate, PreviousExpireDate, SubAccount, WebAccount, Phone, GiftType, PONum, AgeingDate, CycleID, OldDF, OldMatchkey, PurgeDate, MailInhibitor, SpecialFlag, OrderNo, FinishOps, SecondAlpha, ExpDemo1, ExpDemo2, ExpDemo3, ExpDemo4, ExpDemo5, MultDemos, FaxNumber, CurrCode, UnitNum, BFiller, UnitType, QDate, ADate, EmailAddress, ZipExt2, AddInd, TitleLiteral, ADate2, Amoney, CarrierRoute, DeliveryPoint, FinalIssue, AuxiliaryDemos, SecondaryDemos, ABSplit, Misc1Lit10A, Misc1Lit10B, Misc1Lit10C, Misc1Lit10D, OldEffort, Misc2Lit10A, Misc2Lit10B, Misc2Lit10C, Misc2Lit10D, Misc2Lit10E, Misc2Lit10F, Misc2Lit10G, Misc2Lit10H, Misc2Lit10I, Misc2Lit10J, GroupDate, Opt1, Opt2, Opt3, Opt4, Opt5, Opt6, Opt7, Opt8, Opt9, Misc3Num1, RenewalCounter, StateTaxNew, PostageNew, StateTaxTrlNew, PostageTrlNew, ForeignPhone, ForeignFax, ForeignPostalCode, Address2, CheckNumber, CCType, CCNumber, CCExpDate, PostalCarrier, PostalCarrierAcct, TaxExempt, TaxExemptNumber, PONumber, OrdDocLocTrl2, EDate, blnUpdated, datUpdatedOn, Agency from [' + @strPubID + '] where MatchKey = ' +char(39) + @strmatchKey +char(39)
0
 
LVL 34

Accepted Solution

by:
arbert earned 500 total points
Comment Utility
Oops, after you select your @sql=, you need to exec that:Declare @sql varchar(8000)

select @sql=' insert into tblShadowRecords2
     (lngSubscriptionID, Mag, Matchkey2, DomFor, SubName, PreName, FirstName, LastName, Title, Company, Division, Address, City,State, Zip, Zip4, Country, LDL, LDL_YCC, LDL_Batch, LDL_TranCode, PDL, TypeSub, StartDate, ExpireDate, Term, Price, ItmDolVal, StTax, Postage, AmtDue, AmtPd, DepositDate, AuditTrlNum, PayDocLoc, PayDoc_YCC, PayDoc_Batch, PayDoc_TranCode, OrdDocLoc, OrdDoc_YCC, OrdDoc_Batch, OrdDoc_TranCode, NumCopies, MailClass, AdCl, TypeSubTrl, StartDateTrl, ExpireDateTrl, TermTrl, PriceTrl, ItmDolValTrl, StTaxTrl, PostageTrl, AmtDueTrl, AmtPdTrl, DepositDateTrl, AuditTrlNumTrl, PayDocLocTrl, PayDocTrl_YCC, PayDocTrl_Batch, PayDocTrl_TranCode, OrdDocLocTrl, OrdDocTrl_YCC,  OrdDocTrl_Batch, OrdDocTrl_TranCode, NumCopiesTrl, MailClassTrl, AdClTrl, VDate, List, Home, PrimaryIndustryCode, PIGroup, SecondaryIndustryCode, BPASource, SourceID, TitleCode, TitleGroup, Channel, EffortSource, EffortKey, PaymentKey, GroupNum, RenewInd, BillSeriesInd, RenewCounter, DateofLastIssue, RemainingIssues, ForeignStateCode, SuspendCode, Split, ClCounter, OldPC, OldEffs, ReasonCode, PreviousStartDate, PreviousExpireDate, SubAccount, WebAccount, Phone, GiftType, PONum, AgeingDate, CycleID, OldDF, OldMatchkey, PurgeDate, MailInhibitor, SpecialFlag, OrderNo, FinishOps, SecondAlpha, ExpDemo1, ExpDemo2, ExpDemo3, ExpDemo4, ExpDemo5, MultDemos, FaxNumber, CurrCode, UnitNum, BFiller, UnitType, QDate, ADate, EmailAddress, ZipExt2, AddInd, TitleLiteral, ADate2, Amoney, CarrierRoute, DeliveryPoint, FinalIssue, AuxiliaryDemos, SecondaryDemos, ABSplit, Misc1Lit10A, Misc1Lit10B, Misc1Lit10C, Misc1Lit10D, OldEffort, Misc2Lit10A, Misc2Lit10B, Misc2Lit10C, Misc2Lit10D, Misc2Lit10E, Misc2Lit10F, Misc2Lit10G, Misc2Lit10H, Misc2Lit10I, Misc2Lit10J, GroupDate, Opt1, Opt2, Opt3, Opt4, Opt5, Opt6, Opt7, Opt8, Opt9, Misc3Num1, RenewalCounter, StateTaxNew, PostageNew, StateTaxTrlNew, PostageTrlNew, ForeignPhone, ForeignFax, ForeignPostalCode, Address2, CheckNumber, CCType, CCNumber, CCExpDate, PostalCarrier, PostalCarrierAcct, TaxExempt, TaxExemptNumber, PONumber, OrdDocLocTrl2, EDate, blnUpdated, datUpdatedOn, Agency)
     select lngSubscriptionID, Mag, Matchkey2, DomFor, SubName, PreName, FirstName, LastName, Title, Company, Division, Address, City,State, Zip, Zip4, Country, LDL, LDL_YCC, LDL_Batch, LDL_TranCode, PDL, TypeSub, StartDate, ExpireDate, Term, Price, ItmDolVal, StTax, Postage, AmtDue, AmtPd, DepositDate, AuditTrlNum, PayDocLoc, PayDoc_YCC, PayDoc_Batch, PayDoc_TranCode, OrdDocLoc, OrdDoc_YCC, OrdDoc_Batch, OrdDoc_TranCode, NumCopies, MailClass, AdCl, TypeSubTrl, StartDateTrl, ExpireDateTrl, TermTrl, PriceTrl, ItmDolValTrl, StTaxTrl, PostageTrl, AmtDueTrl, AmtPdTrl, DepositDateTrl, AuditTrlNumTrl, PayDocLocTrl, PayDocTrl_YCC, PayDocTrl_Batch, PayDocTrl_TranCode, OrdDocLocTrl, OrdDocTrl_YCC,  OrdDocTrl_Batch, OrdDocTrl_TranCode, NumCopiesTrl, MailClassTrl, AdClTrl, VDate, List, Home, PrimaryIndustryCode, PIGroup, SecondaryIndustryCode, BPASource, SourceID, TitleCode, TitleGroup, Channel, EffortSource, EffortKey, PaymentKey, GroupNum, RenewInd, BillSeriesInd, RenewCounter, DateofLastIssue, RemainingIssues, ForeignStateCode, SuspendCode, Split, ClCounter, OldPC, OldEffs, ReasonCode, PreviousStartDate, PreviousExpireDate, SubAccount, WebAccount, Phone, GiftType, PONum, AgeingDate, CycleID, OldDF, OldMatchkey, PurgeDate, MailInhibitor, SpecialFlag, OrderNo, FinishOps, SecondAlpha, ExpDemo1, ExpDemo2, ExpDemo3, ExpDemo4, ExpDemo5, MultDemos, FaxNumber, CurrCode, UnitNum, BFiller, UnitType, QDate, ADate, EmailAddress, ZipExt2, AddInd, TitleLiteral, ADate2, Amoney, CarrierRoute, DeliveryPoint, FinalIssue, AuxiliaryDemos, SecondaryDemos, ABSplit, Misc1Lit10A, Misc1Lit10B, Misc1Lit10C, Misc1Lit10D, OldEffort, Misc2Lit10A, Misc2Lit10B, Misc2Lit10C, Misc2Lit10D, Misc2Lit10E, Misc2Lit10F, Misc2Lit10G, Misc2Lit10H, Misc2Lit10I, Misc2Lit10J, GroupDate, Opt1, Opt2, Opt3, Opt4, Opt5, Opt6, Opt7, Opt8, Opt9, Misc3Num1, RenewalCounter, StateTaxNew, PostageNew, StateTaxTrlNew, PostageTrlNew, ForeignPhone, ForeignFax, ForeignPostalCode, Address2, CheckNumber, CCType, CCNumber, CCExpDate, PostalCarrier, PostalCarrierAcct, TaxExempt, TaxExemptNumber, PONumber, OrdDocLocTrl2, EDate, blnUpdated, datUpdatedOn, Agency from [' + @strPubID + '] where MatchKey = ' +char(39) + @strmatchKey +char(39)


exec(@sql)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Also, please maintain this abandoned question:
http://www.experts-exchange.com/Web/Web_Languages/XML/Q_20555361.html
0
 

Author Comment

by:cntrymannj
Comment Utility
I would love to maintain that question but i have no idea how to remove it or mark it as anything other than an accepted answer. None of the answered worked and I ended up finding a different way of doing what i needed to do. If you know how to remove the question please tell me.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>If you know how to remove the question please tell me.<<
What are my choices?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi67

And in particular:
Nobody answered my question. What do I do?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi71
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now