How To Determine Is Column Is A Foreign Key
Sep 25, 2001Do anyone know how to find out programmatically is a particular column in
a table is being used as a foreign key?
Thanks,
Kurt
ADVERTISEMENT
How To Determine Is Column Is A Foreign Key
Sep 25, 2001Do anyone know how to find out programmatically is a particular column in
a table is being used as a foreign key?
Thanks,
Kurt
Determine Rowguid In Column And Edit Column Information ASP.NET 1.1 C#
Dec 19, 2006What query should I run?
1. To determine whether a column is a rowguid or not using C# .NET 1.1
2. To add/modify column information and be able to set/change:
- Primary key
- Column Name
- Data Type
- Length
- Allow Null
- Default value
- Precision
- Scale
- Identity
- Identity Seed
- Identity Increment
- Row guid
Thanks a lot!
DBConcurrency Exception, Is There A Way To Determine The Column
Apr 25, 2007Using VS05 Pro, VB, Sql Express...
For a DBConcurrency exception, is there a way to determine the Column(s) that triggered the excepction? The exception itself does not seem to contain the information. You could probably write a very complex method comparing
DataRowVersions
, to determine which value is different, but that seems overkill.
Thanks!
Bob
How Do You Determine The Base Data Type Of A Column!
Sep 28, 2001Does anyone know how to determine the base datatype of a column? I've tried using sp_columns but if there are user defined datatypes on the column it returns that name instead of the base datatype. I've also tries accessing systypes and syscolumns to determine the base datatypes.
Any tips or trick would really be apreciated!
Kurt
Compare Rows To Determine If Column Values Are Different?
Mar 2, 2011I need to determine if a value for a group of rows is the same or different and I am not sure where to begin. I am fairly new to writing T-Sql code. I am working with SQL Server 2005. This is a work assignment; I am not a student.
I have two tables: One for Course and another for CourseDays. As the Course table implies, it lists courses for a school. The CourseDays tables has a row for each day a course is held. For instance, one course maybe held on Monday, Wednesday, and Friday, so there would be three rows, one row for each day. A given course could be held in a different room for each day. I am trying to write a query that returns one row for each course, and if the room number is the same for each day the class is held, return that room number in the row, else return 'Various'.
Course table:
CourseID, Desc
1 English
2 History
3 Science
CourseDays table:
CourseID, Day, RoomID
1 M 320
1 W 445
1 F 680
2 T 123
2 Th 123
3 M 514
3 T 514
3 W 521
3 Th 901
3 F 521
Expected results:
CourseID, Desc, RoomID
1 English Various
2 History 123
3 Science Various
I simplified the example, as there are about 30 columns in the table and about half a million rows.
How To Determine Table And Column Names In A Trigger?
May 5, 2008Hi all,
I'm new to this whole trigger 'thing', so forgive me if this question has been asked and answered a few times already.
I'm in the process of writing a trigger that will send an e-mail to an application admin when any table within a given database is altered (IE, a column is added or deleted). I can get the e-mail to fire off when that happens without any issue, but I'd like to be able to let the admin know which table was tweaked and what new column was added.
Is this a relatively easy thing to do and I'm just not finding the right built-in variable name, or does something more need to be done?
Thanks,
-Mike
Determine Largest Numeric Datatype Scale Of A Column?
Oct 25, 2012I would like to determine the largest numeric datatype scale of a column.
Decimal Largest Scale Solution found here.
Sample Data:
2222.0002430
2222.0000245
2222.0023455
More Than One Column FOREIGN KEY Constraint Specified For Column
Jul 23, 2005How can you indicate that a FOREIGN KEY constraint references twocolumns in two different tables?"SQL Server Books Online" show an example of how to reference twocolumns in the SAME table:REFERENCES ref_table [ ( ref_column [ ,...n ] )Here is the error and the 'bad' SQL code:Server: Msg 8148, Level 16, State 1, Line 4More than one column FOREIGN KEY constraint specified for column'UserOrGroupId', table 'salesforce3.dbo.AccountShare'.CREATE TABLE salesforce3.dbo."AccountShare" ("Id" varchar(18) PRIMARYKEY , ... , "UserOrGroupId" varchar(18) CONSTRAINTFK_UserOrGroupId6349 FOREIGN KEY REFERENCES "User"(Id) REFERENCES"Group"(Id) , ... )
View 2 Replies View RelatedHow To Determine Which Service Pack Level Your SQL Server Is Running And Column Name Spacing Bug?
Aug 10, 2007I have visual studio 2005 with XP home. I'm trying to find out which service pack my SQL server is running as I've been told that Service pack 2 has a few bugs.
For example ADO Applications Report a Syntax Error When Column Names Contain Spaces. This appears to be an error i am recieving. I just wanted to confirm that it is a microsoft bug rather then something Im doing wrong.
Microsoft meantions the bug here: http://support.microsoft.com/kb/264687
I've been looking everywhere. I do not have the "query analyzer window" where you can find this out as I have VS 2005. How would I find this out.
Thank you
Replacing A Column With A Foreign Key
Dec 24, 2005In an *existing* database, how would you remove the City column in the
Employees table, and put in the CityID key column from the Cities table
?
Can A Column Act As Both Primary Key And Foreign Key?
Sep 11, 2011Can a column act as both primary key and foreign key, if so what is script.
And can a table have more than one primary keys/foreign keys, if so what is the script.
Column Foreign Key Constraint 'EOR_ITE_REF_EOR'
Mar 17, 2004I am importing data from a flat, comma delimited text file. It appears to properly import all the records and then on the last record it give me the error
INSERT statement conflicted with COLUMN FOREIGN KEY constraint for 'EOR_ITE_REF_EOR'.
Then it goes on to tell me the database, table and column in which the problem occured which is the first column in the table. It looks like it doesn't like the end of the file for some reason (EOR, end of record?) so I tried deleting the last record from the file and it still does it. I do have a file that I have imported that worked so I tried copying the last record of that file and pasting it into the last record of the file I wanted but that did not work either. The ends of file that works and the file that doesn't work look the same and I can find nothing on 'EOR_ITE_REF_EOR'. Any help would be appreciated.
Adding A Column With Foreign Key Constraint
Oct 1, 2007
Hello!
I am trying to alter a table adding a column with foreignkey constraint. The table that holds the primary key in that relation has data, so i am not being able to execute the alter statement.
This is the error:
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_MOPAeroportoICAO_IDMOPAlimentacaoFerroviaTipo'. The conflict occurred in database 'MOP', table 'MOPAlimentacaoFerroviaTipo', column 'IDMOPAlimentacaoFerroviaTipo'.
This is the script:
ALTER TABLE MOPAeroportoICAO
ADD IDMOPAlimentacaoFerroviaTipo [int] DEFAULT 0 NOT NULL
CONSTRAINT [FK_MOPAeroportoICAO_IDMOPAlimentacaoFerroviaTipo] FOREIGN KEY
REFERENCES MOPAlimentacaoFerroviaTipo (IDMOPAlimentacaoFerroviaTipo)
How can i turn of check when i add the foreign key ?
Thanks in advance
SQL Server 2012 :: Determine Which Column Is Causing Error Converting Data Type Varchar To Numeric?
Aug 14, 2014I'm moving data from one database to another (INSERT INTO ... SELECT ... FROM ....) and am encountering this error:
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.
My problem is that Line 6 is:
set @brn_pk = '0D4BDE66347C440F'
so that is obviously not the problem and my query has almost 200 columns. I can go through one by one and compare what column is int in my destination table and what is varchar in my source tables, but that could take quite a while. How I can work out what column is causing the problem?
Referencing Column List For Foreign Key No Match
Oct 26, 2013I'm encountering this very weird problem, so I create a staff table:
CREATE TABLE Staff (
staffNo numeric(10),
venueNo numeric(10),
name nvarchar(20),
DOB datetime,
position nvarchar(20),
salary numeric(8,2)
CONSTRAINT staff_PK PRIMARY KEY(staffNo, venueNo),
CONSTRAINT venue_FK FOREIGN KEY(venueNo) REFERENCES Venue
);
and then when I create a professional therapist table
CREATE TABLE Professional_Therapist (
staffNo numeric(10),
specialization nvarchar(20),
bonus numeric(8,2),
CONSTRAINT professional_PK PRIMARY KEY(staffNo),
CONSTRAINT staff_FK FOREIGN KEY(staffNo) REFERENCES Staff
);
It says : The number of columns in the referencing column list for foreign key 'staff_fk' does not match those of the primary key in the referenced table 'Staff'.
Identity Column As Foreign Key In Bulk Update
Apr 17, 2007We need to import data from flat/xml files into our database.
we plan to do so in bulk as amount of data is huge, 2GB+
we need to do some validation checks in our code after that we create insert queries.
We have identity columns that are used as foreign keys in child tables. Question is how can I write a bulk/batch insert statement that will propogate the identity column to the child, as for all other we are creating the query in the application memory.
there are 2 parent tables and 1st table value needs to be referred to in 7 tables and second table's value in 6.
Thanks much for your help.
Alter Table Column, Which Is Part Of Foreign Key
Apr 1, 2006In MS SQL Server, I have the following tables with some data in it.create table table1 (column1 varchar(32),column2 int not null,column10 varchar(255),.....primary key (column1, column2),);create table table2 (column1 varchar(32),column2 int not null,column20 varchar(255) not null,....foreign key (column1, column2) references table1(column1, column2));Now, I need to change the all column types from varchar to nvarchar tosupport internationalized character set.I am able to do so, for columns column10 in table1 and column20 oftable2 without any problems by using command:"alter table table1 alter column column10 nvarchar(255);"But, when I try the similar thing for column1 of table1/table2, amgetting one error message saying like: "ALTER TABLE ALTER COLUMN failedbecause one or more objects access this column". I guess, this iscoming because of foreign key relationship between the tables.NOTE: While defining the table2, for foreign key I have not specifiedanything like "on update cascase" ...etc.How can I resolve this issue? Any suggestions/solutions are reallyhelpful to me. Thanks in advance.
View 2 Replies View RelatedINSERT Statement Conflicted With COLUMN FOREIGN KEY Constraint...
Feb 18, 2006Hi there,
I have a stored procedure which i pass a number of parameters into. One of these parameters is staffNo (only passed this in because i couldn't execute the query without it). The thing is this field can be Null, but when trying to pass null into it it comes up with an Foreign Key conflict. staffNo is a foreign key within the table i'm inserting the data into.
This is the error i get:
"INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'PropStaffFK'. The conflict occurred in database 'DewMountain', table 'TblStaff', column 'staffNo'. The statement has been terminated. The 'PropertyAdvert' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. "
Does anyone know of away around this? how to pass a null value to the stored procedure without it causing this error.
Thank you
Melanie
How To Do A Join Using Foreign Keys In Stored In A Database Column?
Apr 17, 2007I have a database column that stores a comma delimited list of foreignkeys. Would someone show me how to do a join using the values from alist stored within a record?For example, a record in tbl_cds.genre_id might have a value of "2,5, 6" corresponding to genre_ids 2 , 5 and 6. I want to jointbl_cds.genre_id to tbl_genre.genre_id using the values in that datafield.It seems I need a loop like this:SELECT * FROM tbl_cdsWHEREBegin Looptbl_cds.genre_id[i] = tbl_genre.genre_idEnd Loop.Would someone give me the correct syntax?Is there an alternative method that would create less overhead?Sorry for such a novice post.
View 2 Replies View RelatedSQL 2012 :: Partition Existing Table On Foreign Key (datetime) Column?
May 28, 2015Is it possible to partition an existing table on a foreign key (datetime) column? Also would partition switching work?
View 0 Replies View RelatedINSERT Statement Conflicted With COLUMN FOREIGN KEY SAME TABLE Constraint
Aug 1, 2006For some reason, I'm getting this error, even without the DBCC Check:
INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'Category_Category_FK1'. The conflict occurred in database 'mydb', table 'Category', column 'CategoryID'.
The statement has been terminated.
The very first insert fails...it was working fine before:
DELETE Category;
-- Now, insert the initial 'All' Root Record
INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'All'
INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 2, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'
/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/
WHILE (@@ROWCOUNT <> 0)
BEGIN
INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID)
SELECT c.CategoryID, s.CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1
FROM Category c INNER JOIN CategoriesStaging s ON c.[Name] = s.ParentCategoryName
WHERE NOT EXISTS (SELECT 1 FROM Category c WHERE s.[CategoryName] = c.[Name])
AND s.CategoryName <> 'All'
Here's the schema:
CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[ParentCategoryID] [int] NULL,
[Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [Category_PK] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [mydatabase]
GO
ALTER TABLE [dbo].[Category] WITH NOCHECK ADD CONSTRAINT [Category_Category_FK1] FOREIGN KEY([ParentCategoryID])
REFERENCES [dbo].[Category] ([CategoryID])
DB Design :: More Than One Table Primary Key Adding In Single Foreign Key Column
Nov 6, 2015I have come up with one scenarios where I have three table like Product, Services and Subscription. I have to create one table say Bundle where I can have some of the product id , service id and Subscription id , i.e. a bundle may contains sum prduct , services and subscription . How I can design these relations ?
View 3 Replies View RelatedDetermine Table Names And Column Names At Runtime?
Jan 22, 2004Hi
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
Thanks.
Transact SQL :: Delete Records From Table (Table1) Which Has A Foreign Key Column In Related Table (Table2)?
Jun 29, 2015I need to delete records from a table (Table1) which has a foreign key column in a related table (Table2).
Table1 columns are: table1Id; Name. Table2 columns include Table2.table1Id which is the foreign key to Table1.
What is the syntax to delete records from Table1 using Table1.Name='some name' and remove any records in Table2 that have Table2.table1Id equal to Table1.table1Id?
How To Determine The Value For The Bar?
Jan 22, 2008Hi all,
How to determine the value for the bar in Neural Network Viewer? I know the value is displaying in the tooltips when we pointing to the bar in the table but i don't know how to get them. So where can i get those of calculation or data of score, probability of value1 and 2, and lift for value1 and 2 ? Is it get from the Microsoft Neural Network Content Viewer? which column and how to calculate? If not, please advise.
Hope my question is clear.
I am looking forward to hearing from you shortly and thanks a lot in advance.
Thanks!
With best regards,
Yours sincerely,
xuenly
Determine Next Available Order ID
Sep 21, 2006I am trying to determine the next available order id using the method below. It works provided the table has a record in it. If it doesn't I get the error "Input string was not in a correct format." I am certain that it is because the query is returning a value of NULL. How can get around that or check for the NULL value?' Establish data connection...Dim sqlConn As New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))'Determine order id number...Dim order_id As IntegerDim strSQL As StringstrSQL = "Select MAX(order_id) from mkt_order"Dim sqlCmd As New SqlCommand(strSQL, sqlConn)Dim sqlDA As New SqlDataAdapter(sqlCmd)Dim sqlDS As New DataSetsqlDA.Fill(sqlDS, "item")If sqlDS.Tables(0).Rows.Count <> 0 Thenorder_id = Convert.ToInt32(sqlDS.Tables(0).Rows(0)(0).ToString()) + 1Elseorder_id = 1End If
View 1 Replies View RelatedHow To Determine Cause Of Timeout?
Aug 3, 1999Good day,
We have a web application (ASP) running on SQL Server 7.0.
Recently, the users are getting quite a lot of timeouts on
the database:
Microsoft OLE DB Provider for ODBC Drivers error '80040e31'
[Microsoft][ODBC SQL Server Driver]Timeout expired
The database is not supposed to be doing too much work, so I can't
understand why these timeouts are occuring. How can I determine
the cause of the timeouts?
The cause could probably anything from a trigger that's taking
too long, a query that's taking too long, or simply bad database design.
I've looked at SQL Server's Profiler, but could not yet use it
successfully to give me any hints of what could cause the timeouts.
Any ideas of how I can use Profiler, Performance Monitor, or any other
tool(s) to see what is happening in the background in the database,
i.e. how much processing a trigger is using, etc.
Thanks very much!
---
Gert Lombard
OSI Airport Systems
South Africa
How To Determine Frequency Of
Dec 28, 2006I'm new to full text catalogs and we have a vendor who's code utilizes them. The database server is SQL 2005 and I am noticing the following message in the SQL log every minute.
Changing the status to MERGE for full-text catalog "ResearchCatalog" (5) in database "DBA_Test" (11). This is an informational message only. No user action is required.
A SQL job is running the following command every minute.
exec sp_fulltext_catalog 'ResearchCatalog', 'start_incremental'
What is the typical frequency for running an incremental?
Can the messages be suppressed?
Thanks, Dave
How To Determine Dbas?
Jul 23, 2005for sql servers how can i determine who [what accounts] are the dbas?
View 1 Replies View RelatedHow To Determine How Much RAM My SQL Server Is Actually Using???
Jul 20, 2005I've been doing a lot of reading on this and my head is starting tohurt! It seems to be quite a feat to work out how much memory isactually being used by our server.I'm running W2K advanced server with SQL 2000 EE, 8GB of RAM, a min of4GB and a max of 6GB is assigned to SQL server.I'm trying to work out whether we've assigned enough or toomuch/little memory to SQL server. My first thought was to let SQLdymanically manage its own memory and see how much it uses, of coursewhen AWE (/3GB /PAE) is enabled it will just use all that isavailable.In perfmon "target server memory" = 6.1GB, "total server memory" =6.1GB, "total pages" = 768000 ( x 8KB = 6.1GB).My second thought was to use "total pages" - the average "free pages"= average mem used, therefore giving me the average amount of memoryused by SQL. I found out that SQL uses a min of 4GB (the min weassigned) and the max of all the memory, 6GB.Is there an easier way of finding out how much memory is actually usedin this situation or is going by the above average the best way?What i'm unsure about is will SQL just use all memory assigned to ituntil it has the whole DB in memory? 20GB including indexes etc....Any help would be greatly apprechiated.
View 2 Replies View RelatedHow To Determine When And If SQL Agent Job Will Run Again?
Dec 1, 2006I need to determine when (maybe) and if (definitely) a SQL Agent job will run again. I need to maintain a table of the next pending execution for each job. I need to be able to update this table from within a SQL Agent job, but preferably from within an executing SSIS package in the job. Is this possible and if so, any suggestions on how?
Thanks
Determine That This Is Deterministic
May 3, 2008Hi all... This is the definition on the M/S site:
"Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same."
Good... straight forward, right? Ok.... try entering thses command seperately:
Create Table Readings (ReadingDate DateTime Not Null);
Create Function [dbo].[funct_SameDate](@datReadingDate Datetime)
Returns DateTime
As
Begin
return @datReadingDate;
End
Alter table Readings add
[TempColumn] as (dbo.[funct_SameDate](ReadingDate)) PERSISTED NOT NULL;
Error on last command returns:
Computed column 'TempColumn' in table 'Readings' cannot be persisted because the column is non-deterministic.
Can someone please explain this to me? The same value is always being returned.
This does work:
Create Table Readings (ReadingDate DateTime Not Null);
Alter table Readings add [TempColumn] as (ReadingDate) PERSISTED NOT NULL;
I obviously want to do more things inside the function, but I can't get by the first step.
Any suggestions?
Thanks!
Forch