Report Model - How To Create A Unique Logical Primary Key In A Named Query
Apr 7, 2008
Hi,
Te following situtation is :
ReportModel is created ,there is only a named query in DSV ,it has a few tables in it(The relationship are inner joins and outer joins).
The question is how could I create a unique logical primary key to identify each unique row in the named query dataset, and also you cannt generate a model unless the named query has a logical primary key . how can I solve this problem,any help?
View 2 Replies
ADVERTISEMENT
Dec 5, 2006
Hi guys,
I added a Named Query on my existing data source view and I recreate the report model, however, I am unable to see the Named Query on the Report Model, I can see only the tables.
Does anybody has any idea?
Sincerely,
View 1 Replies
View Related
Mar 30, 2008
Hi,
Consider the following situtation.
ReportModel is created using named queries. It has multiple entities.
Report Model is deployed to the report server and datasource is set dynamically at runtime(programmatically). When user runs the report (which is created using report model above), I need to change the where clause of one of the entities in ReportModel. How can i acccomplish this programmatically?
View 4 Replies
View Related
Sep 6, 2007
I've created a linked server with a pretty basic Excel spreadsheet, and used this command to create a linked server to it:
sp_addlinkedserver ''XL_SPS_1', 'Excel', 'Microsoft.Jet.OLEDB.4.0', 'c:MyExcel.xls', null, 'Excel 8.0'
I want to use this as the data from which to build a report model. As linked servers don't show up in the Data Source View wizard, I created a view in SQL Server:
create view MyExcel
as
select * from XL_SPS_1...Sheet1$
Okay, great, now the view shows up in the DSV wizard and I can create the data source view. However, when I create a new report model based on this data source view, the Report Model Wizard tells me at "Create entities for all tables" that I've got an error when it processes dbo_MyExcel that "Table does not have a primary key."
I assume this is where the identifying attributes for the entities in the report model are taken from, so I really can't go further. Does anyone have an idea as to how to add a primary key to a linked server (Excel) in SQL 2005? Can this be done? Other than importing spreadsheet data to a SQL table, how can I get around this?
Thanks,
--Stan
View 3 Replies
View Related
May 23, 2007
I have a datasource view DSV1. It points to a datasource DS1 that is considered the "primary".
I have created a Report Model that uses DSV1 (and thus uses DS1)
I created a new datasource, DS2 that I would like to use instead of DS1. (I can't just modify DS1 because if I modify it, it will overwrite it when we go to our Production environment and break that datasource)
So, I can go into DSV1 and change all the references from DS1 to DS2.
But that's where the problem lies.
When I try to build, I get the following error:
"The Table property of the Entity "E1" refers to the Table "dbo_View", which is not in the primary data source."
Somehow, the entity is tied to the "primary" datasource. When I change it back to DS1, everything works fine. Any thoughts? What can I do?
View 1 Replies
View Related
Jul 14, 2007
Hello,
What we'd like to do is programmatically generate and maintain a report model based on how the individual install is configured. I've found some documentation that makes me believe this is possible, but I'm hoping someone can nudge me in the right direction. The ReportingServices2005 class has several model related methods (CreateModel) and I found the .xsd for the semantic model XML file
(http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling/SemanticModeling.xsd),
but no real documentation on how to put together a Report Model document from scratch. Looking at the files generated from BIDS is somewhat overwhelming and I have no idea where I'd pull most of the information.
Finally, I want to create Report Model(.smdl) file without Report Model Designer.
I want to Implement CreateModel Method to create new model, any source code sample..??
Any help that can be provided would be greatly appreciated. Thanks!
Sandip.
View 4 Replies
View Related
May 5, 2008
Hello!
I've created a data source that uses the OLE DB Provider for Teradata to connect to an existing Teradata database. Works fine.
I then use this data source within my project to create a Data Source View. Again, all works well.
However, the final step - creating a Report Model, bombs out in the wizard at the Completing the Wizard step with the following error: "[NCR][Teradata Database] Syntax error, expected something like a name between the 'SET' keyword and the 'TRANSACTION' keyword." (I elected for the wizard to only create entities for all tables, and create attributes).
If I try to create a Model manually, I get the following error when trying to build: "The Entity '<EntityName>' does not have any IdentifyingAttributes. Entity must have at least one IdentifyingAttribute."
Still fairly new to SSRS, so I'm hoping the issue is just a loose nut behind the keyboard Does anyone know what I'm doing wrong in trying to set this up?
Thanks in advance!
View 1 Replies
View Related
Jan 21, 2008
I'm trying to create a few report models in Visual Studio to demonstrate the Report Builder tool and I'm encountering an issue when I try to generate a Report Model.
I get the error message "Specified Method Is Not Supported" when I click Finish on the Report Model wizard. The error detail is:
===================================
Specified method is not supported. (Microsoft Visual Studio)
------------------------------
Program Location:
at Microsoft.ReportingServices.ModelDesigner.Wizards.ChooseSemanticModelName.CreateSqlDsvStatisticsProvider(IDbConnection connection)
at Microsoft.ReportingServices.ModelDesigner.Wizards.ChooseSemanticModelName.BuildModel()
Steps to reproduce:
1. Create a new Report Model project in Visual Studio 2005 against SQL Server 2005
2. Create a new data source. The data source was tested and works correctly
3. Create a new data source view. The data source view was also tested (clicked on Explore Data) and I was able to look at the data in the view
4. Right-click on Report Models and select "Add New Report Model"
5. Click next at the welcome screen
6. Select the data source view created in #3
7. Leave the selected options for report model generation rules for creating the data source view
(Create Entities for all Tables, Create Count Aggregates, Create Attributes, Create Attributes for auto-increment columns, Create date variations, Create numeric aggregates, Create date aggregates, Create roles were all selected)
8. Click next
9. Select Update Model Statistics Before Generating (default option)
10. Click next
11. Click Run
Once I click run it processes for approx 3 seconds and then returns the error above.
If anyone could help me I'd appreciate it. Microsoft hasn't published any details on the namespace and I'm not sure why I'd be getting the error I am.
Thanks in advane,
Maurice
View 7 Replies
View Related
Sep 18, 2007
Hi ,
Please tell me how to create Data Source and Data Source View in Report Model Project for OLAP Cube.Whats the provider to use while creating Data Source in Report Model Project to make connection to OLAP Cube.
Thanks
Raghava
View 3 Replies
View Related
Jul 16, 2007
Hello,
I'm looking for Programmatically genration of Report Model .smdl file.
Please guide me or suggest some links so that ui can refer same.
Is there any smdl genrator or any classes for the same...?
Thanks in advance.
Sandip
View 3 Replies
View Related
Apr 25, 2008
We have the following scenario,
We have our Production server having database on which Few DTS packages execute every night. Most of them have Bulk Insert stored procedures running.
SO we have to set Recovery Model of the database to simple for that period of time, otherwise it will blow up our logs.
Is there any way we can set up log shipping between our production and standby server, but pause it for some time, set recovery model of primary db to simple, execute DTS Bulk Insert Jobs, Bring it Back to Full recovery Model AND finally bring back Log SHipping.
It it possible, if yes how can we achieve this.
If not what could be another DR solution in this scenario.
Thanks Much
Tejinder
View 6 Replies
View Related
Mar 28, 2007
Hi,
I have found that in the autogenerated model attributes are missing for those fields that have relations to other tables. At first, it may look reasonable since a user can still get down to the field's value through the relation/related table. However, if the relation's key fields is the only thing the user wants to display, then going down to the related table is an overkill.
I can add an attribute manually and bind it to the key field(s). Is there an option in the autogeneration process to do it automatically? The only post I've found so far suggests to do everything manually (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1152575&SiteID=1). Is this the only way?
Thank you,
Leonid
View 3 Replies
View Related
May 31, 2007
In tables where reporting or historical information needs to be maintained I find that logical deletion of rows is better than actually deleting the row and any FK relationships. However when one of the columns in the table must be unique this introduces a problem because uniqueness only matters for "active" rows. I'm hoping someone can provide some advice on how to best handle this situation.
Here is an example to work from. Suppose we have a table that contains job positions. The table contains an IDENTITY column for the PK (to avoid duplicating position names), a Name column that must be unique and an IsActive column that is set to 0 when the position has been "deleted". This setup allows for historical reporting to properly pick up the position information even though it might no longer be used in a company. You can substitute your own scenarios here as there are quite a few.
Now if the Name column is marked as a unique column then the DB will enforce integrity which is what we want. However we will want to be able to "delete" a position but later add a new position with the same name. One could argue that we should just "undelete" the original position but that is not often a good idea so assume "undelete" is not an option. Given the unique constraint a new position could not be created if an existing position (active or otherwise) already existed with the same name. We could set up the unique constraint to include the Name and IsActive columns but now we are prevented from ever deleting two positions with the same name.
My initial thought is to create a new UniqueName column that is unique and remove the uniqueness constraint on the Name column. The UniqueName column is set (via a trigger) to the Name column when the position is active. When the position is deleted the trigger changes the UniqueName to include a timestamp or something to ensure uniqueness. This is handled by the system and not exposed to clients.
Any advice or alternatives on this approach (preferably with advantages and disadvantages)? Thanks.
Michael Taylor - 5/31/07
http://p3net.mvps.org
View 4 Replies
View Related
Feb 13, 2008
I've been through a number of tutorials on how to enable row-level security based on a userID, but my problem is more complicated and I do not have sufficient understanding of report models to guess.
My security information is defined in a table within my database. It contains a username and an account mask. An account mask maps to 1 or more account codes contained in the other data tables in my report model. A user may have more than one account mask defined for his account.
I understand the concept of directly mapping the logged-in user to a field containing a matching username. Is it possible to do a two-step mapping, so that based on the user ID I can get the account mask(s) and then evaluate which account codes match the mask(s)?
Or is there a different/better way to set this up? Defining SQL roles/groups is not an option, because of some compatibility issues with external systems.
So far, because my security table has no defined relationship with the data tables, I have not even been able to get it into my report model (Would love any suggestions on that one, too.)
I'd appreciate any ideas or suggestions - even if only something to investigate.
Thanks,
Sarah
View 1 Replies
View Related
Mar 12, 2007
I have a stored procedure that takes a date range and returns all the sales in that date range. I'm trying to create the report model for ad-hoc reporting. When I go to create the dataset view, it only lets me select tables or views.... how do I get around this?
View 2 Replies
View Related
Dec 5, 2005
Running 2005 Beta 3 Refresh. When I first deploy, it works fine. Subsequent deployments yield the following error:
View 9 Replies
View Related
Aug 20, 2007
I'm having a heck of a time with named queries. At this point I just want a proof of concept in my DSV. I have a bunch of tables added to the DSV, and I would like to add a named query to it so the user can pick from the name query in report builder. What has to be done to make it appear in the report builder?
View 1 Replies
View Related
Jan 31, 2008
Hi, it's my first time for using report model as the datasource of reporting service. But I don't know how to build the query string to get data from the report model.
Can anyone help me?
Thanks
View 3 Replies
View Related
Aug 1, 2006
How to create a stored procedure to query the data mining model?
Where can I find some sample code?
Thanks.
Joe.
View 3 Replies
View Related
May 14, 2012
I have a table with plant types and plant names. Certain plants are grouped on a custom field, currently called Field. I am trying to create a query that will give me a result set containing the primary order on Type, but need items with the same 'Field' value grouped by each other.For example, the following shows a standard query result with "order by Type", ie select * from plants order by Type
Code:
ID Type Name Field
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
5 Type5Name5(group2) -group2
6 Type6Name6(group6)
But I want it to look like this, with fields of the same value located next to each other in the result set (but still initially ordered by Type)
Code:
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
5 Type5Name5(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
6 Type6Name6(group6)
View 7 Replies
View Related
Oct 13, 2000
In 6.5 , I was NOT able to use mapped network drive for new database
device if the MSSQLSERVER service is running under local system account,
but if I changed it to using a domain a/c which has the authority to
create files on mapped drive, I was able to do it.
But if I tried it in 7.0, it never work and got msg:
Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name 'e:est2.mdf' may be
incorrect.
Server: Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check
previous errors.
Is it something that's normal for 6.5 and 7.0 ????
i.e. we can only create databases on LOCAL drives ?
I am just thinking of in case we run of space locally, we can temporarily
make use of remote mapped network drive. (say at time of upgrading)
Any one has this experience ?
View 2 Replies
View Related
Oct 20, 2006
Hello
for MS SQL 2000 I want to CREATE a Table as follow :
CREATE TABLE [dbo].[Local] (
[id_Local] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar] (100) NOT NULL,
[Info] [nvarchar] (100) NULL
)
id_Local is PrimaryKey autoincrement
but Name must be UNIQUE (not 2 times the same name)
how can script it ?
thank you
View 2 Replies
View Related
Aug 27, 2007
Hi,
Can anyone tell what are the specific scenarios where Unique key is recommened over primary key ?
While designing a database table in what all cases we should think about going for Unique key rather than a primary key.
Regards,
Amit
View 6 Replies
View Related
Jan 19, 2008
hello all sorry m asking Database Question .. m talking about SQL SERVER 2005 but i have bit confusion... what difference between a
primary key and a unique key? if m not wrong then primary key doesn't allow NULLs, but
unique key allows NULLs but in many searches comes that primary key doesn't allow NULLs, but
unique key allows one NULL only. what this means 'one NULL only'?? according to my practical we can give more then 1 nulls... plz clear this point???
View 1 Replies
View Related
Aug 17, 2007
Is there any way to auto generate the unique primary key when inserting data in MS SQL?
there is a way to create the increasing integer as primary key, but is there any ways other than that?
thank you
View 5 Replies
View Related
Jan 23, 2008
For our database application we get our primary keys by calling a stored procedure that pass's in the table name and outputs the next primary key number assigned to that table.
ALTER procedure [dbo].[sp_getNextKey]
@TableName char(100),
@NextKey T_ID output
as
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
begin transaction
select @NextKey = NextKeyValue from T_KeyGenerator where TableName like @TableName
update T_KeyGenerator set NextKeyValue = NextKeyValue + 1 where TableName like @TableName
commit transaction
I then take that primary key, and other data, and insert that into the desired table.
The Problem I am having is that my user's keep getting "cannot insert duplicate key in table".
So I assume that I do not have the transaction set right, or missing something.
I need it to lock the row in the t_keyGenerator table so that no other users can view that row until I update it with the new value and commit the transaction.
Any help would be greatly appreciated
View 8 Replies
View Related
Nov 9, 2006
I wrote this stored procedure and it works fine, it seems. The questions I have are as follows:
(1) What is the difference between PRIMARY KEY and UNIQUE. They seem to pertain to the same behavior. When I used only UNIQUE as qualifier I did not see that the column was marked as primary in the SQL Management Studio.
What I need is for a column to be unique in the sense that it would not allow duplicate values and it must have an INDEX on it. I need it to be descending.
(2) Did I do it right or there are aspects in here I do not quite see?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[CreateTableDailyClose]
@symbol varchar (10) = null
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL VARCHAR(500)
SET @SQL = 'CREATE TABLE dbo.dailyCl_' + @symbol + ' (
dateTimed DateTime NOT NULL PRIMARY KEY,
opened float NULL,
high float NULL,
low float NULL,
closed float NULL,
volume int NULL,
adjClosed float NULL
)'
EXEC sp_sqlexec @Sql
SET @SQL = 'CREATE UNIQUE INDEX dateTimed ON dbo.dailyCl_' +
@symbol + ' (dateTimed DESC) '
EXEC sp_sqlexec @Sql
END
Thanks.
View 16 Replies
View Related
Feb 23, 2006
my table :
CREATE TABLE [dbo].[users] (
[ID] [int] NOT NULL ,
[A1] [nvarchar] (100) NULL ,
[A2] [nvarchar] (100) NULL ,
[A3] [nvarchar] (100) NULL
) ON [PRIMARY]
i must keep ID columns as primary key
ALTER TABLE [dbo].[users] WITH NOCHECK ADD
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
but now A1+A2 must be unique
how can i do it ?
thank you
View 5 Replies
View Related
Oct 8, 2007
Question: I have a test table like this
CREATE TABLE [dbo].[Test](
[name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[addr] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[name] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
But when I tried to input my data like
'abc', '123 abc'
'abc ','123 abc'
SQL server won't recognize 'abc' and 'abc ' is a different value if the last character is a space. Is there a way to make it as a different value? I tried to drop the primary and input the data. When I ran a group by the name column, 'abc' show 2 instead of 1. Seems SQL server is trying to ignore the space at the end too.
I also noticed unique index have the same problem too. Please help.
View 5 Replies
View Related
Oct 13, 2015
What is the need of Primary Key if everything can be achieved by Unique Key? Everything that primary key does, Unique key can also fill those things.Why primary key is required?
View 4 Replies
View Related
May 27, 2002
Hi,
I am making a program in Visual Basic .NET with SQL Server 2000.
I have a table "MyTable" with a primary key named "Id". The primary key is 'Create Unique' checked and 'Index' selected. When I insert all the fields required, except "Id" of course, I need the new record's "Id" in my VisualBasic program, but I don't know how...
I must do one of them, but don't know how either of them:
-Create a trigger on insertion that will send to the user that sended the insert command the "Id" of the record just created.
or
-get the command in Visual Basic that will send the Insert command with a return field ("Id")
Thanks in advance,
Sebastien Anselmo
View 6 Replies
View Related
Jan 25, 2006
I have a deal table, each of these investments must be unique. I created a int pk : idDeal. Does that make sense or should i just use the deal colm being it has a unique constraint,
Reguarding indexes, should i make the auto # colm my pk and make that the clustered index? and put another index on the Deal Colmn? Any suggestions welcomed
Thank you
View 4 Replies
View Related
Mar 21, 2008
Hi
I have a strange requirement in ETL operation. My Source contains only the details table data. Out of it, I have to load the master table and refer the Master table primary key ID to load Details table. I can easily load the Master Table with aggregate transformation. But problem is how to look up the Master Table Primary Key ID to load the Details table, as the master does not contains any unique key to lookup. This may seems to be strange but this is my requirement. You can refer the Source and Destination data model as below. They may give you clear picture. Can you guys help me out on this?
My Source Table
--------------------------
Citye Type
City Name
Source
New York
Via City1
Pittsburg
Destination City
Chicago
Source
New York
Via City1
Philadelphia
Destination City
Chicago
My Destination
------------------------
Master Table
ID
1
2
Detail Table
Master Table ID
Citye Type
City Name
1
Source
New York
1
Via City1
Pittsburg
1
Destination City
Chicago
2
Source
New York
2
Via City1
Philadelphia
2
Destination City
Chicago
Thanks
View 11 Replies
View Related
