p2p replication error too many arguments specified

Currently, we have peer-to-peer replication on two servers (server1 and
server2).
I added a few columns using sp_repladdcolumn stored procedure. Rows inserted
at Server1
are replicated to Server2 without problems, and rows inserted Server2 can
not be replicated to Server1
because of the error below. Any help is appreciated. thanks, D
Command attempted:
if @.@.trancount > 0 rollback tran
(Transaction sequence number: 0x0000008600000EE7000400000000, Command ID: 1)
Error messages:
Procedure or function sp_MSins_dboCustomers0823818373 has too many arguments
specified. (Source: MSSQLServer, Error number: 8144)
Get help: http://help/8144
Procedure or function sp_MSins_dboCustomers0823818373 has too many arguments
specified. (Source: MSSQLServer, Error number: 8144)
Get help: http://help/8144
Paul,
First time at this. Not sure how to quiesce the system. Does
this mean change db to single user?
I've used sp_repladdcolumn to add two columns to the publisher's article.
Both tables at subscriber and publisher do reflect the new columns.
However, stored procedures at the publisher do not receive the changes
while stored procedures at subscriber do.
When a new record is inserted into the table at publisher, the
new record does get distributed. When a new record is inserted
at the subscriber, the record got an error when being distributed.
Thanks, D
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OwyqlhJwHHA.4612@.TK2MSFTNGP04.phx.gbl...
> 1.. Quiesce the system.
> 2.. Execute the data definition language (DDL) statements to modify the
> schema of published tables. For more information about supported schema
> changes, see Making Schema Changes on Publication Databases.
> 3.. Before resuming activity on published tables, quiesce the system
> again. This ensures that schema changes have been received by all nodes
> before any new data changes are replicated
> The general advice is to do things as above - is that how you made the
> schema change?
> I'm also interested in what has actually happened - do the tables have an
> equal number of columns now? Can you also compare the text of the 2
> sp_MSins_dboCustomers procs (pub and subs) and see if they are different.
> If so, can you tell us which one is correct.
> Cheers,
> Paul Ibison
>

Owner of tables

I have a stored procedure, that among other things drops and recreates two
new tables:
drop table dbo.t_de_tracking
drop table dbo.t_MR_TRACKING
SELECT * INTO dbo.T_DE_TRACKING
FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
T_DE_TRACKING (nolock)')
SELECT * INTO dbo.T_MR_TRACKING
FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
T_MR_TRACKING (nolock)')
When these tables are created the owner is NOT dbo. How can I get the owner
of these tables to become dbo when created by this stored procedure?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200510/1Robert,
Do you really want to drop/create the tables or just TRUNCATE the table and
reload it? Probably not the best approach esp if there are a lot of records
and/or NC indexes. What are the fixed server and fixed database role
memberships of the user creating this proc? Also, you might want to build
some IF logic into testing if the tables exist prior to dropping them.
HTH
Jerry
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:55d48972e2388@.uwe...
>I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the
> owner
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200510/1|||Try naming the stored procedure dbo.[sproc name]
--
Thomas
"Robert R via droptable.com" wrote:

> I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the owne
r
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200510/1
>

Owner of tables

I have a stored procedure, that among other things drops and recreates two
new tables:
drop table dbo.t_de_tracking
drop table dbo.t_MR_TRACKING
SELECT * INTO dbo.T_DE_TRACKING
FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
T_DE_TRACKING (nolock)')
SELECT * INTO dbo.T_MR_TRACKING
FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
T_MR_TRACKING (nolock)')
When these tables are created the owner is NOT dbo. How can I get the owner
of these tables to become dbo when created by this stored procedure?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1Robert,
Do you really want to drop/create the tables or just TRUNCATE the table and
reload it? Probably not the best approach esp if there are a lot of records
and/or NC indexes. What are the fixed server and fixed database role
memberships of the user creating this proc? Also, you might want to build
some IF logic into testing if the tables exist prior to dropping them.
HTH
Jerry
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:55d48972e2388@.uwe...
>I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the
> owner
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1|||Try naming the stored procedure dbo.[sproc name]
--
Thomas
"Robert R via SQLMonster.com" wrote:
> I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the owner
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1
>

Owner of tables

I have a stored procedure, that among other things drops and recreates two
new tables:
drop table dbo.t_de_tracking
drop table dbo.t_MR_TRACKING
SELECT * INTO dbo.T_DE_TRACKING
FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
T_DE_TRACKING (nolock)')
SELECT * INTO dbo.T_MR_TRACKING
FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
T_MR_TRACKING (nolock)')
When these tables are created the owner is NOT dbo. How can I get the owner
of these tables to become dbo when created by this stored procedure?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200510/1
Robert,
Do you really want to drop/create the tables or just TRUNCATE the table and
reload it? Probably not the best approach esp if there are a lot of records
and/or NC indexes. What are the fixed server and fixed database role
memberships of the user creating this proc? Also, you might want to build
some IF logic into testing if the tables exist prior to dropping them.
HTH
Jerry
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:55d48972e2388@.uwe...
>I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the
> owner
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200510/1
|||Try naming the stored procedure dbo.[sproc name]
Thomas
"Robert R via droptable.com" wrote:

> I have a stored procedure, that among other things drops and recreates two
> new tables:
> drop table dbo.t_de_tracking
> drop table dbo.t_MR_TRACKING
> SELECT * INTO dbo.T_DE_TRACKING
> FROM OPENQUERY(ACS_SLR_DE, 'SELECT * FROM ACS_SLR_DATAENTRY..
> T_DE_TRACKING (nolock)')
> SELECT * INTO dbo.T_MR_TRACKING
> FROM OPENQUERY(ACS_SLR_MR, 'SELECT * FROM ACS_SLR_MAILROOM..
> T_MR_TRACKING (nolock)')
> When these tables are created the owner is NOT dbo. How can I get the owner
> of these tables to become dbo when created by this stored procedure?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200510/1
>

owner data objects

I've a problem.

In my MSSql db I've some tables named username.mytable and only one
store procedure named dbo.sp;

CREATE TABLE [pippo].[mytable] (
[year] [int] NOT NULL ,
[month] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [pluto].[mytable] (
[year] [int] NOT NULL ,
[month] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[sp]
AS
select * from mytable
GO

but when I try to execute dbo.sp (from pippo or pluto connection) I've
this error (users pippo and pluto are owner):

Server: Msg 208, Level 16, State 1, Procedure sp, Line 4
Invalid object name 'mytable'.

How can I access to pippo's (or pluto's) data from dbo.sp?

thanks!!zMatteo (origma@.edpsistem.it) writes:
> I've a problem.
> In my MSSql db I've some tables named username.mytable and only one
> store procedure named dbo.sp;
>
> CREATE TABLE [pippo].[mytable] (
> [year] [int] NOT NULL ,
> [month] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [pluto].[mytable] (
> [year] [int] NOT NULL ,
> [month] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE PROCEDURE [dbo].[sp]
> AS
> select * from mytable
> GO
>
> but when I try to execute dbo.sp (from pippo or pluto connection) I've
> this error (users pippo and pluto are owner):
> Server: Msg 208, Level 16, State 1, Procedure sp, Line 4
> Invalid object name 'mytable'.
> How can I access to pippo's (or pluto's) data from dbo.sp?

SELECT * FROM pippo.mytable

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9629B0769BAFBYazorman@.127.0.0.1>...
> zMatteo (origma@.edpsistem.it) writes:
> > I've a problem.
> > In my MSSql db I've some tables named username.mytable and only one
> > store procedure named dbo.sp;
> > CREATE TABLE [pippo].[mytable] (
> > [year] [int] NOT NULL ,
> > [month] [int] NOT NULL
> > ) ON [PRIMARY]
> > GO
> > CREATE TABLE [pluto].[mytable] (
> > [year] [int] NOT NULL ,
> > [month] [int] NOT NULL
> > ) ON [PRIMARY]
> > GO
> > CREATE PROCEDURE [dbo].[sp]
> > AS
> > select * from mytable
> > GO
> > but when I try to execute dbo.sp (from pippo or pluto connection) I've
> > this error (users pippo and pluto are owner):
> > Server: Msg 208, Level 16, State 1, Procedure sp, Line 4
> > Invalid object name 'mytable'.
> > How can I access to pippo's (or pluto's) data from dbo.sp?
> SELECT * FROM pippo.mytable

but for user pluto i'd make a new store procedure...
I'd resolve the problem (attention: it's ok only for not sysadmin users)!!:

CREATE TABLE [dbo].[mytable] (
[user] [smallint] NOT NULL,
[year] [int] NOT NULL ,
[month] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE VIEW [dbo].[myview]
AS
select * from mytable
where user=user_id()
GO

CREATE PROCEDURE [dbo].[sp]
AS
select * from myview
GO

Overriding Defaults

I have a stored procedure that has @.BeginDate and @.EndDate as parameters. I created a report with a default for both. They run just fine. After I deployed, I created Linked Reports and wanted to override the defaults. In the defaults, I tried to put in GetDate() for @.BeginDate and GetDate()+10 for the @.EndDate so this can be passed in the where statement of the stored procedure. I get 'Syntax error converting datetime from character string.'

What I assume is that if I override the default, the stored procedure will process what I put in the @.BeginDate and @.EndDate parameters.

The where statement looks like:

and (m.BeginDate >= @.BeginDate) and (m.EndDate <= @.EndDate)

I'm using Reporting Services 2005 and SQL Server 2005.

Thanks, Iris

I have figured out my own problem. In my where statement, in the stored procedure, I put in a case statement that calculates for today, yesterday, etc. for the BeginDate and EndDate parameters. I then created available values for today, yesterday, etc. I made the default for both parameters 'Today'. Then when I created my linked report from my master report, there was a drop down to select my date range options.

Thanks, Iris

Overloaded Stored Procedure

Hi

I want to create two stored procedures with the same name but accepting different params. Lets say Procedure A can accept param1, Procedure A can also accept param1,param2.

Is there any way SQL Server supports overloaded procedures with different sigantures.

Regards
ImtiazYou can use a feature called numbered stored procedures. So you can create somesp;1 and somesp;2. But I would not recommend doing this since the feature is being deprecated. In general, for data access or modifications you want to keep the TSQL interfaces simple to use.|||Thanks..As expected u had replied.....|||Can we also have something like overloaded functions in SQL 2005.|||No, but optional parameters are supported: CREATE PROC YourProc @.Param1 INT, @.Param2 INT = 0 AS ... -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Imtiaz@.discussions.microsoft.com> wrote in message news:5dcaf46b-f56a-452b-b9a3-dc23f7c4e7a9@.discussions.microsoft.com...HiI want to create two stored procedures with the same name but accepting different params. Lets say Procedure A can accept param1, Procedure A can also accept param1,param2.Is there any way SQL Server supports overloaded procedures with different sigantures.RegardsImtiaz

Overflowed int column

I am trying to run a stored procedure but I get the
following error:
Server: Msg 248, Level 16, State 1, Procedure
sp_MarriageLookup, Line 34
The conversion of the varchar value '2820103430'
overflowed an int column. Maximum integer value exceeded.
Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
Return Code = -6
Output Parameter(s):
@.Barcode = 2820103430
The length of the @.Barcode variable is 10 characters and
it is a nchar type variable so I don't understand the 'int
column' overflow issue.
Also, the wierd thing is that I can run this same stored
procedure on another server (similar database, just
different data) and it works just fine. See below:
Stored Procedure: Unifirst.dbo.sp_MarriageLookup
Return Code = 0
Output Parameter(s):
@.Barcode = 4320000849
I don't see anything noticibly different between the two
databases.
What causes this problem and how do I correct?
Roger.Maximum value for int (SQL Server) data type is 2147483647.
So, if you convert varchar value '2820103430' to int,
overflow error is normal.
In the secend case, when this stored procedure
work correctly on another server, check data type
of the column to which this sp convert varchar parameter
ph

> I am trying to run a stored procedure but I get the
> following error:
> Server: Msg 248, Level 16, State 1, Procedure
> sp_MarriageLookup, Line 34
> The conversion of the varchar value '2820103430'
> overflowed an int column. Maximum integer value exceeded.
> Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
> Return Code = -6
> Output Parameter(s):
> @.Barcode = 2820103430
> The length of the @.Barcode variable is 10 characters and
> it is a nchar type variable so I don't understand the 'int
> column' overflow issue.
> Also, the wierd thing is that I can run this same stored
> procedure on another server (similar database, just
> different data) and it works just fine. See below:
> Stored Procedure: Unifirst.dbo.sp_MarriageLookup
> Return Code = 0
> Output Parameter(s):
> @.Barcode = 4320000849
> I don't see anything noticibly different between the two
> databases.
> What causes this problem and how do I correct?
> Roger.

Overflowed int column

I am trying to run a stored procedure but I get the
following error:
Server: Msg 248, Level 16, State 1, Procedure
sp_MarriageLookup, Line 34
The conversion of the varchar value '2820103430'
overflowed an int column. Maximum integer value exceeded.
Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
Return Code = -6
Output Parameter(s):
@.Barcode = 2820103430
The length of the @.Barcode variable is 10 characters and
it is a nchar type variable so I don't understand the 'int
column' overflow issue.
Also, the wierd thing is that I can run this same stored
procedure on another server (similar database, just
different data) and it works just fine. See below:
Stored Procedure: Unifirst.dbo.sp_MarriageLookup
Return Code = 0
Output Parameter(s):
@.Barcode = 4320000849
I don't see anything noticibly different between the two
databases.
What causes this problem and how do I correct?
Roger.Here is a copy of the SP:
/*
Purpose: Given a marriage tag code, return the original
barcode
Input params: Marriage tag code, type varchar(10)
Output params: Original bar code, type varchar(10)
Error params: Output of '9' indicates lookup failed
*/
CREATE PROCEDURE sp_MarriageLookup
@.Tag varchar(10),
@.Barcode varchar(10) output
AS
Declare @.eventid int
Declare cur_Marriage cursor for
Select CurrentTag from tblMarriage
Where MarriageTag = @.Tag
Open cur_Marriage
Fetch next from cur_Marriage into @.Barcode
If (@.@.Fetch_Status <> 0)
Begin
/* Error log: Lookup failed */
Set @.Barcode = 9
Set @.eventid = 901
End
Else Begin
Fetch Next from cur_Marriage into @.Barcode
If (@.@.Fetch_Status = 0)
Begin
/* Error log: More than one entry for the marriage
code */
Set @.Barcode = 9
Set @.eventid = 902
End
End
Close cur_Marriage
Deallocate cur_Marriage
If (@.Barcode = 9)
Begin
Execute sp_LogError 1, @.eventid, 0, @.Tag
End
GO
>--Original Message--
>I am trying to run a stored procedure but I get the
>following error:
>Server: Msg 248, Level 16, State 1, Procedure
>sp_MarriageLookup, Line 34
>The conversion of the varchar value '2820103430'
>overflowed an int column. Maximum integer value exceeded.
>Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
> Return Code = -6
> Output Parameter(s):
> @.Barcode = 2820103430
>The length of the @.Barcode variable is 10 characters and
>it is a nchar type variable so I don't understand
the 'int
>column' overflow issue.
>Also, the wierd thing is that I can run this same stored
>procedure on another server (similar database, just
>different data) and it works just fine. See below:
>Stored Procedure: Unifirst.dbo.sp_MarriageLookup
> Return Code = 0
> Output Parameter(s):
> @.Barcode = 4320000849
>I don't see anything noticibly different between the two
>databases.
>What causes this problem and how do I correct?
>Roger.
>.
>|||Maximum value for int (SQL Server) data type is 2147483647.
So, if you convert varchar value '2820103430' to int,
overflow error is normal.
In the secend case, when this stored procedure
work correctly on another server, check data type
of the column to which this sp convert varchar parameter
ph
> I am trying to run a stored procedure but I get the
> following error:
> Server: Msg 248, Level 16, State 1, Procedure
> sp_MarriageLookup, Line 34
> The conversion of the varchar value '2820103430'
> overflowed an int column. Maximum integer value exceeded.
> Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
> Return Code = -6
> Output Parameter(s):
> @.Barcode = 2820103430
> The length of the @.Barcode variable is 10 characters and
> it is a nchar type variable so I don't understand the 'int
> column' overflow issue.
> Also, the wierd thing is that I can run this same stored
> procedure on another server (similar database, just
> different data) and it works just fine. See below:
> Stored Procedure: Unifirst.dbo.sp_MarriageLookup
> Return Code = 0
> Output Parameter(s):
> @.Barcode = 4320000849
> I don't see anything noticibly different between the two
> databases.
> What causes this problem and how do I correct?
> Roger.

Overflow error that doesn't make sense....

I'm troubleshooting a stored procedure that suddenly decided to stop working. I narrowed down the problem to the last part of the stored procedure where it selects data from a temp table and inserts it into a physical table in the SQL2000 database.

I keep receiving the following error:

Server: Msg 8115, Level 16, State 8, Line 140
Arithmetic overflow error converting numeric to data type numeric.

The data values all appear to be correct with none of them seeming to be out of precision, but I keep getting the error. I've tried casting all the values and it didn't work. It executes w/o error when I comment out that particular insert. I just don't get it.

Any help would be appreciated. Thanks.

Code below:
-

declare @.dtAsOfdate DATETIME
set @.dtAsOfDate = '2006-04-16';

DECLARE @.RC INTEGER
-
-- 1) Eligible Investments:
-

-- Input: @.SPVId - SPV we are running process for
-- @.Yes - value of enum CCPEnum::eYesNoYes (get by lookup).

-- Output: Recordset (temp table) of Collaterals that are eligible for MV Test (#MVTriggerInvestments).

DECLARE @.Yes INTEGER
EXEC @.RC = [dbo].CPLookupVal 'YesNo', 'Yes', @.Yes OUTPUT
IF (@.RC<>0)BEGIN
RAISERROR ('SP_OCCalculationMVTriggerTest: Failed to find Yes enum', 16, 1) WITH SETERROR
END
drop table #MVTriggerInvestments
BEGIN

SELECT dbal.SPVId,
dbal.CusipId,
dbal.GroupId,
@.dtAsOfDate AS AsOfDate,
dbal.NormalOCRate,
dbal.SteppedUpOCRate,
dbal.AllocMarketValue AS MarketValue,
dbal.NbrDays,
dbal.PriceChangeRatio

INTO #MVTriggerInvestments

FROM DailyCollateralBalance dbal

JOIN CollateralGroupIncludeInOC gin
ON dbal.SPVId = 2
AND gin.SPVId = 2
AND dbal.AsOfDate = '2006-04-16'
AND @.dtAsOfDate BETWEEN gin.EffectiveFrom AND gin.EffectiveTo
AND dbal.GroupId = gin.GroupId
AND gin.IncludeInOC = @.Yes

END
select * from #MVTriggerInvestments
print 'end #1'
--select * from #MVTriggerInvestments --looks ok

--
-- 2) Calculate Weighted Average Price change ratio Market Value (by Group):
-- PCRMV - Price Change Ratio Market Value
--

-- Input : Recordset of collaterals (having New/Old prices, MarketValue defined)
-- Output: Recordset Aggregated by Group (#GroupOCRate)
drop table #MVTriggerGroup
BEGIN

SELECT A.SPVId,
A.AsOfDate,
A.GroupId,
A.NormalOCRate,
A.SteppedUpOCRate,
A.MarketValue,

cast([dbo].fn_divide_or_number (B.PriceChangeRatioMarketValue, B.MarketValueForPeriod, 0.00) as numeric(12,9)) as PriceChangeRatio,

CAST (0 AS NUMERIC(12,9)) AS OCRate,
CAST ('' AS VARCHAR(6)) AS OCRateType,
CAST (0 AS NUMERIC(18,2)) AS DiscMarketValue,
CAST (0 AS NUMERIC(18,2)) AS InterestAccrued

INTO #MVTriggerGroup

FROM
(
SELECT SPVId,
AsOfDate,
GroupId,
NormalOCRate,
SteppedUpOCRate,
cast(SUM(MarketValue) as numeric(18,2)) AS MarketValue

FROM #MVTriggerInvestments
GROUP BY SPVId, AsOfDate, GroupId, NormalOCRate, SteppedUpOCRate
) A --works up to here

JOIN
(SELECT SPVId,
cast(SUM(AllocMarketValue) as numeric(18,2)) AS MarketValueForPeriod ,
cast(SUM(AllocMarketValue * PriceChangeRatio) as numeric(18,2)) as PriceChangeRatioMarketValue,
GroupId

FROM T_DailyCollateralBalance
WHERE SPVId = 2
AND AsOfDate between '2006-03-17' and '2006-04-15'
AND IsBusinessDay = 1
GROUP BY SPVId, GroupId
) B

ON A.SPVId = B.SPVId
AND A.GroupId = B.GroupId

END
print 'end #2'

-- Calculate OCRate to apply for each group.

BEGIN
UPDATE #MVTriggerGroup
SET OCRate = (CASE WHEN ((PriceChangeRatio < 0) AND ABS(PriceChangeRatio) > (0.55 * NormalOCRate)) THEN SteppedUpOCRate
ELSE NormalOCRate
END),
OCRateType = (CASE WHEN ((PriceChangeRatio < 0) AND ABS(PriceChangeRatio) > (0.55 * NormalOCRate)) THEN 'stepup'
ELSE 'normal'
END)
END
print 'end #3'
-
-- Calculate discounted Market Value
-
UPDATE #MVTriggerGroup
SET DiscMarketValue = MarketValue / (1.0 + OCRate * 0.01)
print 'end #4'

-- Insert data from temp tables

-- 1)
select * from #MVTriggerInvestments

print 'begin tran'
BEGIN TRAN
DELETE T_MVTriggerInvestments
WHERE SPVId = 2 AND AsOfDate = '2006-04-16'
print 'DELETE T_MVTriggerInvestments'
--error is here!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
INSERT T_MVTriggerInvestments
(
SPVId ,
CusipId ,
GroupId ,
AsOfDate ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
NbrDays ,
OldPrice ,
NewPrice ,
PriceChangeRatio
)
SELECT SPVId ,
CusipId ,
GroupId ,
AsOfDate ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
NbrDays ,
0.00 ,
0.00 ,
PriceChangeRatio

FROM #MVTriggerInvestments
print 'end mvtriggerinv select'
COMMIT TRAN
--end error!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- 2)
print 'begin tran 2'
BEGIN TRAN
DELETE T_OCTestGroup
WHERE SPVId = 2 AND AsOfDate = '2006-04-16'

INSERT T_OCTestGroup
(
SPVId ,
AsOfDate ,
GroupId ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
PriceChangeRatio,
OCRate ,
OCRateType ,
DiscMarketValue ,
InterestAccrued ,
SectionA ,
CPFace ,
IntExpense ,
Fees ,
SectionB ,
Receivables ,
IntReceivables ,
CashBalance ,
Investments ,
SectionC ,
ExcessCollateral,
MaxCPAllowed
)
SELECT
SPVId ,
AsOfDate ,
GroupId ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
PriceChangeRatio,
OCRate ,
OCRateType ,
DiscMarketValue ,
InterestAccrued ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0

FROM #MVTriggerGroup
print 'end tran 2'
COMMIT TRAN

Are the column definitions for the temp table the same for the table you're inserting into? Double-check the datatypes.|||Yes. I checked that and went through and tried casting any variables that might have been interpreted. I also checked the data and tried excluding certain records that had negative values (for example) and the error persists...
|||Maybe the permanent table has a computed column, or maybe there is a trigger on the table. Can you post the CREATE TABLE statements and the CREATE statements for any indexes, indexed views, or triggers for the tables involved? Steve Kass Drew University 7racer@.discussions.microsoft.com wrote:
> I'm troubleshooting a stored procedure that suddenly decided to stop
> working. I narrowed down the problem to the last part of the stored
> procedure where it selects data from a temp table and inserts it into a
> physical table in the SQL2000 database.
>
> I keep receiving the following error:
>
> Server: Msg 8115, Level 16, State 8, Line 140
> Arithmetic overflow error converting numeric to data type numeric.
>
> The data values all appear to be correct with none of them seeming to be
> out of precision, but I keep getting the error. I've tried casting all
> the values and it didn't work. It executes w/o error when I comment out
> that particular insert. I just don't get it.
>
> Any help would be appreciated. Thanks.
>
> Code below:
> -
>
> declare @.dtAsOfdate DATETIME
> set @.dtAsOfDate = '2006-04-16';
>
> DECLARE @.RC INTEGER
> -
> -- 1) Eligible Investments:
> -
>
> -- Input: @.SPVId - SPV we are running process for
> -- @.Yes - value of enum CCPEnum::eYesNoYes (get by
> lookup).
>
> -- Output: Recordset (temp table) of Collaterals that are eligible for
> MV Test (#MVTriggerInvestments).
>
> DECLARE @.Yes INTEGER
> EXEC @.RC = [dbo].CPLookupVal 'YesNo', 'Yes', @.Yes OUTPUT
> IF (@.RC<>0)BEGIN
> RAISERROR ('SP_OCCalculationMVTriggerTest: Failed to find Yes
> enum', 16, 1) WITH SETERROR
> END
> drop table #MVTriggerInvestments
> BEGIN
>
> SELECT dbal.SPVId,
> dbal.CusipId,
> dbal.GroupId,
> @.dtAsOfDate AS AsOfDate,
> dbal.NormalOCRate,
> dbal.SteppedUpOCRate,
> dbal.AllocMarketValue AS MarketValue,
> dbal.NbrDays,
> dbal.PriceChangeRatio
>
> INTO #MVTriggerInvestments
>
> FROM DailyCollateralBalance dbal
>
> JOIN CollateralGroupIncludeInOC gin
> ON dbal.SPVId = 2
> AND gin.SPVId = 2
> AND dbal.AsOfDate = '2006-04-16'
> AND @.dtAsOfDate BETWEEN gin.EffectiveFrom AND
> gin.EffectiveTo
> AND dbal.GroupId = gin.GroupId
> AND gin.IncludeInOC = @.Yes
>
> END
> select * from #MVTriggerInvestments
> print 'end #1'
> --select * from #MVTriggerInvestments --looks ok
>
> --
> -- 2) Calculate Weighted Average Price change ratio Market Value (by
> Group):
> -- PCRMV - Price Change Ratio Market Value
> --
>
> -- Input : Recordset of collaterals (having New/Old prices, MarketValue
> defined)
> -- Output: Recordset Aggregated by Group (#GroupOCRate)
> drop table #MVTriggerGroup
> BEGIN
>
> SELECT A.SPVId,
> A.AsOfDate,
> A.GroupId,
> A.NormalOCRate,
> A.SteppedUpOCRate,
> A.MarketValue,
>
> cast([dbo].fn_divide_or_number
> (B.PriceChangeRatioMarketValue, B.MarketValueForPeriod, 0.00) as
> numeric(12,9)) as PriceChangeRatio,
>
> CAST (0 AS NUMERIC(12,9))
> AS OCRate,
> CAST ('' AS VARCHAR(6))
> AS OCRateType,
> CAST (0 AS NUMERIC(18,2))
> AS DiscMarketValue,
> CAST (0 AS NUMERIC(18,2))
> AS InterestAccrued
>
> INTO #MVTriggerGroup
>
> FROM
> (
> SELECT SPVId,
> AsOfDate,
> GroupId,
> NormalOCRate,
> SteppedUpOCRate,
> cast(SUM(MarketValue) as numeric(18,2)) AS MarketValue
>
> FROM #MVTriggerInvestments
> GROUP BY SPVId, AsOfDate, GroupId, NormalOCRate,
> SteppedUpOCRate
> ) A --works up to here
>
> JOIN
> (SELECT SPVId,
> cast(SUM(AllocMarketValue) as numeric(18,2)) AS
> MarketValueForPeriod ,
> cast(SUM(AllocMarketValue * PriceChangeRatio) as
> numeric(18,2)) as PriceChangeRatioMarketValue,
> GroupId
>
> FROM T_DailyCollateralBalance
> WHERE SPVId = 2
> AND AsOfDate between '2006-03-17' and '2006-04-15'
> AND IsBusinessDay = 1
> GROUP BY SPVId, GroupId
> ) B
>
> ON A.SPVId = B.SPVId
> AND A.GroupId = B.GroupId
>
> END
> print 'end #2'
>
> -- Calculate OCRate to apply for each group.
>
> BEGIN
> UPDATE #MVTriggerGroup
> SET OCRate = (CASE WHEN ((PriceChangeRatio < 0) AND
> ABS(PriceChangeRatio) > (0.55 * NormalOCRate)) THEN SteppedUpOCRate
> ELSE NormalOCRate
> END),
> OCRateType = (CASE WHEN ((PriceChangeRatio < 0) AND
> ABS(PriceChangeRatio) > (0.55 * NormalOCRate)) THEN 'stepup'
> ELSE 'normal'
> END)
> END
> print 'end #3'
> -
> -- Calculate discounted Market Value
> -
> UPDATE #MVTriggerGroup
> SET DiscMarketValue = MarketValue / (1.0 + OCRate * 0.01)
> print 'end #4'
>
> -- Insert data from temp tables
>
> -- 1)
> select * from #MVTriggerInvestments
>
> print 'begin tran'
> BEGIN TRAN
> DELETE T_MVTriggerInvestments
> WHERE SPVId = 2 AND AsOfDate = '2006-04-16'
> print 'DELETE T_MVTriggerInvestments'
> --error is
> here!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
> INSERT T_MVTriggerInvestments
> (
> SPVId ,
> CusipId ,
> GroupId ,
> AsOfDate ,
> NormalOCRate ,
> SteppedUpOCRate ,
> MarketValue ,
> NbrDays ,
> OldPrice ,
> NewPrice ,
> PriceChangeRatio
> )
> SELECT SPVId ,
> CusipId ,
> GroupId ,
> AsOfDate ,
> NormalOCRate ,
> SteppedUpOCRate ,
> MarketValue ,
> NbrDays ,
> 0.00 ,
> 0.00 ,
> PriceChangeRatio
>
> FROM #MVTriggerInvestments
> print 'end mvtriggerinv select'
> COMMIT TRAN
> --end
> error!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
> !!!!!
> -- 2)
> print 'begin tran 2'
> BEGIN TRAN
> DELETE T_OCTestGroup
> WHERE SPVId = 2 AND AsOfDate = '2006-04-16'
>
> INSERT T_OCTestGroup
> (
> SPVId ,
> AsOfDate ,
> GroupId ,
> NormalOCRate ,
> SteppedUpOCRate ,
> MarketValue ,
> PriceChangeRatio,
> OCRate ,
> OCRateType ,
> DiscMarketValue ,
> InterestAccrued ,
> SectionA ,
> CPFace ,
> IntExpense ,
> Fees ,
> SectionB ,
> Receivables ,
> IntReceivables ,
> CashBalance ,
> Investments ,
> SectionC ,
> ExcessCollateral,
> MaxCPAllowed
> )
> SELECT
> SPVId ,
> AsOfDate ,
> GroupId ,
> NormalOCRate ,
> SteppedUpOCRate ,
> MarketValue ,
> PriceChangeRatio,
> OCRate ,
> OCRateType ,
> DiscMarketValue ,
> InterestAccrued ,
> 0 ,
> 0 ,
> 0 ,
> 0 ,
> 0 ,
> 0 ,
> 0 ,
> 0 ,
> 0 ,
> 0 ,
> 0 ,
> 0
>
> FROM #MVTriggerGroup
> print 'end tran 2'
> COMMIT TRAN
>
>
>
>