Hi friendsI have one Table called tblCategory.I have three Column CatID, CatName, ParentIDI have many records in this TableCatID CatName ParentID1 Cat1 0 2 Cat2 13 Cat3 14 Cat4 25 Cat5 26 Cat6 07 Cat7 68 Cat8 69 Cat9 710 Cat10 711 Cat11 8 Here I have Main Category which has ParentID 0 [ Cat1 and Cat6 ]I Have Sub categories of Cat1 Which has ParentID 1(CatID 1 of Cat1) [ Cat2 and Cat 3 ]Cat 2 has also sub category with ParentID 2 (CatID 2 of Cat2) [ Cat4 and Cat5]I want result looks like asCat1 Cat6 ->>>>>ParentID 0- Cat2 -Cat7 ->>>>>ParentID 1(CatID of Root Cat1) and ParentID 6(CatID of Root Cat6)- - Cat4 - -Cat9 ->>>>>PaerntID 2(CatID of Root Cat2) and ParentID 7(CatID of Root Cat7)- - Cat5 - -Cat10 ->>>>>PaerntID 2(CatID of Root Cat2) and ParentID 7(CatID of Root Cat7) - Cat3 -Cat8 -->>>>>ParentID 1(CatID of Root Cat1) and ParentID 6(CatID of Root Cat6)--No record --Cat11 Can anybody give me solution?Thanks
If i add a new table to my database with a stored procedure, using that same sp or another can i also add a 1-M relationship with another table that is currently in the database. Please if you can provide an example.
i want to insert null values into a column which is a foreign key It has relation form other table.Is it possible to insert null to such a field .if so please tell the synatax to insert(i mean the process)
Hello,I'm relative new to sql and databases and the last few weeks I learnedmyself a lot. I'm trying to make a hotel reservation application.I have a database with a table Booking, a table Room, a tableRoomsPerBooking. So a booking contains date/time etc and a fieldRoomsPerBookingID. The table RoomsPerBooking contains number ofpersons, unitprice etc. and a field ID and a field RoomID. The tableRoom contains data like name, notes etc.now i have two questions:First about relations:The table Booking has relationship: PK table RoomsPerBooking - ID <-->FK table Booking - RoomsPerBookingID.The table RoomsPerBookingID has relationship: PK table Room - ID <-->FK table RoomsPerBooking - RoomIDIs this relationset good for my purpose? I think it is, but I am notsure.The second question is:How do I get available rooms per nightI came this far.... what are the "some statements"?CREATE PROCEDURE dbo.GetAvailableRooms(@BeginDate DATETIME,@EndDate DATETIME)ASSELECT Room.*FROM RoomWHERE Room.ID NOT IN (SELECT DISTINCT room.IDFROM Room room JOIN RoomsPerBooking roomsPerBookingON room.ID = roomsPerBooking.RoomID--Some statements--WHERE booking.FromDate <= @EndDateAND booking.ToDate >= @BeginDate)GO
I have defined a relation between 2 tables in "database diagrams" (IDE)but when a fill my tables vith a sqldataadapter i obtaina tables without relations why??how i can reslve this problem?my code is:Dim da As New SqlDataAdapter("SELECT * FROM table1", cn)Dim ds As New DataSetda.FillSchema(ds, SchemaType.Source, "table1")da.Fill(ds, "table1")--> ds.tables(0).relations.count =0 !!!!!!!!????????!!!
HI!I have a ASPNETDB as my login databas. but now i want to connect this databas or the aspnet_user table to my table, how do i?I want to check the username in aspnet_user table and select the same username in my table?I want to write all sql code in a sql file. so i want to know how i can connect to the sql file from my c# code?I hope somebody understand me...
When we have Table1 and Table2, then we link both tables using a third table Table3 that relates n records in Table1 to n records in table2, how do we call Table3? There is a name in dataBase modeling for that, right?
hi, If I want to make one to many relationship, I put the pk of the parent as a fk in the child . like this customer table (cust_id, fname,lname) customer_order table(order_id,cust_id,qty,price)
I do understant this, but what about look up table, can I consider cust_id inthe customer_order table as a lookup field to the customer table?
I have about six different entities that can have zero or more note entities associated with them. The easy way to do this is obviously to have a different "note" table for each of the entities i.e. WorkItemNote, CustomerNote etc.. But I would much rather have a single "note" table since they would all be identical, so I came up with this design:
CREATE TABLE WorkItem ( WorkItemGuid uniqueidentifier PRIMARY KEY DEFAULT (newid()), -- rest of table declaration removed for bravity )
CREATE TABLE Customer ( CustomerGuid uniqueidentifier PRIMARY KEY DEFAULT (newid()), -- rest of table declaration removed for bravity )
CREATE TABLE Note ( NoteId int IDENTITY(1,1) NOT NULL PRIMARY KEY, ReferenceGuid uniqueidentifier NOT NULL, Text ntext NOT NULL, -- rest of table declaration removed for bravity )
This way I can get notes associated with a given entity, either Customer or WorkItem, by just selecting from the Note table with its WorkItemGuid or CustomerGuid.
My question is: Is this the best approach to what I am trying to accomplish?
(ps: Apologies if "many to one" is not the right terminology)
This is the stored procedure which I'm using. Don't get any syntax error, only not the correct output.
create procedure sp_Select_log_By_ProfileId @ProfileId int as select l.LogId, l.LogDescription, l.CreatedOn, l.LogAuthorId from Logs l inner join ProfileLogs pl on pl.LogId = l.LogId where pl.ProfileId = @ProfileId;
hi, I got confused for a moment about creating data structure for UMLcomposition (strong aggregation) relation one-to-many.I used Rose/DataModeler to do so.[Parent] <filled_diamond>-------- [Child]I got P/FK (primary key of my component is foreign key of it's container) inmy child table:Parent: PK Parent_IDChild: P/FK Parent_IDthat way I found out I got relation 1:1, I'm still not sure how to createdata structure realizing compositiona one-to-many.
Hi All, I have a little problem and i hope u will help me. Well, my problem is, i want to make a relation between 2 tables which r not in the same databse. Database D1 >>> Table T1 Database D2 >>> Table T2
So i want something like this: Table T1: ID >>> PK
Table T2: ID >>> PK Table1ID >>> FK from Table T1
so, i will have a relation between the 2 tables. Thanks in Advance,
there is two similar fields (Size) with the same datatype, I want to make a relation in a way if I change the value in (TableX.Size) the same value will be applied to (TableY.Size).
Hi, I am new to sql and is working with sql server managment 2005 +c# 2005.
My application needs to create a blockdiagram sort of thing say if in my database i got a table 'Addition' with 'a', 'b', 'c',and the primary key addition_id, and c is related to a and b as c = a+ b.
there is stored procedure name usp_addition which contains this relation. Each time any insert or update is done this sp is executed and all the values are updated for accordingly. My problem starts in the front end where i need to draw the graphical representation of table addition.
In this graphical representation, I need to draw the labels a, b, c and the arrows from a and b which will connect to c, showing that c has a, b as inputs.
I got the label using dataset and datacolumns but hte problem is how to create the arrows the name of labels (i.e my column names from which the arrow should start and end)
How does I get the information that c as two inputs a, b. I dont need the values since i just want to view the columns in table and which column is input to another column.
Since I need to do this dynamically because my tablename, and the number and name of column would differ does any body knows how to do this.
I usually crate relation with database but not use physical relation * My question is if crate physical relation is best way or not and what advantage and disadvantage of physical relation and if it the best way to make relation
I have 2 w2k servers with different domains and On which SQL2k is running on both the Servers. Both the servers don't have any Trust Relations between them.
My questions is, Is it possible to create Linked Servers between sql servers but their Operating Systems doesn't have Trust Relationships?.
I have two tables. CREATE TABLE Service( SERVId varchar (10) NOT NULL, SERVName varchar (30) NOT NULL, SERVDesc TEXT NULL, PRIMARY KEY(SERVId) )
CREATE TABLE New( SERVId varchar (10) NOT NULL, NDId varchar (10) NOT NULL, NDName varchar (30) NOT NULL, NDDesc text NULL, PRIMARY KEY(SERVId,NDId) FOREIGN KEY(SERVId) REFERENCES Service(SERVId) )
Msg 547, Level 16, State 2 DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__Newdev__SERVId__4939E6D2'. The conflict occurred in database 'test', table 'New', column 'SERVId' Command has been aborted.
The same problem occurs even if the second table i.e. new is declared as follows, i.e created with only one primary key: CREATE TABLE New( SERVId varchar (10) NOT NULL, NDId varchar (10) NOT NULL, NDName varchar (30) NOT NULL, NDDesc text NULL, PRIMARY KEY(NDId) FOREIGN KEY(SERVId) REFERENCES Service(SERVId) )
what might be the problem? But the same relation works on MS Access. Is there anyother way of declaring this kind of relationship i.e. primary and foreign key?Can anyone pl. help me in solving this problem of foreign key relation.
Is there any way to find the dependency between two datasets in ssrs report. I want to delete one of dataset but need to find if I delete that it will work or any relation with the other dataset.
Hi Group,(I am just starting with SQL Server 2005.)On SQL Server 7 I used often the nice relation schema, where I used todraw out the whole database, especially the Foreign Keys constraints.I found these relational schemas very handy to study an old database Ibuild a few years earlier that needs some updating.I tried to find such an utility in SQL Server 2005, but cannot find it.I did found the FK-constraints, but it is just an popup where I candefine them.The overview such a visual schema gave was really great and I miss it.Question: Is it gone in SQL Sevrer 2005, or do I just not know where tolook? If the latter, please guide me. :)Thanks for your time.Regards,Erwin Moller
Hi, I have heard alot of people talk about using C# for scripting withing SSIS and the answer to that question is no apparently. You can only use VBA but there is no extensive or in my respect any documentation as reference for using VBA in SSIS. I am new to VBA because i used to use jscipt and vbscript in early version of sql server. But in order to learn using VBA, I need to learn it in which there seems to be no reference in relation to SSIS. All the VBA references you find are in relation to MS Office. Is there anything i am missing or can someone show me where to go as my reference for VBA in SSIS. Its bad enough to change the scripting language but worse not to provide any reference materials for uses to get up to speed.
What for a solution is "the best" for diamon relation? Abort one constraint in table 4 and repair with trigger? Or something else?
Description in book online: Cascading Referential Integrity Constraints:
The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree containing no circular references. No table can appear more than once in the list of all cascading referential actions that result from the DELETE or UPDATE. The tree of cascading referential actions must not have more than one path to any given table. Any branch of the tree is terminated when it encounters a table for which NO ACTION has been specified or is the default.
NO ACTION is realy BAD and USELESS solution, when want to delete row in table4 every time ..
Thx a lot
Example:
Use tempdb go
IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id('table4') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE table4 ;
IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id('table2') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE table2 ;
IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id('table3') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE table3 ;
IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id('table1') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE table1 ; go
create table table1 (table1_ID integer not null primary key)
create table table2 (table2_ID integer not null primary key, table1_ID integer not null)
create table table3 (table3_ID integer not null primary key, table1_ID integer not null)
create table table4 (table4_ID integer not null primary key, table2_ID integer not null, table3_ID integer not null) go
alter table table2 add constraint fk_one foreign key (table1_ID) references table1 (table1_ID) on delete cascade on update cascade
alter table table3 add constraint fk_two foreign key (table1_ID) references table1(table1_ID) on delete cascade on update cascade
alter table table4 add constraint fk_three foreign key (table2_ID) references table2(table2_ID) on delete cascade on update cascade
alter table table4 add constraint fk_four foreign key (table3_ID) references table3(table3_ID) on delete cascade on update cascade go
I am developing a small project that converts a dataset to an equivalent structure in SqlCe. The application converts every field, table and constraints to SqlCe, decomposing de dataset structure into Sql sentences.
The application works fine, the tables and constraints are reconstructed without error. but I found some issues: After conversion, the usual test is to connect to SqlCe, using Data->Add New Data Source and generate a dataset from the resulting tables.
I found that some tables throw the following message:
Failed to add relation(s).An item with the same key has already been added.
After that, the dataset returned contains the tables, but not the relations. After some trial I found that Data Source Configuration Wizard (DSCW) has some problem with foreign keys using Primary keys with multiple fields. If I drop that foreign keys, DSCW inferes the dataset without problem.
Interesting note: I am able to convert the dataset to an Sql Server using the same Sql sentences. If I use Data Source Configuration Wizard I have no errors.
Any advise to understand of fix this problem will be aprecciated. Is that a bug from DSCW ? Thanks Fernando
Now say I got two sellers (1 & 2), and only one article (1, offered by seller 1). The delete query should delete seller 2 because he is not offering articles anymore.
This probably is dead simple in SQL, something like DELETE FROM seller WHERE COUNT(article.SellerID=seller.ID)==0 but honestly, my SQL books leave me guessing here, and I found no online ref for cases like this.
--Example Schema posted at end of message:---For reporting purposes, I need to build a single comma delimited string ofvalues from the "many" tableof a database.The easiest way to see what I want is to look at the sample after mysignature.By the way, this is actually a busines problem, not homework! I justcreated a simpleexample using class and persons because everyone is familiar with thatrelationship.I have two tables on the 'one' side of the relationship: PERSON and CLASSThe ENROLLMENT table resolves the many to many relationship between PERSONand CLASS.(I know that a real system would be date effective, etc, but this is just asimple example.that will show my problem). ENROLLMENT has one row for each Class in which aPerson is enrolled.Look at the sample report: I have to "flatten" the join result and listthe class titles in acomma delimited string. I am stuck with this reporting requirement, and Iam NOT going to denormalizethe tables.One way to accomplish the result is to use a cursor to step through the rowsand build a "Classes"string with concatenation. I don't much like this option. I am not writingthe front end code,but I want to make it easy for the developer. Ideally, I would like to givehim a flattened viewso he can just do a simple join and run his report.I believe that what I want cannot be accomplished with ANSI SQL. However,does MS SQL have someextensions that could help me do the job? Failing that, how could I write astored procedure that wouldreturn the personID and the "Classes" string in a format that would bejoinable to the other tables?Thanks,Bill MacLeanP.S. Some people like to see actual database scripts as samples instead ofa textual representation.I have pasted in a script that creates sample tables and populates them.--Sample Tables and Reports:TABLE PERSONPersonID LastNM FirstNM--------- ----------- ---------1 Smith John2 Jones Sara3 Smith LucilleTABLE CLASSClassID ClassNM----------- ------------------10 SQL Server 10120 C++25 Object Oriented Design40 Inorganic Chemistry50 Organic Chemistry80 Early Lit.TABLE ENROLLMENTPersonID ClassID-------- ---------1 102 101 401 803 203 25SAMPLE REPORTPerson ID Name Classes--------- ----------------------- -----------------------------------------------------------------1 Smith, John SQL Server 101, Inorganic Chemistry,Early Lit.2 Jones, Sara SQL Server 1013 Smith, Lucille C++, Object Oriented Design/************************************************** ********SQL Server Script/************************************************** ********/CREATE TABLE [dbo].[CLASS] ([ClassID] [int] NOT NULL ,[ClassNM] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[ENROLLMENT] ([PersonID] [int] NOT NULL ,[ClassID] [int] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[PERSON] ([PersonID] [int] NOT NULL ,[LastNM] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[FirstNM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOALTER TABLE [dbo].[CLASS] WITH NOCHECK ADDCONSTRAINT [PK_CLASS] PRIMARY KEY CLUSTERED([ClassID]) ON [PRIMARY]GOALTER TABLE [dbo].[ENROLLMENT] WITH NOCHECK ADDCONSTRAINT [PK_ENROLLMENT] PRIMARY KEY CLUSTERED([PersonID],[ClassID]) ON [PRIMARY]GOALTER TABLE [dbo].[PERSON] WITH NOCHECK ADDCONSTRAINT [PK_PERSON] PRIMARY KEY CLUSTERED([PersonID]) ON [PRIMARY]GOALTER TABLE [dbo].[ENROLLMENT] ADDCONSTRAINT [FK_ENROLLMENT_CLASS] FOREIGN KEY([ClassID]) REFERENCES [dbo].[CLASS] ([ClassID]),CONSTRAINT [FK_ENROLLMENT_PERSON] FOREIGN KEY([PersonID]) REFERENCES [dbo].[PERSON] ([PersonID])GO-- Insert rwo for each CLASSINSERT INTO CLASS VALUES (10,'SQL Server 101');INSERT INTO CLASS VALUES (20,'C++');INSERT INTO CLASS VALUES (25,'Object Oriented Design');INSERT INTO CLASS VALUES (40,'Inorganic Chemistry');INSERT INTO CLASS VALUES (50,'Organic Chemistry');INSERT INTO CLASS VALUES (80,'Early Lit.');-- Insert row for each PERSONINSERT INTO PERSON VALUES (1, 'Smitn','John');INSERT INTO PERSON VALUES (2, 'Jones','Sara');INSERT INTO PERSON VALUES (3, 'Smith','Lucille');--Insert row for each ENROLLMENTINSERT INTO ENROLLMENT VALUES (1,10);INSERT INTO ENROLLMENT VALUES (1,40);INSERT INTO ENROLLMENT VALUES (1,80);INSERT INTO ENROLLMENT VALUES (2,10);INSERT INTO ENROLLMENT VALUES (3,20);INSERT INTO ENROLLMENT VALUES (3,25);
Hi, There alot of talk about Service Master and DB Master keys and the importance of backing them up seperately. What about the DPAPI keys used to protect the Service Master keys. What procedures to I take to back those up during my database backup. If I understand correctly, if I do a backup on one machine, and then try to restore in a different machine, I won't be able to decrypt data becuase the DPAPI keys don't "match". This is assumming I don't use a password to protect my DB Master keys, I just rely on the service master to protect them.
I kow for a solid comparison between using datareaders and datasets I will have to perform that myself. But for now I will be utilizing datasets... What I am doing is currently utilizing assemblies to create my datasets ahead of time. I will eventually compile them as dlls. I'm just utilizing assemblies during my building/testing fase. My questions is: Is it faster to completely build the datasets and all needed connections inside the assemblies/dlls and fill them? Or to build the datasets and connections as a sub procedure that can be accessed and then fill them as each required set of data is needed? I ask because I will be having many different data connections and so I'm not sure if it's faster to explicitly build/fill almost each and every one and have them compiled at runtime ready to be accessed, or to file them when called from a sub etc...? As I take it, the server should track and monitor which are used the most, and cache them, so as to operate faster. I wonder if it will still do this if the datasets aren't pre-filled?
