Hi all:
Just joined forums, so a big hello to you all :)
Just wondering if someone could explain the following two terms too me?
*Semantic Constraint
*Access Control
Are there limitations each can impose within a relational database?
Many thanks
Olly :D
Hello,
I got the following error while trying to create a report model:
The semantic model is not valid. Details: The element 'Entity' in namespace 'http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling' has incomplete content. List of possible elements expected: 'IdentifyingAttributes' in namespace 'http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling'. Line 943, position 3. (rsModelingError) (Report Services SOAP Proxy Source)
------------------------------
For help, click: http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsModelingError&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=9.00.1399.00
------------------------------
Program Location:
at Microsoft.SqlServer.Management.UI.RSClient.RSClientConnection.GenerateModel(String DataSource, String Model, String Parent, Property[] Properties)
at Microsoft.SqlServer.Management.UI.RSWrapper.NodeDataSource.GenerateModel(String name, String folderpath, String description)
at Microsoft.SqlServer.Management.UI.RSUserInterface.DataSourcePropertiesGenerateModel.OnRunNow(Object sender)
at Microsoft.SqlServer.Management.SqlMgmt.PanelExecutionHandler.Run(RunType runType, Object sender)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)
I've searched every forum and every page from microsoft but couldn't find a single reference about this problem. I don't know what to do and I really need to get this model working fine for report builder
Can you help me?
André
Hello
While following the instructions on the tutorial "Creating a Report Model Based on an Analysis Services Cube", I got this error when trying to create the report model:
The semantic model is not valid. Details: The element 'Entity' in namespace 'http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling' has incomplete content. List of possible elements expected: 'IdentifyingAttributes' in namespace 'http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling'. (InvalidSemanticModel)
I have tried to use a SSAS cube of my own, which is working fine and tried to create a model based on it so I can create ad-hoc reports.
Parameters chosen on the data source creation:
Connection Type: Microsoft SSAS
Connection String: Data Source=localhostSQL2005;Initial Catalog="TAP Analysis"
Connect Using: Windows Integrated Security
To SSAS using the Microsoft SQL Server Management Studio, I use Windows Authentication.
In the model creation, I have tried to switch to other credentials, but the error still remains... Can you help me with this one?
Thanks
André
Does SQL 2005 offer any tools to perform Latent Semantic Analysis on large data sets? Say I have millions of daily search queries and I'd like to link queries to one another based on semantic content with a goal of mapping them to larger "categories" . One way of doing this is to compare word frequency and proximity to construct a semantic "weight space". This is the approach generally used in LSA.
Anyone using SQL 2005 for this? Anyone see a way it could be done?
Using SQL Server 2005 Business Intelligence Studio, I created a Data Source (Test.ds), Data Source View (Test.dsv), and a Report Model (Test.smdl). It is very easy to deploy this model into a Report Server, from the Business Intelligence Studio, by right clicking the Report Model Project and choosing 'Deploy'.
View 9 Replies View Related
I recently changed my datasource in our Reporting Service Model. We changed to a quicker SQL server with disk capacity. I am able to run new reports but when I try to run old ones that were on the old datasource, I get an error. The datasource has been changed to point to the new server (datasource).
The error is: An error has occurred during report processing. (rsProcessingAborted)
Semantic query compilation failed: e EmptySemanticQuery The SemanticQuery does not contain any Groupings or MeasureGroups. SemanticQuery must contain at least one of these elements. (SemanticQuery ''). (rsSemanticQueryEngineError)
I will appreciate any help.
Nick
Hi,
I am having trouble getting the following query to execute in the report builder:
OPEN Symmetric KEY DemoKey Decryption BY Certificate DemoCertificate;
SELECT
PIN,
EffDate,
Cast(DecryptByKey(Address1) AS varchar(200)) AS Address1,
Cast(DecryptByKey(Address2) AS varchar(200)) AS Address2,
Cast(DecryptByKey(Address3) AS varchar(200)) AS Address3,
Cast(DecryptByKey(City) AS varchar(125)) AS City,
Cast(DecryptByKey(State) AS varchar(125)) AS State,
Cast(DecryptByKey(ZipCode) AS varchar(90)) AS ZipCode,
Cast(DecryptByKey(Country) AS varchar(125)) AS Country
FROM Address
CLOSE Symmetric KEY DemoKey;
I am getting the following error when I try to generate a report in the report builder:
Semantic query execution failed. Incorrect syntax near the keyword 'Open'.
Incorrect syntax near ')'.
----------------------------
Query execution failed for data set 'dataSet'.
----------------------------
An error has occurred during report processing.
I was getting a similar error when I tried to enter the query using the Named Query window of the dsv file. However, I was able to overcome this obstacle by entering the query directly into the code of the dsv file and it works perfectly. That is, when I click on "Explore Data" in the dsv design view the data is retrieved from the database and is decrypted properly.
It appears that the report builder's semantic query engine does not accept transact-sql statements that involve data decryption. Does anybody know if this is true? Or is there some workaround for this situation? Any help would be greatly appreciated.
Thanks,
Mark
Hi
I get the following error when running a report in report builder using the Adventureworks sample model:
Semantic query execution failed. Incorrect syntax near 'NULLAND'.----------------------------Query execution failed for data set 'dataSet'.----------------------------An error has occurred during report processing.
Here is how to replicate the error:
Create a table report using the Adventure Works model.
Select Product entity, add Product Category and #Products to the table.
Edit formula for #Products to "COUNT(Products)" and add a filter on Products for "Discontinued Date is empty". (I want a count of products that have not been discontinued).
If you run the report now, it willl work as expected.
Add a report filter for "Product.Color is empty" (I only want to see products that don't have a color)
Run the report to get the above error.
While the above is a contrived example, I am getting the same error on a data model that I am developing for a customer.
Am I missing something, or is this a bug in Report Builder?
Thanks
Hi, all.
I am trying to create table with following SQL script:
Code Snippet
create table Projects(
ID smallint identity (0, 1) constraint PK_Projects primary key,
Name nvarchar (255) constraint NN_Prj_Name not null,
Creator nvarchar (255),
CreateDate datetime
);
When I execute this script I get following error message:
Error source: SQL Server Compact ADO.NET Data Provider
Error message: Named Constraint is not supported for this type of constraint. [ Constraint Name = NN_Prj_Name ]
I looked in the SQL Server Books Online and saw following:
CREATE TABLE (SQL Server Compact)
...
< column_constraint > ::= [ CONSTRAINT constraint_name ] { [ NULL | NOT NULL ] | [ PRIMARY KEY | UNIQUE ] | REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ]
As I understand according to documentation named constraints should be supported, however error message says opposite. I can rephrase SQL script by removing named constraint.
Code Snippet
create table Projects(
ID smallint identity (0, 1) constraint PK_Projects primary key,
Name nvarchar (255) not null,
Creator nvarchar (255),
CreateDate datetime
);
This script executes correctly, however I want named constraints and this does not satisfy me.
I have a scenario of where the standard Full-Text search identifies keywords but Semantic Search does not recognize them as keywords. I'm hoping to understand why Semantic Search might not recognize them. The context this is being used in medical terminology and the specific key words I noticed missing right off the bat were medications.
For instance, if I put the following string into a FT indexed table
'J9355 - Trastuzumab (Herceptin)'
AND
'J9355 - Trastuzumab emtansine'
The Semantic Search recognized 'Herceptin' and 'Emtansine' but not 'Trastuzumab'
Nor in
'J8999 - Everolimus (Afinitor)'
It did not recognize 'Afinitor' as a keyword.
In all cases the Base of Full-Text did find those keywords and were identifiable using the dmvsys.dm_fts_index_keywords_by_document.It does show the index as having completed.
why certain words might not be picked up while others would be? Could it be a language/dictionary issue? I am using English and accent insensitive settings?
We are using SQL CE 3.5 on tablet PCs, that synchs with our host SQL 2005 Server using Microsoft Synchronization Services. On the tablets, when inserting a record, we get the following error:
A duplicate value cannot be inserted into a unique index. [ Table name = refRegTitle,Constraint name = PK_refRegTitle
But the only PK on this table is RegTitleID.
The table structure is:
[RegTitleID] [int] IDENTITY(1,1) NOT NULL,
[RegTitleNumber] [int] NOT NULL,
[RegTitleDescription] [varchar](200) NOT NULL,
[FacilityTypeID] [int] NOT NULL,
[Active] [bit] NOT NULL,
The problem occurs when a Title Number is inserted and a record with that number already exists. There is no unique constraint on Title Number.
Has anyone else experienced this?
Hi Everyone,
Currently, we are facing a serious problem of conversion from Sql Query to Semantic Query and we are looking for any API or Component that handles this conversion process.
Requirement Details:
I'd like to tell the story happened which lead us to this requirement. We want to convert Shazam Reports (SRW) to Reporting Services (RDL) reports that support Report Builder (semantic queries). So, we planned to convert the SRW query structure into a SQL query structure and later to Semantic Query, which is recognized by the Report Builder.
In that process, we've successfully developed a parser component that parses the SRW's query structure into standard Sql query structure but now we struggling, as we don't know how to convert an sql query into semantic query.
We are running out of time and any information towards the solution is a great help to us.
Hope I'd get some positive reply from you.
Regards,
Sinha
Hi,
I am trying to figure out how to do this.
For each row, only one out of two columns(id1,id2) should be populated. So if the id1 column is already populated and the application tries to fill in something for id2 then we just simply don't want to allow that and vice versa.
I am thinking triggers is the way to go. What do you think?
thanks
Rozina
using alter table syntax how can i insert the field TramingChoiceCd
Extend the constraint on NetwkChannel table UQ__TetwkChannel__50FB042B to include TramingChoiceCd
Which is the preferred method Rule, Check Constraint or Trigger?
I want to set a column to todays date when the column is = "T"
else when "F" set it to a future date. Each time there is a insertion into
the table.
I have a varchar field in a table.I want to restrict the entries in that field as "yes" or "no" nothing else.No record will be allowed for this field without yes or no.My question is is it possible without using any trigger for the table?I want to do it with the help of a constraint.
View 1 Replies View RelatedWhen I see desing table option in enterprise manager of a table I don't see any constraints, but when I extract ddl I can see all 6 of them. They are all unique constraints not the check constraints. Is this normal. I am new to SQL Server and would appreciate some explanation.
Thanks
Hi, i want to put a contraint on a table which much check agains two values in the same column for the same member.
For example, i don't want a male to get information based on breast cancer, and i don't want a female to get information based on prostate cancer.
I have included some sample data. Just copy and paste.
Code Block
DECLARE @MemberLookupValues TABLE (OptionID INT, ValueID INT, Description VARCHAR(20))
INSERT @MemberLookupValues VALUES (3, 10, 'Male')
INSERT @MemberLookupValues VALUES (3, 11, 'Female')
INSERT @MemberLookupValues VALUES (7, 69, 'Prostate Cancer')
INSERT @MemberLookupValues VALUES (7, 70, 'Breast Cancer')
DECLARE @MemberValues TABLE (MemberID INT, OptionID INT, ValueID INT)
INSERT @MemberValues VALUES (1, 3, 10)
INSERT @MemberValues VALUES (1, 7, 69)
INSERT @MemberValues VALUES (1, 7, 70)
INSERT @MemberValues VALUES (2, 3, 11)
INSERT @MemberValues VALUES (2, 7, 69)
SELECT * FROM @MemberLookupValues
SELECT * FROM @MemberValues
I've highlighted the values that must be stopped.
So the basic check would be, IF OptionID = 3 AND ValueID = 10 then it must not allow you to insert the values OptionID = 7 AND ValueID = 70
I hope that makes sense.
Any help will be greatly appreciated, if you need any more informaiton then just ask,
Kind Regards
Carel Greaves
Hi I was wodering how to add an OR statment right in the Check Constraint expression.
This is what I am starting with in the database
([zip] like '[0-9][0-9][0-9][0-9][0-9]')
and what I want well not exact but this would answer my question
([zip] like '[0-9][0-9][0-9][0-9][0-9] || [A-Z][A-Z][A-Z][A-Z][A-Z]')
Thanks for any help
Is it possible to set an index of no duplicates on a column other than the primary key of a table? If yes, how is this done?
View 2 Replies View Relatedhow can i implement delete constraint? i mean i don't want the rows of the primary key table to be deleted if they are used as foreign key in some other table. so i want to check if that PK is used as foreign key in other tables before deleting.
View 10 Replies View RelatedHi,I want to add unique contraint to EXISTING column in EXISTING table.I have ms sql 2005.How to do that?
View 7 Replies View Relatedhi, I want to implement a constraint on a talbe for two fields
phone numbers should b (###)###-####
and ss# should be ###-##-####
How can I create such constraint. I tried, but got an error message and could not save the table with the new changes.
Thanks
Ahmed
student --- enrollment is 1- to - many . ssn is pk in student. ssn and courseid is pk in enrollment. later I added the foreign key constraint FK_SSN in enrollment table reference student table. it is ok.
enrollment --- lessonhistory is 1-to -many, ssn, courseid and lessonid is pk in lessonhistory . I tried to set FK_SSN foreign key constraint in lessonhistory table reference enrollment table, it always show error message " no primary key in referewnce talbe enrollment "
I don't know how to fix it. could you help me out, thanks!!
Can somebody tell me about on delete constraint and where should it be used - table having foriegn key?
coz want i want is- the moment i delete the data from the table whose primary key is been referenced as foreign key , The Data in all the tables where its primary key is beeen used as forein key should be deleted.
:confused:
Does anyone have any Idea on how I could enforce a unique constrait across multiple tables?
View 1 Replies View RelatedMy company does medical billing for several clients. Each client may have a contract with several insurance companies (Payors) for rates for different procedures. I have a table named FeeSchedule with four (relevant) columns: Client, CPTCode (procedure code), Payor, and Fee.
Some Payors don't really negotiate, and have the same FeeSchedule for all clients. I know the right way to track that is have a separate set of records for each client, even for payors like medicare where every client will have the same set of records. However, the data-entry burden for populating that table would be very large, and I would like to use be able to have use a NULL client like a wildcard.
The problem is I then can't use client as part of the key, and I need to be able to restrict it so that if I have a NULL client for a specific CPT and Payor and I can't also have a another Client for that CPT and Payor.
Any ideas how to accomplish this or the equivalent functionality?
Hi everybody
we have the following tables
1)
Country
Countryid CountyNAme
INIndia
MYMalaysia
UKUnitedKingdom
here Countryid is the primary key.
2) MainDept
DeptID Deptname Countryid
CMCashManagementIN
CBConsumerBankingMY
CSCustomer SupportIN
IBInternetBankIN
here deptid is the primary key
3) UserMaster
Uid Uname Deptid Countryid
001 Chris CMIN
002 Raja CSIN
003 Ram CBMY
here Uid is the primary key.
The problem is when i change the countryid from one country to another for a deptname.
THe change is not reflected in the usermaster table as it still shows the previous countryid.
For eg. user Chris belongs to dept Cash management which is situated in india.
Now if i change in mainDept table the cash management from india(IN) to say malaysia(MY).the corresponding change is not reflected in usermaster table.it still shows india. So when i query for chris in usermaster i get an error
as i am searching in india for cash mangement.
i tried using on update cascade but here it did not work as i have to make DeptID & countryID in MainDept table as composite key & use Deptid & countryid in usermaster as refernce key.
Since i have 20-25 tables also referencing the above 2 tables i have to set reference key in all these tables & these tables are in turn referenced elsewhere in other tables. Thus i end up creating a large no. of composite keys.
IS there any other way to solve this problem?
note : In sqlserver we can give on update cascade still it has the above problem
but in Oracle on update cascade is not possible
Can anybody suggest a solution for this in both sql server and in oracle
Thanks u verymuch
I am having problem to find the right syntax to DROP a column with contrainst and recrate it
I get an error
if exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='myTable'
and COLUMN_NAME='myDate' )
ALTER TABLE [dbo].[myTable] DROP COLUMN myDate
GO
ALTER TABLE [dbo].[myTable] WITH NOCHECK ADD
myDate datetime CONSTRAINT [DF_myDate] DEFAULT (GetDate())
GO
Query Analyser says :
Server: Msg 5074, Level 16, State 1, Line 5
The object 'DF_myDate' is dependent on column 'myDate'.
Server: Msg 4922, Level 16, State 1, Line 5
ALTER TABLE DROP COLUMN myDate failed because one or more objects access this column.
Server: Msg 2705, Level 16, State 4, Line 2
Column names in each table must be unique. Column name 'myDate' in table 'dbo.myTable' is specified more than once.
thank you for helping
Hello, I have hit the wall here...can't make sense of this one.
I have a script that creates a PRIMARY KEY constraint called PK_tblDRG
CODE:
ALTER TABLE [dbo].[tblDRG]
ALTER COLUMN [record_id] Uniqueidentifier NOT NULL
Go
ALTER TABLE [dbo].[tblDRG]
WITH NOCHECK ADD PK_tblDRG PRIMARY KEY CLUSTERED
(
[record_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
All is fine with that. I run this to verify:
EXEC sp_pkeys @table_name = 'tblDRG'
,@table_owner = 'dbo' ,@table_qualifier = 'Relational_05Q3'
which returns this:
TABLE_QUALIFIERTABLE_OWNERTABLE_NAMECOLUMN_NAMEKEY_SEQPK_NAME
Relational_05Q3dbotblDRGrecord_id1PK_tblDRG
Now I want to drop the constriant if it exists:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PK__tblDRG]') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE [dbo].[tblDRG] DROP CONSTRAINT PK__tblDRG
AND I get this in return:
The command(s) completed successfully.
So, lets double check:
EXEC sp_pkeys @table_name = 'tblDRG'
,@table_owner = 'dbo' ,@table_qualifier = 'Relational_05Q3'
AND I STILL GET THIS:
TABLE_QUALIFIERTABLE_OWNERTABLE_NAMECOLUMN_NAMEKEY_SEQPK_NAME
Relational_05Q3dbotblDRGrecord_id1PK_tblDRG
Hmmmmm. Looks like the IF statement didn't do it's job. Ok fine. I'll just kill it myself:
ALTER TABLE [dbo].[tblDRG] DROP CONSTRAINT PK__tblDRG
AND I GET THIS?!?!?!?!
Server: Msg 3728, Level 16, State 1, Line 1
'PK__tblDRG' is not a constraint.
What am I not getting here? Is it me...I can take If I am a bone head here.
Any help would be appreciated. Thanks!
Suppose I have a table called "Languages" with two fields. One field is an Identity field that acts as the Primary Key. The second field is "LanguageName" which would naturally be "English", "Spanish", etc. What I want to do is put a constraint on the LanguageName field so that someone cannot enter the same name twice.Is it better to create an Index --> Create UNIQUE and use:a) Constraint?b) Index with Ignore duplicate key checked?Is there any benefit of one over the other for my purpose? Thanks.
View 4 Replies View RelatedHmm,
I'm creating a table that contains information from two other tables. I'd like to have a constraint on some columns so that thay can only contain information that exists in the other two tables. This is what I got:
create table dbo.KUSE_Bills
(
SERVICE_ID int IDENTITY(1,1) NOT NULL primary key,
BILLDATE datetime NOT NULL,
CNAME varchar(100) NOT NULL,
SNAME varchar(100) NOT NULL,
PRICE money NOT NULL,
UNIT varchar(50) NOT NULL,
NUMBER integer NOT NULL,
BILLSUM money NOT NULL
)
GO
I tried something like this:
create table dbo.KUSE_Bills
(
SERVICE_ID int IDENTITY(1,1) NOT NULL primary key,
BILLDATE datetime NOT NULL,
CNAME varchar(100) NOT NULL,
SNAME varchar(100) NOT NULL,
PRICE money NOT NULL,
UNIT varchar(50) NOT NULL,
NUMBER integer NOT NULL,
BILLSUM money NOT NULL
CONSTRAINT chk_cname CHECK (CNAME NOT IN (
SELECT CNAME FROM KUSE_Customer))
)
GO
But subqueries are not allowed...
So how can I do it?
I have a created a database with nearly 20 tables in it, so I cannot post all of my statements. I am having an issue though with two tables' referential integrity when it comes to foreign key constraints. I want to constrain them to cascade updates from the primary key tables, but I keep getting the error msg 1785
Introducing FOREIGN KEY constraint 'FK__PURCHASE___ITEM___64F971E5' on table 'PURCHASE_ORDER_LINE' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Here is the table with the primary key:
create table MERCHANDISE (
ITEM_ID varchar (8) NOT NULL PRIMARY KEY,
DESCRIPTION CHAR (60),
STANDARD_PRICE MONEY,
[code]...
I have tried making an update trigger but it does not work.