I am doing a data conversion in which I drop constraints before inserting data and then reapply at the end. I would like to also drop the not null constraints for certain columns, transfer the nulls and then fill them in later before reapplying the constraints. Is there a relatively simple way to drop and reapply the not null constraints?
View 4 RepliesAssuming that I have created relationships (PKs and FKs) on my tables already, does the following statement permanently remove a Foreign Key constraint, or does it mearly disable it?ALTER TABLE myTable NOCHECK CONSTRAINT FK_myForeignKeyGO Also, I can't seem to find out how to temporarily remove the Identity qualifier of a field, and then reset it back as Identity later. Any help?Finally, will a failure of ALTER TABLE affect the @@ERROR variable? Can I check @@ERROR after each ALTER TABLE table statement to see if @@ERROR <> 0?The reason for both of these issues is that I am redesigning an unnormalized database, and I need to write a large script to drop all constraints on all tables, transform the data as normalized into the new table structure, and then re-enable constraints, Identity fields, etc. Thanks.
View 3 Replies View RelatedI think the answer to this question will be something like 'you'll have to re-initialize the subscribers', but I need to ask anyway...
I created a publication where my foreign key constraint we NOT created with NOT FOR REPLICATION. My publication is configured to replicate DDL changes. Is there any way I can drop and re-create the constraint in the publisher and get replication to push the change to the subscribers?
Thanks for your help
Graham
Hello Everyone,
I'm implementing a web-based Employee Self Service product that uses SQL Server as its backend. The product uses a fairly extensive data model which was automatically generated via a custom written schema.
I received a message from the vendor today that states that I need to ADD an additional field to an existing COMPOUND PK in one of their tables.
I wanted to check on one item before I DROP the existing 4 field PK and rebuild it as a 5 field PK.
Since the PK from the table in question is used as a FK in three other tables, will I be allowed to DROP then RECREATE the PK ?
If I do so, will it effect the FKs in the other 3 tables ?
Thanks in advance.
Mike
I made a constraint on a temporary table in a stored procedure but now i can't delete it.
Here's what happened:
I ran this in a stored procedure
CREATE TABLE #TeFotograferen (RowID int not null identity(1,1) Primary Key,Stamboeknummer char(11) ,Geldigheidsdatum datetime, CONSTRAINT UniqueFields UNIQUE(Stamboeknummer,Geldigheidsdatum)
next time i ran the stored procedure it gave me
There is already an object named 'UniqueFields' in the database.
but since the temporary table is out of scope i cannot delete the constraint
I tried
delete from tempdb..sysobjects where name = 'UniqueFields'
and
declare @name
set @name=(SELECT name from sysobjects where id=(Select parent_obj from sysobjects where name='UniqueFields'))
drop table @name
giving me
Ad hoc updates to system catalogs are not allowed.
or
Cannot drop the table '#TeFotograferen__________________________________ __________________________________________________ _________________000000000135', because it does not exist or you do not have permission.
Hi, all.
I am trying to create table with following SQL script:
Code Snippet
create table Projects(
ID smallint identity (0, 1) constraint PK_Projects primary key,
Name nvarchar (255) constraint NN_Prj_Name not null,
Creator nvarchar (255),
CreateDate datetime
);
When I execute this script I get following error message:
Error source: SQL Server Compact ADO.NET Data Provider
Error message: Named Constraint is not supported for this type of constraint. [ Constraint Name = NN_Prj_Name ]
I looked in the SQL Server Books Online and saw following:
CREATE TABLE (SQL Server Compact)
...
< column_constraint > ::= [ CONSTRAINT constraint_name ] { [ NULL | NOT NULL ] | [ PRIMARY KEY | UNIQUE ] | REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ]
As I understand according to documentation named constraints should be supported, however error message says opposite. I can rephrase SQL script by removing named constraint.
Code Snippet
create table Projects(
ID smallint identity (0, 1) constraint PK_Projects primary key,
Name nvarchar (255) not null,
Creator nvarchar (255),
CreateDate datetime
);
This script executes correctly, however I want named constraints and this does not satisfy me.
In Microsoft SQL Server, I have a documents table and a table whichcategorizes the documents, which we'll call categories.I tried running UPDATE statements on the categories table previouslyand I ran into a foreign key constraint. The error given was "UPDATEstatement conflicted with COLUMN REFERENCE constraint FK..."So I got rid of the Foreign Key relationship, and tried running anUPDATE statement against the categories table again.I'm now getting the following message:'Cannot UPDATE "categories" because "documents" exists.'There must be something hanging around maintaining that relationship,but I'm not sure where it would be found.I was thinking about dropping the table and then adding it back again,but I'm not entirely sure what that would do.Any help is appreciated in advance.thanks,Geoff
View 3 Replies View RelatedI have a copy of this database table and the first thing I noticed was that the Primary Key was pretty much useless and there were no sensible indexes. Every query hitting this table ended up table scanning.So I thought I would try dropping the existing Primary Key constraint and then creating a more natural key that would make data retrieval quicker (hopefully). I understand that creating a clustered index on this table is going to take a long time as ALL the data will need to be reorganised (I estimate this will take at least 1 hour). However, just dropping the existing Primary Key constraint is taking forever.I can see that the server is doing a lot of disk reading/ writing and the wait type in Activity Monitor is PAGEIOLATCH_EX.I would have thought that just dropping a primary key would not change the data in the table, just delete the associated index.
View 4 Replies View RelatedI am trying to drop the "allow nulls" characteristics on an existing table
column. I know that there are not Nulls currently in this column, nor will
there ever be. How do I get rid of that "allow nulls" checkmark on an
existing table structure? Thanks, Craig.
Can I create a unique constraint on a column that can contain null values?
I need the control of the non null values (must be unique)...null is Ok if there's more than one.
I tried creating a unique constraint but i'm getting the error (duplicate keys <null>)
I have a stored procedure that adds constraints from a variable:
ALTER procedure [dbo].[addMyConst](@txtAuto_ourlim money OUTPUT,@txtGen_ourlim money OUTPUT)ASEXECUTE('ALTER TABLE [dbo].[tbl1] ADD CONSTRAINT DF_auto_ourlim DEFAULT ' + @txtAuto_ourlim + ' FOR Auto_ourlim')EXECUTE('ALTER TABLE [dbo].[tbl1] ADD CONSTRAINT DF_Gen_ourlim DEFAULT ' + @txtGen_ourlim + ' FOR Gen_ourlim')
This works fine unless one of the variables is null or empty. Then I get the error: "Incorrect syntax near the keyword 'FOR'
Evidendly SQL Server Express sees "....DEFAULT + + FOR..." but I don't know.
I've fiddled with this a long time and haven't a clue how to fix it. Any help would be appreciated. Thanks, Steve
hi,
I've done Googling and forum hunting but haven't had success finding a simple answer... My table schema is such that it requires the (int) LinkedItemID field to be nullable but still those fields which are set must not be duplicates. I see constraint is out of question and also identity doesn't seem to fit since I'm not using autofill for this particular field. Is there some other way doing this on Sql Server 2005?
Thank you.
Hi!I have a table with a check constraint. But unfortunately it does notwork like I wanted.CREATE TABLE MAP([R_ID] [T_D_ID] NOT NULL,[R_ID1] [T_D_ID] NULL,CONSTRAINT CHECK_ID1 CHECK (R_ID1 = R_ID OR R_ID1 = NULL),CONSTRAINT [PK_MAP] PRIMARY KEY ([R_ID]))R_ID1 should always have the value of R_ID or NullThe following statements should cause errors:insert into map (R_ID, R_ID1)values(1,2);update map set R_ID1=3 where R_ID=1;But there occur no errors. Does anyone have an idea? It is an SQL Server2000.TIASusanne
View 6 Replies View RelatedAfter adding a Unique constraint to a database I cannot add more than one record with a null value for the constrained field. I've tried both adding the constraint to an empty table as well as a table with multiple null values already in the subject field; both efforts have failed.
According to BOL SQL-7 allows Unique Constraints on fields with Null values. Am I missing a step? I do need to allow nulls in the field yet ensure that when there is a non-null value it is unique.
The SQL statement I've used is: ALTER TABLE tbl_MasterUIC ADD CONSTRAINT uniquesamplenbr UNIQUE NONCLUSTERED (samplenbr)
Thanks for any and all suggestions
Hi all,
I am trying to add a unique index/constraint on a column that allows NULL values. The column does have NULL values and when I try to create a unique constraint, I get the following error.
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 9. Most significant primary key is '<NULL>'.
Are'nt you allowed to create a UNIQUE constraint on a NULL column? Books Online says that you are allowed to create a unique constraint on NULL columns, then why am I getting this error.
Any help would be appreciated.
Thanks,
Amir
Cannot find an answer to this in previous posting, though there are similar topics.
My primary key "ID" requires a value (is not nullable), and not explictly providing it with one when I update a new record gives the following error:
Cannot insert the value NULL into column 'ID', table 'AdClub.mediaq.News'; column does not allow nulls. INSERT fails.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'ID', table 'AdClub.mediaq.News'; column does not allow nulls. INSERT fails.
However, trying to stuff that field with a recordCount+1 value (or any value), I get this error:
Violation of PRIMARY KEY constraint 'Primary Key'. Cannot insert duplicate key in object 'News'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'Primary Key'. Cannot insert duplicate key in object 'News'.
Cannot figure this one out. The value I'm providing for that field is known to be unique, but the SQL Server spits it out each time.
Is there a way to have the ID primary field automatically update with a new value when a new record is generated? This is how I used to do it in Access, but cannot find a similar feature in SQL Server. I'm sure I'm missing something simple, but right now I'm stuck in this "Catch-22" situation.
Please help!
Why we the Unique Constraint doesn't allow the multiple null values in Sql Server?
View 2 Replies View RelatedWe have a database where many tables have a field that has to be lengthened. In some cases this is a primary key or part of a primary key. The table in question is:-
/****** Object: Table [dbo].[DTb_HWSQueueMonthEnd] Script Date: 09/25/2014 14:05:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DTb_HWSQueueMonthEnd](
[Code] ....
The script I am using is
DECLARE@Column varchar(100)--The name of the column to change
DECLARE@size varchar(5)--The new size of the column
DECLARE @TSQL varchar(255)--Contains the code to be executed
DECLARE @Object varchar(50)--Holds the name of the table
DECLARE @dropc varchar(255)-- Drop constraint script
[Code] ....
When I the the script I get the error message Could not create constraint. See previous errors.
Looking at the strings I build
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] DROP CONSTRAINT PK_DTb_HWSQueueMonthEnd
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10)
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] ADD CONSTRAINT PK_DTb_HWSQueueMonthEnd PRIMARY KEY NONCLUSTERED ([Patient System Number] ASC,[Episode Number] ASC,[CensusDate] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
They all seem fine except the last one which returns the error
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'DTb_HWSQueueMonthEnd'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
None of the fields I try to create the key on are nullable.
Is there a way to temporarily disable replication? My problem is that I have pull subscribers that I do not want accessing the database at certain times, so that data updates and backups can occur. Is there a way to make that happen?
View 4 Replies View Related
I and my backup admin have full server, database, and SRS admin rights. We both have several subscription reports active. I need to temporarily disable all subscriptions for today. Of course, I can see my subscriptions, get into them, and stop them. However, from SQL Server Management Studio / Reporting Services, I can see her subscriptions but can not find a way to disable them.
Any ideas?
Guys,
Is there a way to temporarily disable logging into the transaction log.
In our system, we perform purging of our database every night, where the purging consists of 2 steps:
1. For each table, insert the data, to be deleted, into a corresponding "purged" table, to remain there for one day only.
2. For each table, delete the unnecessary data (i.e. same data stored in purged tables in step 1)
During these 2 steps, the transaction log grows, and since we perform the transactional log back up, the back up at that time is huge. We are running a bit low on the hard disk space and I'd like to disable logging into the transaction log when these operations are performed.
I really don't care about being able to recover this data.
I thought that one option is to set the database to simple recovery, then perform the purging of the database, and then change back to full.
However, I think that trans log can grow even if recovery model is simple [although you won't be able to retrieve any changes].
So, is there a way to delete a portion of a table [or insert into it] so that no data is written to a transaction log (I know that we can use TRUNCATE if we need to remove whole table without logging)?
Thanks a lot
I am looking for "substitution variables to temporarily store value" with sqlcmd in sql server . I know the query in oracle, but looking for equivalent query in sql server?
In oracle query is like this:
select *
from tablename
where deptnumber = &department_number;
Enter Value for department_number
I am looking this query in sql serevr2008.
I have synchronous mirroring. Some times I loose connection to witness and mirror servers. These times primary server is down. Is there any way I can change mirroring to asynchronous when primary server is down due to communication break down between witness and mirrored servers? I can break mirroring but to re-establish mirroring, I have to backup and restore on the other side. So if I can change mirroring to asynchronous when primary server is down due to connection breakdown between witness and mirrored server, then when witness and mirror servers come back, I don't need to restore the entire database. Ofcourse I could use asynchronous always but that does not failover automatically. I am thankful to all answers and suggestions. Thanks.
View 3 Replies View RelatedI have an application that dumps massive amounts of data into a database during the installation. My log file always ends up being 30-40GB+ at the end of the install. Can I turn off logging while I do the install and enable it after? What are my options.
View 6 Replies View Relatedis there a way to temporarily remove a record and then putting it back again? in my case I have lots of test patient with last)name ='test' and first_name='test'. I want to remove these test patient temporarily , do my calculation and them put them back.
The table is called "person" and all these test patients are in there including real patient under their last-name, first_name.
Poor Performance Temporarily Cured by Re-booting SQL Server
Please can you help?
SQL Server 2000.
Accessed by VB.Net 2003.
Server set up by SQL consultant (who is no longer available).
We have a regular problem, which is most noticeable when analysing data (rather than data inserts/deletions) for producing xml or csv files or reports, even though the amount of data is relatively small. However structure of database means that there are still quite a lot of records and a lot of links between tables (database is currently being redesigned but in the mean time the problem still exists).
The problem is that access slows down dramatically and in some cases all but stops (one example is a csv file being produced where only a few lines have been output in 30 minutes). In every case the symptoms can be solved by re-booting the SQL server, after which the entire csv file is output in 10 minutes. Obviously though we wish to solve the problem and not just the symptom.
Additional information:
Transaction logs are backed up at 13:00 and in the evening (both periods of very low usage).Full backup is run in the evening and at 13:00 (both periods of very low usage), with integrity check performed prior to full backup. Database is optimized after the full backup including shrinking the database leaving 15% of the data space free.
I am testing Insert, Update etc. and messing up my database with "dirty" data. Is there a way to temporarily drop FOREIGN KEY constraints to truncate a table after testing without dropping the whole table and rebuilding it?
Newbie
I need to run a script in production that adds primary key to a table. Because table is large, I can't run it in one shot, the log file is not so large to accommodate it. Instead, I created a new table with same structure plus new surrogate primary key, and I populate it in a cursor loop.
I already ran it many times in test server, and no problems with that. But the problem will be in production when applications will be accessing this table and try to insert/update it while I am running my loop.
So I am looking for a solution how to block users to access this table, it's OK if they receive an error. Setting database to single user mode will not work because I don't want to block them from all the rest tables in this database.
I have transactional replication configured where the publisher and subscriber are on two different servers. Yesterday a database upgrade was carried out, and the DBA dropped replication by issuing sp_removedbreplication on the published database. The subscription is still set up.
I have two questions:
1 - What is the safest way to temporarily switch off replication without losing the publication or subscription? As far as I was aware (my replication knowledge isn't great), simply disabling the relevant agents would do the job.
2 - I now have the task of creating the publication again (fortunately we have a saved script). If I recreate this publication, will I be able to point the existing subscription at it?
Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you
set ansi_nulls off
go
declare
@inFileName VARCHAR (100),
@inFileSize INT,
@Id int,
@inlanguageid INT,
@inFileVersion VARCHAR (100),
@ExeState int
set @inFileName = 'A0006337.EXE'
set @inFileSize = 28796
set @Id= 1
set @inlanguageid =null
set @inFileVersion =NULL
set @ExeState =0
select Dr.StateID from table1 dR
where
DR.[FileName] = @inFileName
AND DR.FileSize =@inFileSize
AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)
AND DR.languageid = isnull(@inlanguageid,null)
AND DR.[ID]= @ID
)
go
set ansi_nulls on
How I can measure the volume of data created temporarily to replace usage of physical tables in an SQL query.
View 1 Replies View RelatedWe are using SQL CE 3.5 on tablet PCs, that synchs with our host SQL 2005 Server using Microsoft Synchronization Services. On the tablets, when inserting a record, we get the following error:
A duplicate value cannot be inserted into a unique index. [ Table name = refRegTitle,Constraint name = PK_refRegTitle
But the only PK on this table is RegTitleID.
The table structure is:
[RegTitleID] [int] IDENTITY(1,1) NOT NULL,
[RegTitleNumber] [int] NOT NULL,
[RegTitleDescription] [varchar](200) NOT NULL,
[FacilityTypeID] [int] NOT NULL,
[Active] [bit] NOT NULL,
The problem occurs when a Title Number is inserted and a record with that number already exists. There is no unique constraint on Title Number.
Has anyone else experienced this?
Is there a way we can prevent a object owner from dropping his tables. Example:
There is a user called 'tom' who is given create table permissions, 'tom' creates the table completes his dev and then the table is moved into production where 'tom' is still the owner. However, in production, 'tom' does not have the create table privs. Because 'tom' is the owner of the table, he is still able to drop the table in production. This is what I am trying to avoid. I would like to retain 'tom' as the owner and want to take away his create/drop privs.