BCP doubles the result - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

BCP doubles the result

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-03-24 : 08:46:19

Janet writes "Hi
I am new and unexperienced SQL developer. I was not able to find an explanation to my issue anywhere in the help topics. I have two SPs that are running on SQL7 for years properly, however, when the same code I execute on SQL2000 server, the result is doubled.

Here is the line from .bat file that executes the SP:

@bcp "exec %dbname%..spAMSMTrans" queryout %flpath%\%flnm%.txt -S%servername% -U%uid% -P%pswd% -f%flpath%\%flnm%.fmt -e%flpath%\%flnm%.err -o%flpath%\%flnm%.out

Thanks a lot for your help
The SP is written as follows:

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[spAMSMInv]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[spAMSMInv]

GO

-- Set Up MS SQL Server Specifics -----------------------------------------------------------

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

-- Begin Stored Procedure Creation ----------------------------------------------------------
CREATE PROC dbo.spAMSMInv AS

set nocount on

DECLARE @lastRunDt datetime
DECLARE @defaultRunDt datetime
DECLARE @CNTRL_NAME char(8)

-- Set defaults for the variables

SELECT @CNTRL_NAME = 'AMSMTRAN'
SELECT @defaultRunDt = '2002-04-22 00:00:00.000'

-- Get the last run date from the control table
SELECT @lastRunDt = last_run_date
FROM AMSM_RUN_CNTL
WHERE cntl_name = @CNTRL_NAME

-- See if we need to set the default run date (initial run only)
IF @lastRunDt is NULL
SELECT @lastRunDt = @defaultRunDt
INSERT AMSM_INVENTORY
(Account_ID
,CUSIP_ID
,Qty_of_Shares
,Unit_cost
,Trade_Dt
,Ind
)

Union ALL
SELECT CONVERT(CHAR(8), holding.Account_ID)
,SUBSTRING(ASSET.CUSIP_ID,1,9)
,str(TAX_DETAIL.Shares_Par_Value_Qty, 15,4)
,str(TAX_DETAIL.Federal_Tax_Cost_amt/TAX_DETAIL.Shares_Par_Value_Qty, 20,8)
,ISNULL(CONVERT(char(8), TAX_DETAIL.Federal_Tax_Acquisition_Dt, 112),'00010101' )
,case
when TAX_DETAIL.Federal_Tax_Cost_amt/TAX_DETAIL.Shares_Par_Value_Qty > 99999.99999999
or TAX_DETAIL.Federal_Tax_Cost_amt/TAX_DETAIL.Shares_Par_Value_Qty < -99999.99999999
then 'O'
else ' '
end

FROM HOLDING INNER JOIN

ASSET ON HOLDING.Property_Num = ASSET.Property_Num AND
NOT asset.asset_class_cd in (1,2,3,4,5,6,11)
left JOIN
TAX_DETAIL ON HOLDING.Account_ID = TAX_DETAIL.Account_ID and
HOLDING.Property_Num = TAX_DETAIL.Property_Num and
HOLDING.Portfolio_Num = TAX_DETAIL.Portfolio_Num
left JOIN
account ON HOLDING.Account_ID = account.Account_ID

WHERE HOLDING.Shares_Par_Value_Qty <> 0
and HOLDING.sale_dt is null
and not account.Minor_Account_Tp IN (92, 93)
and not Control_Group_Cd in (11,13) --j.p per Lisa N exclude Insurance Trust accounts
and account.Account_Status_Cd = 0
and not account.Control_id in (401, 450)
and TAX_DETAIL.Federal_Tax_Cost_amt is not null
and TAX_DETAIL.Shares_Par_Value_Qty is not null
--and (account.Account_Termination_Dt is null
-- or not account.Account_Termination_Dt <= @lastRunDt)
UNION ALL

SELECT CONVERT(CHAR(8),ACCOUNT_balance.Account_ID)
,'999999999'
,str(principal_Cash_Amt + ISNULL(prin_cash,0), 15,4)
,' 1.00000000'
,ISNULL(CONVERT(char(8),Account_Balance.Last_Update_Dt, 112),'00010101' )
,' '

FROM Account_Balance left join acct_prin_cash
ON ACCOUNT_balance.Account_ID = acct_Prin_cash.Account_ID

left JOIN ACCOUNT
ON Account_Balance.Account_ID = account.Account_ID

WHERE not account.Minor_Account_Tp IN (92, 93)
a

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-24 : 12:31:59

Forget about bcp for now. Does the stored procedure show the doubling up in Query Analyzer?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-24 : 14:35:38

What happens when you just execute the sproc?

How many rows do you get?

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-24 : 14:40:29

Taking another look at it, I think the problem is here:

...
INSERT AMSM_INVENTORY
(Account_ID
,CUSIP_ID
,Qty_of_Shares
,Unit_cost
,Trade_Dt
,Ind
)

Union ALL
SELECT CONVERT(CHAR(8), holding.Account_ID)
...

What's with the UNION ALL after the INSERT? It should only be between SELECTs.

And shouldn't this:

WHERE not account.Minor_Account_Tp IN (92, 93)

become this:

WHERE account.Minor_Account_Tp NOT IN (92, 93)

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources