page break after list not working

Has anyone experienced a problem with page breaks after list's?
I have a list and i have selected for a page break after list but it does
not appear to be working?
How can I force a page break for each row process from the dataset?Dave,
Not sure if this will help but I think when viewing as html, the page break
is not shown, but should work in pdf format or print preview.
cheers,
Batty.
"dave" wrote:
> Has anyone experienced a problem with page breaks after list's?
> I have a list and i have selected for a page break after list but it does
> not appear to be working?
> How can I force a page break for each row process from the dataset?|||I resolved my problem by ensuring that the width of my lists and subreports
were clearly smaller than the page i was trying to print on. When you have a
subreport for example that has a width wider than the page, very strange
things will happen.
Hope this helps.

Package structure in SSIS

Hi

I have been working with DTC packages together with analysis service, and are

now begining on SSIS.

But I have some demands that's been giving me headache before, so I hope

there's a solution in the SSIS for the following.

My DTC packages was build to be executed i 5 steps/Packages

1. Transfeer data from my Oracle 9.2.0.4 to SQL 2000

2. Transform data

3. Quality check

4. Process dimension

5. Process cubes

BUT in 2005, I want a little more flexibilty. I want to execute smaller

packages, let's say, only my "general ledger".

This way I will be able to update some of my important cubes quicker. And

not wait until all data

is transferred.

So what I have done until now is that I have created SSIS packages per module. I

have a General_ledger.dtsx and a Sales.dtsx. Which I can execute separately.

I also have a MasterFlow.dtsx which contain an "Execute package Task"

for each package.

I can run the MasterFlow and it will run through all packages once every night.

Each package contains

1. Truncate package tabels

2. Transfer package

tabels

3. Execute dimensions

data

4. Execute cubes

5. Process

Dimensions

6. Process Cubes

Is this the best way?

I'm not satisfied

myself because,

when I run MasterFlow. I would like to execute all

"step 1" in all packages, before "step 2" and so forth.

Then I would be sure that all data is OK when all Cubes are processed, if I

have a virtual cube that contains data from 2 packages.

How would you design this kind of setup?

Thanx in adv.

Best Regards

Bjarne R Mikkelsen

A-TEX A/S

Why not have a third child package that processes the cubes? then you only have to execute the cube process upon successful completion of everything else.

Or, in each child package you could process the measure group (I'm assuming you are using AS2005) relevant to teh data that you have just transformed.

Your approach up to now seems pretty sound to me.

-Jamie

|||Thank you.

The processing of dimension and cubes are the least time consuming, so it would be a possibility, simply to arrange these in a 3. child package, which will be executed everythime a single package is run.

/Bjarne

Package 'Microsoft SQL Management Studio Package' failed to load

i am working on sql server 2005. i removed dotnet framework 2.0 from my
system and again i installed it . Now i am getting a message Package 'Microsoft SQL Management Studio Package' failed to load , if i click
on ok it is closing.
so please give a solution and it is a trail version of 180 from
microsoft technologies

Quote:

Originally Posted by chandrasekharallu

i am working on sql server 2005. i removed dotnet framework 2.0 from my
system and again i installed it . Now i am getting a message Package 'Microsoft SQL Management Studio Package' failed to load , if i click
on ok it is closing.
so please give a solution and it is a trail version of 180 from
microsoft technologies

It wasn't a wise decision to uninstall .NET 2.0, as SQL Server is deeply integrated with .NET 2.0.

Try reinstalling SQL Server, but I'm not sure if it will correctly identify your trial period.

Package Fail connections on moving to server

I am working with the ssis packages.I am working in the development enviroment.Once i place them on the server all my connection to text files fails.I dont want to sit and Keep changing with respect to server again and again.

What is the best way to set connection like for example:in my development it can be on e:\data folder

But on server F:\Data

With me changing again and again, can this be set within the package in the development environment.

Please le me know.

Use a package configuration to populate a variable with the location of the files. Then use an expression on the connection manager to combine the file location variable with the hard-coded filenames.
|||

Would you please explain in little more details

Thanks in advance

|||If you aren't familiar with Package Configurations, you should read the topic in Books Online. You can use them to set the connection string on your flat file connection managers, as Jay suggests.

Package doesn't finish, but no error

I have been working with this for about a month now, and no similar problems to date. Today I am trying to introduce 4 configuration flags that control whether optional ETL stage feeds are executed. I did this by adding a do-nothing script component. The precedent and constraint is used, and it checks the boolean variable flag. The first package executes fine. But it never returns from there. This precedent has nothing fancy on it either. It simply does not run any more of the package, make any more conditional checks, nor the common completion tasks. It just seems to think it is done.

The optionals all fire execute package tasks. One thing that might be tripping it up is that I attempt to run one package twice, each time with varying parent package variable set to control it to use a different destination database for each run. Should this not be OK to do?

Any hints would be greatly appreciated.Hi Lee Gillie,

You might consider using a "For-each loop" instead with a variabel that changes value for each iteration...

Regards
Simon
|||Thanks Simon for considering this. I wasn't specific enough for you to see the 4 flags control running of 3 distinct packages, where one package is run with differening parameter setups. It would seem I would still need this kind of condition logic on the precedence within the loops to set up for each, even if there were but a single execute package task, and it is the condition on precedence that seems to be messing me up. It is not clear how the loop would avoid the need to have precedence control logic. Your response seems to presume that a simple expression to evaluate a boolean variable on a precedence does not work, and we are trying to avoid that? But perhaps my vision of implementing your idea is not right?

Thanks again, - Lee

Package Configurations - Using One config file for Multiple Packages

Hi All,

I am working on a project currently where we have many SSIS packages and we want to minimize the number of config files to be used. What I was thinking was using one master config file which will have Server/Database info which will be used by all the packages. Now the thing is there are multiple packages which are being executed from within a master package and I was wondering if this will be an issue. Is there anything else that I will have to keep in mind? Any help is appreciated.

Thanks

If my memory serves correctly then you will get lots of warnings about references in your config file to properties that don't exist in your package. But it should still execute OK.

-Jamie

|||Thanks Jamie

Package Configuration wizard:-SQL Configurations Configuration filter not working

Hi --I was wondering if this is a bug when I add new data in my table SSIS Confiurations and give wizard a new Configuration filter the package configuration wizard can not see the new values --the old values from the previous configuration are still showingis there any known workaround or forced refresh I can do

thanks in advance Dave

Background:

SQL Package Configurations are most important because they provide the possibility of a central configuration store for your entire enterprise!!!!!!!! and is in my mind the only way to go

http://sqljunkies.com/WebLog/knight_reign/archive/2005/01/24/6843.aspx

Wizard results:

Name:
ETL

Type:
SQL Server

Connection name:
ETLConfiguration

Any existing configuration information for selected configuration filter will be overwritten with new configuration settings.

Configuration table name:
[dbo].[SSIS Configurations]

Configuration filter:
PT_CUST_ABR

Target Property:
\Package.Variables[User::gsPreLoad].Properties[Value]
\Package.Variables[User::gsPostLoad].Properties[Value]
\Package.Variables[User::gsLoad].Properties[Value]
\Package.Variables[User::gsFlatFilename].Properties[Value]
\Package.Variables[User::gsFileName].Properties[Value]
\Package.Variables[User::gsCDOMailTo].Properties[Value]
\Package.Variables[User::gsCDOMailSubject].Properties[Value]
\Package.Variables[User::giRecordCount].Properties[Value]
\Package.Variables[User::giFileSize].Properties[Value]
\Package.Variables[User::giBatchID].Properties[Value]
\Package.Variables[User::gdFileDateCreated].Properties[Value]
\Package.Connections[MyDatabase].Properties[ServerName]
\Package.Connections[MyDatabase].Properties[InitialCatalog]

USE [ETLConfiguration]
GO
/****** Object: Table [dbo].[SSIS Configurations] Script Date: 05/23/2006 13:34:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SSIS Configurations](
[ConfigurationFilter] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValue] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[PackagePath] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValueType] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]

I have just tried this scenario and cannot reproduce it. I added several SQL configurations and all worked fine.

I wonder if you could try to repro, while running SQL Profiler - that way you may discover if there are any issues in updating your config table.

Donald

|||

Thanks Donald --first off I have discovered a workaround. I think its way too risky and error prone to let the wizard write the actual values into the configuration table --you can imagine the chaos in large datawarehouse that has lots of new of records to enter into in the table!!!! Lets think it through--it I have say 5 new staging tables to add to the warehouse thats 65 manual entrys I would have to make with the wizard--no way is this workable

instead I prefer to sql script my new entrys and load in on hit

The workaround is to run the SQL insert script --then go to the wizard and change the configuration filter and go through to the endat this stage if you look at the variables they wont have changed since your last entry to the configuration table

Step1: Save the package

Step2: Exit out of visual studio completely and reopen again and go back into package

Hey presto the new values in the variables have changed

Can confirm this workaround only for SQL Server 2005

I have not tested for SQL Sever 2005 SP1

cheers Dave

Ownership Chains Not Working?

I have two databases, say A & B.
I have a table in database A called Table 1.
I have a view in database B called View 1.
B.View 1 selects * from A.Table 1.
Both the view and the table are owned by dbo.
I create a new user and grant him select on B.View 1 and database permission
on only database B.
When this user queries B.View 1, he gets an error saying he is not a user of
database A. Why?
I thought the chain of ownership for dbo for both table and view would allow
this scenario to work? I don't want this user to be a member of the public
role for database A, I only want him to be a member of database B.
Any help would be appreciated!
Thanks,
BenThe user needs to be a valid user in both databases. The
user needs database access to the other database but you
don't need to grant object permissions as long as the
database chain is unbroken. This includes the database
owners, not just the objects involved.
-Sue
On Mon, 21 Jun 2004 16:26:49 -0500, "Becker"
<ben@.benbecker.net> wrote:

>I have two databases, say A & B.
>I have a table in database A called Table 1.
>I have a view in database B called View 1.
>B.View 1 selects * from A.Table 1.
>Both the view and the table are owned by dbo.
>I create a new user and grant him select on B.View 1 and database permissio
n
>on only database B.
>When this user queries B.View 1, he gets an error saying he is not a user o
f
>database A. Why?
>I thought the chain of ownership for dbo for both table and view would allo
w
>this scenario to work? I don't want this user to be a member of the public
>role for database A, I only want him to be a member of database B.
>Any help would be appreciated!
>Thanks,
>Ben
>
>|||Sue,
Thanks for the response. My only problem is that the public profile in
database A has all kinds of privileges that I don't want this user to have
and I can't seem to revoke the public role for a user for a database? Seems
public must be granted to all users for a database that will access it? I'm
on SQL Server 7.
Thanks,
Ben
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:558fd057if2vl481dth6uot53cica0cuin@.
4ax.com...
> The user needs to be a valid user in both databases. The
> user needs database access to the other database but you
> don't need to grant object permissions as long as the
> database chain is unbroken. This includes the database
> owners, not just the objects involved.
> -Sue
> On Mon, 21 Jun 2004 16:26:49 -0500, "Becker"
> <ben@.benbecker.net> wrote:
>
permission[vbcol=seagreen]
of[vbcol=seagreen]
allow[vbcol=seagreen]
public[vbcol=seagreen]
>|||Hi Ben,
Yes...all users in a database are members of the public
role and you can't delete users out of the public role. If
all users accessing the database do not need all the
privileges then it may be better to move those permissions
out of public and into a user defined role.
-Sue
On Mon, 21 Jun 2004 22:33:50 -0500, "Becker"
<ben@.benbecker.net> wrote:

>Sue,
>Thanks for the response. My only problem is that the public profile in
>database A has all kinds of privileges that I don't want this user to have
>and I can't seem to revoke the public role for a user for a database? Seem
s
>public must be granted to all users for a database that will access it? I'
m
>on SQL Server 7.
>Thanks,
>Ben
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:558fd057if2vl481dth6uot53cica0cuin@.
4ax.com...
>permission
>of
>allow
>public
>|||I agree with Sue. Personally, I grant permissions only to user-defined
roles and never grant permissions to public.
If you must keep your public role permissions, you can create a role in
DatabaseA for your DatabaseB users and DENY the unwanted permissions to that
role. Remember that DENY always takes precedence over GRANT.
Hope this helps.
Dan Guzman
SQL Server MVP
"Becker" <ben@.benbecker.net> wrote in message
news:eVGlAnAWEHA.1380@.TK2MSFTNGP12.phx.gbl...
> Sue,
> Thanks for the response. My only problem is that the public profile in
> database A has all kinds of privileges that I don't want this user to have
> and I can't seem to revoke the public role for a user for a database?
Seems
> public must be granted to all users for a database that will access it?
I'm
> on SQL Server 7.
> Thanks,
> Ben
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:558fd057if2vl481dth6uot53cica0cuin@.
4ax.com...
> permission
user[vbcol=seagreen]
> of
> allow
> public
>

Owner of a database

Hello,
What are the effects in working with a database owned by a user 'X'? In other
words, what are the differences whether i.e. the owner is 'sa', a user which is
db_owner, or a "normal" user?
[SQL Server 2K]
Thank You
Joachim
Hi
> What are the effects in working with a database owned by a user 'X'? In
> other
> words, what are the differences whether i.e. the owner is 'sa', a user
> which is
> db_owner, or a "normal" user?
One is that a "normal user" cannot delete/drop any objects created by
another user.
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:eiHwrUAIGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hello,
> What are the effects in working with a database owned by a user 'X'? In
> other
> words, what are the differences whether i.e. the owner is 'sa', a user
> which is
> db_owner, or a "normal" user?
> [SQL Server 2K]
> Thank You
> Joachim
|||Iam not sure what it is that you want to know but when using SQL
Authentication, you can create you own user say userx and make them
owner of the database i.e assign the role db_owner, There is not
different really with using 'sa', but when you are using sa make sure
you have protected your database correctly
|||You are talking about two different things here.
Making someone the actual owner of a database is NOT the same as putting
them in the db_owner role.
If they are the true owner, their login name will be stored in the
sysdatabases table in the master database, and when they use the database of
which they are the owner, they will have the user name DBO.
A user in the database can be put in the db_owner role, but they still have
their own user name, they are not DBO. Their name is not stored anywhere
outside the database. Within the database they have all the permissions of
the true owner, but they keep their own user name.
Please read about logins, users and database roles in the Books Online.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<Lucky.Ncube@.gmail.com> wrote in message
news:1138016039.779184.79720@.g44g2000cwa.googlegro ups.com...
> Iam not sure what it is that you want to know but when using SQL
> Authentication, you can create you own user say userx and make them
> owner of the database i.e assign the role db_owner, There is not
> different really with using 'sa', but when you are using sa make sure
> you have protected your database correctly
>

Owner of a database

Hello,
What are the effects in working with a database owned by a user 'X'? In othe
r
words, what are the differences whether i.e. the owner is 'sa', a user which
is
db_owner, or a "normal" user?
[SQL Server 2K]
Thank You
JoachimHi
> What are the effects in working with a database owned by a user 'X'? In
> other
> words, what are the differences whether i.e. the owner is 'sa', a user
> which is
> db_owner, or a "normal" user?
One is that a "normal user" cannot delete/drop any objects created by
another user.
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:eiHwrUAIGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hello,
> What are the effects in working with a database owned by a user 'X'? In
> other
> words, what are the differences whether i.e. the owner is 'sa', a user
> which is
> db_owner, or a "normal" user?
> [SQL Server 2K]
> Thank You
> Joachim|||Iam not sure what it is that you want to know but when using SQL
Authentication, you can create you own user say userx and make them
owner of the database i.e assign the role db_owner, There is not
different really with using 'sa', but when you are using sa make sure
you have protected your database correctly|||You are talking about two different things here.
Making someone the actual owner of a database is NOT the same as putting
them in the db_owner role.
If they are the true owner, their login name will be stored in the
sysdatabases table in the master database, and when they use the database of
which they are the owner, they will have the user name DBO.
A user in the database can be put in the db_owner role, but they still have
their own user name, they are not DBO. Their name is not stored anywhere
outside the database. Within the database they have all the permissions of
the true owner, but they keep their own user name.
Please read about logins, users and database roles in the Books Online.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<Lucky.Ncube@.gmail.com> wrote in message
news:1138016039.779184.79720@.g44g2000cwa.googlegroups.com...
> Iam not sure what it is that you want to know but when using SQL
> Authentication, you can create you own user say userx and make them
> owner of the database i.e assign the role db_owner, There is not
> different really with using 'sa', but when you are using sa make sure
> you have protected your database correctly
>

Owner of a database

Hello,
What are the effects in working with a database owned by a user 'X'? In other
words, what are the differences whether i.e. the owner is 'sa', a user which is
db_owner, or a "normal" user?
[SQL Server 2K]
Thank You
JoachimHi
> What are the effects in working with a database owned by a user 'X'? In
> other
> words, what are the differences whether i.e. the owner is 'sa', a user
> which is
> db_owner, or a "normal" user?
One is that a "normal user" cannot delete/drop any objects created by
another user.
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:eiHwrUAIGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hello,
> What are the effects in working with a database owned by a user 'X'? In
> other
> words, what are the differences whether i.e. the owner is 'sa', a user
> which is
> db_owner, or a "normal" user?
> [SQL Server 2K]
> Thank You
> Joachim|||Iam not sure what it is that you want to know but when using SQL
Authentication, you can create you own user say userx and make them
owner of the database i.e assign the role db_owner, There is not
different really with using 'sa', but when you are using sa make sure
you have protected your database correctly|||You are talking about two different things here.
Making someone the actual owner of a database is NOT the same as putting
them in the db_owner role.
If they are the true owner, their login name will be stored in the
sysdatabases table in the master database, and when they use the database of
which they are the owner, they will have the user name DBO.
A user in the database can be put in the db_owner role, but they still have
their own user name, they are not DBO. Their name is not stored anywhere
outside the database. Within the database they have all the permissions of
the true owner, but they keep their own user name.
Please read about logins, users and database roles in the Books Online.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<Lucky.Ncube@.gmail.com> wrote in message
news:1138016039.779184.79720@.g44g2000cwa.googlegroups.com...
> Iam not sure what it is that you want to know but when using SQL
> Authentication, you can create you own user say userx and make them
> owner of the database i.e assign the role db_owner, There is not
> different really with using 'sa', but when you are using sa make sure
> you have protected your database correctly
>

OverWrite SqlExpress .LDF file ??

Hi :)

I have a website that uses SqlExpress ...on it i have a database that was working ok ...until i made a few modifications to the database (had a few rows).

I have upload the database (only the .mdf file) to the app_data folder ...but now i get this message :

One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
Cannot open database "ArtWork" requested by the login. The login failed.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
Log file 'c:\xxxx\xxx\xxxx\xxxx\xxxx\App_Data\ArtWork_log.LDF' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.

I try to delete the .LDF file but it gives me access denied ...i cheched the permissions for the file and everything is ok

How can i solve this??

Thanks and Cheers

Have you moved both the .mdf file and .ldf file to new location (c:\xxxx\xxx\xxxx\xxxx\xxxx\App_Data\)?

Override a foreign key constraint

A website that I'm working on has users sign in and keeps a log of the pages they go to. The log table has a foreign key in it that links to the username is the users table. I need to update the username for one of the users but the foreign key is preventing me from doing so. What is the benefit of having a foreign key like this? Can I delete it to update the username or is there a better way?

Foreign Key constraints ensure the integrity of the data is maintained. If there was no FK in place. then it would be easy to get orphaned and inconsistent data. In your example, if you just update a username to be Bob from Terry then all the records associated with Terry in the log table will now have no link back to the users record ie the Terry records will be orphaned.

Soooooo, in answer to your question, you can drop it make your update but it will throw an error when you try and recreate the FK unless you update the log records too.

You can create these constraints with a cascading updates/deletes which will filter your changes down to the child tables but in this case, it looks like its turned off.

I'd suggest just doing the following

Psuedo code:

UPDATE Log set Username = Bob WHERE username = Terry

UPDATE user set username = Bob WHERE username = Terry.

HTH!

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
>
>
>
>