Circular Dependencies From Constraints

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.ID
Folders.SiteID -> Sites.ID
Files.FolderID -> Folders.ID
Files.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


ADVERTISEMENT

Help - Tasks That Have Circular Dependencies

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

How To Write Query For Add Dependencies And Remove Dependencies?

Dec 1, 2007

 I need to write a code for remove dependencies between Table1.Prikey and Table2.Idand  add dependencies between Table1.Prikey and Table3.Id how to write the code? please help .. thank you very much 

View 2 Replies View Related

Circular Reference To Update

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

UGH! Failed To Enable Constraints. One Or More Rows Contain Values Violating Non-null, Unique, Or Foreign-key Constraints.

Jan 9, 2007

I know this is probably a flick of a switch but I cannot figure out which switch.  Setup is SQL Server / Stored Procedures / DAL / BLL(skipped for testing) / PL.  The stored procedure queries from only one table and two columns are ignored because they are being phased out.  I can run the stored procedure and preview the data in the DAL but when I create a page with an ODS linked to the DAL and a GridView I get this error.  I checked every column that does not allow nulls and they all have values.  I checked unique columns (ID is the only unique and is Identity=Yes in the table definition).  I checked foreign-key columns for values that are not in the foreign table and there are none.  Any ideas why do I get this? 
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

View 3 Replies View Related

Failed To Enable Constraints. One Or More Rows Contain Values Violating Non-null, Unique, Or Foreign-key Constraints.

Jan 17, 2008

Hi,
    I am getting the above error when trying to load a report into my Web Application, I have tracked the error down to one specific field in my database. Even though this field is a NVarChar field and is of size 30 it would seem that  there is an issue returning the value from the field. I can write it into the database no problems but when I try to get it out of the database it returns the above error.
e.g
MOB 401.908.804 - Fails
0401.907.324 - okay
8239 9082 (pager) - fails
Anyone got an idea on how to fix this????
Regards..
Peter.

View 7 Replies View Related

DB Design Question. Circular Reference?

Jun 25, 2007

I have a hypothetical question. Suppose you have three database tables. One represents the moneyin 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 isset 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)typeOfCurrnecyAmount
Change --> this table represents coins------ChangeIdAmounttypeOfCurrnecy
MoneyType --> table Indicates the type of currency, change or dollars---------typeOfCurrnecy(PK)

View 5 Replies View Related

T-SQL (SS2K8) :: Circular Variable Reference

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

Circular Relationship-&&>can We Get Duplicate Records

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

Transact SQL :: Find Circular Reference In The Table

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

Cannot Write Logfile Error 1059 : Circular Service Dependency

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

Job Dependencies

Aug 13, 2007

Is it possible to make the execution of one job dependent on the successful execution of a previous job?

View 1 Replies View Related

Object Dependencies

Apr 3, 2001

Is there a way for me to gather all the dependencies information on all tables and views at one time?

View 2 Replies View Related

Identifying Dependencies

May 20, 2004

How can get an object's dependencies in SQL Server. For example it I have written a procedure which accesses some tables inside it then the procedure is said to be dependent on that table. Or one procedure might call another procedure and hence dependent.

Can I know an object's dependent objects from any system table. I think Oracle has a table USER_DEPENDENCIES which provides this info (I may be wrong :-().

Can anyone help ?

View 7 Replies View Related

Fix Dependencies During Or After Renaming

Jul 20, 2005

The previous post regarding dependencies reminded me of another bugger I comeup with from time to time. SQL Server allows us to rename objects via asystem stored procedure (I usually use an ADP front-end that presumably callsthe same procedure), but after the rename, I find singnificant strangeness.For instance, triggers are still connected to the tables, but the trigger textstill refers to the old table name. Views and stored procedures dependent onthe table are broken, and are not fixed by even by restarting the server,though I thought the cached plans were supposed to be dumped on shut-down,then recreated on demand later.Is there some tool or technique to rename a table and automatically detect andproperly drop/rebuild dependent triggers, views, stored procedures,relationships, and anything else I'm not thinking of?Oh yeah, and there are extended properties to deal with as well, but I guessthat's intractible unless we know what client build them (e.g. Access ADP),and what it expects to be recorded there, but I suppose it would be nice tojust dump them rather than leave them there possibly corrupted from a client'spersective.

View 4 Replies View Related

SQL Cache Dependencies

May 24, 2007

Hi,


I am currently using SQLCacheDependencies to help me manage my cache in an ASP.NET application that is hosted across a web farm. This utilises the SQL Server 2005 service broker feature.

I have implemented this and is currently working but i have a few questions i want to clear up and i hope someone can assist me in that.


1/. I am starting the broker service by using the command
System.Data.SqlClient.SqlDependency.Start( <connection string> );
I currently have that piece of code running when the application starts in the global.asax file.
What i want to know is will all SQL queries that hit the database register a notification?
Is is possible to only have a selected query register a notification?

In my web application only a handful of database queries require a sqlcachedependency to be created. I dont want all queries to be registering a notification as that will place a burden on my backend server.

1/. So does all queries register a notification once the broker is started?
2/. Is there a way to only tell selected queries to register a notification?
3/. When a cache object is invalidated because of a absolute and slidingscaletimeout, does the existing sqlconnection for that service broker get disposed?

Thanks
Jim

View 4 Replies View Related

Dependencies Of A Table

Aug 7, 2006

Hello,

I have a table in my SQL2000 server and there's a field in this table being created by an object - with a GREEN PLUS sign (look like an APPEND query in MsAccess). How can I locate and edit/modify that (append query-like) object within my SQL 2000 - Enterpise Manager? Here's some more details:

Table: Customer

Field1: CustomerID

Field2: CustomerRegion_ID (contains the ID above & RegionID below)



Table: Region

Field: Region_ID

--------------------------------------------------------------

When I checked on the dependencies of the table "Customer" I saw 1 dependency and this dependency has 2 table depend on it back-to-back (Customer & Region).

I want to modify the "Field2" of the "Customer" table and I looked everywhere on my VB/stored-procedure codes but couldn't find what triggle the creation of the field. The only thing that I suspect that would be the append object with "GREEN PLUS" sign. Any help would be appreciated.

Peter.





View 1 Replies View Related

Table Dependencies

Nov 3, 2006

Hi

Is there any way to know as what are all the entities or objects which are dependent upon a given Table? I wanted to know if i update a particular table in a given database on a server, what are all the other tablesobjects which gets effected because of this updation, may be because of some update triggers defined on this tabl or through cascading updates etc. Any system stored procedure which can help me to know these dependencies.



Many Thanks in Advance!

View 4 Replies View Related

SSIS Dependencies

Mar 8, 2007

Hello Everyone,

I want to run the SSIS service on a few computers. Can I "only" install this service or does this service needs an instance of the SQL server database as well?

* If it does need the database, can the database be shared between various instances of the service or does each one of them need their own database instance?

* are there any particular databases which are needed by SSIS in order to execute? like master, msdb or tempdb?

regards,
Abhishek.

View 5 Replies View Related

How To Get All The Dependencies Of A Stored Procedure?

Mar 5, 2008

Hi All, How to get all the Dependencies of a Stored Procedure?  Dependencies may exists in other databases also. For Example Stored procedure "sp_Employee" exists in the DB "Employee" This SP calling the another SP exists in another DB "Organization" You help would be appreciated.. RegardsGokul 

View 2 Replies View Related

Data Transfer And FK Dependencies

Aug 8, 2001

I want to write a query that lists all table names in the order (based on foreign key dependencies to the other tables) that the tables need to be loaded. Using DTS to transfer the data, the tables seem to be loaded in alphabetical order, which causes the load to fail on databases with heavy use of foreign keys. Can anyone offer some help?

View 2 Replies View Related

View Dependencies Across Containers

May 20, 2008

Hi All

I am trying to get a list of all the dependencies for objects in a database.

I know about sp_depends however many of the objects are not in the catalogue that the object is in and therefore are not shown.

Any help would be useful

--
David

View 4 Replies View Related

Cross Database Dependencies

Jan 3, 2008

As far as I know SQL Server 2005 lists only dependencies within the same database. In many cases though there are objects in one database depending on objects in a different database on the same server. I unsuccessfully looked for a tool to list those and I ended up creating a script to do the job instead. Any improvements are appreciated, e.g. the script uses syscomments which may no longer be available in the next SQL Server version - I have not found a replacement sys.XXX table yet. Please note that you need to use a connection with access to all the SQL databases on a server and that it may take a while to run the script below if you have many databases with numerous objects in there. On the other hand these are exactly the cases when you need to know which of the thousands of objects are dependent on the table or view you are looking at.

P.S. Does anybody know where the extended properties get stored (hint - not in syscomments)? I was looking for those too, since often I document in the description of a field that it links to "DatabaseABC.dbo.TableXYZ.Field1"

Plamen Kouzov

==========
declare @searchstring varchar(50)
declare @dbid int
declare @DBName varchar(1024)
declare @command varchar(1024)
declare @ServerDBs table([dbid] smallint, DBName varchar(1024))
select @searchstring = 'DatabaseABC.dbo.TableXYZ'
insert into @ServerDBs ([dbid], DBName)
select [dbid], [name]
from master.dbo.sysdatabases
where [name] not in ('master','tempdb','model','msdb','pubs','Northwind')

create table #ObjectsFound (DBName varchar(500), ObjectName varchar(500))
while (select count(*) from @ServerDBs)>0
BEGIN
set @dbid = (select TOP 1 dbid from @ServerDBs)
set @DBName = (select DBName from @ServerDBs where dbid = @dbid)
set @command = 'use ' + @DBName + '; insert into #ObjectsFound(DBName, ObjectName) select ''' + @DBName + ''', [name] from sysobjects where id in (select [id] from syscomments where text like ''%' + @searchstring + '%'') order by 1'
exec sp_sqlexec @command
delete from @ServerDBs where dbid = @dbid
END
select * from #ObjectsFound order by 1,2
drop table #ObjectsFound

View 1 Replies View Related

Checking For Primary Key Dependencies

Jul 20, 2005

Anybody know if there is a system function that can be used from astored procedure that determines if a given primary key has existingdependencies? I want to make a check for this and if there are none, Iwill delete the record. If there are, I will change a field calledbitStatus from 1 to 0. Enterprise Mgr. does something like this underAll Tasks, Display Dependencies. The normal way I do it is to manuallycheck for the existance of the primary key in every dependent table.SQL 2000 server

View 3 Replies View Related

SSIS Dependencies On SQL Deployment

Mar 8, 2007

Sorry to start a new thread, actually Darren answered my first question on SSIS dependencies but I have a clarification and somehow the software of this forum doesn't allow me to post again on my previous thread. (my posts disappears after some time).

My question is that if I choose to deploy the SSIS packages in SQL Server, then SSIS would have a dependency on SQL Database.

Do we know where the deployed packages are stored in SQL. This information is needed so that I can maintain and monitor the DB where the deployed packages are stored.

Also, do you think its a best practice to deploy the packages in SQL ( I think it will be good because then I can have consistent version of a package deployed on many machines... since I store my packages on one consistent DB rather than multiple file folders on each machine).

regards,
Abhishek.

View 1 Replies View Related

File Dependencies At Deployment?

Aug 7, 2006

I have packages with the execute process task. I have included the exe-file from path A using add->existing item to the SSIS-project so that it lies under miscellaneous files. Deploying these packages using the deployment utility the file gets in the deployment folder that I copy to the server. Running the package installer and installing to SQL Server all files get copied to the B path (dtspackages under sql server by default). Trying to run the package however results in complaints about the file not being at location A. I thus changed the path in the execute process task to a relative one and then I get a complaint that the file "is not in path". So how do I do to make sure it finds the executable at location B where it actually is? I thought the deployment utility would handle this on it's own?

View 1 Replies View Related

Drop Table Dependencies!!!!

Mar 6, 2008

Hi folks ...
I am back with another quick question here.
I was working on a table and on my absence it was dropped by one of my collegues. I restored it from the backup. The problem is it is not showing all the stored procedures and other tables under show dependencies as the original table was.
Is it going to affect the way the stored proc. depenendant on it works..or everything will be fine?
If the answer is yes...is there a way I can add the stored procedures or link it to the new table?
Thanks for reading and any insight is more than welcome.
Tanya

View 1 Replies View Related

Insert Values Of Table And Its Dependencies

Dec 4, 2006

Hello,I have 3 tableTable 1 : list of "whatever" programTable 2: list of tasks for each programTable 3: list of user for each taskWhen I have a new program, I want to select existing task and copy them and assign them to my new program. But I also want to copy the list of user of each task.Is there a way to do that in sql?I do not really want to go through each single task, then copy it with the new program, then get the @@identity of the inserted task and  then assign the same user to the newly inserted task.Thanks

View 3 Replies View Related

How Can I Create A Cross-database Dependencies?

Nov 4, 2002

I have a table EMPLOYEE in database PERSONNEL, and a table JOBS in database JOBMAN, I want to create a dependencies on the primary key EMP_NUM in table EMPLOYEE and the foreign key EMP_NUM in table JOBS, how can I do it in Enterprise Manager or any other tools?

View 5 Replies View Related

What Is DEPENDENCIES Folder In Sqlserver Table Mean Please.

Feb 27, 2007

Hi Foilks , I know it s easy question. I know databases but not SQL dbase. Please explain if you can.
what is "DEPENDENCIES" folder in sqlserver table mean please?

Dosston

View 8 Replies View Related

Display Table Dependencies By Script

Sep 12, 2001

Hello

I tried to execute sp_depends to get a list of Table dependencies
as when you click on "All tasks/Display dependencies" but I only get a few
not all of them.

Is there anybody who knows why I got only a part of the list

Thanks in advance

View 1 Replies View Related

Checking Columns Level Dependencies

Nov 4, 2004

Hi all,
Need a big favour, I have to collect information like
TableName-> it's all columnNames--> dependent table name of EachColumn(where this column is refrenced)

I have managed to get some info like:-

select so.name as 'Table Name',sc.name as 'Column Name',st.[name], sc.length,sc.isnullable,so.crdate
from syscolumns sc
inner join sysobjects so on so.id=sc.id
inner join systypes st on st.xtype= sc.xtype
where so.xtype='U'
order by so.name

sp_depends and sp_MSdependencies is not returning me same fields..

View 1 Replies View Related

Check Dependencies For Stored Procedures

Dec 27, 2004

Hello, everyone:

I have 40 stored procedures in a database. A table X is called by some procedures. How to check which procedure call table X, and how many procedures call table X? Thanks

ZYT

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved