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
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 RelatedHello,
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.
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!
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
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 RelatedHi 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
i have written query to find out column names of a table in the database .
SELECT t.NAME AS TABLEName
,SCHEMA_NAME(schema_id)+'.'+c.name AS ColumnName
FROM sys.tables t
INNER JOIN sys.columns c ON c.object_id = t.object_id
WHERE OBJECT_NAME(c.object_id) LIKE '%Message%'ORDER BY t.NAME
So far i got the Expected result but what ever column i am looking i want to find out the Dependency of column realted tables. by executing the above query i am getting table name and column name. How can i get the column related dependent tables.
Hi everybody.
I need to find all the views that depend on a table in a different database, in order to refresh them once the table is altered.
In the BOL I found the following script that is very useful, but I can't use it if the view and the table are in different database.
Where can I find the dependencies in this case?
Thank you very much.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/9ce1d07c-ee66-4a83-8c73-cd2cc104dd08.htm
Creating a script that updates all views that have dependencies on a changed object.USE AdventureWorks;
GO
SELECT DISTINCT 'EXEC sp_refreshview ''' + name + ''''
FROM sys.objects so INNER JOIN sys.sql_dependencies sd
ON so.object_id = sd.object_id
WHERE type = 'V'
AND sd.referenced_major_id = object_id('Person.Contact')
Hi.I'm getting errors like this when I try to run an upgrade script I'm trying towrite/test:altering labels to length 60Server: Msg 5074, Level 16, State 4, Line 5The object 'ALTPART_ANNOT_ANNOTID_FK' is dependent on column 'label'.I used this to bracket my script:sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"gosp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"go/* updates here */sp_msforeachtable @command1="print '?'",@command2="ALTER TABLE ? CHECK CONSTRAINT all"gosp_msforeachtable @command1="print '?'",@command2="ALTER TABLE ? ENABLE TRIGGER all"goI guess the alter table nocheck constraint isn't disabling the fk'scompletely?Is there a way around this, or do I manually have to do the constraintdropping/recreating?ThanksJeff Kish
View 3 Replies View RelatedT-SQL script for the following request ?
Generate script for view dependencies of user defined table types ?
I want to make a query, stored procedure, or whatever which will only display the primary key where there does no exist a foreign key in linked table.For example. If I had two tables with a one to many relationship.A [Computer] has one or more [Hard Drives]. I want to select only those computers which do not have a Hard Drive(s) associated with them. That is, show all computers where the Computer_ID field in the [Hard Drives] table does not exist. This seems simple but I'm drawing a blank here.
View 1 Replies View RelatedI have nine type of buttons,
EnrollAmtBTM
PlacAmtBTM and so on, I also have a SQL setver view V_Payment_Amount_List from here i need to display the data on the button
this is the select value to display when i choose the agency list and the amount corresponding to that agency_ID is displayed here the agency_ID is fetched from the SQL CONDITION
THIS IS WHERE I GET FETCH AGENCY DATA WHEN SELECTED i.e SQL CONDITIONprotected void CollectAgencyInformation()
{
WebLibraryClass ConnectionFinanceDB;ConnectionFinanceDB = new WebLibraryClass();
string SQLCONDITION = "";string RUN_SQLCONDITION = "";
SessionValues ValueSelected = null;int CollectionCount = 0;if (Session[Session_UserSPersonalData] == null)
{ValueSelected = new SessionValues();
Session.Add(Session_UserSPersonalData, ValueSelected);
}
else
{
ValueSelected = (SessionValues)(Session[Session_UserSPersonalData]);
}ProcPaymBTM.Visible = false;PaymenLstBTN.Visible = false;
Dataviewlisting.ActiveViewIndex = 0;TreeNode SelectedNode = new TreeNode();
SelectedNode = AgencyTree.SelectedNode;
SelectedAgency = SelectedNode.Value.ToString();
Agencytxt.Text = SelectedAgency;
Agencytxt2.Text = SelectedAgency;
Agencytxt3.Text = SelectedAgency;DbDataReader CollectingDataSelected = null;
try
{CollectingDataSelected = ConnectionFinanceDB.CollectedFinaceData("SELECT DISTINCT AGENCY_ID FROM dbo.AIMS_AGENCY where Program = '" + SelectedAgency + "'");
}
catch
{
}DataTable TableSet = new DataTable();
TableSet.Load(CollectingDataSelected, LoadOption.OverwriteChanges);int IndexingValues = 0;foreach (DataRow DataCollectedRow in TableSet.Rows)
{if (IndexingValues == 0)
{SQLCONDITION = "where (Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() + "'";
}
else
{SQLCONDITION = SQLCONDITION + " OR Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() + "'";
}
IndexingValues += 1;
}SQLCONDITION = SQLCONDITION + ")";
ConnectionFinanceDB.DisconnectToDatabase();if (Dataviewlisting.ActiveViewIndex == 0)
{
Dataviewlisting.ActiveViewIndex += 1;
}
else
{
Dataviewlisting.ActiveViewIndex = 0;
}
SelectedAgency = SQLCONDITION;
ValueSelected.CONDITION = SelectedAgency;
???? this is where i use to get count where in other buttons and are displayed.... but i changed the query to display only the Payment_Amount_Budgeted respective to the agency selected. from the viewRUN_SQLCONDITION = "SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;
try
{
CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(RUN_SQLCONDITION);
EnrollAmtBTM.Text = CollectionCount.ToString();
}
catch
{
}////this is my CollectedFinaceDataCount-- where fuction counts the records in the above select statement if i use for eg.
"SELECT Count(Placement_Retention_ID) FROM dbo.V_Retention_6_Month_Finance_Payment_List"
here is the functionpublic int CollectedFinaceDataCount(String SQLStatement)
{int DataCollection;
DataCollection = 0;
try
{
SQLCommandExe = FinanceConnection.CreateCommand();
SQLCommandExe.CommandType = CommandType.Text;
SQLCommandExe.CommandText = SQLStatement;
ConnectToDatabase();DataCollection = (int) SQLCommandExe.ExecuteScalar();
DisconnectToDatabase();
}catch (Exception ex)
{Console.WriteLine("Exception Occurred :{0},{1}",
ex.Message, ex.StackTrace.ToString());
}
return DataCollection;
}
So here mu requirement request is to display only the value fronm the view i have against the agency selected
Please help ASAP
Thanks
Santosh
I got one table with 3 columns = Column1, Column2, Column3
Sample Table
Column1 | Column2 | Column3
------------------------------------
A | 12 | 0
A | 13 | 2
B | 12 | 5
C | 5 | 0
Select Column1, Column2, Column3 as New1
Where Column1 = A AND Column2 = 12 AND Column3 = 0
Select Column1, Column2, Column3 as New2
Where Column1 = A AND Column2 = 12 AND Column3 >0
The only difference is one condition Column3 = 0 and another one Column3 > 0. This two condition is not an "AND" condition... but just two separate information need to be display in one table.
So how do i display the result in one table where the new Output will be in this manner
Column1 | Column2 | New1 | New2|
Thanks
hello forum friends,
i need to display the database table in ASP
page.how it is possible,can anyone explain me
with code.
Regards
Prathap
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid
Hi this is the first time i am using sql and i have a problem with displaying the data in the way i want.
i have the following table:
EmpID Cust_Name
1 Mary
1 John
2 David
2 John
Annd i am trying to get it to display like this on the UI
EmpID Cust_Name
1 Mary, John
2 David, Andrew
is there anyway to do this?
Hi,
I have a result set from a store procedure which looks like this.
Column1 Column2 Column3
A 1 lit1
A 1 Rep2
A 1 Reg3
B 2 ram1
B 2 lim2
I want to show data in my table like this:
Column1 Column2 Column3
A 1 lit1; Rep2;Reg3
B 2 ram1;lim2
Can anyone tell me how I can do this?
Thanks
Ashwini
I have a fairly complicated report consisting of numerous datasets and numerous tables.
In one of my tables I want to display the message "No data to display" if nothing comes back from the query. Currrently the table just disappears if there is no data. Is there a way to display the table if there is nothing in the query?
Hi guys,
I have one table which contains more than 200 rows of data. Our customer wants this report to be displayed only in one page with vertical scorll bar on the right of it.
Could anybody tell me if it is possible?
Thanks.
Danny
Ok here goes. I have 3 tables, one holds case info, the 2nd holds possible outcome on the charges, and they're joined on a 3rd table (CaseOutComes). With me so far? Easy stuff, now for the hard part.
Since there's a very common possiblitly that the Case has multiple charges, we need to track those, and therefore, display them on a datagrid or some other control. I want the user to be able to edit the info and have X number of dropdowns pertaining to how many ever charges are on the case. I can get the query to return the rows no sweat, but ...merging them into 1 record (1 row) with mutiple drops is seeming impossible -- I thought about using a placeholder and added the controls that way, but it was not in agreement with what I was trying to tell it .
Any ideas on how to attack this?
What is the best approach to handle this situation? I have three different databases, which has it's own stored procedure. I need to call them all at page load and piece together the data. The common demoninator is the date.
2007
JAN
FEB
MAR
APR
row 1
50
60
89
63
row 2
44
21
62
46
2006
JAN
FEB
MAR
APR
row 1
60
90
65
41
row2
984
650
452
762
Row 1 and Row 2 come from two different databases and stored procedures.
How can I query the data and present it as it's shown above?
Thank you!
How would I list the users in the users table that have duplicate IDs or count of IDs > 1?The UserName field is unique.
State UserName First Last ID City CountTX Kkeaton Kathryn Keaton 1001 Dallas 2TX KakiKeaton Kathryn Keaton 1001 Dallas 2I think I have to use a subselect? If I use group by then it won't show both records. It shows only one of them.Thanks
Craig
Basically, i am still relatively new to ASP.net and SQL. And i have the following query.
I have a skills table to which the user enters their skills using the following fields: Skillcatagory, SKill, Current Level, Target Level, target date and comments and the serial of the user.
I need to check via our staff table, which people have had a skill entered for them. And then produce a report on who has not had a skill entered for them.
This table has a serial of the user column aswell which is unique.
If there is more information that i can give you to assist me, please ask me.
You help would be greatly appreciated.
Hi,
I made a table to display information from northwind database. It displays fine on IE5.0 but will only show the first line in netscape.
Do you know what it may be???
Hi ..
i am SqL beginner. i having trouble output what i want from table.
table contain 3 columns
________________________________
|(names)|(item)|(location)|
1.| Jimmy | pizza| TX |
2.| Joe | ball | CA |
3.| Joe | ball | WA |
4.| Jim | shoes| AZ |
________________________________
i try to select all records out from this table. but column 2 and 3 contain same information in names and item only different is location. how can distinct one of them?? and display like the below, please advise.
|(names)|(item)|
1.| Jimmy | pizza|
2.| Joe | ball |
3.| Jim | shoes|
________________________________
This is my case. I want in my datagrid to display first record from table A and second record from table B. This is because i need to display a price in the column where it will have current price and history price, therefore the first row will be current price and second row will be history price.
This is the output that i trying to do.
Item | Price
-------|---------
A | 2.50 --------> Table A
A | 1.50 --------> Table B
Please let me know if my explanation is not details enough.
Thanks
I'm trying to Display 2 fields using the SalesOrderDetail table.
CarrierTrackingNumber
Count the number of CarrierTrackingNumber and give alias name of “Count CarrierTrackingNumber.”
I only want to return records where the CarrierTrackingNumber has count = or greater than 2.
Below is the SQL I developed, but I'm getting this error when I try to execute: Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '4911-403C-98' to data type int.
SELECT
CarrierTrackingNumber,
(
SELECT
COUNT(CarrierTrackingNumber)
FROM SalesOrderDetail
WHERE CarrierTrackingNumber >= 2
) AS CountCarrierTrackingNumber
FROM
SalesOrderDetail
Hi all,
I have created a trigger in SQLExpress which looking for any insertion to a specific table in a database:
Create Trigger Tg_Insert on [tablename]
FOR Insert
AS
Select * FROM Inserted
---------------------------------------------------------
Assume there is nothing wrong with this trigger as I have already checked the output with Query tool.
My question is how can I display the newly inserted data to the client? In my case, Console application.
I want to do something like Console.WriteLine("data from inserted table here");
How can I achieve this? FYI, I'm using VS2005 with sqlexpress 2005.
Thanks in advance.
Regards,
SHAH
Is it possible to make the execution of one job dependent on the successful execution of a previous job?
View 1 Replies View RelatedDECLARE @emp VARCHAR(1024) declare @emp1 varchar(1024)declare @emp2 varchar(1024)SELECT @emp1 = COALESCE(@emp1 + ',', '') + cast(eid as varchar(10)),@emp = COALESCE(@emp + ',', '') + ename ,@emp2 = COALESCE(@emp2 + ',', '') + desigFROM emp SELECT eid=@emp1,ename = @emp,desig=@emp2
View 1 Replies View RelatedHi,please help me, i have a data driven site,i need to display a table who look like this Name Total Used RemainingA 10 5 5B 20 13 7C 12 12 if you would look on the field of total, used and remaining, they look like an excel formula which is total - used = remaining or vise versa.but the problem in this table is the database, it look like thisname On/OffA 1A 1A 0and soon....... B 1B 0B 1and soon.... until C so how do i count the total, is the number of A's or B's or C's in the whole database then Group By the 'name' fieldthen how do i count the used is the number of ON in the On/Off field while remaining is counting the Off,so my question is i have RUN this using 3 different gridview each has its own datasource, that look like this for the used select name, count(on/off)
from myTable
where on/off = 1
group by name for the remaining select name, count(on/off)
from myTable
where on/off = 0
group by name and for the total, just used the same Select but without the where clause,i get this problem when the field "used" (please refer from the 1st table) it has a blank value, which is because my data base show that in deed name "C" has not been used, but eve though it should show '0' instead of a blank, so how can i do that???please help me, thanks
I have a table in SQL, that I know how to connect to using ADO, but Ineed help on how to read records from that table.So on a form I have a listbox where I want to populate that and i havea textbox with a userid.Here is an example of the table:USER ID TYPE PAYMENT=====================0001 CARD 150.000001 CASH 250.000002 CASH 175.00If I have 0001 in the txtuserid textbox, I then want to display inthe listbox:CARD 150.00CASH 250.00How would I do this?thanks
View 1 Replies View Related