Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Finding All References To A Column

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?

View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Invalid Column References Editor

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?

View Replies !   View Related
Finding A Column ??????
How to find if a column exists in Database Table ?

thanx in advance

View Replies !   View Related
Finding A Column In A Database
Hi all,How do I find all tables containing a column (say a column includingthe string 'value')?ThanksBruno

View Replies !   View Related
Finding Tables Having A Particular Column
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 ?


View Replies !   View Related
Finding Column Name Associated With View
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

create view v_EmployeeDetails
select EmpId,Firstname from Employee


create view v_PersonalD
select SSN from Employee

View Replies !   View Related
Finding Contribution Of A Given Column

hi all,
I am in the process of converting Oracle package into SQL Server Stored procedure. I could not find SQL SERVER counter part for the oracle's ratio_to_report function.Using the conventional SQL with Subquery is Adversely affecting the perforamance. can any one help me ?
Thanks in advance

View Replies !   View Related
Error Finding Identity Column
I'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 AS Colname, UPPER( 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 = O.object_id  JOIN systypes   U ON C.xusertype = U.xusertype    WHERE =  'Fred' AND C.status = 128  ORDER BY C.colid SELECT AS Colname, UPPER( 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 = O.object_id  JOIN systypes   U ON C.xusertype = U.xusertype    WHERE =  '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?

View Replies !   View Related
Finding Dashes/hyphens In A Column.
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 Replies !   View Related
Finding Real Name For Column Alias
Not sure if this can be done, but you are a clever bunch. I am looking for a way (preferably through a stored procedure) to return the original column name for an aliased cloumn. For example, I have a table view like:

"SELECT id, firstname, lastname, qty as Amount FROM tblTeam"

Returned columns are id, firstname, lastname, Amount. I would like to look up the results returned by the view, and get a list of the ORIGINATING columns (eg. id, firstname, lastname, qty) from the returned data. I am using php script to run stored proc. Thanks.

View Replies !   View Related
Finding Max/Min Value In A Column When Row Data Repeat

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





            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 AS entrydate


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

View Replies !   View Related
Finding The Difference Between Two Column From Two Different Datasets

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?

View Replies !   View Related
Finding Keywords In Sql Server Column
I have a string EX: "How are you doing" Now. I wanted this string to be splitted in to respective words and return me those words in sql server. I have to use or call this SP from another SP.

View Replies !   View Related
Finding UNIQUE Values In Only One Column
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.



View Replies !   View Related
Finding Stored Procs That Ref/Update A Column
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 Replies !   View Related
Finding Matching Data In One Column That Does Not Match In Another
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.


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?

View Replies !   View Related
Finding The Highest Or Largest Number In A Column Problem
Example data
As you can see, CA11603 is the largest number in this list. 
 When I try the follow sql code,
WHERE (SUBSTRING([MyCode], 1, 2) = 'CA')
The largest number comes back as CA997
When I try
 The largest number comes back as CA997
TOP 1 (SchoolMasterCode)
WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA') ORDER BY Schoolmastercode
 The largest comes back as CA10001
When I try....
TOP 1 (SchoolMasterCode)
WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA')
 The largest comes back as CA1278
What am I doing wrong?

View Replies !   View Related
TSQL Script For Finding Column Names In Stored Procedures
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 Replies !   View Related
Finding Value Of One Column Associated With The Max Value Of Another Column
I'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.

View Replies !   View Related
Indirect References

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.


View Replies !   View Related
Cross-database References
 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!

View Replies !   View Related
AS 2005 References/Books
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.

View Replies !   View Related
Inter-Database References
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 Replies !   View Related
Computed Field References
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.

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

View Replies !   View Related
Excel Formula References
I 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?



View Replies !   View Related
FROM Clause Requiring Username References?!
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?


View Replies !   View Related
Foreign Key References Invalid Table

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?


-- Mitch Curtis
-- A2create.sql

-- Set the active database to KWEA.

-- Drop existing tables (if any).
DROP TABLE Ownership;
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.
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
waiting# INT 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
tenant# INT NOT NULL,
CONSTRAINT ten_fk FOREIGN KEY(tenant#) REFERENCES Tenant(tenant#)

CREATE TABLE Property_Owner
phone_number INT NOT NULL

CREATE TABLE Property_Status_Report
address VARCHAR(30) 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)

occupant_limit INT NOT NULL,
comments VARCHAR(30) NULL,
FOREIGN KEY(staff#) REFERENCES Staff(staff#)

manager# INT NOT NULL,
FOREIGN KEY(manager#) REFERENCES Staff(staff#)

staff# INT NOT NULL,
property_address VARCHAR(30) NOT NULL,
phone_number INT NOT NULL,
street 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)

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 Ownership;
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.

View Replies !   View Related
Adding References To Script Task
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)


Dim folders As String()

Dim files As String()

ftpConnection.GetListing(folders, files)


Dts.TaskResult = Dts.Results.Success

End Sub


View Replies !   View Related
Sql Server For Oracle Developer And DBA - References
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.


View Replies !   View Related
Matrix Reports With References To Multiple Datasets
Hello and thank you for the help in advance.
I know this has to be possible maybe I am just missing somthing.
I am creating a matrix report which will compare year by year quotes to orders The issue is  quotes and orders each have their own dataset.  I will be pivoting on JobType which is in both datasets and spelled the same. Is there a way to do this or will I have to figure out how to union the tables?  If not possible why does it allow you to name the dataset in the expression?
Thanks, Leo

View Replies !   View Related
Looking For References For Querying Active Directory (AD) Through SQL Server
Does anyone know of any good references (books or web sites) that provide examples of querying AD from SQL Server? I have the database link setup and have done two very simple queries against AD but I would like to see more in-depth examples.


View Replies !   View Related
Finding 'Error Column' Causing Error
Good Morning,


Am I new at this so please bear with me.  I searched the site and found threads on identifying the meaning of an error code and have instituted that scripting.  It is telling me that I have a 'The data value cannot be converted for reasons other than sign mismatch or data overflow.' error.  It identifies the colum as 5301. 

I searched the threads and found one telling me I could do an advanced editor search on the lineage id to find the column being referenced.  I have traced the entire process using this information and cannot find a reference lineage id of 5301.  Was that thread information accurate, and if so what do I do now?   If not, can someone tell me how the heck I find column 5301? I cannot load my table from a flat file because of this. 


Work so far:

I have checked for integrity between column definitions and source flat file.  I applied derived column changes to make the data transform to the appropriate data type/order where necessary.  This part works without error. (Or seems to, there is no error output from this piece.)  It is only on the final attempt to load that the process errors with these messages.


Thank you in advance to anyone who can help me.



View Replies !   View Related
SQL - Foreign Key With References Of Multiple Tables With Same Primary Key Field
I want to create a table withmember id(primary key for Students,faculty and staff [Tables])and now i want to create issues[Tables] with foreign key as member idbut in references i could not able to pass on reference as orcondition for students, faculty and staff.Thank You,Chirag

View Replies !   View Related
While Using SQL Server 2005 .Net Integration Are Dynamic Web References Allowed?
This is my problem: I have a dynamic web reference for a SQL Server UDF coded in C#, but I am unsure of where URL for the webservice is being read from.

I am working with SQL Server 2005 to create a User Defined Function, however I need to have a web reference to the SQLReportingService2005 web service. I will be distributing this function to my customers so I need this web reference to be dynamic.

Although the webservice says it is dynamic, I do not see a app.config file  to place the changing URL. Does anyone know where a dynamic web service pulls the URL from in this case?


Thanks in advance,


View Replies !   View Related
How To Update Hard Coded Database References In Stored Procedures ?
Hi There,

Our company deals with financial education and typically has 9 different databases which have some cross referenced stored procedures. Every time we replicate Production database into TEST and DEV environments, we had to manually update the database references in Stored procedures. and it usually takes atleast a week and until then all the dev and test work has to wait.

Hence, I wanted to write a script, Here the code below.

-- These two variables must contain a valid database name.
DECLARE @vchSearch VarChar(15),
        @vchReplacement VarChar(15)

SET @vchSearch = 'Search'
SET @vchReplacement = 'Replacement'
-- Select the Kaplan Database Names in the Current Server

DECLARE @tblDBNames TABLE (vchDBName VarChar(30))

IF @vchSearch NOT IN (SELECT vchDBName FROM @tblDBNames)
    PRINT 'Not a Valid Search DB Name'
    GOTO Terminate
IF @vchReplacement NOT IN (SELECT vchDBNAME FROM @tblDBNames)
    PRINT 'Not a Valid Replacement DB Name'
    GOTO Terminate

-- We have Valid DB Names, lets proceed...
--USE @vchReplacement

SET @vchSearch = '%' + @vchSearch + '..%'
SET @vchReplacement = '%' + @vchReplacement + '..%'

-- Get Names of Stored Procedures to be altered
DECLARE @tblSProcNames TABLE (vchSPName VarChar(100))

    DISTINCT so.Name
        ON sc.Id = so.Id
    AND sc.Text LIKE @vchSearch

-- Now, the table @tblSprocNames has the names of stored procedures to be updated.
-- And we have to Some HOW ?!! grab the stored proc definition and use REPLACE() to
-- update the database reference
-- Then, use cursors to loop through each stored proc and upate the reference

Now, I have got stuck how to extract the body of a stored procedure into a variable.

Please Help.... I dont want spend weeks of time in the future to do this work manually.


View Replies !   View Related
Problem Of Adding Trusted Accounts To Reporting Services: 'Some Or All Identity References Could Not Be Translated'

I created a new database called "TestReportServer" as mentioned in the installation instruction but I didn't
see (or could select) the option "Create the report server database in SharePoint integrated mode".
How can I select this option? Do I need to remove the reporing services and reinstall it again? Any suggestions?

After creating the database I get the error 'Some or all identity references could not be translated'.

The user I selected is a local administrator and has permission to all groups starting with wss.

I guess the database is not created as a sharepoint integration mode as I can start Server Management Studio
and see the database. Is that a correct assumption?

I hope somebody out there can help as I am strating to bang my head towards my desk right now :-)



View Replies !   View Related
Copy And Delete Table With &"Foreign Key References(...,...) On Delete Cascade?
Need some serious help with this one...

Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software...

Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the lowest common, a 'recipe' of steps that will work on all DBMS's.

The Problem:
There might be simpler ways to do this with SqlServer (all ears :-) - just in case I can't make it cross platform right now) but, with simplistic DBMS's (SqlLite, etc) there is no way to ALTER table once formed: one has to COPY the Table to a new TMP name, adding a Column in the process, then delete the original, then rename the TMP to the original name.

This appears possible in SqlServer too long as there are no CASCADE operations.
Truncate table doesn't seem to be the solution, nor drop, as they all seem to trigger a Cascade delete in the Foreign Table.

So -- please correct me if I am wrong here -- it appears that the operations would be
along the lines of:
a) Remove the Foreign Key references
b) Copy the table structure, and make a new temp table, adding the column
c) Copy the data over
d) Add the FK relations, that used to be in the first table, to the new table
e) Delete the original
f) Done?

The questions are:
a) How does one alter a table to REMOVE the Foreign Key References part, if it has no 'name'.
b) Anyone know of a good clean way to get, and save these constraints to reapply them to the new table. Hopefully with some cross platform ADO.NET solution? GetSchema etc appears to me to be very dbms dependant?
c) ANY and all tips on things I might run into later that I have not mentioned, are also greatly appreciated.


View Replies !   View Related
&&"Multiselect&&" Way For Delete Invalid References???
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,

View Replies !   View Related
What Are &"references&" Permissions?
Whats the definitoin. I've been digging for a while, but cannot locate.

TIA, cfr

View Replies !   View Related
Foreign Key Table References Per Table (Max 253)
I've just found out about the concept that a table should only be references by foreign keys a maximum of 253 times throughout the database. I was hoping someone could give me a better idea of the dangers of disregarding this recommendation.

In our database, we have a Users table, which contains all of the users of a given system. In nearly every other table in our database, we have a field to indicate which user created the record. This is a reference back to the Users table. But as the number of tables has grown, we've surpassed that 253 limit. All I can tell, practically, is that I can no longer delete from the Users table. The query will fail, telling me the query optimizer ran out of memory, and that I should simplify my query.

The Users table is the only table that even comes close to this number of foreign key references. I don't even mind being unable to delete from the table. I'm mostly wondering if there are other problems with my design that will cause issues?

Is there a better way to keep track of who created a record in the database? Is it bad design to reference my Users table in so many places?


View Replies !   View Related
Finding Right Row
i have

create table test

(id_text int
,text_name varchar(10)
,id_author int)

insert into test (id_text, text_name, id_author) values (205, 'name1', 123)
insert into test (id_text, text_name, id_author) values (205, 'name2', 124)
insert into test (id_text, text_name, id_author) values (205, 'name2', 125)
insert into test (id_text, text_name, id_author) values (206, 'name2', 124)
insert into test (id_text, text_name, id_author) values (207, 'name2', 122)
insert into test (id_text, text_name, id_author) values (207, 'name2', 128)

select id_text, id_author from test

and i need to get out only id_text with the first author.
any solutions for sql server 2000?

thank you

View Replies !   View Related
Finding Something In SQL

I am very new to SQL and I have an issue. We have a website that has pictures on the site. We want to take them down but the pictures are somehow tied into the database, they aren't on any of the servers. How can I find these pictures that seem to be somehow hard coded into the database?

Any help would be greatly appreciated.

View Replies !   View Related
Finding The Max Value
I have a report that is grouped by week.  The details of the week have the date, time interval (half hour), and the maximum value for that day.  So in the example below I am showing the week of May 21st.  Then I have all of the days of that week, sorted by date as requested, with the halfhour the maximum value occured in on that day.  So Monday the 22nd has the maximum value for the week at 254 (and is highlighted in red).  Where the ????? are below, I need to display '1400' which is the interval, but have not yet been able to figure out how to do this. 

I tried the following expression: 
=iif(max(Fields!MaxValue.Value) = Fields!MaxValue.Value, Fields!Interva.Value, "X")

but this only works if the first day is the maximum value.



Sun, 5/21/2006

Mon, 5/22/2006

Tue, 5/23/2006

Wed, 5/24/2006

Thu, 5/25/2006

Fri, 5/26/2006

Sat, 5/27/2006


Any ideas are greatly appreciated!

View Replies !   View Related
Finding SQL Triggers
I have a table TEST.
 I want to find the triggers which use this table test.
For example: If there is a table A, and a insert in A causes a trigger to insert data in TEST,
I want to find this particular trigger.
Any ideas?
I am using SQL server 2000
thank you

View Replies !   View Related
Finding Data
I have a form where users can enter an appointment on this form some personal details are entered. I want the users to be able to write in a textbox the users surname. they will then press a button next to the textbox that will bring up a smaller screen displaying that users details :
SELECT * FROM Members where surname = (textbox1)
Alternatively the users can click the button and bring up a screen where the users can put the surname into a textbox and then bring up the users details this way.
I am not sure how to do this or how to select from the database where a field in the database is the same as the information put in to a text.
Is this possible? Thanks in advance Mike

View Replies !   View Related

Copyright © 2005-08, All rights reserved