Collapsable Columns not showing up in export to Excel

Dear Anyone,

I have created a report that has collapsable columns. We had used the visible toggle property in reporting services. While the colpsable column works well in report manager, it doesnt work anyone when we export the report to excel. Even of the report is expanded, the report in excel would always show up with just the columns that are the basis for the collapsable column.

Is this really the behavior when it comes to collapsable columns and exporting to excel?

Example:

Column A toggles Column B, Column C and Column D.

Thanks,

Joseph

I've solved this issue setting the hidden columns to be by defualt visible and then letting the user decide if to hide them or not.

In this way excel export it is correct.

HTH.

Alberto

|||

Hello Alberto,

Would you mind sharing your findings?

Thanks,

Joseph

|||I will appreciate if you could post the code and explanation how you did that.

Collapsable Columns not showing up in export to Excel

Dear Anyone,

I have created a report that has collapsable columns. We had used the visible toggle property in reporting services. While the colpsable column works well in report manager, it doesnt work anyone when we export the report to excel. Even of the report is expanded, the report in excel would always show up with just the columns that are the basis for the collapsable column.

Is this really the behavior when it comes to collapsable columns and exporting to excel?

Example:

Column A toggles Column B, Column C and Column D.

Thanks,

Joseph

I've solved this issue setting the hidden columns to be by defualt visible and then letting the user decide if to hide them or not.

In this way excel export it is correct.

HTH.

Alberto

|||

Hello Alberto,

Would you mind sharing your findings?

Thanks,

Joseph

|||I will appreciate if you could post the code and explanation how you did that.

Collapsable Columns not showing up in export to Excel

Dear Anyone,

I have created a report that has collapsable columns. We had used the visible toggle property in reporting services. While the colpsable column works well in report manager, it doesnt work anyone when we export the report to excel. Even of the report is expanded, the report in excel would always show up with just the columns that are the basis for the collapsable column.

Is this really the behavior when it comes to collapsable columns and exporting to excel?

Example:

Column A toggles Column B, Column C and Column D.

Thanks,

Joseph

I've solved this issue setting the hidden columns to be by defualt visible and then letting the user decide if to hide them or not.

In this way excel export it is correct.

HTH.

Alberto

|||

Hello Alberto,

Would you mind sharing your findings?

Thanks,

Joseph

|||I will appreciate if you could post the code and explanation how you did that.

Cognos to RS

Hi all!

I am taking a cognos report and created an identical one in RS. Can anyone tell me what this means and help figure out how to do it in RS?
cognos
TO_DATE(SUBSTR(DATE_TIME_STAMP,1,14),'YYYYMMDDHH24MISS')

RS (i am guessing)
CONVERT(SUBSTRING(DATE_TIME_STAMP,1,14),yyyyMMddhh?m?ss)

can anyone help?

Thanks in avance,
KerrieIs this a SQL function or a function in the report? If it is the latter, the function for SUBSTR is Mid() (http://whidbey.msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vbalr/html/8b6caa3e-973a-4f18-b0e4-93956a9dee9b.asp). You probably want to use the Format() function (http://whidbey.msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vbalr/html/c6f5bdda-a7c3-4f38-86cc-1cf47aa940b3.asp) in place of the TO_DATE.|||you could try :

http://www.sqlobserver.com

SQLobSERVER is a new independent forum specialising in BI/ETL including across the vendors (ie., Cognos)

Sorry about the blurb, but thought it might to be useful..

Cognos to RS

Hi all!

I am taking a cognos report and created an identical one in RS. Can anyone tell me what this means and help figure out how to do it in RS?
cognos
TO_DATE(SUBSTR(DATE_TIME_STAMP,1,14),'YYYYMMDDHH24MISS')

RS (i am guessing)
CONVERT(SUBSTRING(DATE_TIME_STAMP,1,14),yyyyMMddhh?m?ss)

can anyone help?

Thanks in avance,
KerrieIs this a SQL function or a function in the report? If it is the latter, the function for SUBSTR is Mid() (http://whidbey.msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vbalr/html/8b6caa3e-973a-4f18-b0e4-93956a9dee9b.asp). You probably want to use the Format() function (http://whidbey.msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vbalr/html/c6f5bdda-a7c3-4f38-86cc-1cf47aa940b3.asp) in place of the TO_DATE.|||you could try :

http://www.sqlobserver.com

SQLobSERVER is a new independent forum specialising in BI/ETL including across the vendors (ie., Cognos)

Sorry about the blurb, but thought it might to be useful..

Code elements not showing

Hi
I created a vb function and put it in the code for report properties.
But when I use the =Code. in mye report is doesn't show.
Do I have to do something special to compile it or make it work?
I want to post some information back to the header in my report
and the only way I have found to do that is by using
the Code.-expression.
Regards
KnutSeems like intellisense don't work properly for custom code.
It worked ok.
"Knut" wrote:
> Hi
> I created a vb function and put it in the code for report properties.
> But when I use the =Code. in mye report is doesn't show.
> Do I have to do something special to compile it or make it work?
> I want to post some information back to the header in my report
> and the only way I have found to do that is by using
> the Code.-expression.
> Regards
> Knut

COALESCE function problem

Hi, I downloaded "Microsoft SQL Server Compact Edition" v3.1, installed, created database in VS2005, created table TESTTABLE with one column TEST NVARCHAR 200 and run this command against it:

SELECT coalesce(test,NULL) from testtable

but it says

Error : The function is not recognized by SQL Server Compact Edition. [ Name of function = coalesce,Data type (if known) = ]

In this forums there are more usages of coalesce function, so I think it is supported and fully functional, but where is the problem, then ?

My system is Windows Vista Business (Czech version), version of sqlcese30.dll and sqlceqp30.dll is 3.0.5300.0.

thank you for suggestions

J.S.

The COALSECE finction works fine in SQL Compact 3.1, it is a problem with the tool you are using. Have successfully tested in SQL Server Management Studio SP2. It is a problem with the Visual Studio Server Explorer query parser.

|||But, I'am not using any tool - I am using it from application (.NET 2.0). I will make a sample...|||

Hmm, i dont't know where was the problem, but now problem is another:

The specified argument value for the function is not valid. [ Argument # = 2,Name of function(if known) = coalesce ]

my source code i used:

string fn = args[0];

string cs = "Data source='"+fn+"'";

SqlCeConnection conn = new SqlCeConnection(cs);

conn.Open();

//only if table does not exists

//SqlCeCommand cmd1 = new SqlCeCommand("CREATE TABLE TESTTABLE ( TEST NVARCHAR(200) NULL )", conn);

//cmd1.ExecuteNonQuery();

SqlCeCommand cmd2 = new SqlCeCommand();

cmd2.CommandText = "SELECT COALESCE(TEST,@.p1) FROM TESTTABLE";

cmd2.Parameters.AddWithValue("p1", "no value");

cmd2.Connection = conn;

SqlCeDataAdapter adapter = new SqlCeDataAdapter(cmd2);

DataSet ds = new DataSet();

adapter.Fill(ds);

another command

SELECT COALESCE(TEST,'no value') FROM TESTTABLE

is OK, but this is not my scenario...

I think the problem is in parameter - it cannot be used in COALESCE function ?

|||

Looks like you must change the statement to not use parameters!

|||

But this is against th best practices - to use parameters instead of literals...

Of course, I am using SqlServerCe as alternative data provider (user can configure SqlServer/SqlServerCe) and i dont want to write another (bad!) code to achive same functionality...

any ideas ? will this be supported in another version ?

thank you

|||

Hi Jan.

This piece of code breaks with the error you mention using SQL Compact 3.1 in VS 2005, but works (res = "no value") with SQL Compact 3.5 beta 2 and VS 2008 beta 2:

Code Snippet

SqlCeConnection conn = new SqlCeConnection(@."Data Source=C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Samples\Northwind.sdf;");
conn.Open();
SqlCeCommand cmd2 = new SqlCeCommand();
cmd2.CommandText = "SELECT COALESCE(Region,@.p1) FROM Customers WHERE [Customer Id] = 'ALFKI'";
cmd2.Parameters.AddWithValue("p1", "no value");
cmd2.Connection = conn;
object res;
res = cmd2.ExecuteScalar();

So time will mend all wounds!

|||

Oh ! Great, this is, what I want to read...

Thank you for your quick and brilliant answers.

COALESCE function problem

Hi, I downloaded "Microsoft SQL Server Compact Edition" v3.1, installed, created database in VS2005, created table TESTTABLE with one column TEST NVARCHAR 200 and run this command against it:

SELECT coalesce(test,NULL) from testtable

but it says

Error : The function is not recognized by SQL Server Compact Edition. [ Name of function = coalesce,Data type (if known) = ]

In this forums there are more usages of coalesce function, so I think it is supported and fully functional, but where is the problem, then ?

My system is Windows Vista Business (Czech version), version of sqlcese30.dll and sqlceqp30.dll is 3.0.5300.0.

thank you for suggestions

J.S.

The COALSECE finction works fine in SQL Compact 3.1, it is a problem with the tool you are using. Have successfully tested in SQL Server Management Studio SP2. It is a problem with the Visual Studio Server Explorer query parser.

|||But, I'am not using any tool - I am using it from application (.NET 2.0). I will make a sample...|||

Hmm, i dont't know where was the problem, but now problem is another:

The specified argument value for the function is not valid. [ Argument # = 2,Name of function(if known) = coalesce ]

my source code i used:

string fn = args[0];

string cs = "Data source='"+fn+"'";

SqlCeConnection conn = new SqlCeConnection(cs);

conn.Open();

//only if table does not exists

//SqlCeCommand cmd1 = new SqlCeCommand("CREATE TABLE TESTTABLE ( TEST NVARCHAR(200) NULL )", conn);

//cmd1.ExecuteNonQuery();

SqlCeCommand cmd2 = new SqlCeCommand();

cmd2.CommandText = "SELECT COALESCE(TEST,@.p1) FROM TESTTABLE";

cmd2.Parameters.AddWithValue("p1", "no value");

cmd2.Connection = conn;

SqlCeDataAdapter adapter = new SqlCeDataAdapter(cmd2);

DataSet ds = new DataSet();

adapter.Fill(ds);

another command

SELECT COALESCE(TEST,'no value') FROM TESTTABLE

is OK, but this is not my scenario...

I think the problem is in parameter - it cannot be used in COALESCE function ?

|||

Looks like you must change the statement to not use parameters!

|||

But this is against th best practices - to use parameters instead of literals...

Of course, I am using SqlServerCe as alternative data provider (user can configure SqlServer/SqlServerCe) and i dont want to write another (bad!) code to achive same functionality...

any ideas ? will this be supported in another version ?

thank you

|||

Hi Jan.

This piece of code breaks with the error you mention using SQL Compact 3.1 in VS 2005, but works (res = "no value") with SQL Compact 3.5 beta 2 and VS 2008 beta 2:

Code Snippet

SqlCeConnection conn = new SqlCeConnection(@."Data Source=C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Samples\Northwind.sdf;");
conn.Open();
SqlCeCommand cmd2 = new SqlCeCommand();
cmd2.CommandText = "SELECT COALESCE(Region,@.p1) FROM Customers WHERE [Customer Id] = 'ALFKI'";
cmd2.Parameters.AddWithValue("p1", "no value");
cmd2.Connection = conn;
object res;
res = cmd2.ExecuteScalar();

So time will mend all wounds!

|||

Oh ! Great, this is, what I want to read...

Thank you for your quick and brilliant answers.

COALESCE function problem

Hi, I downloaded "Microsoft SQL Server Compact Edition" v3.1, installed, created database in VS2005, created table TESTTABLE with one column TEST NVARCHAR 200 and run this command against it:

SELECT coalesce(test,NULL) from testtable

but it says

Error : The function is not recognized by SQL Server Compact Edition. [ Name of function = coalesce,Data type (if known) = ]

In this forums there are more usages of coalesce function, so I think it is supported and fully functional, but where is the problem, then ?

My system is Windows Vista Business (Czech version), version of sqlcese30.dll and sqlceqp30.dll is 3.0.5300.0.

thank you for suggestions

J.S.

The COALSECE finction works fine in SQL Compact 3.1, it is a problem with the tool you are using. Have successfully tested in SQL Server Management Studio SP2. It is a problem with the Visual Studio Server Explorer query parser.

|||But, I'am not using any tool - I am using it from application (.NET 2.0). I will make a sample...|||

Hmm, i dont't know where was the problem, but now problem is another:

The specified argument value for the function is not valid. [ Argument # = 2,Name of function(if known) = coalesce ]

my source code i used:

string fn = args[0];

string cs = "Data source='"+fn+"'";

SqlCeConnection conn = new SqlCeConnection(cs);

conn.Open();

//only if table does not exists

//SqlCeCommand cmd1 = new SqlCeCommand("CREATE TABLE TESTTABLE ( TEST NVARCHAR(200) NULL )", conn);

//cmd1.ExecuteNonQuery();

SqlCeCommand cmd2 = new SqlCeCommand();

cmd2.CommandText = "SELECT COALESCE(TEST,@.p1) FROM TESTTABLE";

cmd2.Parameters.AddWithValue("p1", "no value");

cmd2.Connection = conn;

SqlCeDataAdapter adapter = new SqlCeDataAdapter(cmd2);

DataSet ds = new DataSet();

adapter.Fill(ds);

another command

SELECT COALESCE(TEST,'no value') FROM TESTTABLE

is OK, but this is not my scenario...

I think the problem is in parameter - it cannot be used in COALESCE function ?

|||

Looks like you must change the statement to not use parameters!

|||

But this is against th best practices - to use parameters instead of literals...

Of course, I am using SqlServerCe as alternative data provider (user can configure SqlServer/SqlServerCe) and i dont want to write another (bad!) code to achive same functionality...

any ideas ? will this be supported in another version ?

thank you

|||

Hi Jan.

This piece of code breaks with the error you mention using SQL Compact 3.1 in VS 2005, but works (res = "no value") with SQL Compact 3.5 beta 2 and VS 2008 beta 2:

Code Snippet

SqlCeConnection conn = new SqlCeConnection(@."Data Source=C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Samples\Northwind.sdf;");
conn.Open();
SqlCeCommand cmd2 = new SqlCeCommand();
cmd2.CommandText = "SELECT COALESCE(Region,@.p1) FROM Customers WHERE [Customer Id] = 'ALFKI'";
cmd2.Parameters.AddWithValue("p1", "no value");
cmd2.Connection = conn;
object res;
res = cmd2.ExecuteScalar();

So time will mend all wounds!

|||

Oh ! Great, this is, what I want to read...

Thank you for your quick and brilliant answers.

CmdExec job failed

Hi,
I created a job on sql server 2000, using the Operating System Command
option, under the command box, I inserted the command cmd /C
e:\docs\extracts\scripts\make_asal.bat, but when I try to run it, I get this
error message Executed as user:SEFDBA\sqlseragent.
'e:\docs\extracts\scripts\make_asal.bat' is not recognized as an internal or
external command, operable program or batch file. Process Exit Code 1.
The step failed. Please help, what am I doing wrong? Thanks.
New SQL Server DBA
Check the path and .bat file spelling? Type the exact same thing in from a
CMD prompt and see if it works.
HTH
Jerry
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:50ECA2ED-CAD2-4077-9C6F-BB606D4951F4@.microsoft.com...
> Hi,
> I created a job on sql server 2000, using the Operating System Command
> option, under the command box, I inserted the command cmd /C
> e:\docs\extracts\scripts\make_asal.bat, but when I try to run it, I get
> this
> error message Executed as user:SEFDBA\sqlseragent.
> 'e:\docs\extracts\scripts\make_asal.bat' is not recognized as an internal
> or
> external command, operable program or batch file. Process Exit Code 1.
> The step failed. Please help, what am I doing wrong? Thanks.
> --
> New SQL Server DBA

CmdExec job failed

Hi,
I created a job on sql server 2000, using the Operating System Command
option, under the command box, I inserted the command cmd /C
e:\docs\extracts\scripts\make_asal.bat, but when I try to run it, I get this
error message Executed as user:SEFDBA\sqlseragent.
'e:\docs\extracts\scripts\make_asal.bat' is not recognized as an internal or
external command, operable program or batch file. Process Exit Code 1.
The step failed. Please help, what am I doing wrong? Thanks.
--
New SQL Server DBACheck the path and .bat file spelling? Type the exact same thing in from a
CMD prompt and see if it works.
HTH
Jerry
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:50ECA2ED-CAD2-4077-9C6F-BB606D4951F4@.microsoft.com...
> Hi,
> I created a job on sql server 2000, using the Operating System Command
> option, under the command box, I inserted the command cmd /C
> e:\docs\extracts\scripts\make_asal.bat, but when I try to run it, I get
> this
> error message Executed as user:SEFDBA\sqlseragent.
> 'e:\docs\extracts\scripts\make_asal.bat' is not recognized as an internal
> or
> external command, operable program or batch file. Process Exit Code 1.
> The step failed. Please help, what am I doing wrong? Thanks.
> --
> New SQL Server DBA

CmdExec job failed

Hi,
I created a job on sql server 2000, using the Operating System Command
option, under the command box, I inserted the command cmd /C
e:\docs\extracts\scripts\make_asal.bat, but when I try to run it, I get this
error message Executed as user:SEFDBA\sqlseragent.
'e:\docs\extracts\scripts\make_asal.bat' is not recognized as an internal or
external command, operable program or batch file. Process Exit Code 1.
The step failed. Please help, what am I doing wrong? Thanks.
--
New SQL Server DBACheck the path and .bat file spelling? Type the exact same thing in from a
CMD prompt and see if it works.
HTH
Jerry
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:50ECA2ED-CAD2-4077-9C6F-BB606D4951F4@.microsoft.com...
> Hi,
> I created a job on sql server 2000, using the Operating System Command
> option, under the command box, I inserted the command cmd /C
> e:\docs\extracts\scripts\make_asal.bat, but when I try to run it, I get
> this
> error message Executed as user:SEFDBA\sqlseragent.
> 'e:\docs\extracts\scripts\make_asal.bat' is not recognized as an internal
> or
> external command, operable program or batch file. Process Exit Code 1.
> The step failed. Please help, what am I doing wrong? Thanks.
> --
> New SQL Server DBA

CMDEXEC Can't Find File, Yet Runs In a "Run" Dialog

Good morning, I hope someone here has had this problem and can tell me what
I'm doing wrong.
I have created a CMDEXEC job to run a program. I know my path is correct
because it works in a Run dialog. Yet SQL Server Agent reports "Process
could not be created... the system cannot find the file specified" in the
error log.
The job step is currently set to the path and name of the exe. I.e.:
\\server\folder\app.exe
Agent reports "Success" for this step after just a second, but the
application runs for an hour or so when it's started manually. Even if the
application was failing and crashing out, which it does not do when run
manually, it should take it a few seconds. Is Agent starting the app in the
background, asynchronously?
Any suggestions appreciated!JonOfAllTrades wrote:
> Good morning, I hope someone here has had this problem and can tell me wha
t
> I'm doing wrong.
> I have created a CMDEXEC job to run a program. I know my path is correct
> because it works in a Run dialog. Yet SQL Server Agent reports "Process
> could not be created... the system cannot find the file specified" in the
> error log.
> The job step is currently set to the path and name of the exe. I.e.:
> \\server\folder\app.exe
> Agent reports "Success" for this step after just a second, but the
> application runs for an hour or so when it's started manually. Even if th
e
> application was failing and crashing out, which it does not do when run
> manually, it should take it a few seconds. Is Agent starting the app in t
he
> background, asynchronously?
> Any suggestions appreciated!
It's a permissions problem. SQL Agent is running as a user (or maybe
Local System) that doesn't have permission to access
\\server\folder\app.exe. Fix that, and all should be well.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

CMDEXEC Can't Find File, Yet Runs In a "Run" Dialog

Good morning, I hope someone here has had this problem and can tell me what
I'm doing wrong.
I have created a CMDEXEC job to run a program. I know my path is correct
because it works in a Run dialog. Yet SQL Server Agent reports "Process
could not be created... the system cannot find the file specified" in the
error log.
The job step is currently set to the path and name of the exe. I.e.:
\\server\folder\app.exe
Agent reports "Success" for this step after just a second, but the
application runs for an hour or so when it's started manually. Even if the
application was failing and crashing out, which it does not do when run
manually, it should take it a few seconds. Is Agent starting the app in the
background, asynchronously?
Any suggestions appreciated!JonOfAllTrades wrote:
> Good morning, I hope someone here has had this problem and can tell me what
> I'm doing wrong.
> I have created a CMDEXEC job to run a program. I know my path is correct
> because it works in a Run dialog. Yet SQL Server Agent reports "Process
> could not be created... the system cannot find the file specified" in the
> error log.
> The job step is currently set to the path and name of the exe. I.e.:
> \\server\folder\app.exe
> Agent reports "Success" for this step after just a second, but the
> application runs for an hour or so when it's started manually. Even if the
> application was failing and crashing out, which it does not do when run
> manually, it should take it a few seconds. Is Agent starting the app in the
> background, asynchronously?
> Any suggestions appreciated!
It's a permissions problem. SQL Agent is running as a user (or maybe
Local System) that doesn't have permission to access
\\server\folder\app.exe. Fix that, and all should be well.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Cmd shell proxy

I am attempting to execute xp_cmdshell with a non-sysadmin db login. I have created a Windows account and the associated proxy account in SQL Server. I have verified SQL Server is showing the proxy account credentials. I am still getting the following error. What am I missing? Guidance is very appreciated.

Microsoft OLE DB Provider for SQL Server error '80040e09'

EXECUTE permission denied on object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

Try granting execute permission on xp_cmdshell to the non-sysadmin login

use master;

GRANT EXECUTE on xp_cmdshell to [non-sysadmin_login]

Clustering Dimension

I am new to data mining and have a question about OLAP dimensions built from models. Do you know if you can use a dimension that was created by the mining model wizard in the same cube that is being used as the source for mining model it self? I keep getting an error about a dependency loop and just want to make sure that I am not trying to do something impossible.
If this is illegal, do you know of a way of doing this without essentially having one cube dependant on another via a mining model and dimension? I tried to use the wizard to create a mining off of a table but it did not give me the option of creating a dimension from it.

You have to create a new cube that is "essentially" dependant on the other via the model and dimension if you want to use the model directly as a data mining dimension. This uses linked measure groups and doesn't really impact performance or storage space - it's mostly just metadata.

The only option of using a table-based model as a dimension is to write out the cluster labels and simply make the cluster label as a dimension attribute. You could even append the cluster label to the source data (e.g. the customer table) and not have a seperate dimension, simply a browseable attribute on the dimension of interest

|||Makes sense. Thanks.|||

Jamie, can you provide more information on how to do this? We'd like to have a series of clusters in an existing household dimension. That is, we need multiple occurences of cluster model results. I've looked at the data source, dimension, and cube created by the data mining model, but I don't see where the case ID (Household Key) and the cluster name could be extracted to update the existing dimension. We're using the cube for the data mining source.

I'd also like to know how to keep the linked cube and the source cube metadata in sync. If I make a change to the source cube, say by adding a new measure, the metadata for the linked cube gets out of sync. I've been deleting the data mining dimension, cube, and dsv and them adding them back in using the data mining menu in the model. What am I doing wrong here?

Clustering Dimension

I am new to data mining and have a question about OLAP dimensions built from models. Do you know if you can use a dimension that was created by the mining model wizard in the same cube that is being used as the source for mining model it self? I keep getting an error about a dependency loop and just want to make sure that I am not trying to do something impossible.
If this is illegal, do you know of a way of doing this without essentially having one cube dependant on another via a mining model and dimension? I tried to use the wizard to create a mining off of a table but it did not give me the option of creating a dimension from it.

You have to create a new cube that is "essentially" dependant on the other via the model and dimension if you want to use the model directly as a data mining dimension. This uses linked measure groups and doesn't really impact performance or storage space - it's mostly just metadata.

The only option of using a table-based model as a dimension is to write out the cluster labels and simply make the cluster label as a dimension attribute. You could even append the cluster label to the source data (e.g. the customer table) and not have a seperate dimension, simply a browseable attribute on the dimension of interest

|||Makes sense. Thanks.|||

Jamie, can you provide more information on how to do this? We'd like to have a series of clusters in an existing household dimension. That is, we need multiple occurences of cluster model results. I've looked at the data source, dimension, and cube created by the data mining model, but I don't see where the case ID (Household Key) and the cluster name could be extracted to update the existing dimension. We're using the cube for the data mining source.

I'd also like to know how to keep the linked cube and the source cube metadata in sync. If I make a change to the source cube, say by adding a new measure, the metadata for the linked cube gets out of sync. I've been deleting the data mining dimension, cube, and dsv and them adding them back in using the data mining menu in the model. What am I doing wrong here?

Clustered Primary Key and Foreign Key: T-SQL

I am having a little trouble getting this to work right, but have come a ways since I started this...
...other tables created first and with no problems.... then these two with the last table being the problem
I need to set one foreign key in the second table referencing the first table.
But, the primary key is clustered with the two foreign keys and I get the error...
There are no primary or candidate keys in the referenced table 'courseScores'
that match the referencing column list in the foreign key 'FK_course'.

CREATE TABLE dbo.courseScores (
courseId varchar(20) NOT NULL
CONSTRAINT FK_courseId_courseStructure2 FOREIGN KEY (courseId)
REFERENCES courseStructure (courseId),

studentId varchar(20) NOT NULL
CONSTRAINT FK_studentId_students2 FOREIGN KEY (studentId)
REFERENCES students (studentId),

CONSTRAINT PK_courseScore PRIMARY KEY CLUSTERED (courseId, studentId)

)
CREATE TABLE dbo.objScores ( tmp int IDENTITY(1,1) PRIMARY KEY,
objective varchar(50) NOT NULL,

courseIdvarchar(20)NOT NULL
CONSTRAINT FK_course FOREIGN KEY (courseId)
REFERENCES courseScores (courseId)
)

Once I get it working, then the tmp will be gone and then set 3 foreign keys as the clustered primary, fyi.
Not sure how to reference half a primary key?
Any help is greatly appreciated....
Thanks all,
Zath

A primary key guarantees uniqueness of rows, thereby also creating aguarantee that any foreign key referencing that primary key will bereferencing exactly one row. Your primary key is guaranteeinguniqueness of the combination of columns (courseid, studentid) -- butno such guarantee is made for ONLY courseid. If that column isindeed unique, apply a UNIQUE constraint to it and your foreign keywill work. Otherwise, you are going to have to propagate thestudentid column into the objScores table to get the full reference.
By the way, what do these tables represent? Perhaps we shouldback up before figuring out how to create these keys and see if there'sa better way to model the data.

|||

Thanks! Setting the previous field to UNIQUE did the trick!
Worked on this all day yesterday and databases are not my forte. I'll stick to code thank you
But, if you want to see the complete working version and have suggestions, I'm open...
CREATE TABLE dbo.courseStructure (courseID varchar(20) NOT NULL PRIMARY KEY,
courseName varchar(256) NOT NULL
)

CREATE TABLE dbo.objStructure (objID varchar(20) NOT NULL PRIMARY KEY,
objName varchar(256) NOT NULL,
courseID varchar(20) NOT NULL,
CONSTRAINT FK_courseID_courseStructure FOREIGN KEY (courseID)
REFERENCES courseStructure (courseID)
)

CREATE TABLE dbo.students (studentId varchar(20) NOT NULL PRIMARY KEY
CONSTRAINT FK_studentId_students FOREIGN KEY (studentId)
REFERENCES userAccount (userID)
)

CREATE TABLE dbo.courseScores (
courseId varchar(20) NOT NULL UNIQUE
CONSTRAINT FK_courseId_courseStructure2 FOREIGN KEY (courseId)
REFERENCES courseStructure (courseId),

studentId varchar(20) NOT NULL
CONSTRAINT FK_studentId_students2 FOREIGN KEY (studentId)
REFERENCES students (studentId),

lessonLocation varchar(20),
lessonStatus varchar(20),
lessonScoreRaw varchar(20),
lessonScoreMin varchar(20),
lessonScoreMax varchar(20),
startDate datetime,
completeDate datetime,
CONSTRAINT PK_courseScore PRIMARY KEY CLUSTERED (courseId, studentId)

)

CREATE TABLE dbo.objScores (
objective varchar(50) NOT NULL,

objID varchar(20) NOT NULL
CONSTRAINT FK_objId_objstructure FOREIGN KEY (objID)
REFERENCES objStructure (objID),

studentId varchar(20) NOT NULL
CONSTRAINT FK_studentId_students3 FOREIGN KEY (studentId)
REFERENCES students (studentId),

courseId varchar(20) NOT NULL
CONSTRAINT FK_course FOREIGN KEY (courseId)
REFERENCES courseScores (courseId),

objStatus varchar(20),
objScoreRaw varchar(20),
objScoreMin varchar(20),
objScoreMax varchar(20)

CONSTRAINT PK_objScores PRIMARY KEY CLUSTERED (objID, studentId, courseId)
)
Zath

|||What do these tables represent? What is an 'objScore'?

|||Doing a SCORM and LMS thing and for testing, we are using sql server db.
It is a testing site.
A student may enroll in multiple courses.
Each course has multiple objectives and scores...
There are many other tables in the db, but for this part, only the useraccount table needed to be accessed for the student ID.
But, it seems to be running ok for now.....
But always open to suggestions to improve it.
Zath

clustered ix and non on same field

I have a SS 2000 database that was created from a purchased, commercial
software product. I.e. I had no input into the design. This is your typical
OLTP and DSS database. I notice on many tables that there is a clustered
index and non-clustered index on the same column. I feel like this is a
mistake and I should delete every non-clustered index that has a clustered
index, assuming the clustered index is the primary key. Is it ever
advantageous to have both a CIX and nonCIX on the same field so that the
optimizer can use one for range/sorts (CIX) and the other for single record
lookup (nonCIX)?
TIA - nick
The only case I can think of is when the NC covers the query and you have low selectivity. SQL
Server can now read the NS index pages only which will be fewer pages than the CL pages for the
corresponding data-range over the column. However, I suspect that this is the PK and possibly even
some surrogate key, so an NC index on this column will probably not cover that many queries.
I've seen stranger things with commercial software databases... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:31AC2451-7141-4573-8E4D-C3E87FCA9623@.microsoft.com...
>I have a SS 2000 database that was created from a purchased, commercial
> software product. I.e. I had no input into the design. This is your typical
> OLTP and DSS database. I notice on many tables that there is a clustered
> index and non-clustered index on the same column. I feel like this is a
> mistake and I should delete every non-clustered index that has a clustered
> index, assuming the clustered index is the primary key. Is it ever
> advantageous to have both a CIX and nonCIX on the same field so that the
> optimizer can use one for range/sorts (CIX) and the other for single record
> lookup (nonCIX)?
> TIA - nick
|||In addition to Tibor's reply: such an index can also help if there is no
other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
I would consider it a bad practice to blindly add a nonclustered index
of the clustered index to each and every table. Only if a query like the
mentioned examples are very important (above average) one could
incidentally consider such an extra index. Otherwise, it is mostly
wasting space and adding overhead for index maintenance.
Gert-Jan
Nick wrote:
> I have a SS 2000 database that was created from a purchased, commercial
> software product. I.e. I had no input into the design. This is your typical
> OLTP and DSS database. I notice on many tables that there is a clustered
> index and non-clustered index on the same column. I feel like this is a
> mistake and I should delete every non-clustered index that has a clustered
> index, assuming the clustered index is the primary key. Is it ever
> advantageous to have both a CIX and nonCIX on the same field so that the
> optimizer can use one for range/sorts (CIX) and the other for single record
> lookup (nonCIX)?
> TIA - nick
|||> In addition to Tibor's reply: such an index can also help if there is no
> other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
which is a case where the index covers the query, but possibly an important example worth pointing
out explicitly. :-)
Also, it can be worth to mention that any NC index will cover above query, and probably SQL Server
will pick the NC index over the most narrow column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:467AEDBF.806A445F@.toomuchspamalready.nl...[vbcol=seagreen]
> In addition to Tibor's reply: such an index can also help if there is no
> other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
> I would consider it a bad practice to blindly add a nonclustered index
> of the clustered index to each and every table. Only if a query like the
> mentioned examples are very important (above average) one could
> incidentally consider such an extra index. Otherwise, it is mostly
> wasting space and adding overhead for index maintenance.
> Gert-Jan
>
> Nick wrote:
|||Tibor Karaszi wrote:
>
> which is a case where the index covers the query, but possibly an important example worth pointing
> out explicitly. :-)
> Also, it can be worth to mention that any NC index will cover above query, and probably SQL Server
> will pick the NC index over the most narrow column.
Yes, and this is a somewhat interesting, because the NC index that
matches the clustered index will (by definition) always be the
narrowest, because the clustered key is part of any nonclustered index
key. So for SELECT COUNT(*) FROM the_table, this type of index is
perfect (however small the difference with another NC index).
Gert-Jan
|||> Yes, and this is a somewhat interesting, because the NC index that
> matches the clustered index will (by definition) always be the
> narrowest,
How true, Gert-Jan.
Good catch, an easy thing to overlook (just as I did). :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:467BF4AB.FB8F8F07@.toomuchspamalready.nl...
> Tibor Karaszi wrote:
> Yes, and this is a somewhat interesting, because the NC index that
> matches the clustered index will (by definition) always be the
> narrowest, because the clustered key is part of any nonclustered index
> key. So for SELECT COUNT(*) FROM the_table, this type of index is
> perfect (however small the difference with another NC index).
> Gert-Jan

clustered ix and non on same field

I have a SS 2000 database that was created from a purchased, commercial
software product. I.e. I had no input into the design. This is your typical
OLTP and DSS database. I notice on many tables that there is a clustered
index and non-clustered index on the same column. I feel like this is a
mistake and I should delete every non-clustered index that has a clustered
index, assuming the clustered index is the primary key. Is it ever
advantageous to have both a CIX and nonCIX on the same field so that the
optimizer can use one for range/sorts (CIX) and the other for single record
lookup (nonCIX)?
TIA - nickThe only case I can think of is when the NC covers the query and you have lo
w selectivity. SQL
Server can now read the NS index pages only which will be fewer pages than t
he CL pages for the
corresponding data-range over the column. However, I suspect that this is th
e PK and possibly even
some surrogate key, so an NC index on this column will probably not cover th
at many queries.
I've seen stranger things with commercial software databases... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:31AC2451-7141-4573-8E4D-C3E87FCA9623@.microsoft.com...
>I have a SS 2000 database that was created from a purchased, commercial
> software product. I.e. I had no input into the design. This is your typic
al
> OLTP and DSS database. I notice on many tables that there is a clustered
> index and non-clustered index on the same column. I feel like this is a
> mistake and I should delete every non-clustered index that has a clustered
> index, assuming the clustered index is the primary key. Is it ever
> advantageous to have both a CIX and nonCIX on the same field so that the
> optimizer can use one for range/sorts (CIX) and the other for single recor
d
> lookup (nonCIX)?
> TIA - nick|||In addition to Tibor's reply: such an index can also help if there is no
other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
I would consider it a bad practice to blindly add a nonclustered index
of the clustered index to each and every table. Only if a query like the
mentioned examples are very important (above average) one could
incidentally consider such an extra index. Otherwise, it is mostly
wasting space and adding overhead for index maintenance.
Gert-Jan
Nick wrote:
> I have a SS 2000 database that was created from a purchased, commercial
> software product. I.e. I had no input into the design. This is your typic
al
> OLTP and DSS database. I notice on many tables that there is a clustered
> index and non-clustered index on the same column. I feel like this is a
> mistake and I should delete every non-clustered index that has a clustered
> index, assuming the clustered index is the primary key. Is it ever
> advantageous to have both a CIX and nonCIX on the same field so that the
> optimizer can use one for range/sorts (CIX) and the other for single recor
d
> lookup (nonCIX)?
> TIA - nick|||> In addition to Tibor's reply: such an index can also help if there is no
> other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
which is a case where the index covers the query, but possibly an important
example worth pointing
out explicitly. :-)
Also, it can be worth to mention that any NC index will cover above query, a
nd probably SQL Server
will pick the NC index over the most narrow column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:467AEDBF.806A445F@.toomuchspamalready.nl...[vbcol=seagreen]
> In addition to Tibor's reply: such an index can also help if there is no
> other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
> I would consider it a bad practice to blindly add a nonclustered index
> of the clustered index to each and every table. Only if a query like the
> mentioned examples are very important (above average) one could
> incidentally consider such an extra index. Otherwise, it is mostly
> wasting space and adding overhead for index maintenance.
> Gert-Jan
>
> Nick wrote:|||Tibor Karaszi wrote:
>
> which is a case where the index covers the query, but possibly an importan
t example worth pointing
> out explicitly. :-)
> Also, it can be worth to mention that any NC index will cover above query,
and probably SQL Server
> will pick the NC index over the most narrow column.
Yes, and this is a somewhat interesting, because the NC index that
matches the clustered index will (by definition) always be the
narrowest, because the clustered key is part of any nonclustered index
key. So for SELECT COUNT(*) FROM the_table, this type of index is
perfect (however small the difference with another NC index).
Gert-Jan|||> Yes, and this is a somewhat interesting, because the NC index that
> matches the clustered index will (by definition) always be the
> narrowest,
How true, Gert-Jan.
Good catch, an easy thing to overlook (just as I did). :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:467BF4AB.FB8F8F07@.toomuchspamalready.nl...
> Tibor Karaszi wrote:
> Yes, and this is a somewhat interesting, because the NC index that
> matches the clustered index will (by definition) always be the
> narrowest, because the clustered key is part of any nonclustered index
> key. So for SELECT COUNT(*) FROM the_table, this type of index is
> perfect (however small the difference with another NC index).
> Gert-Jan