I am trying to create an opportunity report that includes notes from the notes entity. Unfortunatly, when I pull the fields from the notes entity into my reports, it seems there is no way to tie the notes to specific opportunities or accounts that they are associated with and I get all notes under the first item listed on my report. Is there a way to link data sets like Access lets you link tables?
Customers order a product and enter in a source code (sourceCd). This sourceCd is tied to a marketing program. Idea being we can see that 100 customers ordered from this promo, 200 from this catalog, etc etc. The sourceCd that a customer enters is not always accurate so there is a magic process that adjusts this OrigSourceCd into a final SourceCd, that may or may not be the same.
I am trying to generate a result set of customer count by sales program based on both the original and final source code. Problem is, I have to do each query separately because in one, I have to join SourceCdKey to SourceCdKey to get the program associated with that SourceCd and in the other i have to join OrigSourceCdKey to SourceCdKey to get the program associated with the original sourceCd. There are some programs is one results set that are not in the other, and vice versa.
I'm trying to generate a list of that shows customer counts before and after for each program, some which may be null for one, but have counts for the other. I have tries creating 2 separating views and joining them but that doesn't work because it only returns the ones they have in common.
With Item as( Select 1 as ItemId,'ItemName1' as ItemName,100 as position union all Select 2 as ItemId,'ItemName2' as ItemName,200 as position union all Select 3 as ItemId,'ItemName3' as ItemName,300 as position union all Select 4 as ItemId,'ItemName4' as ItemName,400 as position union all Select 5 as ItemId,'ItemName5' as ItemName,500 as position union all Select 6 as ItemId,'ItemName6' as ItemName,600 as position union all Select 7 as ItemId,'ItemName7' as ItemName,700 as position),
Mapping as ( Select 1 as Parent, 2 as child union all Select 1 as Parent, 3 as child union all Select 1 as Parent, 4 as child union all Select 5 as Parent, 6 as child union all Select 5 as Parent, 7 as child )Expected Result:
First of all, we are using SQL Server 2005 with a SQL Mobile subscriber and we are attempting to use Data Partitions on our current database schema which contains associative tables for many-to-many relationships.
We have two tables, a User table and an Audit table. A user can be assigned more than one Audit. An Audit can be assigned to more than one User. So an AuditUser associative table exists. If data partitions are used based on User, then any Audits that are assigned to one or more users should be copied to the proper partition for each User (the msmerge_current_partition_mappings table with the proper partition_id values).
In order to insert records with such a schema, the following steps occur in order:
Insert new row into Audit table with new rowguidInsert entry into AuditUser table associating the auditguid with every userguid that is assigned this audit.
Merge replication triggers are fired on insert of the Audit row and another one for the insert of the AuditUser row.
When the Audit row is inserted, the replication trigger follows the following logic:
Inserts a copy of that row into the msmerge_contents table. Evaluates the row to determine which partition(s) this row should be copied to as well (msmerge_current_partition_mappings table). To do this, it checks to see if the AuditGuid is referenced in one or more AuditUser rows. Since we haven€™t inserted the AuditUser row at this point, the trigger€™s logic doesn€™t find a partition to copy this row to.
When the AuditUser row is inserted, the replication trigger performs the same logic as with the Audit row, it:
Inserts a copy of that row into the msmerge_contents table.Evaluates the row to determine which partition(s) this row should be copied to as well (msmerge_current_partition_mappings table). Since the row meets the criteria for one or more partitions, it is copied to the msmerge_current_partition_mappings table for each partition that exists.
When replication occurs, we see only the AuditUser rows copied down to our device, and not the corresponding Audit rows. Now that we understand the triggers, it is plain to see why. If the AuditUser row could be inserted first, then the trigger on the Audit row would copy that row into the proper partitions and all would work well. However, the Audit row must be inserted first, so that foreign key relationship constraints are preserved.
It seems that the Update trigger on the AuditUser row actually walks the relationships and copies any related child rows to the msmerge_current_partition_mappings table.
I don't know if this is the correct forum or if it would perhaps be the server setup & upgrade, however, I'll ask here first. I am going through the SQL 2005 data mining tutorials and have encountered an error that states:
Query (1,6) The '[System].[Microsoft].[AnalysisServices].[System].[DataMining].[AssociationRules].[GetStatistics]' function does not exist.
After looking around for a bit it appeared that this function is generally used in a stored procedure run from the server. Is this correct? What do I need to do to either correctly import the function into the project or to make sure that it is installed on the server?
We are running the Ent Ed. now, however, we just upgraded to this after I had already started on this project. Could that be a problem?
I have a table which maps two related IDs. That table has 3 columns: ID, BHID & EPID. I need to find all of the BHIDs where the EPID is unique. It seems easy enough, but I keep going in circles..
USE [CGB] GO /****** Object: Table [dbo].[ePID_BHID] Script Date: 04/15/2015 15:48:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
Hello, Why do I receive this error "Cannot add an entity that already exists." I'm trying to add three data at one call using LINQ, after getting the error above then refreshing the page, only one data is inserted.... Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim db As personalDataContext = New personalDataContext Dim p As New personal For i = 0 To 3 - 1 p.name = "Mick" p.number = "01213" p.picture = "image/image.jpg" db.personals.InsertOnSubmit(p) db.SubmitChanges() Next End Sub... cheers,imperialx
Database newbie question: My sample database (T-SQL syntax): CREATE DATABASE sample GO USE sample CREATE TABLE customers (CustomerId int IDENTITY(1,1) PRIMARY KEY CLUSTERED,name varchar(50),address varchar(50)) CREATE TABLE invoices (InvoiceId int IDENTITY(1,1) PRIMARY KEY CLUSTERED,date datetime,CustomerId int REFERENCES customers(CustomerId)) INSERT customers (name,address) VALUES ('First Company Ltd.','New York') INSERT customers (name,address) VALUES ('Second Company Ltd.','Washington') INSERT invoices (date,CustomerId) VALUES ('Jan 1, 2004',1) INSERT invoices (date,CustomerId) VALUES ('Jan 2, 2004',1) INSERT invoices (date,CustomerId) VALUES ('Jan 2, 2004',2) SELECT * FROM customers GO CustomerId name address 1 First Company Ltd. New York 2 Second Company Ltd. Washington SELECT i.date,c.name,c.address FROM invoices AS i INNER JOIN customers AS c ON i.CustomerId=c.CustomerId GO date name address 2004-01-01 00:00:00.000 First Company Ltd. New York 2004-01-02 00:00:00.000 First Company Ltd. New York 2004-01-02 00:00:00.000 Second Company Ltd. Washington UPDATE customers SET address='Boston' WHERE name='First Company Ltd.' GO INSERT invoices (date,CustomerId) VALUES ('Jan 3, 2004',1) SELECT * FROM customers GO CustomerId name address 1 First Company Ltd. Boston 2 Second Company Ltd. Washington SELECT i.date,c.name,c.address FROM invoices AS i INNER JOIN customers AS c ON i.CustomerId=c.CustomerId GO date name address 2004-01-01 00:00:00.000 First Company Ltd. Boston 2004-01-02 00:00:00.000 First Company Ltd. Boston 2004-01-02 00:00:00.000 Second Company Ltd. Washington 2004-01-03 00:00:00.000 Second Company Ltd. Boston Is it possible in any of the RDBMS's to make this last query return the following result set? 2004-01-01 00:00:00.000 First Company Ltd. New York 2004-01-02 00:00:00.000 First Company Ltd. New York 2004-01-02 00:00:00.000 Second Company Ltd. Washington 2004-01-03 00:00:00.000 First Company Ltd. Boston
I have an MDS entity which will be managed in excel. I do not want the user to enter the Code and Name so I have configured code to auto generate and name to default to another attribute value. I have then hidden code and name in excel.
When I use excel and enter the attribute and publish, the name column is blank in the MDS table.Am I missing something here with the defaulting?The Action is defined as 'Name Defaults to Country'. Where Country is the attribute I am populating.
can anyone help me with my diagram pleasee..i have been banging my head on it for weeks. i have 6 entities Student, Course, Module, Attendance, and Result
Polato Student Record System "The Polato university administration has decided to computerise their student record system. The system must be able to automatically print a new ID card for students on their admission and after completion of each academic year. The card should have the student’s name, number of years and the message welcome to 1st, 2nd or final year. In a bid to curb the rate of incompletion, the system should also be able to record attendance of students to any class. The system should be able to automatically send out a warning to any student that misses any two lectures for the first time. (Of course the student should come up with a very good reason for missing the lectures or else he/she will be removed from the module). Missing three or more lectures leads to automatic withdrawal from the module involved. A student will receive notice from the system, right after missing the second lecture. The system also must be able to calculate each student’s results for that semester only showing details of all modules taken. You should use the simple formula; the total of all the modules taken, divided by the number of modules"
I have VS 2005 and SQL Server 2005 Express installed and I created 4 tables and setup the primary and foreign keys and can view the individual foreign key relationships by right clicking on the foreign key.
Is there a way to view a table relationship diagram (fields with primary and foreign keys), such as Access provides. Does VS 2005 or SQL Server 2005 have that capability?
I have a table called Posts which contains an attribute Hospital ID. This is a foreign Key to the Hospitals table, which contains two fields : Name & Region.
In my report model, I would like the Hospital ID to be replaced with just the Hospital name. I have set the Hospitals entity to IsLookup=True and the IdentifyingAttribute to Hospital ID. However, in the report builder Hospital ID is still being displayed with no sign of the hospital name.
Can anyone point out where I'm going wrong? I have tried setting the Name field in Hospitals to Role or Merge as I read that using 'name' might cause some problems but that made no difference
I would like to ask you sthg as far as the e-r model is concerned! I haven't started of course to use SQL yet,cause i have to make the e-r model first.
I have a a question to make :
For example i have to keep data for a Personal Computer (PC) So i have the entity : COMPUTER COMPUTER has many characteristics/fields like the primary key which is going to be PC_ID and it's unique for every computer,NAME_OF_PC the name and also TYPE_OF_MOTHERBOARD, TYPE_OF_RAM, TYPE_OF_HARD_DISK etc.
I have to keep for the TYPE_OF_MOTHERBOARD the manufacturer, the motherboard's code etc.
Shall i make an entity for the motherboard or for the HARD_DISK or for the RAM? Cause i want next to change these fields of every COMPUTER if it's n eeded! e.g. i have the PC_ID = 12 and i want to change the hard disk cause it's not working!(shall i keep an entity of the hard disk or not?)
student - sno (pk), sname subjects - subno(pk), subname marks - sno(fk), subno(fk), marks
While creating the report model:
1.Created the data source - no problem 2. created the data source view - no problem added all 3 tables here. 3. created the report model - only subject and student show up here. Where is the marks table?
I'm trying to find out whether it is possible to integrate an external entity into MS SQL Server in following way:
the entity defines certain set of tables when user performs a query on one of these tables, the query is sent to the entity for processing and results are returned back to SQL server (and to client)
I thought integration services could be used for this task, but I'm quite unfamiliar with MS SQL server, so I don't know whether it would be a feasible solution...
Working in a project using Entity Framework (Code First)...
Until now the project has been connected to a (generated) SQL Server Compact 4.0 database,
but now we want to connect to a SQL Server (at least 2008R2 since we will use FILESTREAM...)
Our problem right now is the possibility to enter Infinity values into REAL columns in the DB...
It works in the SQL Server Compact but we have not been able to get it to work in 2008R2 or 2014
The insertion of Infinity values is constructed by the Entity Framework (from using float.PositiveInfinity in C#) automatically so I mainly wonder if it at all is possible in a "real" database. Maybe there are some configurations possible to get it to work?
Is it possible to set an entity is update but can't add/delete? I want to control adding/deleting members in SSIS from another source system, but only allow users to change certain attributes.
I am using CRM 3.0 and have a requirement to list all the the tables, attributes names and display names and datatypes. Is there any easy way to export this information from the CRM tool or prepare a SQL query that will list the information?
My problem seems simple but I can't how to do it with MDS... or even if it's possible !
I've got 2 entities "Agent" and "Function".Â
"Agent" has a code, a name, and an attribute called "function_code" which refers to "Function"'s code
"Function" as a code and a name. Name is the description of the function.Â
I'd like to see in a single row :Â
"Code", "Name", "Funcion_code" and "Name" (the last one from the entity "Function").Â
In SQL it will something likeÂ
Select a.code, a.name, a.function_code, f.name from agent a, function f where a.function_code = f.code
I've tried with explicit hierarchy, derived hierarchy, consolidated members...Â
I was able to have an entity with all those attributes but I can choose the attribute I want. My goal is that, according to "Function_code" in Agent, I get the "Name" of the function...Â
I have a datagridview bound to a table that is part of an Entity Framework model. A user can edit data in the datagridview and save the changes back to SQL. But, there is a stored procedure that can also change the data, in SQL, not in the datagridview. When I try to "refresh" the datagridview the linq query always returned the older cached data. Here's the code that I have tried using to force EF to pull retrieve new data:
// now refresh the maintenance datagridview data source using (var context = new spdwEntities()) { var maintData = from o in spdwContext.MR_EquipmentCheck where o.ProdDate == editDate orderby o.Caster, o.Strand select o; mnt_DGV.DataSource = maintData; }
When I debug, I can see that the SQL table has the updated data in it, but when this snippet of code runs, maintData has the old data in it.
I was able to view the MDS entity through web interface. But now when i click on web interface I am not able to view it from internet explorer and Mozilla firefox. However when I try it from a different laptop with my login I am able to see the MDS entities. I tried reinstalling the Microsoft silver light but am still facing the same issue.I also have all the required access for viewing the entities..Is there any settings that I have to do for the explorer so that I will be able to view the entitites .
in simple words it's about versioning at record level.ExampleTableEmployee - EmployeeId, EmployeeName,EmployeeAddress, DepartmentId,TableDesignationMap - EmployeeId, DesignationId, EffectiveDate,validityTableDepartment - DepartmentId, DepartmentTableDesignation - DesignationId, designationVia Modify-Employee-Details screen following are editableEmoyeeNameEmployeeAddressDepartmentDesignationthis screen should allow user to navigate through changes history.Example :Version -1EmoyeeName John SmithEmployeeAddress 60 NewYorkDepartment AccountsDesignation AccountantVersion -2EmoyeeName John SmithEmployeeAddress 60 NewYorkDepartment AccountsDesignation Chief Accountant - changedVersion -3EmoyeeName John SmithEmployeeAddress 60 NewYorkDepartment Sales - changedDesignation Marketing Manager - changedQuestion :What is the best proposed database design for maintaining historyrecords bound with version and retrieval techniqueBest RegardsSasanka
Is there any way to set a default date for a member in MDS. My requirement is that business user can enter either current date or future date but not past date.
I used "MDSModelDeploy deployclone" do deploy a package with EntityA, EntityB and EntityC to a production environment. I then deleted EntityB from my dev environment and used "MDSModelDeploy deployupdate" do update the model in prodution. After the deployUpdate, EntityB still exists in production. Is there something special I need to do (option of MDSModelDeploy?) in order for the deploy to delete an entity during a migration?
While building Report Model Solution in Business Intelligent Studio i am getting following errror- More than one item in the Entity 'table' has the name 'columns'. Item names must be unique among immediate siblings.
Please advice how to resolve this one. Thanks Ashwin.
I have an entity (A), in which I use domain based attribute. The second entity (B) has several attributes. My problem is that, I would like to filter the first entity (A) based on an attribute that belongs to the second entity. The only way I can filter it (in MDS Excel add-in or Explorer) is by using Code or Name from the second entity.
I have in mind a couple of solutions, but they require some coding with xml saved query from Excel.
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error: [Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
---------------------------------------------------------------------- I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
