Database

FREE Code Conversion VB6 ASP VB.NET C# ASP.NET C++ JAVA PHP DELPHI ColdFusion
HTML Marquee & its alternatives

4-Aug-13 23:00

So do the formatting of the data in the report. DO NOT try and make the database do the UIs job.

The query into the database should get you all the data required in the report, it is then up to you to format the report the way you need it to look, this has nothing to do with the query.

Never underestimate the power of human stupidity
RAH

Re: Crystal Reporting

thatraja4-Oct-13 20:16

thatraja

4-Oct-13 20:16

Use the Suppress if duplicated[^] option

thatraja

Nobody remains a virgin, Life screws everyone Sigh | :sigh:

OLAP hung on Cube Full Process: Building indexes for the <Measure Group Name> partition has started.

devvvy2-Aug-13 18:19

devvvy

2-Aug-13 18:19

OLAP hung on Cube Full Process:
Building indexes for the <measure group="" name=""> partition has started.

Observation:
a. Cube Size - if my Date dimension contains only say 5 or 10 days of history, then this problem goes away. If I build the cube with say 25-35 days of history, then Cube Full Process hung on above statement (and CPU goes flat/zero afterwards)
b. When put Analysis Service on SQL Profiler,

XML

Building indexes for the <Measure Group Name> partition has started.

is the last statement captured on profiler but after which CPU goes flat and nothing seems to be happenning (no error/warning captured)
c. Even if server has more physical memory, Analysis Service does not take all the memory available (It's configured to use 80% of avail memory)[^]

Any idea? Thanks

dev

Re: OLAP hung on Cube Full Process: Building indexes for the <Measure Group Name> partition has started.

Jörgen Andersson6-Aug-13 9:45

Jörgen Andersson

6-Aug-13 9:45

What OS is it, and how much memory do you have on the server?

Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln

Database Design to store revisions of creating an assessment

civic0602-Aug-13 8:57

civic060

2-Aug-13 8:57

What would be the best way to handle storing revisions of an assessment where a user may edit and save and then final finish the creation of an assessment. Here we would like to keep all revisions.

Re: Database Design to store revisions of creating an assessment

Mycroft Holmes2-Aug-13 14:26

Getting Foreign Key Constraint Error Unusually

2-Aug-13 14:26

Use Word and turn on revisions... SMACK ouch

Add a RevisionID field to your table and manually increment it when the user checks out the version.

In your UI allow the user to "check out" a copy of the data (make another record in your table), allow the user to save the copy of the next revision and they are not allowed to check it out again until they either commit the revision or discard it.

Never underestimate the power of human stupidity
RAH

indian1432-Aug-13 8:32

indian143

2-Aug-13 8:32

Hi All,

I have a stored procedure that is inserting some values in Master and details table and I am getting the following error. Some times I am getting this error but some times I am not getting this error. When I am inserting detail table values I am checking everything if I am inserting any values that are not there in the Master table, but I am not getting any sign that I am inserting any values that are not there in Master table.

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Identifier_Entity". The conflict occurred in database "IdentityResolutionService", table "dbo.Entity", column 'EntityId'.

My scenario is: I have Entity table, Identifier table, Entity is the master table Identifier is the detail table. I am actually taking data from another table called Students to load all the students of a state, sid, dsid and ssn are the columns that are important from the students table. One sid may have multiple dsids depending upon year, school district etc, one ssn one sid. Now I have taken Rank on the SID to load all this data from Students table to Staging table. Now I have taken all the distinct SIDs and put it into Master table.
In the Identifier table when I am loading the SIDs, DSIDs and SSNs, I have taken the corresponding Rank value for that row, instead of creating one. I explained this only because there is no way that I can miss the Foreign key relationship. But one thing when I am loading the Entity values I am making it as Auto increment off, insert statement and then making it on because there is another application that is using the same table as auto increment on. But another thing is as the user doesn't have alter table permissions, I have created a Stored Proc which takes table name and insert statement as parameters so that I can make auto increment off and run the insert command in the parameter and makes auto increment on. After doing this I make auto increment on. This stored procedure runs under proxy user account.
Then I run insert into Identifier table, some how the insert into Identifier table is failing with the above error message. What could be the reason. Some time it works, some time doesnt. Is it because of dynamic sql or proxy user or the huge amount of data that is being processed because of all the students in a state, like around 4 or 5 million records am processing. In the Identifier table it is having more than 10 million records or am I doing any mistake. Because of its hugeness I am unable to debug the Stored Proc also very well the reason is it runs for an hour or something. All the time is going just in testing.

Is there any other reasons apart from unavailable values in master-detail tables for Foreign key constraint failure.

Please understand me and help me whatever you can, that would really great.

Thanks & Regards,

Abdul Aleem Mohammad
St Louis MO - USA

Mycroft Holmes2-Aug-13 14:20

2-Aug-13 14:20

indian143 wrote:
I am loading the Entity values I am making it as Auto increment off,

I suspect this is the cause of the problem, I can't see any reason for removing the IDENTITY attribute and the error message points directly to this as issue.

Never underestimate the power of human stupidity
RAH

indian1432-Aug-13 17:04

indian143

2-Aug-13 17:04

Can I put my code here

Thanks & Regards,

Abdul Aleem Mohammad
St Louis MO - USA

jschell2-Aug-13 14:33

jschell

2-Aug-13 14:33

indian143 wrote:
I explained this only because there is no way that I can miss the Foreign key relationship

But the error is telling you SPECIFICALLY that that assumption is wrong.

Hypothetically how do you know that, for instance, the SSN was never entered wrong? Or that someone didn't make up a SSN?

The error is telling you that either you must clean the existing data before using it or you must use another key.

whats diffrence between in two query in performance respect

indian1432-Aug-13 17:01

indian143

2-Aug-13 17:01

I am not using SSN directly as foreign key what I am using is the rank generated depending upon the SID is foreign key means for each SID there will be an EntityId and the same Entity Id I will be using for loading the DSID and SSN too. There is no way that I will go beyond Entities that are created by SID's Rank.

I can give you the code, can you please look into it.

Thanks & Regards,

Abdul Aleem Mohammad
St Louis MO - USA

mhd.sbt2-Aug-13 5:48

Re: whats diffrence between in two query in performance respect

2-Aug-13 5:48

hi to all
whats diffrence between two below query in performance respect:
query 1:

SQL

SELECT  n, dbo.AddOne(n) AS r
FROM dbo.T1
ORDER BY r;

function addOne is this:

SQL

CREATE FUNCTION dbo.AddOneInline(@n AS BIGINT) RETURNS TABLE
AS
RETURN SELECT @n + 1 AS val;
GO

and query2:

SQL

SELECT  n, (SELECT val FROM dbo.AddOneInline(n)) AS r
FROM dbo.T1
ORDER BY r;

and defination of function is:

SQL

GO
CREATE FUNCTION dbo.AddOneInline(@n AS BIGINT) RETURNS TABLE
AS
RETURN SELECT @n + 1 AS val;
GO

query 2 is very good in performance and i want to know whats difference between in this two query

Eddy Vluggen2-Aug-13 8:32

Eddy Vluggen

2-Aug-13 8:32

The difference is that one is written out as a subquery.

It's a micro-optimization IMHO, but you can use the Management Studio[^] to check the execution plan and compare both versions.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Database for Bridge Asset Management

Nebilo1-Aug-13 22:25

Nebilo

1-Aug-13 22:25

Hi everyone,

I wanted to ask a question that was confusing me how to design the db.This bridge database that i was going to design has 3 different districts(Locations) say A,B and C.These districts have different road segments,sections.I temporarily designed the db using separate tables but having the same fields.As i said these tables only differ by the thing i mentioned.

I know I'm not following the design rules,so i need you to help me on that.

Thanks

Bernhard Hiller2-Aug-13 0:10

Bernhard Hiller

2-Aug-13 0:10

And when the next district is added, you add another table?
No! Create a table for the districts, and then use "foreign keys" to connect to the sections table.

Mycroft Holmes2-Aug-13 1:07

2-Aug-13 1:07

Bernhard Hiller wrote:
foreign keys

What be these foreign keys you speak of!

Never underestimate the power of human stupidity
RAH

Nebilo2-Aug-13 1:47

Nebilo

2-Aug-13 1:47

BTW there are fields that provide the bridge information,it's bridge number(p.k),name,design year,asset value and other fields.
Now there are 3 different districts A,B and C.Under District A,there are different sections and road segments that correspond to A only.Same thing goes to B and C.These districts are not related in any way in other words.
That's why i created 3 separate tables based on these districts.

face time out when run query with nested select

Eddy Vluggen2-Aug-13 0:29

Eddy Vluggen

2-Aug-13 0:29

Nebilo wrote:
I know I'm not following the design rules,so i need you to help me on that.

How? Yes, I could sum up some widely used constructs, but that's not a decent alternative for a good design.

There's explanation on database-normalization in the wikipedia, and various articles on CodeProject. Alternatively, you could fake a design[^].

I'll gladly help with the normalization rules if you get stuck Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

mhd.sbt1-Aug-13 21:54

Re: face time out when run query with nested select

1-Aug-13 21:54

hi to all
when i run this query i faced with timeout

SQL

DELETE FROM bml.LoanInstallment WHERE ID IN (select * from wfInstanceNodes where ID=@nodeID)

this occurred when nested select return null and i think it is because of that i have no index on id;
and i set index to id but still this query faced with time out

note that bml.LoanInstallment has 11000000 record and this query run without problem for a table with
a few records.
thanks for any help

Bernhard Hiller2-Aug-13 0:09

Bernhard Hiller

2-Aug-13 0:09

Do you run into a timeout when you do a SELECT instead of a DELETE? E.g. SELECT COUNT(ID) FROM bml... etc.? And how many entries are to be deleted?
Are there indices for the ID columns in both tables?
Are there any constraints, e.g. an OnDeleteCascade?

Re: face time out when run query with nested select

mhd.sbt2-Aug-13 2:45

Re: face time out when run query with nested select

2-Aug-13 2:45

id is uniqueness and
and one entries finally will be deleted.
and i don't have any constraint on bml table
thanks in advanced

Eddy Vluggen2-Aug-13 0:10

Eddy Vluggen

2-Aug-13 0:10

Your subquery should not select "all" columns, only the ID-column.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: face time out when run query with nested select

mhd.sbt2-Aug-13 2:42