Feb 28, 2008
I have just about figured everything out except for 2 things.
First sometimes there will not be a ‘177’ record. How can I structure my IF statement so I can bypass the code. Other wise it creates a hdr and a blank detail record? Or is there another conditional verb I can use that might be better?
Second I can’t get my cnt to show up in my hdr records. The print statement is not even showing them. What am I doing wrong?
Any ideas?
Thanx in advance,
Here's my code:
DECLARE @Cnt int
DECLARE @Sys varchar (4);
DECLARE @vwSys varchar (4), @vwPrin varchar (4)
/* Define Cursor */
/*Works like an ARRAY*//*holds current record */
Declare vwCursor CURSOR FOR
SELECT Sys, Prin FROM dbo.vwPrin
ORder by Sys, Prin
open vwCursor
FETCH NEXT FROM vwCursor INTO @vwSys, @vwPrin
print '1st FETCH '
Print @vwSys
Print @vwPrin
SET @Sys = @vwSys
WHILE @@Fetch_Status = 0--34
BEGIN
INSERT INTO tblOutput (Batch_Type, Batch_Num, Sys, Prin, Terminal_id, Op_Code)
SELECT DISTINCT TOP(1) '9', @Cnt, Sys, Prin, Term_Id, Op_Code
FROM dbo.tblTrans
WHERE Sys = @vwSys AND Prin = @vwPrin
INSERT INTO tblOutput (Account_Num, Tran_Code, SubTrans, Terminal_Id, Op_Code)--45
SELECT Account_Number, Transaction_Code, SubTrans, Term_id, Op_Code
FROM dbo.tblTrans
WHERE dbo.tblTrans.Transaction_Code = '020' AND Sys = @vwSys AND Prin = @vwPrin
SET @Cnt = @Cnt + 1
Print @cnt
INSERT INTO tblOutput (Batch_Type, Batch_Num, Sys, Prin, Terminal_id, Op_Code)
SELECT DISTINCT TOP (1) '9', @cnt, Sys, Prin, Term_Id, Op_Code
FROM dbo.tblTrans
WHERE Sys = @vwSys AND Prin = @vwPrin
INSERT INTO tblOutput (Account_Num, Tran_Code, Clerk_Code, Memo_Text)--45
SELECT Account_Number, Transaction_Code, Clerk_Code, Memo_Text
FROM dbo.tblTrans
WHERE dbo.tblTrans.Transaction_Code = '115' AND Sys = @vwSys AND Prin = @vwPrin
SET @Cnt = @Cnt + 1
[b]IF (SELECT Transaction_Code = '177' AND Sys = @vwSys AND Prin = @vwPrin FROM dbo.tblTrans)[/b]
BEGIN
INSERT INTO tblOutput (Batch_Type, Batch_Num, Sys, Prin, Terminal_id, Op_Code)
SELECT DISTINCT TOP (1) '9', @cnt, Sys, Prin, Term_Id, Op_Code
FROM dbo.tblTrans
WHERE Sys = @vwSys AND Prin = @vwPrin
INSERT INTO tblOutput (Account_Num, Tran_Code, SubTrans, SubTrans_2, Terminal_Id, Op_Code)--45
SELECT Account_Number, Transaction_Code, SubTrans, SubTrans2, Term_Id, Op_Code
FROM dbo.tblTrans--55
WHERE dbo.tblTrans.Transaction_Code = '177' AND Sys = @vwSys AND Prin = @vwPrin
SET @Cnt = @Cnt + 1
/*Continue FETCH Command until EOF */
FETCH NEXT FROM vwCursor INTO @vwSys, @vwPrin
print ' 2nd FETCH '
Print @cnt
END
--Print @vwSys
--Print @vwPrin
END
CLOSE vwCursor
DEALLOCATE vwCursor
END
View 8 Replies
View Related
Oct 23, 2007
I am trying to export a table from SQL 2005 to excel and I recieve this error:
Error 0xc002f210: Prepearation SQL Task:Executing the query "CREATE TABLE...
Here is the error report:
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Success)
- Setting Destination Connection (Success)
- Validating (Success)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Error)
Messages
* Error 0xc002f210: Preparation SQL Task: Executing the query "CREATE TABLE `customer` (
`company` LongText,
`custid` LongText,
`custnum` Long,
`name` LongText,
`address1` LongText,
`address2` LongText,
`address3` LongText,
`city` LongText,
`state` LongText,
`zip` LongText,
`country` LongText,
`resaleid` LongText,
`salesrepcode` LongText,
`territoryid` LongText,
`shiptonum` LongText,
`termscode` LongText,
`shipviacode` LongText,
`printstatements` Byte,
`printlabels` Byte,
`printack` Byte,
`fincharges` Byte,
`credithold` Byte,
`groupcode` LongText,
`discountpercent` Decimal(6,2),
`primpcon` Long,
`primbcon` Long,
`primscon` Long,
`comment_` LongText,
`estdate` DateTime,
`faxnum` LongText,
`phonenum` LongText,
`taxexempt` LongText,
`aracctid` LongText,
`markupid` LongText,
`billday` Long,
`oneinvperps` Byte,
`defaultfob` LongText,
`creditincludeorders` Byte,
`creditreviewdate` DateTime,
`creditholddate` DateTime,
`creditholdsource` LongText,
`creditclearuserid` LongText,
`creditcleardate` DateTime,
`creditcleartime` LongText,
`edicode` LongText,
`obsolete803_editest` Byte,
`obsolete803_editranslator` LongText,
`character01` LongText,
`character02` LongText,
`character03` LongText,
`character04` LongText,
`character05` LongText,
`character06` LongText,
`character07` LongText,
`character08` LongText,
`character09` LongText,
`character10` LongText,
`number01` Decimal(20,9),
`number02` Decimal(20,9),
`number03` Decimal(20,9),
`number04` Decimal(20,9),
`number05` Decimal(20,9),
`number06` Decimal(20,9),
`number07` Decimal(20,9),
`number08` Decimal(20,9),
`number09` Decimal(20,9),
`number10` Decimal(20,9),
`date01` DateTime,
`date02` DateTime,
`date03` DateTime,
`date04` DateTime,
`date05` DateTime,
`checkbox01` Byte,
`checkbox02` Byte,
`checkbox03` Byte,
`checkbox04` Byte,
`checkbox05` Byte,
`currencycode` LongText,
`countrynum` Long,
`langnameid` LongText,
`bordercrossing` LongText,
`formatstr` LongText,
`btname` LongText,
`btaddress1` LongText,
`btaddress2` LongText,
`btaddress3` LongText,
`btcity` LongText,
`btstate` LongText,
`btzip` LongText,
`btcountrynum` Long,
`btcountry` LongText,
`btphonenum` LongText,
`btfaxnum` LongText,
`btformatstr` LongText,
`parentcustnum` Long,
`taxregioncode` LongText,
`iccust` Byte,
`contbillday` Long,
`emailaddress` LongText,
`shippingqualifier` LongText,
`allocprioritycode` LongText,
`linkportnum` Long,
`webcustomer` Byte,
`customertype` LongText,
`nocontact` Byte,
`territorylock` Byte,
`custurl` LongText,
`pendingterritoryid` LongText,
`extid` LongText,
`consolidateso` Byte,
`bill_frequency` LongText,
`creditincludepi` Byte,
`globalcust` Byte,
`ictrader` Byte,
`taxauthoritycode` LongText,
`externaldeliverynote` Byte,
`globalcredincord` Byte,
`globalcredincpi` Byte,
`globalcurrencycode` LongText,
`externalid` LongText,
`globalcredithold` LongText,
`globallock` Byte,
`checkduplicatepo` Byte,
`creditlimit` Decimal(15,0),
`custpilimit` Decimal(15,0),
`globalcreditlimit` Decimal(15,0),
`globalpilimit` Decimal(15,0),
`docglobalcreditlimit` Decimal(15,0),
`docglobalpilimit` Decimal(15,0),
`rfqattachallow` Byte,
`discountqualifier` LongText,
`number11` Decimal(20,9),
`number12` Decimal(20,9),
`number13` Decimal(20,9),
`number14` Decimal(20,9),
`number15` Decimal(20,9),
`number16` Decimal(20,9),
`number17` Decimal(20,9),
`number18` Decimal(20,9),
`number19` Decimal(20,9),
`number20` Decimal(20,9),
`date06` DateTime,
`date07` DateTime,
`date08` DateTime,
`date09` DateTime,
`date10` DateTime,
`date11` DateTime,
`date12` DateTime,
`date13` DateTime,
`date14` DateTime,
`date15` DateTime,
`date16` DateTime,
`date17` DateTime,
`date18` DateTime,
`date19` DateTime,
`date20` DateTime,
`checkbox06` Byte,
`checkbox07` Byte,
`checkbox08` Byte,
`checkbox09` Byte,
`checkbox10` Byte,
`checkbox11` Byte,
`checkbox12` Byte,
`checkbox13` Byte,
`checkbox14` Byte,
`checkbox15` Byte,
`checkbox16` Byte,
`checkbox17` Byte,
`checkbox18` Byte,
`checkbox19` Byte,
`checkbox20` Byte,
`shortchar01` LongText,
`shortchar02` LongText,
`shortchar03` LongText,
`shortchar04` LongText,
`shortchar05` LongText,
`shortchar06` LongText,
`shortchar07` LongText,
`shortchar08` LongText,
`shortchar09` LongText,
`shortchar10` LongText,
`allowaltbillto` Byte,
`demanddeliverydays` Long,
`demanddatetype` LongText,
`demandaddleadtime` Long,
`demandaddaction` LongText,
`demandchangeleadtime` Long,
`demandchangeaction` LongText,
`demandcancelleadtime` Long,
`demandcancelaction` LongText,
`demandnewlineleadtime` Long,
`demandnewlineaction` LongText,
`demandqtychangeleadtime` Long,
`demandqtychangeaction` LongText,
`demandchangedateleadtime` Long,
`demandchangedateaction` LongText,
`tradingpartnername` LongText,
`resdelivery` Byte,
`satdelivery` Byte,
`satpickup` Byte,
`hazmat` Byte,
`doconly` Byte,
`refnotes` LongText,
`applychrg` Byte,
`chrgamount` Decimal(16,2),
`cod` Byte,
`codfreight` Byte,
`codcheck` Byte,
`codamount` Decimal(16,2),
`groundtype` LongText,
`notifyflag` Byte,
`notifyemail` LongText,
`declaredins` Byte,
`declaredamt` Decimal(16,2),
`periodicitycode` Long,
`servsignature` Byte,
`servalert` Byte,
`servhomedel` Byte,
`deliverytype` LongText,
`servdeliverydate` DateTime,
`servphone` LongText,
`servinstruct` LongText,
`servrelease` Byte,
`servauthnum` LongText,
`servref1` LongText,
`servref2` LongText,
`servref3` LongText,
`servref4` LongText,
`servref5` LongText,
`earlybuffer` Long,
`latebuffer` Long,
`demandunitpricediff` Byte,
`demandunitpricediffaction` LongText,
`excfromval` Byte,
`addressval` Byte,
`rebatevendornum` Long,
`rebateform` LongText,
`creditcardorder` Byte,
`demandcheckforpart` Byte,
`demandcheckforpartaction` LongText,
`changedby` LongText,
`changedate` DateTime,
`changetime` Long,
`chargecode` LongText,
`individualpackids` Byte,
`intrntlship` Byte,
`certoforigin` Byte,
`commercialinvoice` Byte,
`shipexprtdeclartn` Byte,
`letterofinstr` Byte,
`ffid` LongText,
`ffcompname` LongText,
`ffaddress1` LongText,
`ffaddress2` LongText,
`ffaddress3` LongText,
`ffcity` LongText,
`ffstate` LongText,
`ffzip` LongText,
`ffcountry` LongText,
`ffcountrynum` Long,
`ffphonenum` LongText,
`nonstdpkg` Byte,
`deliveryconf` Long,
`addlhdlgflag` Byte,
`upsquantumview` Byte,
`upsqvshipfromname` LongText,
`upsqvmemo` LongText,
`upsqvemailtype` LongText,
`ffcontact` LongText,
`etcaddrchg` Byte,
`PROGRESS_RECID` Decimal(19,0),
`PROGRESS_RECID_IDENT_` Decimal(19,0)
)
" failed with the following error: "Too many fields defined.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
(SQL Server Import and Export Wizard)
- Copying to `customer` (Stopped)
- Post-execute (Stopped)
- Cleanup (Stopped)
I am new to SQL so any help would be greatly appreciated.
View 1 Replies
View Related
