--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....
One of our Oracle Tables changed and I am wondering if there's any way that I can query all of our Stored Procedures to try and find out if that Oracle Table Name is referenced in any of our SQL Server Stored Procedures OPENQUERY statements?
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
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)
The script below can be used to determine the reference levels of all tables in a database in order to be able to create a script to load tables in the correct order to prevent Foreign Key violations.
This script returns 3 result sets. The first shows the tables in order by level and table name. The second shows tables and tables that reference it in order by table and referencing table. The third shows tables and tables it references in order by table and referenced table.
Tables at level 0 have no related tables, except self-references. Tables at level 1 reference no other table, but are referenced by other tables. Tables at levels 2 and above are tables which reference lower level tables and may be referenced by higher levels. Tables with a level of NULL may indicate a circular reference (example: TableA references TableB and TableB references TableA).
Tables at levels 0 and 1 can be loaded first without FK violations, and then the tables at higher levels can be loaded in order by level from lower to higher to prevent FK violations. All tables at the same level can be loaded at the same time without FK violations.
Tested on SQL 2000 only. Please post any errors found.
Edit 2006/10/10: Fixed bug with tables that have multiple references, and moved tables that have only self-references to level 1 from level 0.
This script finds table references and ranks them by level in order to be able to load tables with FK references in the correct order. Tables can then be loaded one level at a time from lower to higher. This script also shows all the relationships for each table by tables it references and by tables that reference it.
Level 0 is tables which have no FK relationships.
Level 1 is tables which reference no other tables, except themselves, and are only referenced by higher level tables or themselves.
Levels 2 and above are tables which reference lower levels and may be referenced by higher levels or themselves.
declare @table table ( TABLE_NAME nvarchar(200) not null primary key clustered ) set nocount off
print 'Load tables for database '+db_name()
insert into @table select TABLE_NAME = a.TABLE_SCHEMA+'.'+a.TABLE_NAME from INFORMATION_SCHEMA.TABLES a where a.TABLE_TYPE = 'BASE TABLE'and a.TABLE_SCHEMA+'.'+a.TABLE_NAME <> 'dbo.dtproperties' order by 1
print 'Load PK/FK references' insert into @r selectdistinct PK_TABLE = b.TABLE_SCHEMA+'.'+b.TABLE_NAME, FK_TABLE = c.TABLE_SCHEMA+'.'+c.TABLE_NAME from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a join INFORMATION_SCHEMA.TABLE_CONSTRAINTS b on a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA and a.UNIQUE_CONSTRAINT_NAME = b.CONSTRAINT_NAME join INFORMATION_SCHEMA.TABLE_CONSTRAINTS c on a.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA and a.CONSTRAINT_NAME = c.CONSTRAINT_NAME order by 1,2
print 'Make copy of PK/FK references' insert into @rs select * from @r order by 1,2
print 'Load un-referenced tables as level 0' insert into @t select REF_LEVEL = 0, a.TABLE_NAME from @table a where a.TABLE_NAME not in ( select PK_TABLE from @r union all select FK_TABLE from @r ) order by 1
-- select * from @r print 'Remove self references' delete from @r where PK_TABLE = FK_TABLE
declare @level int set @level = 0
while @level < 100 begin set @level = @level + 1
print 'Delete lower level references' delete from @r where PK_TABLE in ( select TABLE_NAME from @t ) or FK_TABLE in ( select TABLE_NAME from @t )
insert into @t select REF_LEVEL =@level, a.TABLE_NAME from @table a where a.TABLE_NAME not in ( select FK_TABLE from @r ) and a.TABLE_NAME not in ( select TABLE_NAME from @t ) order by 1
if not exists (select * from @r ) begin print 'Done loading table levels' print '' break end
end
print 'Count of Tables by level' print ''
select REF_LEVEL, TABLE_COUNT = count(*) from @t group by REF_LEVEL order by REF_LEVEL
print 'Tables in order by level and table name' print 'Note: Null REF_LEVEL nay indicate possible circular reference' print '' select b.REF_LEVEL, TABLE_NAME = convert(varchar(40),a.TABLE_NAME) from @table a left join @t b on a.TABLE_NAME = b.TABLE_NAME order by b.REF_LEVEL, a.TABLE_NAME
print 'Tables and Referencing Tables' print '' select b.REF_LEVEL, TABLE_NAME = convert(varchar(40),a.TABLE_NAME), REFERENCING_TABLE =convert(varchar(40),c.FK_TABLE) from @table a left join @t b on a.TABLE_NAME = b.TABLE_NAME left join @rs c on a.TABLE_NAME = c.PK_TABLE order by a.TABLE_NAME, c.FK_TABLE
print 'Tables and Tables Referenced' print '' select b.REF_LEVEL, TABLE_NAME = convert(varchar(40),a.TABLE_NAME), TABLE_REFERENCED =convert(varchar(40),c.PK_TABLE) from @table a left join @t b on a.TABLE_NAME = b.TABLE_NAME left join @rs c on a.TABLE_NAME = c.FK_TABLE order by a.TABLE_NAME, c.PK_TABLE
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,1) INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,2) INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,3)
SELECT * FROM PRODUCT_SALES
/* SalesmanID is reference key from Sales Master and ProductID is reference key from Product Master. How should i restrict user through DB with any constraint check, if user tries to enter
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,2),
It should throw error , if possible user defined message would be more useful.
I am having trouble trying to find the max of 2 columns in one table. I've tried using a common table expression and a subquery, but can't seem to get the correct results. I want to get the max from refnum, then the max "number" associated with that max refnum along with the date and decision
Someone ran an update statement multiple times so their are multiple entries in the table. What is the quickest way to track down the multiple entries? I would only want to see where timein and timeoff exist in the table multiple times for the same id. So this would be a duplicate
I have a SQL text column from SP_who2 in table #SqlStatement:
like 1row shown below :
"update Panel set PanelValue=7286 where PanelFirmwareID=4 and PanelSettingID=9004000"
I want to find what table and column names are in the text ..
I tried like below ..
Select B.Statement from #sp_who2 A LEFT JOIN #SqlStatement B ON A.spid = B.spid where B.Statement IN ( SELECT T.name, C.name FROM sys.tables T JOIN sys.columns C ON T.object_id=C.object_id WHERE T.type='U' )
Something like this : find the column names and tables name
I usually go for the largest datafile and then query in-there...But now I need to automate it for several instances... I need to be able with one script quickly retrieve the top 5 largest tables for the entire instance,not by database...
I need to find the missing months in a table for the earliest and latest start dates per ID_No. As an example:
create table #InputTable (ID_No int ,OccurMonth datetime) insert into #InputTable (ID_No,OccurMonth) select 10, '2007-11-01' Union all select 10, '2007-12-01' Union all select 10, '2008-01-01' Union all select 20, '2009-01-01' Union all select 20, '2009-02-01' Union all select 20, '2009-04-01' Union all select 30, '2010-05-01' Union all select 30, '2010-08-01' Union all select 30, '2010-09-01' Union all select 40, '2008-03-01'
For our ETL process, we maintain a TransformationList table that has the source view and the destination table. Data is copied from the view into the table (INSERT INTO). I am trying to find column names in the Views that are not column names in the associated Table.
In the below example, want to end up with three records:
I have it almost working, except that there is a table, ChangeColPrefix table, that is used by the ETL process to change some of the view's column name prefixes. Some of the source views have column names with prefixes that do not match the destination table column names. Say view SouthBase has all the column names prefixed with SB - like SBAcct, SBName. And the Destination table of Area District has ADAcct, ADName. There would be a row in the ChangeColPrefix for SouthBase, SB, AD, 1, 2 that would be used by the ETL process to create the INSERT INTO Area District From SouthBase.
I need to use this ChangeColPreifx to find my unmatching columns between my source views and destination tables. With out that table SBAcct and SBName from SouthBase will not appear to match the columns of ADAcct and ADName, but they do match.
I want to end up with these three records as non-matching:
View1, Column4 View2, Column4 View2, Column5
View1 has Salumn2 and View2 has Salumn5, and they must be changed to Column2 and Column5 as per the ChangeColPrefix table before running the Select from INFORMATION_SCHEMA.COLUMNS EXCEPT Select from INFORMATION_SCHEMA.COLUMNS looking for unmatched columns.
/***** Set Up Test Data *****/ -- Create 2 test views IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View1]')) DROP VIEW dbo.[View1] GO CREATE VIEW View1 AS SELECT '1' AS Column1 , '2' AS Salumn2 , '4' AS Column4;
Given a table A, I need to find all the tables that are in PK-FK with A and the columns in the reference. I can get the tables that have the FK relation through sysreferences or sysconstraints or sysforeignkeys but I have not been able to find out how to identify the specific column that is in the relation. Any one has any idea?
How can I list the stored procedures and user-defined functions that reference a given column? I could search ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES for '%MyColumnName%' but MyColumnName is not always unique.
I currently have two SQL server books for MS SQL Server 2000. One is aprep book for the 70-229 exam, the other is a Wrox book: "professionalSQL Server 2000 Programming."I'm looking for more T-SQL books that give me PRACTICAL tips onwriting advanced queries. What book do you refer to? Please post them.
I am trying to eliminate a scalar function by rewriting a view. I currently have a structure similar to the following:
With FirstQuery as (SELECT Trial, SBOI, TBOI, FROM TU WHERE SBOI Between 22000 and 22999 and TBOI < 10000), strt as (SELECT TOP 1 TimeOfEvent, Trial, SBOI, TBOI From TU ORDER BY TimeOfEvent),
[Code] ....
What I trying to do is for the Trial, SBOI, and TBOI in FirstQuery, find the Start Time and Stop Time. TimeOfEvent is an integer in milliseconds. Say I have the following data:
TrialID SBOI TBOI TimeOfEvent A 22000 5000 5 A 22000 5000 10 B 22000 5000 8 B 22000 5000 15
So the DISTINCT output would be:
Trial SBOI TBOI StartTime StopTime A 22000 5000 5 10 B 22000 5000 8 15
The problem I am having is that strt is selecting the smallest time period. It doesn't seem to care what the current record in cte is. I thought that by using my joins, it would make it select smallest TimeOfEvent for the Trial, SBOI, and TBOI that are selected in cte. Obviously that is not the case. So, I was trying to add a WHERE to the strt Select Statement such as
WHERE TrialID = FirstQuery.TrialID and SBOI = FirstQuery.SBOI and TBOI = FirstQuery.TBOI but it isn't working either.
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?
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
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?)
Hello,I have a query that I need help with.there are two tables...Product- ProductId- Property1- Property2- Property3PropertyType- PropertyTypeId- PropertyTypeThere many columns in (Product) that reverence 1 lookup table (PropertyType)In the table Product, the columns Property1, Property2, Property3 all contain a numerical value that references PropertyType.PropertyTypeIdHow do I select a Product so I get all rows from Product and also the PropertyType that corresponds to the Product.Property1, Product.Property2, and Product.Property3ProductId | Property1 | Property2 | Property3 | PropertyType1 | PropertyType2 | PropertyType3 PropertyType(1) = PropertyType for Property1PropertyType(2) = PropertyType for Property2PropertyType(3) = PropertyType for Property3I hope this makes sence.Thanks in advance.
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.
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
int nvarchar(100) and in Request field i put below data : 1 <request><F3>353535</F3><F6></F6></request> 2 <request><F5>353535</F5><F6></F6></request>3 <request><F3>353535</F3><F6></F6></request>
now i need to a query that i can find records that exists <F3> and if exists , remove just the <F3> tag
below like : 1 <request><F6></F6></request> 2 <request><F5>353535</F5><F6></F6></request>3 <request><F6></F6></request>
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?
Serial Count 001 2 the count is 2 because Serial 001 has an MSDSID of 20 and 22 002 1 the count is 1 because Serial 002 only has MSDSID 21 003 2 the count is 2 because Serial 003 has an MSDSID of 21 and 22 004 1 the count is 1 because Serial 002 only has MSDSID 23
It would be even better if the results just showed where the count is greater than 1.
I want to send an email twice a day, from database. So I have planned to make a storedproce which will be called by a job (which will select some record from one table and put it in other table based on a flag) but I want to run it in a transaction so that if email is send successfully then only it should commit else it should rollback.
How can i find that "Mail queued" now i should commit.
I have looked around quite a bit, but mostly what I have found is looking to see if a table is used or if a column is in a stored procedure and honestly most of what I have seen does not work.
I want to reduce our nightly import by removing any columns that are not being used. We insert into our staging tables, Stage1 for example. And say Stage1 has column1 and column2. If those columns are not being used, then I want to remove them from Stage1. The only catch is that every Stage1 table has a v_Stage1. v_Stage1 should have all the columns from Stage1, but doesn't always. So I need to know what columns from Stage1 are used somewhere other than v_Stage1 and what columns from v_Stage1 are not used.
insert into example values('fd') insert into example values('fd') insert into example values('fd1') insert into example values('fd23') insert into example values('fda23') insert into example values('fd23g')
