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 want to build Parent child relation . i have two aproaches .I would like to know which is the best solution ?
1)1st method:-
Parent table with parent id . Child table with child id and parent id.Foreign key relationship exists between parent and child tables with cascade delete option enabled.
Parent TAble
Id name
1 XYZ
Child table
id name parent id
1 abc 1
2 qwe 1
2)2nd method
table with id and parent ID. Top level element will have null value in table. eg
id name parent id
1 xyz
2 abc 1
3 qwe 2
4 adf 1
Retrieve data using recursive queries supported in SQL Express.
Which is the best solution to store parent child relationship???
I have new client we changed the file retention period on for log ship on SQL 2000. Now the files are not being deleted. I looked at all the sp's but could not finde the one that actually deletes the log file that was copied over. I could just rebuild log ship but this is on a prod box, was trying to figure out what and when the files get deleted, and by what sp
This one is a needle in the haystack. I have a fairly robust Dell server running WIn 2003, 16 gig of memory, SQL SP3a using log shipping failing over to a local server and then back to my location across a WAN, using a vb6 app with about 60 users, a 24X7 shop Mon-Sat. 2 times in the past month the production server has created a corrupt log ship file which gets applied to my failover server and then corrupts the failover DB to both failover servers. Of course it is my 20 gig db and the only time I can recreate the failover is Saturday night. The production DB is not corrupt and is fine. Has anyone ever run into this before. Currently working with MS and Dell, but hoping someone had experience with this !
I have 1 production server and wanto to add 1 more server.
my production server is using sql 2000 stnd edition.
I want to have an indentical database from my production server.
the problem is my second server is using sql 2005 stnd edition.
I have try to use replication but because the my production database didn't have relationship on the database so I can't use transaction replication, I can't use mirroring and log ship too because the sql use the different version.
Hi, I tried many times checking "Save password" in connection manager editor. But everytime I open the editor again and I see the password field blank. I took it as normal, and deployed my IS Package, It couldnt run because of login failed for user 'sa'.
How do I force IS to save password on any ProtectionLevel ?
I have some trouble with shipping my transaction logs to the secondary database be it on
another server or within the same server to another database instance.
Im using SQL Server 2005 workGroup editions with Service Pack 2.
Here are the problems that i encountered.
I do hope someone has bumped into such a problem and willing to help me out.
I tried on 2 separate servers (not domain environment) and also between 2 separate instances (which is supposed to be simple!) on our development server,but was unsuccessful.
Between 2 separate instances on the development server - No error after configuring Log shipping though. The configuring went through and it was a success. Transaction logs was backup every minute and it got copied over to the other folder. But SQL Agent not doing its last job which is supposed to restore to the secondary database on another instance.No errors given out in SQL Agent error log files.Anywhere else im supposed to look to see if there are errors given out? Both instances SQL Agent has the same log on username and password with Administartive rights
So what went wrong?
Between the 2 servers- The transaction logs was backup every 1min on the primary server but it didn't got copied over to to the secondary database. - Error message given was:Error in restoring database to the secondary database.Network path given could not be found. Can't open the AxTest.bak file. (i am very sure i have type the correct network path,even have shared it out and i think the firewall is blocking incoming traffic since unlike our development server, which allows us to access when we use Start>Run and type in the ip address,user name and password,the primary server will only tell me no network path was found) I also believe it's because the SQL Agent on the secondary database server wasn't given permission to access the primary database folder. I've shared out the drive and folder on the secondary server and even have allowed SQL Agent to read,write and modify on both servers. For the primary and secondary SQL Agent, I configure their log on to be the same user account name and password which have administrative rights. So what went wrong?
Isit really true that both servers have to be in domain environment before you can configure log shipping,mirroring and replication?
Hope someone help me out of this predicament.Thank you in advance!
I've a SSRS report with the details in regards to shipping information. Currently in my report the 'Scheduled Ship Date' is set to following Monday, if we miss the 'Requested Delivery Date'. However, I need to change this report to set shipment date('Scheduled Ship Date') to Tuesday and Friday for the shipment having 'Requested Delivery Date' for this week or next week(current week and next week).
For example- Jobs that have shipment date between 3/1/2015 - 3/7/2015 will have 'Scheduled Ship Date' as Tuesday and Friday of current week. If we miss the shipment for this week it should be scheduled for next Tuesday(3/9/2015) and Friday (3/13/2015). I need to follow this for current week(3/1/2015-3/8/2015) and following week(3/9/2015-3/15/2015) and after that all the following weeks will have 'Scheduled Ship Date' as Tuesdays.
Below is the SQL:
USE tempdb; GO DECLARE @Date datetime; SET @Date = GETDATE(); --SET @Date = '2014-07-25'; DECLARE @TEST_DATA TABLE
If I perform a point in time restore on a database that is currently a primary log shipping database, will the rollback be reflected on the secondary servers or are extra steps neccesary to accomplish this?
If I wanted to make a report that shows different fields, one being cost, but I only wanted certain people to see cost based on their AD groups, is there a way to hide this information?
Basically, domain/user1 belongs to AD purchasing group purchasing, he can see item, description and cost, where domain/user2 belongs to sales so he can only see item and description?
Does SQL have a function that return "null" for records which don't exist? Per example in a FK relation ship, that not all records in the first table have a "child" in the second table, so it returns null records.
I've a SSRS report that reports the 'ScheduledShipDate' based on 'Requested Delivery date'. Currently, on my report 'ScheduledShipDate' is set to Monday of each week since we ship every mondays. I need to change the 'ScheduledShipDate' since from now the ship date will be Wednesday, Thursday and Friday of each week. Here is my DDL.
USE tempdb; GO DECLARE @Date datetime; SET @Date = GETDATE(); --SET @Date = '2014-07-25'; DECLARE @TEST_DATA TABLE ( DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
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?
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