I have a task to where I need to move a column from one table to
another. I want to be sure I update any view, stored procedure,
trigger, etc. that references the column. I simply want a query that
will report the related objects and then I will update them manually
but before I go and try and figure out how to do this by querying the
sys tables is there an sp_sproc that will do this?
I got the following problem to solve in TSQL. I don't want to use a cursor. But with the set based solution I am stuck.
Here is my problem:
DECLARE @tmp TABLE (CustomerID INT, CustomerLink INT, PRIMARY KEY(CustomerID))
INSERT @tmp
VALUES(100001,0)
,(100002,100001)
,(100003,100001)
,(100004,100003)
,(100005,100006)
[Code] ....
Desired result:
[CustomerID of a group member],[smallest CustomerID per Group]
|(GroupID)|CustomerID|CustomerLink|
|100001 |100001 |100001 |
|100001 |100002 |100001 |
|100001 |100003 |100001 |
|100001 |100004 |100001 |
|100005 |100005 |100005 |
[Code] .....
doesn't work for crossreferences :.(
RESULTSET:
CustomerID CustomerLink
100001 100001
100002 100001
100003 100001
100004 100001
100005 100005
100006 100006 --wrong
[Code] .....
Hi All,
I'm pretty new with SSIS. I have written some custom component which will read from flat file and write it into database. Between those processes, there are other processes which will analyse and transform the data being transfered. Everytime, I remove a column from upstream component, I have to go through each component to fix up column reference mapping using Invalid Column References Editor. There are lots of clicking involves just to remove a column from upstream component. My package has about 15 components. I am just wondering, is there anyway for us to prevent that editor to pop up, instead fixing invalid column reference programatically in ReInitializeMetadata?
Thanks,
Will
Hi,
How to find if a column exists in Database Table ?
thanx in advance
Hi All,
How to find all the tables of a database containing a column . for example,how list all the tables of employee database having employeeid ?
Cheers,
Mathi
India.
I want to find columns name associated with view. For example; can you please help me to write query? I need to write for several tables.
TableName Column View
---------- -------- -------
Employee EmpId v_EmployeeDetails
Employee Firstname v_EmployeeDetails
Employee Lastname --
Employee SSN v_PersonalD
Employee DOB --
___________________________________________________
--just example
go
create view v_EmployeeDetails
as
select EmpId,Firstname from Employee
go
go
create view v_PersonalD
as
select SSN from Employee
Hi all,How do I find all tables containing a column (say a column includingthe string 'value')?ThanksBruno
View 2 Replies View RelatedI'm using SQL 2005 Server Management Studio -
I go to the Northwind database - new query window and paste this code, I just found on this forum, to find the Identity column:SELECT C.name AS Colname, UPPER(U.name) AS Coltype, C.status AS Cstatus, C.ColId, C.Id AS Cid, C.length, C.xprec, C.xscale FROM syscolumns C JOIN sys.tables O ON C.id = O.object_id JOIN systypes U ON C.xusertype = U.xusertype WHERE O.name = 'Fred' AND C.status = 128 ORDER BY C.colid SELECT C.name AS Colname, UPPER(U.name) AS Coltype, C.status AS Cstatus, C.ColId, C.Id AS Cid, C.length, C.xprec, C.xscale FROM syscolumns C JOIN sys.tables O ON C.id = O.object_id JOIN systypes U ON C.xusertype = U.xusertype WHERE O.name = 'Products' AND C.status = 128 ORDER BY C.colid
However, I get an error message saying:Invalid object name 'sys.tables'.
What might I be missing here?
Aim – Find the latest #Account.[Last_Post_Date] for the Fdmsaccountno and populate the latest date for that Parentid. In a new column Called “Parent Last Post Date"
Current results
FdmsaccountnoParentidLast_Post_Date
87802012188487823334288920140105
87802012788187823334288920140103
87802012888987823334288920131231
87870180988087823334288920131217
87802012088687823334288920131204
87823334288987823334288920131201
87870180888287823334288920131130
87802011588687823334288920131120
87875705088487823334288920131011
Desired results
FdmsaccountnoParentidLast_Post_DateParent Last Post Date
8780201218848782333428892014010520140105
8780201278818782333428892014010320140105
8780201288898782333428892013123120140105
8787018098808782333428892013121720140105
8780201208868782333428892013120420140105
8782333428898782333428892013120120140105
8787018088828782333428892013113020140105
8780201158868782333428892013112020140105
8787570508848782333428892013101120140105
My query is
select
#Account.Fdmsaccountno,
#Account.Parentid,
#Account.[Last_Post_Date],
from #Account
inner join [Dim_Outlet] on #account.FDMSAccountNo = [Dim_Outlet].FDMSAccountNo
where #Account.Parentid = '878233342889'
order by [Last_Post_Date] desc
Hi,I'm having trouble running the following query:select * from message where text_body like ' ----------%'ie, five spaces followed by at least ten hyphens. The query doesn'tcomplete, so eventually I cancel it. If I remove the hyphens from thequery ("... like ' %'") then it runs fine (though it doesn't findthe correct data).Am I confusing SQL Server by using a wildcard or regular expression?(I'm using SQL Server 2000 - 8.99.760).Thanks in advance for any helpRichard
View 5 Replies View RelatedQuery :-
 SELECT
 MAX(TIME_VAL) AS Duration1 ,
 MIN(TIME_VAL) AS Duration2 ,Â
 -- VALUE OF LATITUDE CORRSPONDING TO MAX(TIME_VAL)
 -- VALUE OF LATITUDE CORRSPONDING TO MIN(TIME_VAL)
 (Select LONGITUDE from (select LONGITUDE , row_number()
  Â
[Code] ....
I am not getting Correct values for StartLongitude , EndLongitude. How can i achieve this to get the val of latitude corssponding to max/ min of time_val column.
Hi
I always seem to come back to dataset issues. Nonetheless, here is my problem:
I am retrieving data using a stored procedure that lists the number of bookings per hour and the revenue that generates also per hour. So, you enter a single date in the stored procedure, for example 2001-10-01 and it will bring back 24 rows corresponding to the 24 hours (obviously) and two columns, one displaying the number of bookings and one displaying how much revenue.
Pretty simple so far.
I have a report using SQL Reporting Services that allows users to enter two dates and therefore you get two tables with the relevant dates data. For example, table one has date 1's data and table 2 has as you'd expect date 2's data. Both tables use different datasets.
The reason why I am using different tables and different datasets is that the stored procedure I use only allows the use of one date at a time therefore, I use a second dataset to generate the second date's data.
The problem occurs when I want to calculate a difference between the two date's data, for example have another table that shows the differrence between table one and table two for bookings for each corresponding hour.
Anyone have any ideas as to how I could achieve this?
I have a table. In that table I have a list by student number that lists the entry dates into a particular grade. When trying to list only the first time entered, there is no unique way to identify one row from another other than the date. Is there a way to use max or min to only pull one date per student number? I have done a series of case when statements and I am able to get it down to 1 to 2 entries per student number, but I need to get it down to only 1 date per student number.
Thank you for your help
SELECT DISTINCT mx.stu_num,
CASE WHEN er.STU_NUM = ep.STU_NUM
THEN er.enterdater
ELSE CASE WHEN ea.STU_NUM = ep.STU_NUM
THEN ea.enterdatea
ELSE CASE WHEN eb.STU_NUM = ep.STU_NUM
THEN ep.enterdatep
ELSE eb.enterdateb
END
END
END AS entrydate
FROM dbo.mx AS mx LEFT OUTER JOIN
dbo.v_EntryDate9_R AS er ON mx.stu_num = er.stu_num LEFT OUTER JOIN
dbo.v_EntryDate9_P AS ep ON mx.stu_num = ep.stu_num LEFT OUTER JOIN
dbo.v_EntryDate9_A AS ea ON mx.stu_num = ea.stu_num LEFT OUTER JOIN
dbo.v_EntryDate9_B AS eb ON mx.stu_num = eb.stu_num
I am using Access 2007 and I have 10 columns of data.
I am trying to select all the data from all 10 columns where the first column's data returns only the Unique values and the other columns return all the data from the row that is returned from the first column.
I have used this
SELECT DISTINCT [SFR Rates All].Sorter, [SFR Rates All].SProgram, [SFR Rates All].S_Price, [SFR Rates All].Min_Loan, [SFR Rates All].Max_Loan
FROM [SFR Rates All];
But that returns all the data in the table and all values in the Sorter column with duplicates because each row has distinct data.
I am trying to return unique values from [SFR Rates All].Sorter (the first column) and all the data from the other 9 columns that are contain the row with the unique value in Sorter.
I know that I am missing something basic but well, I can't figure it out.
Thanks
Bob
I have a table that stores part numbers and manufactuers. Somehow this table has become corrupt showing different manufacturers with the same part numbers.
I know this will take a bit of manual digging to fix, but I want to find a way to pull all rows that have the same part number that have different manufacturers, or just pull up any "duplicate" part numbers and I can determine what is right or wrong as far as the manufactuers and make those changes.
I have tried this, but it does not seem to want to work.
Code:
Select * from my_table
Where partnumber = (select partnumber from my_table) and compName <> (select compName from my_table)
I have tried other variations of the same, but nothing seems to want to show me just the items that have the same part numnbers and different manufacturers. I do not care if there are duplicates of the same part number/manufacturer entries, just if the part number is duplicated where the manufacturers are not the same.
These are the rows I want to edit and group by part number. I have almost a million rows of entries and this is not something I want to go through row by row. :-P
Any ideas?
I am trying to find a way where I can search for a column that is associated in all tables of the database. I have created a query but is not executing correctly.
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t8
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Status%'
ORDER BY schema_name, table_name;
I have a split string function that will take a comma delimited string and give back a table with all the values.I have a table that has a column with a comma delimited comma delimited list of states.
I can use a where clause to find one state in the table (such as all records that have CA in the states string).But need to find out how to find all the rows that have all or any of the states from a comma delimited parameter.Here is the schema
CREATE FUNCTION [dbo].[split] (@list nvarchar(MAX))
RETURNS @tbl TABLE (svar nvarchar(10) NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int
[code]....
Could any suggest to me a good way to programmatically identify which SPsupdate a database column. I would like to create a cross reference for ourdatabase.
View 5 Replies View RelatedExample data
CA1000
CA10001
CA10002
CA10003
CA11597
CA11603
CA1001
CA998
CA999
As you can see, CA11603 is the largest number in this list.
When I try the follow sql code,
SELECT
MAX([MyCode])
FROM
[MyTable]
WHERE (SUBSTRING([MyCode], 1, 2) = 'CA')
The largest number comes back as CA997
When I try
MAX([MyCode])
FROM
[MyTable]
WHERE [MyCode] LIKE 'CA%'
The largest number comes back as CA997
SELECT
TOP 1 (SchoolMasterCode)
FROM
SchoolMaster
WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA') ORDER BY Schoolmastercode
The largest comes back as CA10001
When I try....
SELECT
TOP 1 (SchoolMasterCode)
FROM
SchoolMaster
WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA')
The largest comes back as CA1278
What am I doing wrong?
Does anyone have a TSQL utility (that they can share) that recursively searchs sysobjects for a matching input parameter string(for instance column name) for stored procedure object properties that returns the stored procedure name?
View 1 Replies View RelatedI've got a rowset (intermediate result in a large query) that's equivalent to this table:
create table T (
K bigint,
V bigint,
W tinyint,
primary key(K,W)
)
What I want is "for each K, find the V associated with the largest W". Or,
select K,V from T t1 where W = (select max(W) from T t2 where t2.K = t1.K)
What I'd like is a solution that does not involve a self-join on the table (which the sub-query effectively is). The rowset can be sorted by (K asc, W desc), in which case I'm looking for the first (K,V) pair for each unique value of K.
In the larger query, T could have 100's of thousands of rows, and as I mentioned, T is the result of another query on my base tables. I can express T as a CTE easily enough, but the resulting query plan (using the sub-select, as above) ends up performing the query that generated T repeatedly in order to implement the self join.
I can save T into a temp table - in fact, that's what I'm doing now - but I'd rather avoid using a temp table if possible. This query needs to be of the highest possible performance, so if there's a no-temp-table, no-self-join solution, I'm all ears!
I could imagine making a CLR UDF that does the filtering that I want, but I'm not convinced that it'd be a good choice - any insights on whether that's worth trying would be appreciated as well.
Whats the definitoin. I've been digging for a while, but cannot locate.
TIA, cfr
Normally, you establish referential integrity so that foreign key in one table points to a primary in another. Here is a composite key:
(A_ref, B_ref) => (A, B)
Consider a situation where A is a primary key in table T1. It is refered by T2, which has A,B as it's prmary key. An example of this situation would be a table of printers and table of batches a printer has printed at specific date. The batches are identified by Printer, Date, BatchNo within the date. Now, we create a temporary table T3, which addresses "today" batches. The "today" reference is taken from a date is taken from some record in the DB and, combined with the Printer ID and BatchNo, must point to a record in T2. Is it possible to specify such a complex relationship to ensure the referential integrity?
The advantages of the integirty are:
-- the referred records are pinned down from removal
-- it is not possible to refer unexisting object; thus, the referee is ensured to refer an existing one.
Thanks.
Hi All -
I have come across the need to use a cube in AS 2005 for an application. Unfortunately this will be the first time writting an ASP.Net application which uses one. I was wondering if any of you had any good books or references that I could look at to get an idea of how I should approach this issue.
Thanks in advance.
Hi,
I'm doing a web application that will get some information from an ERP.
At this moment I have 2 databases:
1) The aspnetdb, where I have the tables for Merbership and Role
2) The ERP database
I need to put my web application tables on one of these two DB's. This tables will reference the users from the membership and some products from the ERP DB.
I will store products requests that will store both UserID (from aspnetdb) and ProductID (from ERP DB). I'm thinking to put these tables on the aspnetdb, so that all web application tables stick together. But, I will loose tha ability to make joins with the ERP database, right?
Do you think this will work? Can someone make some comments about this situation, and give me some tips?
Thank you!
I am currently developing a stored procedure that includes a number of computed fields. Is it possible to reference a computed value, (eg. FLdA), or do I need to CREATE a temp file and then reference the FldA and FldB values. I have simplified my code, it is much more extensive in that there are numerous WHEN clauses attached to each FldA and FldB computation.
SELECT FldA = CASE
WHEN .... THEN CurQty * 1.5
WHEN .... THEN CurQty * 1.75 ELSE 0 END),
FldB = CASE .....
NewValue = CASE
WHEN .... THEN FldA * CurValue
WHEN .... THEN FldB * CurValue
etc.
HelloSuppose a database Db1 with tables tl1 and tl2 and a second database db2with tables tl3 et tl4.Is it possible to make a join between tables of the two databases ?As for example, Select * from tl1 INNER JOIN tl3 where tl1.Field1 =tl3.Field3Thank for any helpThierry
View 1 Replies View RelatedI have a data list that will grow over time. The values are listed vertically in a column; most recent value at the bottom. I am trying to figure out how to setup a formula to figure out the standard deviation on the most recent 30 values automatically. For instance if the column contains 30 values and I add the 31st value, I'd like to have to have the standard deviation displayed in a cell and automatically shift from calculating on values 1-30 to values 2-31. Is this possible?
Jeff.
We installed SQL7 over the weekend. Everything was working peachy through yesterday. This morning SQL server is requiring all of the queries to require references to the username in the from clause...
For example
"Select * from mytable" used to work fine. Not it requires "select * from username.mytable"
I'm logged in as the same username, which is the DB Owner as well. Any idea why this is happening all the sudden?
TIA
Kevin
Hey.
I'm trying to create some tables in my database but I'm getting some errors... The one which is causing the most trouble is Msg 1767, Level 16, State 0, Line 38
Foreign key 'ten_fk' references invalid table 'Tenant'.
I'm not sure why it's complaining... can anyone help me out here?
Cheers!
-- Mitch Curtis
-- A2create.sql
-- Set the active database to KWEA.
USE KWEA;
-- Drop existing tables (if any).
DROP TABLE Ownership;
DROP TABLE Tenant;
DROP TABLE Staff;
DROP TABLE Property;
DROP TABLE Property_Status_Report;
DROP TABLE Property_Owner;
DROP TABLE Placement_Record;
DROP TABLE Candidate_Tenant;
DROP TABLE Waiting_List;
-- Create new tables.
CREATE TABLE Waiting_List
(
waiting# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
candidate_name VARCHAR(20) NOT NULL,
anticipated_start_date SMALLDATETIME NULL,
anticipated_end_date SMALLDATETIME NULL,
max_affordable_rent SMALLMONEY NOT NULL
);
CREATE TABLE Candidate_Tenant
(
candidate# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
waiting# INT NULL,
name VARCHAR(20) NOT NULL,
phone_number INT NOT NULL,
required_property_type VARCHAR(10) NOT NULL,
CONSTRAINT w_fk FOREIGN KEY(waiting#) REFERENCES Waiting_List(waiting#)
);
CREATE TABLE Placement_Record
(
opening# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
tenant# INT NOT NULL,
start_date SMALLDATETIME NOT NULL,
end_date SMALLDATETIME NOT NULL,
total_bonds SMALLMONEY NOT NULL,
weekly_rent SMALLMONEY NOT NULL,
CONSTRAINT ten_fk FOREIGN KEY(tenant#) REFERENCES Tenant(tenant#)
);
CREATE TABLE Property_Owner
(
owner# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
name VARCHAR(20) NOT NULL,
phone_number INT NOT NULL
);
CREATE TABLE Property_Status_Report
(
address VARCHAR(30) NOT NULL,
report_date SMALLDATETIME NOT NULL,
weekly_rent SMALLMONEY NOT NULL,
month_rent_start_date SMALLDATETIME NOT NULL,
month_rent_end_date SMALLDATETIME NOT NULL,
maintenance_fee SMALLMONEY NOT NULL,
month_inspection_history VARCHAR(30) NULL,
CONSTRAINT ar_pk PRIMARY KEY(address, report_date),
FOREIGN KEY(address) REFERENCES Property(address)
);
CREATE TABLE Property
(
address VARCHAR(30) PRIMARY KEY NOT NULL,
staff# INT IDENTITY(1,1) NOT NULL,
type VARCHAR NOT NULL,
occupant_limit INT NOT NULL,
comments VARCHAR(30) NULL,
FOREIGN KEY(staff#) REFERENCES Staff(staff#)
);
CREATE TABLE Staff
(
staff# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
manager# INT NOT NULL,
name VARCHAR(20) NOT NULL,
FOREIGN KEY(manager#) REFERENCES Staff(staff#)
);
CREATE TABLE Tenant
(
tenant# INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
staff# INT NOT NULL,
property_address VARCHAR(30) NOT NULL,
name VARCHAR(20) NOT NULL,
phone_number INT NOT NULL,
street VARCHAR(20) NOT NULL,
city VARCHAR(20) NOT NULL,
postcode INT NOT NULL,
category VARCHAR(10) NOT NULL,
comments VARCHAR(30) NULL,
FOREIGN KEY(staff#) REFERENCES Staff(staff#),
FOREIGN KEY(property_address) REFERENCES Property(address)
);
CREATE TABLE Ownership
(
address VARCHAR(30) NOT NULL,
owner# INT NOT NULL,
CONSTRAINT ao_pk PRIMARY KEY(address, owner#),
FOREIGN KEY(address) REFERENCES Property(address),
FOREIGN KEY(owner#) REFERENCES Property_Owner(owner#)
);
-- Display tables.
SELECT * FROM Waiting_List;
SELECT * FROM Candidate_Tenant;
SELECT * FROM Placement_Record;
SELECT * FROM Property_Owner;
SELECT * FROM Property_Status_Report;
SELECT * FROM Property;
SELECT * FROM Staff;
SELECT * FROM Tenant;
SELECT * FROM Ownership;
Errors:
Msg 3701, Level 11, State 5, Line 8
Cannot drop the table 'Ownership', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 9
Cannot drop the table 'Tenant', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 10
Cannot drop the table 'Staff', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 11
Cannot drop the table 'Property', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 12
Cannot drop the table 'Property_Status_Report', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 13
Cannot drop the table 'Property_Owner', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 14
Cannot drop the table 'Placement_Record', because it does not exist or you do not have permission.
Msg 1767, Level 16, State 0, Line 38
Foreign key 'ten_fk' references invalid table 'Tenant'.
Msg 1750, Level 16, State 0, Line 38
Could not create constraint. See previous errors.
How do you add a reference if the assembly you want to reference does not live in C:WindowsMicrosoft.NETFrameworkv2.0.50727? It doesn't seem to make sense that you cannot access the connections the AquireConnection() method serves since the interfaces are defined in Microsoft.SQLServer.DTSRuntimeWrap.dll (in C:Program FilesMicrosoft SQL Server90SDKAssemblies).
For example the code below works fine, in order to make it work I had to copy DTSRuntimeWrap.dll to v2.0.50727, surely any assembly which is been added to the GAC should be available as a referece?
Public Sub Main()
Dim cmgr As Microsoft.SqlServer.Dts.Runtime.ConnectionManager = Dts.Connections("FTP Connection Manager")
Dim cn As Object = cmgr.AcquireConnection(Nothing)
Dim ftpConnection As IDTSFtpClientConnection90
ftpConnection = TryCast(cn, IDTSFtpClientConnection90)
ftpConnection.Connect()
Dim folders As String()
Dim files As String()
ftpConnection.GetListing(folders, files)
ftpConnection.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
Hi everyone,
When you've got -for example- a derived column task linked with a Flat file and then you change any field and come back to derived column task again you have select that field
with two possibilities:
1-€˜Leave as invalid column reference€™ /
2-€˜Delete invalid column reference€™
It€™s easy when just one is affected but when you have eight or ten is very tedious do the same one by one. Any way for to do same but selecting more than one?
Thanks for any input,
Hi all,
Would anyone know of any references (online or books) that make it easier for experienced Oracle people to Learn SQL Server. The type of things I'd like to know for example are
The environment for SQL server on a PC (e.g. where the
datafiles are, what's the replacement for tnslistener - general architecture info)
Any significant differences that I'd need to know for creating tables and applications, like what do I use instead of varchar2 for example.
that should get me started.
tia,
Dave