DB Design Question. Circular Reference?
Jun 25, 2007
I have a hypothetical question. Suppose you have three database tables. One represents the money
in a cash register. This money can be composed of Dollars or any amount of change. At any
particular time you might have no dollars in the register but only change, or both or vice versa. To
represent this I created three tables. One for Currency, Dollars and Change. My database is
set up so that there is a one to many relationship between the table CashRegister and Dollars. And also a
one to many relationship between CashRegister and change.
Is this alright? Or is this somehow a circular reference?
CashRegister
-----------
CurrencyID(PK)
Dollars --> this table represents dollar amounts
-------
DollarsId(PK)
typeOfCurrnecy
Amount
Change --> this table represents coins
------
ChangeId
Amount
typeOfCurrnecy
MoneyType --> table Indicates the type of currency, change or dollars
---------
typeOfCurrnecy(PK)
View 5 Replies
ADVERTISEMENT
Apr 25, 2008
I have a table like the following
Table=Items
Type / Name
3 / Active Item
3 / Active Item
4 / New Item
4 / New Item
5 / Old Item
5 / Old Item
How do I run a querry to update the Type Colum entrys from 5's to 3's? I have tried the following:
UPDATE Items SET Type = 3 WHERE Type = 5
The above querry passes SQL Querry analyzer, but the querry itself provides the following error: Unable to parse query text.
I think the error is due to the fact that I am using a circular reference to update the same Column that I am testing on.
MS-SQL 2005 SP-2
Any help would be FANTASTIC!
View 4 Replies
View Related
Mar 6, 2014
I'm going around in circles trying to visualise what's happening in this code,
declare @ReturnValue int, @ProcessFlag int
exec @ReturnValue=spGetProcessFlag @ProcessFlag output
select @ReturnValue as ReturnValue, @ProcessFlag as ProcessFlag
I get the feeling that the code runs spGetProcessFlag, and assigns the value to either @ReturnValue or @ProcessFlag.
By the way - next task is to direct SSIS whether to fail package or continue running.
View 1 Replies
View Related
Jun 1, 2015
Create table #tblActvity
(
activityIDvarchar (50),
activityParentIDvarchar(50)
)
Insert into #tblActvity
SElect '1',Null
[Code] ...
--If I pass activityId 3 or 2 or 4 it should return 0 as none of the activity is circular but If I pass 5, 6 or 7 it should return 1 as they have circular reference....
I need a sql qry which will require to find a circular reference in it.....
As in above sample of data ,If I pass activityId 3 or 2 or 4 to qry it should return 0 as none of the activity is circular but If I pass 5, 6 or 7 it should return 1 as they have circular reference....
View 4 Replies
View Related
Oct 11, 2006
Hi There,This is related to a ms access database but since I use the SqlDataSource control I thought I should post here.I have a project that I was working on with this ms access db and using sql controls, everything was working just finesince one day I started getting "Object reference not set to an instance of an object" messages when I try to designa query or retrieve a schema, nothing works at design time anymore but at runtime everything is perfect, its a lotof work for me now to create columns,schemas and everything manually, I've tried reinstalling visualstudio, ado componentsbut nothing seems to fix it, did this ever happen to any of you guys?any tip is really appreciated thanks a lot
View 2 Replies
View Related
Oct 7, 2015
I have a requirement of table partitioning. we have 10 years of data on a table which is 30 billion up rows on 2005 server we are upgrading it to 2014. we have to keep 7 years of data. there is no keys on table or date column. since its a huge amount of data and many users its slow down the process speed. we are thinking to do partition on 7 years for Quarterly based. but as i said there is no date column on table we have to use reference table to get date. is there a way i can do the partitioning with out adding date column on table? also does partition will make query faster?
I have think three ways to do it.
1. leave as it is.
2. 7 years partition on one server
3. 3 years partition on server1 and 4 years partition on server2 (for 4 years is snapshot better?)
View 3 Replies
View Related
Jun 22, 2006
In visual studio 2005, I create a new Integration Services Project. It tries to create the first package by default "Package.dtsx". The "Package.dtsx[Design]" tab displays
Microsoft Visual Studio is unable to load this document
Object reference not set to an instance of an object
I try to create new SSIS package or edit an existing one (from tutorial), I get the same error in the SSIS graphical user interface tab.
Thanks for your help.
View 3 Replies
View Related
Jan 6, 2007
Hey!
I am creating a kind of file browser for an application of mine. The principle is quite straight forward. It consists of folders and files. Each folder can contain other folders and files and so on. The twitch however is that i need a special root entity called site. The site is very much alike a folder but has some other properties. The site can contain folders and files.
To achieve this ive created te following tables (truncated for clearity):
#################### Sites ##################### ID [Int] ## ... ####################
#################### Folders ##################### ID [Int] ## SiteID [Int] ## FolderID [Int] ## ... ####################
#################### Files ##################### ID [Int] ## SiteID [Int] ## FolderID [Int] ## ... ####################
Both the folder table and the files table have a check constraint that ensures that either SiteID or FolderID is NULL. They WILL be part of EITHER a folder or a site. Not both!
Then i set up the foreign constraints as follows:Folders.FolderD -> Folders.IDFolders.SiteID -> Sites.IDFiles.FolderID -> Folders.IDFiles.SiteID -> Sites.ID
All constraints have cascade on delete and therefore the last of them cannot be created as it would be circular. (Wich it wont in this case, but theoreticaly its possible)
Iknow WHY this is rendering an error. But how can i work around it? Or would you suggest another design of the tables?
View 2 Replies
View Related
Sep 18, 2006
Hello,
I have a package, which calls a sub package to poulate a table depending on a flag in the database (using an ExecuteSQL task to return flagged table name).
The inner package populates some tables, and calculates what needs to be processed next. It sets the next flag.
However, I can't make this work in the control flow, as once the Execute package has completed, I need to start again from the top, as the flag will have changed to the next item.
I hope that I have explained this well enough.
I really need this to work, but SSIS will not let me create a circular dependency. Does anyone know a way around this, or can offer me an alternative solution??
I am getting desperate, so any suggestions will be welcome
Many thanks
View 4 Replies
View Related
Sep 20, 2006
Hi ,
I have two tables test1 and test2 .
Schema of table test1 is
A pk(primary key)
B
C fk (foreign key)
Schema of table test2 is
A
B pk
C fk
Table test1 and test2 have following relationships
1) test1.a pk -> test2.c fk
2) test2.b pk -> test1.c fk
Hence the two tables have circular relationship.
My question is when i query all rows of test1 based on a specific value of test1.a, can we get multiple rows in any case.
I am using following query to retrive the records:
select test1.* from A as A
inner join B on A.a=B.c
inner join A as AA on B.b= AA.c
where AA.a ='100'
Please let me know whether any duplicate row case is possible and if yes, please give any example.
Regards
View 1 Replies
View Related
Feb 14, 2007
I have a problem that looks like it has not been discussed before inthese groups.I have a simple SQLAgent job that runs sp_who (could be anything, butlet's just say sp_who for this example). I have set the jobstep towrite to an output file "T:out.txt". If the job is owned by anadmin, it runs fine and writes the output file. If it is owned by anon-admin user, it gets the following error msg:Warning: cannot write logfile t:out.txt. Error 1059 : Circularservice dependency was specified. The step failed.I know about setting up the SQLAgent CMDExec proxy account, and havedone that. In fact, both SQLAgent and the SQLAgent cmdexec proxy usethe same domain account, which is in the administrator group of thelocal server. So, I know that security is not the issue.When a simple job runs and writes to an output file, what service orservice group could it be trying to start or modify? I looked throughthe list of Services, and could not find any circular dependencies.Is there a utility to detect this? Why would running under onecontext (as an admin) be ok while the other context (non-admin on SQL,but using the same admin domain service account) fails?Thanks in advance for any info you might have.
View 3 Replies
View Related
Sep 10, 2014
I am using vs 2010 to write my dtsx import scripts.I use a script component as a source to create a flat file destination file.Everything have been working fine,but then my development machine crashed and we have to install everything again.Now when i use the execute package utility to test my scripts i get the following error:
Error system.NullReferenceException: Object refrence not set to an instance reference.
In PreExecute section
TextReader = new system.io.streamreader(" file name")
In the CreateNewOutputRows:
dim nextLine as string
nextLine = textReader.ReadLine
[code]...
is there something which i did not install or what can be the error?
View 0 Replies
View Related
May 13, 2015
I have a scenario like below
Product1
Product2 Product3
Product4 Product5
Product1 1
1 0 0
1
Product2 1
1 0 0
1
Product3 0
0 1 1
0
Product4 0
0 1 1
0
Product5 1
1 0 0
1
How to design tables in SQL Server for the above.
View 2 Replies
View Related
Feb 13, 2002
Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:
-------
Fixture
-------
fix_id
fix_date
fix_played
----
Team
----
tem_id
tem_name
-----------
TeamFixture
-----------
fix_id
tem_id
homeorawayteam
goals
It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.
Fixture contains the details about the fixture like date and fixture id and has it been played
Team contains team info like team id, name, associated graphic
TeamFixture is the table which links the fixture to it's home and away team.
TeamFixture exists to prevent a many to many type relationship.
Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!
View 2 Replies
View Related
Aug 18, 2015
I would like to create a table called product. My objective is to get list of packages available for each product in data grid view column while selecting each product. Each product may have different packages type (eg:- Nos, CTN, OTR etc). Some product may have two packages and some for 3 packages etc. Quantity in each packages also may be differ ( for eg:- for some CTN may contain 12 nos or in other case 8 nos etc). Prices for each packages also will be different that also need to show. How to design the table..
Product name :
Nestle milk |
Rainbow milk
packages :
CTN,OTR, NOs |
CTN, NOs
Price:
50,20,5 |
40,6
(Remarks for your reference):CTN=10nos, OTR=4 nos
| CTN=8 Nos
View 3 Replies
View Related
Feb 6, 2004
Hi y'all
Does anybody know reference or pdf file or free e-learning on the web?
View 1 Replies
View Related
Jun 18, 2001
DECLARE Loan_cursor CURSOR FOR
SELECT Loan_No,store
FROM loan
WHERE maturity_date > '2001-04-30' and loan_no like 'ABL%'
OPEN Loan_cursor
-- Perform the first fetch.
FETCH NEXT FROM Loan_cursor
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
Declare @LoanNo varchar(12)
** Set @LoanNo = Loan_No
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM Loan_cursor
END
CLOSE Loan_cursor
DEALLOCATE Loan_cursor
when l run the cursor l get the error
Server: Msg 207, Level 16, State 3, Line 15
Invalid column name 'Loan_No'.
.
If l reference it as Set @LoanNo = LoanTable.Loan_No l get the error
Server: Msg 107, Level 16, State 3, Line 15
The column prefix 'loan' does not match with a table name or alias name used in the query.
All l'm trying to do is to compare the loan number that l get from the cursor to the value in the loans table. Maybe reference is a better word
View 1 Replies
View Related
Jun 6, 2008
Hi friends,
Just i modified one sub stored procedures which is not getting affected in the main stored procedures
Create procedure main
as
begin
set nocount on
--First sp
exec Data_transfer_sp
--Second sp
Exec Clearance_sp
set nocount off
end
In data_transfer_sp,i have commented the select statement
but still iam use to the select result while execution the main sp.
Note:I have compiled the data_transfer_sp after making comment
.Txs in advance
View 3 Replies
View Related
Sep 11, 2007
Hi All ...
WITH myCTE (x,y,z) AS (SELECT x,y,z, from myTable)
SELECT x,y,sum(z) from myCTE
SELECT y,z,sum(x) from myCTE
the second SELECT fails, and says invalid object name. does the CTE go out of scope after i reference it once?
never mind the semantics of what I am SELECTing, I just want to be able to reference the CTE more than once in my SP
am I trying to use the CTE in a way that was not intended?
View 5 Replies
View Related
Sep 11, 2007
Hi All ...
WITH myCTE (x,y,z) AS (SELECT x,y,z, from myTable)
SELECT x,y,sum(z) from myCTE
SELECT y,z,sum(x) from myCTE
the second SELECT fails, and says invalid object name. does the CTE go out of scope after i reference it once?
never mind the semantics of what I am SELECTing, I just want to be able to reference the CTW more than once in my SP
am I trying to use the CTE in a way that was not intended?
View 1 Replies
View Related
Oct 15, 2007
hi ,
can any body help me that how can i use my .net dll into my rdl file.
thanks
Gorla
View 1 Replies
View Related
Feb 3, 2008
in this code ,whtat is the references of
RelationalDataSourceView
and also
what are the references of these?
OleDbConnection
OleDbCommand
OleDbDataAdapter
View 1 Replies
View Related
Nov 16, 2006
Can anyone see why I would get the 'Object Referenece not set to an instance of an object error in the following code?
It happens on this line:
MyAdapter.SelectCommand = New SqlCommand(SelectStatement, MyConnection) 'Populate the text boxes from database for alumni fields.
Dim MyAdapter As SqlDataAdapter
Dim MyCommandBuilder As SqlCommandBuilder
Dim DetailsDS As DataSet
Dim Row As DataRow
Dim SelectStatement As String = "Select ClientID,ClassYear,HouseName,CampusName,EducationMajor,GraduationDate FROM tblclient Where [ClientID]=" & _
ClientIDSent
Dim ConnectStr As String = _
ConfigurationManager.ConnectionStrings("TestConnectionString").ConnectionString
Dim MyConnection As SqlConnection = New SqlConnection(ConnectStr)
MyAdapter.SelectCommand = New SqlCommand(SelectStatement, MyConnection)
MyCommandBuilder = New SqlCommandBuilder(MyAdapter)
MyAdapter.Fill(DetailsDS, "tblClient")
Row = DetailsDS.Tables("tblClient").Rows(0)
ClassYearText.Text = Row.Item("Classyear").ToString()
HouseNameText.Text = Row.Item("HouseName").ToString()
CampusNameText.Text = Row.Item("CampusName").ToString()
EducationMajorText.Text = Row.Item("EducationMajor").ToString()
GraduationDateText.Text = Row.Item("GraduationDate").ToString()
View 2 Replies
View Related
Jan 21, 2004
Hi,
I'm looking for a good reference guide online as I am more used to mysql (and stil quite limited vocab at that)
I create table outside of a database by accident and I'm now looking for the sql syntaxt for moving tables, but I can't find it anywhere?
most simple guides don't seem to provide the syntax to do this. :(
View 11 Replies
View Related
Feb 23, 2004
does any one know any good pages to get some sql commands
and explainations thanks
View 2 Replies
View Related
Sep 20, 2000
Hi,
I am access SQL 7 via ADO in some ASP pages. My database objects were created under a user name aliased to the dbo. As a result, when I have my client logon to make a connection, I have to preface all my object references with the name of the owner, i.e., Select * from mydboname.people . Is there a way to avoid having to prefix all my transact sql statements with the owner?
thanks,
steve
View 2 Replies
View Related
Sep 1, 2004
I have two tables that are reference to each other by foreign key, now I would like to alter the table so the it doesn't reference each other any more. What is the syntax to alter a table so that the fk field doesn't reference a table anymore. Thanks in advance for the help.
View 1 Replies
View Related
Feb 17, 2004
What would you recommend for a good MDX book ?
specifically, for syntax, data structures and sample code.
thx
C
View 1 Replies
View Related
Jun 21, 2008
As I posted earlier, I am relatively new to TSQL. I have been given a few books:
O'Reilly SQL on SQL Server 2005
O'Reilly SQL in a Nutshell
Mastering SQL Server 2005 - Sybex
Beginning SQL Server 2005 Programming - Wrox
I have a copy of SQL2005 Developers Edition on a development server. So I think I have some decent tools at my disposal.
I was wondering if there are any other sites or forums that might be suggested for learning SQL programming? Right now, I am just making stuff up and following examples in books, etc. I would like to find a site where there are beginner level projects for no other purpose than educational. Something where I can get more hands on of the design and programming aspects. Just to practice.
Thanks,
grinch
View 5 Replies
View Related
Jun 14, 2006
i have this code,
CREATE TABLE s
(sno VARCHAR(5) NOT NULL PRIMARY KEY,
name VARCHAR(16),
city VARCHAR(16)
)
CREATE TABLE p
(pno VARCHAR(5) NOT NULL PRIMARY KEY,
descr VARCHAR(16),
color VARCHAR(8)
)
CREATE TABLE sp
(sno VARCHAR(5) NOT NULL REFERENCES s,
pno VARCHAR(5) NOT NULL REFERENCES p,
qty INT CHECK (qty>=0),
PRIMARY KEY (sno, pno)
)
when i run this query i get an error saying:
Column 's.sno' is not the same length as referencing column 'sp.sno' in foreign key 'FK__sp__sno__5EBF139D'. Columns participating in a foreign key relationship must be defined with the same length.
can anyone assist me what to be done?
View 6 Replies
View Related
Jul 14, 2006
Hi ,
I have the following structure:
Item table
----------
ItemId
ItemName
Item Transaction Table
----------------------
TransactionId
GiverItemId
SenderItemId
the data is somewhat like this:
Item table
__________
1abc
2xyz
3pnr
4rew
5dds
6djs
7dsf
Item Transaction table
---------------------
112
224
343
437
Now i have a reauirement to build a stored proc in which all the transactions starting from one transaction like, if i want to know the chain for item no 2 it shall give the following result:
24
43
37
if i want to know the chain for item no 3 then it shall give following
37
if i want to know the chain for item no 1 then it shall give following
12
24
43
37
Please help.. Its urgent.....
Thanks
View 1 Replies
View Related
Mar 27, 2008
I am teaching myself XML and was wondering if anyone knows
any good books to gently breaking me into XML.
Kind Regards
Rob
View 2 Replies
View Related
Jul 20, 2005
I need to parse SQL statements directly and extract each segmentindividually. Is there a way reference the Microsoft SQL Parserdirectly from VB.Net?Thanks!*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 6 Replies
View Related
