Problem with aggregation...

I am currently facing 2 problems
1. with calculated cells on my cube. Bascially I have used a Time dimension
broken into year, week and days and a measure which is aggregated as
distinct count
Imagine for 2004, week 52, I have 7 days and the distinct count for all days
are 2, I am not able to see that the total distinct count for week 52 as 14
(7 *2), is there any way to do tat?
2. I also have a caculate measure which rely on the distinct count measure
to calcualte percentages, the returned values for the 7 days in week 52 is
correct, but it's summing up for the total in week 52 which it should be
actaully doing an average.
Can someone help me out with this or at least point out how to go about
doing this? ThanksHello all, I just found out that the 2nd problem is due to the first, but
I'm still trying to figure out how to do a aggregated distinct count value
for my cube. Just to make things clearer, here's an example
Distinct Count Measure
Week 1 Total 3 (<- I need this to be 13)
Days
1 2
2 2
3 1
4 1
5 1
6 3
7 3
I enabled drilldown for the cube and verified that the week1 total is really
a distinct count of 3 only... but what I am really trying to is to count
distinctly for days... but aggregate for Weeks and Years... Is there anyway
to achieve that?
Any form of advise is greatly appreciated... thanks in advance
"Nestor" <test@.test.com> wrote in message
news:Oow6ZqKLFHA.3832@.TK2MSFTNGP12.phx.gbl...
> I am currently facing 2 problems
> 1. with calculated cells on my cube. Bascially I have used a Time
dimension
> broken into year, week and days and a measure which is aggregated as
> distinct count
> Imagine for 2004, week 52, I have 7 days and the distinct count for all
days
> are 2, I am not able to see that the total distinct count for week 52 as
14
> (7 *2), is there any way to do tat?
> 2. I also have a caculate measure which rely on the distinct count measure
> to calcualte percentages, the returned values for the 7 days in week 52 is
> correct, but it's summing up for the total in week 52 which it should be
> actaully doing an average.
>
> Can someone help me out with this or at least point out how to go about
> doing this? Thanks
>|||You can use Calculated Member or Calculated Cell.
If you use Calculated Member,
Dcount = IIF(Time.CurrentMember.Level.Name = "Day", [Distinct Count
Measure], Sum(Time.CurrentMember.Children, [Dcount]))
Or if you use Calculated Cell,
Calculation Subcube: {[Measures].[Distinct Count Measure]},
Descendants(Time.Year.Members, Week, SELF_AND_BEFORE)
Calculation Value: Sum(Time.CurrentMember.Children, [Distinct Count
Measure])
But this is the case when you consider only time dimension. I'm not sure you
have to consider more dimensions.
Ohjoo Kwon
"Nestor" <test@.test.com> wrote in message
news:u$nyDYVLFHA.3988@.tk2msftngp13.phx.gbl...
> Hello all, I just found out that the 2nd problem is due to the first, but
> I'm still trying to figure out how to do a aggregated distinct count value
> for my cube. Just to make things clearer, here's an example
> Distinct Count Measure
> Week 1 Total 3 (<- I need this to be 13)
> Days
> 1 2
> 2 2
> 3 1
> 4 1
> 5 1
> 6 3
> 7 3
> I enabled drilldown for the cube and verified that the week1 total is
really
> a distinct count of 3 only... but what I am really trying to is to count
> distinctly for days... but aggregate for Weeks and Years... Is there
anyway
> to achieve that?
> Any form of advise is greatly appreciated... thanks in advance
>
> "Nestor" <test@.test.com> wrote in message
> news:Oow6ZqKLFHA.3832@.TK2MSFTNGP12.phx.gbl...
> dimension
> days
> 14
measure[vbcol=seagreen]
is[vbcol=seagreen]
>|||Thanks a lot of the help Ohjoo, I'm using calculated member and I'm
inputting the MDX statement into the ValuedExpression, basically this is the
MDX i've keyed into the Value Expression
iif
([My Time].CurrentMember.Level.Name = "Day",
[Measures].[Distinct Products],
iif([My Time].CurrentMember.Level.Name = "Year",
sum([My Time].CurrentMember.Children, [Measures].[New Calculated
Measure]), <-- Error here
sum([My Time].CurrentMember.Children, [Measures].[Distinct
Products])
)
)
What I am trying to do is to count distinctly for days only, for weeks it
should aggregate the days distinct count and for years it should aggregate
the weeks sum. The calculated measure is simply called "New Calculated
Measure"
Can this be done?
count(distinct(<measure to count> ), exlcudeempty)
"Ohjoo Kwon" <ojkwon@.olap.co.kr> wrote in message
news:OWnHDMWLFHA.2796@.tk2msftngp13.phx.gbl...
> You can use Calculated Member or Calculated Cell.
> If you use Calculated Member,
> Dcount = IIF(Time.CurrentMember.Level.Name = "Day", [Distinct Count
> Measure], Sum(Time.CurrentMember.Children, [Dcount]))
> Or if you use Calculated Cell,
> Calculation Subcube: {[Measures].[Distinct Count Measure]},
> Descendants(Time.Year.Members, Week, SELF_AND_BEFORE)
> Calculation Value: Sum(Time.CurrentMember.Children, [Distinct Count
> Measure])
> But this is the case when you consider only time dimension. I'm not sure
> you
> have to consider more dimensions.
> Ohjoo Kwon
>
> "Nestor" <test@.test.com> wrote in message
> news:u$nyDYVLFHA.3988@.tk2msftngp13.phx.gbl...
> really
> anyway
> measure
> is
>|||Next is simpler.
IIF(Time.CurrentMember.Level.Name = "Day",
[Distinct Products],
Sum(Time.CurrentMember.Children, [New Calculated Measure])
)
Ohjoo
"Nestor" <n3570r@.yahoo.com> wrote in message
news:OT2O0gbLFHA.1156@.TK2MSFTNGP09.phx.gbl...
> Thanks a lot of the help Ohjoo, I'm using calculated member and I'm
> inputting the MDX statement into the ValuedExpression, basically this is
the
> MDX i've keyed into the Value Expression
> iif
> ([My Time].CurrentMember.Level.Name = "Day",
> [Measures].[Distinct Products],
> iif([My Time].CurrentMember.Level.Name = "Year",
> sum([My Time].CurrentMember.Children, [Measures].[New[/vbc
ol]
Calculated[vbcol=seagreen]
> Measure]), <-- Error here
> sum([My Time].CurrentMember.Children, [Measures].[
Distinct
> Products])
> )
> )
>
> What I am trying to do is to count distinctly for days only, for weeks it
> should aggregate the days distinct count and for years it should aggregate
> the weeks sum. The calculated measure is simply called "New Calculated
> Measure"
> Can this be done?
>
> count(distinct(<measure to count> ), exlcudeempty)
>
> "Ohjoo Kwon" <ojkwon@.olap.co.kr> wrote in message
> news:OWnHDMWLFHA.2796@.tk2msftngp13.phx.gbl...
but[vbcol=seagreen]
count[vbcol=seagreen]
all[vbcol=seagreen]
52[vbcol=seagreen]
about[vbcol=seagreen]
>|||thanks a lot Ohjoo, you've been of great assistances...
"Ohjoo Kwon" <ojkwon@.olap.co.kr> wrote in message
news:%23jpmrEcLFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Next is simpler.
> IIF(Time.CurrentMember.Level.Name = "Day",
> [Distinct Products],
> Sum(Time.CurrentMember.Children, [New Calculated Measure])
> )
> Ohjoo
>
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:OT2O0gbLFHA.1156@.TK2MSFTNGP09.phx.gbl...
> the
> Calculated
> but
> count
> all
> 52
> about
>

Problem while installing SQL Server 2005 Express Edition on Windows 2000 PRO SP4...Can't Instal

Hi everyone...

I'm facing a problem while installing the SQL Server 2005 Express on Windows 2000 Pro with SP4.

After finishing the downloading from Microsoft I start the installation and things seems to go ok but during Extracting Files step a message appears "SQL Server Setup unexpectedly failed. For more information, review the setup summary log file in %Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt" and it terminates the installation.

I located the log file and open it but can't understand the problem..I copy the text and paste it here for anyone who can understand it.Please, it is very importand for me to work with SQL Server for my project. Any advice or information or anything else?

Thank you all,

HERE IS THE LOG FILE:

"Microsoft SQL Server 2005 Setup beginning at Wed Mar 14 07:45:33 2007
Process ID : 2684
c:\c7720b32114815db82783dd06c2d54b9\setup.exe Version: 2005.90.3042.0
Running: LoadResourcesAction at: 2007/2/14 7:45:33
Complete: LoadResourcesAction at: 2007/2/14 7:45:33, returned true
Running: ParseBootstrapOptionsAction at: 2007/2/14 7:45:33
Loaded DLL:c:\c7720b32114815db82783dd06c2d54b9\xmlrw.dll Version:2.0.3609.0
Complete: ParseBootstrapOptionsAction at: 2007/2/14 7:45:33, returned false
Error: Action "ParseBootstrapOptionsAction" failed during execution. Error information reported during run:
Could not parse command line due to datastore exception.
Source File Name: utillib\persisthelpers.cpp
Compiler Timestamp: Wed Jun 14 16:30:14 2006
Function Name: writeEncryptedString
Source Line Number: 124
-
writeEncryptedString() failed
Source File Name: utillib\persisthelpers.cpp
Compiler Timestamp: Wed Jun 14 16:30:14 2006
Function Name: writeEncryptedString
Source Line Number: 123
-
Error Code: 0x80070002 (2)
Windows Error Text: Error: Action "ActivateLoggingAction" threw an exception during execution. Error information reported during run:
Datastore exception while trying to write logging properties.
Source File Name: datastore\cachedpropertycollection.cpp
Compiler Timestamp: Wed Jun 14 16:27:59 2006
Function Name: CachedPropertyCollection::findProperty
Source Line Number: 130
-
Failed to find property "primaryLogFiles" {"SetupStateScope", "", ""} in cache
Source File Name: datastore\propertycollection.cpp
Compiler Timestamp: Wed Jun 14 16:28:01 2006
Function Name: SetupStateScope.primaryLogFiles
Source Line Number: 44
-
No collector registered for scope: "SetupStateScope"
02B1CFC0Unable to proceed with setup, there was a command line parsing error. : 2
Error Code: 0x80070002 (2)
Windows Error Text:
Class not registered.
Failed to create CAB file due to datastore exception
Source File Name: datastore\cachedpropertycollection.cpp
Compiler Timestamp: Wed Jun 14 16:27:59 2006
Function Name: CachedPropertyCollection::findProperty
Source Line Number: 130
-
Failed to find property "HostSetup" {"SetupBootstrapOptionsScope", "", "2684"} in cache
Source File Name: datastore\propertycollection.cpp
Compiler Timestamp: Wed Jun 14 16:28:01 2006
Function Name: SetupBootstrapOptionsScope.HostSetup
Source Line Number: 44
-
No collector registered for scope: "SetupBootstrapOptionsScope"
Message pump returning: 2"

Something seems to be going wrong with the datastore that SQL setup uses to store information during installation. I'm going to send this over to the setup experts.

Mike

|||

Thank you a lot Mike....

If you find anything from them Please LET ME KNOW...

|||

Hi Prassakis,

Suggest to create a CD after you extracted the data from install package SQLEXPR32.EXE

on the commandline into a Folder with SQLEXPR32.EXE -X

See details under the issue I raised: Express SP2 install error

Maybe it might solve it for you too?

cheers,

Fred

|||

Hi Tyodotnet,

Although i have tried your suggestion, it didn't fix the problem of installation....So, any other suggestions are well received for trying....

Thanks a lot,

cheers,

Takis

Problem while installing SQL Server 2005 Express Edition on Windows 2000 PRO SP4...Can't In

Hi everyone...

I'm facing a problem while installing the SQL Server 2005 Express on Windows 2000 Pro with SP4.

After finishing the downloading from Microsoft I start the installation and things seems to go ok but during Extracting Files step a message appears "SQL Server Setup unexpectedly failed. For more information, review the setup summary log file in %Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt" and it terminates the installation.

I located the log file and open it but can't understand the problem..I copy the text and paste it here for anyone who can understand it.Please, it is very importand for me to work with SQL Server for my project. Any advice or information or anything else?

Thank you all,

HERE IS THE LOG FILE:

"Microsoft SQL Server 2005 Setup beginning at Wed Mar 14 07:45:33 2007
Process ID : 2684
c:\c7720b32114815db82783dd06c2d54b9\setup.exe Version: 2005.90.3042.0
Running: LoadResourcesAction at: 2007/2/14 7:45:33
Complete: LoadResourcesAction at: 2007/2/14 7:45:33, returned true
Running: ParseBootstrapOptionsAction at: 2007/2/14 7:45:33
Loaded DLL:c:\c7720b32114815db82783dd06c2d54b9\xmlrw.dll Version:2.0.3609.0
Complete: ParseBootstrapOptionsAction at: 2007/2/14 7:45:33, returned false
Error: Action "ParseBootstrapOptionsAction" failed during execution. Error information reported during run:
Could not parse command line due to datastore exception.
Source File Name: utillib\persisthelpers.cpp
Compiler Timestamp: Wed Jun 14 16:30:14 2006
Function Name: writeEncryptedString
Source Line Number: 124
-
writeEncryptedString() failed
Source File Name: utillib\persisthelpers.cpp
Compiler Timestamp: Wed Jun 14 16:30:14 2006
Function Name: writeEncryptedString
Source Line Number: 123
-
Error Code: 0x80070002 (2)
Windows Error Text: Error: Action "ActivateLoggingAction" threw an exception during execution. Error information reported during run:
Datastore exception while trying to write logging properties.
Source File Name: datastore\cachedpropertycollection.cpp
Compiler Timestamp: Wed Jun 14 16:27:59 2006
Function Name: CachedPropertyCollection::findProperty
Source Line Number: 130
-
Failed to find property "primaryLogFiles" {"SetupStateScope", "", ""} in cache
Source File Name: datastore\propertycollection.cpp
Compiler Timestamp: Wed Jun 14 16:28:01 2006
Function Name: SetupStateScope.primaryLogFiles
Source Line Number: 44
-
No collector registered for scope: "SetupStateScope"
02B1CFC0Unable to proceed with setup, there was a command line parsing error. : 2
Error Code: 0x80070002 (2)
Windows Error Text:
Class not registered.
Failed to create CAB file due to datastore exception
Source File Name: datastore\cachedpropertycollection.cpp
Compiler Timestamp: Wed Jun 14 16:27:59 2006
Function Name: CachedPropertyCollection::findProperty
Source Line Number: 130
-
Failed to find property "HostSetup" {"SetupBootstrapOptionsScope", "", "2684"} in cache
Source File Name: datastore\propertycollection.cpp
Compiler Timestamp: Wed Jun 14 16:28:01 2006
Function Name: SetupBootstrapOptionsScope.HostSetup
Source Line Number: 44
-
No collector registered for scope: "SetupBootstrapOptionsScope"
Message pump returning: 2"

Something seems to be going wrong with the datastore that SQL setup uses to store information during installation. I'm going to send this over to the setup experts.

Mike

|||

Thank you a lot Mike....

If you find anything from them Please LET ME KNOW...

|||

Hi Prassakis,

Suggest to create a CD after you extracted the data from install package SQLEXPR32.EXE

on the commandline into a Folder with SQLEXPR32.EXE -X

See details under the issue I raised: Express SP2 install error

Maybe it might solve it for you too?

cheers,

Fred

|||

Hi Tyodotnet,

Although i have tried your suggestion, it didn't fix the problem of installation....So, any other suggestions are well received for trying....

Thanks a lot,

cheers,

Takis

Problem while installing SQL Server 2005 Express Edition

I am facing a problem while installing SQL Server 2005 Express Edition on my machine. Included below is the text of the logfile Summary.txt

Microsoft SQL Server 2005 9.00.2047.00
==============================
OS Version : Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Time : Tue Feb 13 20:49:01 2007
ABC-621F39F0E0D : The current system does not meet recommended hardware requirements for this SQL Server release. For detailed hardware requirements, see the readme file or SQL Server Books Online.
Machine : ABC-621F39F0E0D
Product : Microsoft SQL Server Setup Support Files (English)
Product Version : 9.00.2047.00
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0015_ABC-621F39F0E0D_SQLSupport_1.log
--
Machine : ABC-621F39F0E0D
Product : Microsoft SQL Server Native Client
Product Version : 9.00.2047.00
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0015_ABC-621F39F0E0D_SQLNCLI_1.log
--
Machine : ABC-621F39F0E0D
Product : Microsoft SQL Server VSS Writer
Product Version : 9.00.2047.00
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0015_ABC-621F39F0E0D_SqlWriter_1.log
--
Machine : ABC-621F39F0E0D
Product : MSXML 6.0 Parser (KB927977)
Product Version : 6.00.3890.0
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0015_ABC-621F39F0E0D_MSXML6_1.log
--
Machine : ABC-621F39F0E0D
Product : SQL Server Database Services
Error : SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]SSL Provider: The certificate chain was issued by an authority that is not trusted.
Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.
--
Machine : ABC-621F39F0E0D
Product : Microsoft SQL Server 2005 Express Edition
Product Version : 9.1.2047.00
Install : Failed
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0015_ABC-621F39F0E0D_SQL.log
Last Action : InstallFinalize
Error String : SQL Server Setup could not connect to the database service for server configuration. The error was: {Microsoft}{SQL Native Client}SSL Provider: The certificate chain was issued by an authority that is not trusted.
Error Number : 29515
--

hi...did u get the solution, I am also facing the same problem.

will appreciate any help.

thanks

Problem while installing SQL Server 2005 Express Edition

I am facing a problem while installing SQL Server 2005 Express Edition on my machine. Included below is the text of the logfile Summary.txt

Microsoft SQL Server 2005 9.00.2047.00
==============================
OS Version : Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Time : Tue Feb 13 20:49:01 2007
ABC-621F39F0E0D : The current system does not meet recommended hardware requirements for this SQL Server release. For detailed hardware requirements, see the readme file or SQL Server Books Online.
Machine : ABC-621F39F0E0D
Product : Microsoft SQL Server Setup Support Files (English)
Product Version : 9.00.2047.00
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0015_ABC-621F39F0E0D_SQLSupport_1.log
--
Machine : ABC-621F39F0E0D
Product : Microsoft SQL Server Native Client
Product Version : 9.00.2047.00
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0015_ABC-621F39F0E0D_SQLNCLI_1.log
--
Machine : ABC-621F39F0E0D
Product : Microsoft SQL Server VSS Writer
Product Version : 9.00.2047.00
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0015_ABC-621F39F0E0D_SqlWriter_1.log
--
Machine : ABC-621F39F0E0D
Product : MSXML 6.0 Parser (KB927977)
Product Version : 6.00.3890.0
Install : Successful
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0015_ABC-621F39F0E0D_MSXML6_1.log
--
Machine : ABC-621F39F0E0D
Product : SQL Server Database Services
Error : SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]SSL Provider: The certificate chain was issued by an authority that is not trusted.
Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.
--
Machine : ABC-621F39F0E0D
Product : Microsoft SQL Server 2005 Express Edition
Product Version : 9.1.2047.00
Install : Failed
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0015_ABC-621F39F0E0D_SQL.log
Last Action : InstallFinalize
Error String : SQL Server Setup could not connect to the database service for server configuration. The error was: {Microsoft}{SQL Native Client}SSL Provider: The certificate chain was issued by an authority that is not trusted.
Error Number : 29515
--

hi...did u get the solution, I am also facing the same problem.

will appreciate any help.

thanks

Problem while accessing sysprocesses table

Hi all,
I am facing one wired problem with sysprocesses table of system table.
What i am doing is executing some stored procedures though code written
in dot net.
What i want is to check those stored procedure's id in sysprocesses
table and then update status in one user defined table.
So when user started say 4 stored procedure. and when i check
sysprocesses table even if my 4 stored procedures are running those are
not getting displayed in sysprocesses table.
I am checking each processid and all my stored proceudures are heavy
running means there is no possibility that they will complete execution
within say 1 to 2 min.
So my question is why sysprocess table is not giving me information
about those procedures which i am running.
Can some one shed some light on it.
Any help will be truely appreciated.
Thanks in advance.try
sp_who2
and see if there are processes running from the machine which has the dotnet
code running.|||As you mentioned you execute the sp though code written in dot net, it
won't show directly in the sysprocesses table as a sp in the cmd field.
If you execute the sp in QA, you will then see it clearly.
Alternatively, run the profiler to capture the action.
Mel

Problem when getting this output ?

Hi everybody,

I am facing a problem when getting this out put ?

i have a table call Trans looks like this

Tran_Id Opt_Id TranDate

1 141 01/02/2007 dd/mm/yyyy

2 542 01/06/2007 dd/mm/yyyy

and i got a table call Appointment like this

Opt_Id Arrived_Date

141 01/01/2007 dd/mm/yyyy

542 01/02/2007 dd/mm/yyyy

No i need to display the output like this

Weeks Wait No Of Transaction

0 to 3 0

3 to 10 1

morethan 10 1

In that above Example,we have to calculate the Weeks Wait = datediff(dd,Appointment.Arrived_Date,Trans.TranDate)

Any Idea ? to get that output ?

regards

suis

here it is,

Code Snippet

Create Table #trans (

[Tran_Id] int ,

[Opt_Id] int ,

[TranDate] datetime

);

Set DateFormat DMY

Insert Into #trans Values('1','141','01/02/2007');

Insert Into #trans Values('2','542','01/06/2007');

Create Table #appointment(

[Opt_Id] int ,

[Arrived_Date] datetime

);

Insert Into #appointmentValues('141','01/01/2007');

Insert Into #appointmentValues('542','01/02/2007');

Select

DescValue [Weeks Wait]

,Count(Tran_Id) [No Of Transaction]

From

(

Select

Tran_ID

,Case When DateDiff(DD,[Arrived_Date], [TranDate])/7 <4 Then '0-3'

When DateDiff(DD,[Arrived_Date], [TranDate])/7 <11 Then '4-10'

Else '> 10' End [Weeks Wait]

from

#trans T

Join #appointment A

On T.[Opt_Id]=A.[Opt_Id]

) as Data

Right Outer Join (Select '0-3' DescValue Union All Select '4-10' Union All Select '> 10') as Descdata

on data.[Weeks Wait] = Descdata.DescValue

Group By

DescValue

Problem when Exporting a Report which have subreports in it

Hello
i am facing a problem when i render a report in Excel which have a subreport
in it.
it give me error "Sub Report within Table/Matrix are ignored".
how can i solve this issue plz help.
ThanksHi Malik,
Don't use table objects. Instead, place the sub-report within a listbox
object.
Aardvark
"Malik Asif Joyia" wrote:
> Hello
> i am facing a problem when i render a report in Excel which have a subreport
> in it.
> it give me error "Sub Report within Table/Matrix are ignored".
> how can i solve this issue plz help.
> Thanks
>
>

Problem when creating FK

Hello everyone,

I'm created a new SQL Server 2005 database for a new project and I'm facing a little situation regarding FKs. I guess that's what you get when you move from MySql 4 to Sql Server 2005 ;)

Here's the situation: I have a table called contact, assigned to the schema person, and that table handles all the base information regarding users. Informations such as FirstName, LastName, etc, are stored in the [Person].[Contact]. So far so good.

As a design decision I've decided to keep track of the nick names (Alias) that a given member had over-time. In order to do this I've designed the [Person].[GlobalAlias] table like this:

Id uniqueidentifier [NOT NULL] [PK]
ContactId uniqueidentifier [NOT NULL] [FK Person.Contact Id]
Alias nvarchar(30) [NOT NULL]
AddedByContactId uniqueidentifier [NOT NULL] [FK Person.Contact Id]
AddedDate datetime [NOT NULL]
AddedObs nvarchar(MAX) [NULL]
RemovedByContactId uniqueidentifier [NULL] [FK Person.Contact Id]
RemovedDate datetime [NULL]
RemovedObs nvarchar(MAX) [NULL]
ModifiedDate datetime [NOT NULL]
Status bit [NOT NULL]

This table seems to work great dispite the fact that it generates an error when I try to set the FKs. For the column [Person].[GlobalAlias] ContactId I set a FK to the column [Person].[Contact] Id with cascade on delete and on update. Now, when I go to the [Person].[GlobalAlias] AddedByMembershipId and try to add a FK to the column [Person].[Contact] Id it works as long as I define NO ACTION on delete and on update. Obviously, this could originate rows where the [Person].[Contact] that added a [Person].[GlobalAlias] was deleted and there's still a reference to that ID in the [Person].[GlobalAlias] AddedByMembershipId

Here's the error that I'm getting from the database:

'Contact (Person)' table saved successfully
'GlobalAlias (Person)' table
- Unable to create relationship 'FK_GlobalAlias_AddedByContactId_Contact_Id'.
Introducing FOREIGN KEY constraint 'FK_GlobalAlias_AddedByContactId_Contact_Id' on table 'GlobalAlias' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.

How could I solve this? Perhaps the only way is to normalize it even further and create a history table?

Best regards,
DBA

That is pretty much what you appear to have now. Just that you are trying to set up foreign key relationships into it, which you probably shouldn't.

With a cascading delete, if you deleted someone, the delete would want to then cascade to every record in your GlobalAlias table that referenced them (Every alias they ever had, and any alias's they had ever created for someone else, or removed from someone else). That probably isn't what you wanted. I could see possibly wanting the updates to cascade, but you normally don't want users to be able to change their uid, so the FK really isn't all that useful for cascading updates.

problem using System.Data.Oledb.OleDbDataAdapter for importing Excel

Hi All,

I am facing this weird problem of partial data being fetched when using oledbadapter for fetching data from a Excel spreadsheet. A few cells containing text values are not being fetched..I get an empty string. When i replace this text value by an integer, the problem is no more a problem...

Anyone having faced this problem or having knowledge about this..pls respond..

Thanks in advance

I'll recommend the use of DTS. then

create an SP that triggers the DTS thru xp_cmdshell using dtsrun.

call the SP from the application