Problem with sql

I've got 2 table :department and employee. There's a column called deptNo reference employee to department as a foreign key.

1) When I want to delete the employee table, it gives me this error:
Server: Msg 3726, Level 16, State 1, Line 1
Could not drop object 'Employee' because it is referenced by a FOREIGN KEY constraint.

2) If I enter the statement below:
CREATE TABLE Employee(EmployeeNo int PRIMARY KEY, EmployeeName varchar(10)UNIQUE, DeptNo int NOT NULL, JobTitle char(10),FOREIGN KEY(DeptNo)REFERENCES Department(DeptNo)ON UPDATE CASCADE ON DELETE CASCADE)
it gives me an error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ON'.It looks like you had the wrong direction for your constraint, which is what you might be trying to correct. To eliminate the error you can delete the foreign key constraint (since you are recreating the table and constraint). For the 2nd part, try the following (I modified your version slightly) :

CREATE TABLE Employee(EmployeeNo int PRIMARY KEY, EmployeeName varchar(10)UNIQUE,
JobTitle char(10),deptno int NOT NULL FOREIGN KEY REFERENCES Department(DeptNo)ON UPDATE CASCADE ON DELETE CASCADE)|||What version of SQL Server are you running?

ON DELETE CASCADE is only supported in MS SQL Server 2000

Problem with report DataSource

Hello,
I am deploying reports via the web service. I create a folder, then create
a data source. Once I create a report, I make a reference to the data source
created and change the reports invalid data source to the reference.
When I did this with a data source with credentials stored on the report
server, it worked with no problems. When I changed this to prompt for
crendentials, accessing the report via web services cannot find the data
source associated with the report (the one defined in the rdl does not exist
in the folder fyi). Is this because a reference has no name in the database?
It does work via the Report Manager.
Here are some code snippets:
this.ReportService.CreateReport(reportType.ToString(), "/" + dbName, true,
definition, null);
//change its datasource to the new one
DataSourceReference reference = new DataSourceReference();
reference.Reference = "/" + dbName + "/" + dbName;
DataSource[] dataSources = this.ReportService.GetReportDataSources("/" +
dbName + "/" + reportType.ToString() );
for(int i=0;i<dataSources.Length;i++)
{
if(dataSources[i].Item is InvalidDataSourceReference)
{
dataSources[i].Item = reference;
}
}
this.ReportService.SetReportDataSources("/" + dbName + "/" +
reportType.ToString() ,dataSources);
and for credentials:
DataSourceCredentials d = new DataSourceCredentials();
d.DataSourceName = dbName;
d.UserName = userName;
d.Password = password;
this.mDataSourceCredentials = new DataSourceCredentials[]{d};
any help would be greatly appreciated.It seems recently that my posts never get replied to - Im sure others must
share the same frustration. The solution I came up with was to read the .rdl
file in as xml, search for all data source nodes and replace the old name
(attribute) with the new name. I also created the data source as a data
source, not as a reference.
"comet61" wrote:
> Hello,
> I am deploying reports via the web service. I create a folder, then create
> a data source. Once I create a report, I make a reference to the data source
> created and change the reports invalid data source to the reference.
> When I did this with a data source with credentials stored on the report
> server, it worked with no problems. When I changed this to prompt for
> crendentials, accessing the report via web services cannot find the data
> source associated with the report (the one defined in the rdl does not exist
> in the folder fyi). Is this because a reference has no name in the database?
> It does work via the Report Manager.
> Here are some code snippets:
> this.ReportService.CreateReport(reportType.ToString(), "/" + dbName, true,
> definition, null);
> //change its datasource to the new one
> DataSourceReference reference = new DataSourceReference();
> reference.Reference = "/" + dbName + "/" + dbName;
> DataSource[] dataSources = this.ReportService.GetReportDataSources("/" +
> dbName + "/" + reportType.ToString() );
> for(int i=0;i<dataSources.Length;i++)
> {
> if(dataSources[i].Item is InvalidDataSourceReference)
> {
> dataSources[i].Item = reference;
> }
> }
> this.ReportService.SetReportDataSources("/" + dbName + "/" +
> reportType.ToString() ,dataSources);
> and for credentials:
> DataSourceCredentials d = new DataSourceCredentials();
> d.DataSourceName = dbName;
> d.UserName = userName;
> d.Password = password;
> this.mDataSourceCredentials = new DataSourceCredentials[]{d};
> any help would be greatly appreciated.
>

Problem with reference dimension & snowflaked tables

OK, this is a bit complicated, so I hope I can explain it clearly. I'll try not to get bogged down in the details of the problem domain, but bear with me.

We have a cube with a regular dimension and a reference dimension. The reference dimension is connected through the fact table through the regular dimension (there are many other dimensions as well, but I'll simplify the situation here). Both the regular dimension and the reference dimension have hierarchies that that include as their top level a level called "System". For both of the dimensions, the "System" level is snowflaked in from the same table. So the regular dimension table contains a column called System_key, and a corresponding attribute. Similarly, the reference dimension table also contains a column called System_key, and a corresponding attribute. Both keys point in to the same table. Naturally, the regular dimension table also has a column that points to the reference dimension table, as you would expect with a reference dimension.

OK, I hope that's clear (if a little pathological). Essentially, the "System" attribute is present on both dimensions, and both dimensions get that value by snowflaking in to the same table.

So the problem comes when looking at measures broken down by System. The regular dimension has a "System" user hierarchy which contains a single level, the System level. When I drag that hierarchy on to a pivot chart, I get the wrong results.

From what I can tell, when processing the dimension it is populating the system attribute for the regular dimension based on the key value stored in the reference dimension table, not the regular dimension table. I have no idea why it would do that. But I look at the SQL statement generated during processing of the key attribute for the regular dimension, and it includes in the where statement a join between the reference dimension System_key column and the System table, rather than the regular dimension System_key column as I would expect. The result is totally wrong values.

Does this make any kind of sense? Any idea what could be causing this, and what might fix it (note that totally redoing all the incestuous table relationships at this point is probably not an option).What if, in the Data Source View, you create another instance of the "System" attribute table, using a simple Named Query like: "select * from System"? Then you snowflake one instance with the regular dimension table, and the other instance with the reference dimension table. My guess (maybe someone can confirm this?) is that, unless you explicitly use role-playing dimensions (like Date in Adventure Works), a single snowflaked table in the DSV is interpreted as a single instance, even if it's incorporated into the schema of multiple dimensions. And in your case, I'm assuming that breaking the "System" attribute out into its own role-playing dimension is not an option?|||Deekpak,
Thanks for the suggestion. That does seem to solve the problem.

However, it does seem like a bit of a hack. It would be nice to know if this is a bug in SSAS that may be fixed in the future, or if there's something fundamental that I don't understand here. We snowflake in this table in multiple other dimensions as well (though none others with reference dimensions currently), so it would be good to know if we're setting ourselves for more trouble later.

Any 'Softies out there care to comment?