Any script out there for disabling all the constraints on all the tables in a database?
Since there are more than 100 tables I need to import the data into, is there any generic script that I can use to disable all the constraints and triggers etc on all the tables?
Thanks,
I want to disable foreign key constraints en mass. Is there a way to do this?
I know that I can go into each table and navigate to the Relationship tab of Properties and uncheck the "Enforce relationship for INSERTs and UPDATEs" box, but I'd much prefer to automate this process with a query since there are 160+ tables and probably 200+ relationships to disable.
I figure that sysconstraints my be the ticket, but I will keep experimenting until I get the right solution. In the meantime, any insight to steer me in the right direction is appreciated.
HI all,
I'm trying to have a SProc that will initialize a database for me. This db is in development (I'm primarily writing SSIS packages, atm), and I constantly need to truncate the tables, then re-add a dummy/unknown row (PK/Identity value = 1). Of course, I need triggers not to fire (got that part working), and FK constraints to be bypassed temporarily -- that's the problem.
Here's where I'm at:
----------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[_InitializeDB]
AS
SET NOCOUNT ON
DECLARE @name varchar(255)
DECLARE @sql nvarchar(255)
DECLARE tables CURSOR FOR SELECT [name] FROM [sysobjects] WHERE [type]='U' AND [name]<>'sysdiagrams'
OPEN tables
FETCH NEXT FROM tables INTO @name
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql = 'ALTER TABLE ['+ @name + '] NOCHECK CONSTRAINT ALL'
EXEC sp_executeSQL @sql
SET @sql = 'DISABLE TRIGGER ALL ON [' + @name + ']'
EXEC sp_executeSQL @sql
SET @sql = 'TRUNCATE TABLE [' + @name + ']'
EXEC sp_executesql @sql
BEGIN TRY
SET @sql = 'INSERT INTO [' + @name + '] (Active) VALUES (0)'
EXEC sp_executeSQL @sql
END TRY
BEGIN CATCH
PRINT @sql + ':'
PRINT ERROR_MESSAGE()
END CATCH
SET @sql = 'ENABLE TRIGGER ALL ON [' + @name + ']'
EXEC sp_executeSQL @sql
SET @sql = 'ALTER TABLE ['+ @name + '] CHECK CONSTRAINT ALL'
EXEC sp_executeSQL @sql
FETCH NEXT FROM tables INTO @name
END
CLOSE tables
DEALLOCATE tables
----------------------------------------------------------------------------------
Running this Sproc produces (for the first ref'd table):
Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'Person' because it is being referenced by a FOREIGN KEY constraint.
I want to make a copy of a table and this table has several constraints and
I would like to keep all of them
How should I do it? Thank you
Trying to get all the constraints for particular Tables in my database. I try:
SELECT constraint_name, constraint_type FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
and getting nada?
hey i was just wondering how to display the constraints from a particular table and display all the constraints and wat columns they apply to, someone told me they keyword u use but didnt tell me how to use it :P
View 5 Replies View RelatedThree tables (all new, no data) will receive data from dBase and betransposed into them...All three have auto generated IDENTITY columnsand pk and fk constraints. Can someone provide me with anunderstandible sample?Thanks,Trint
View 3 Replies View RelatedDear Experts,When I use a single table I can easily use constraints to enforce mybusiness logic, but what do I do when I normalize a single table intomultiple tables.For example, imagine that my initial table has the columns ID, Name,Salary with the constraint that Salary is not NULL. Now imagine thatI break this into two tables, one with ID and Name and another with IDand Salary. I would like to have a constraint that prevents thecreation of a row with (ID,Name) in the first table unless acorresponding row in the second table is also created.I can enforce this logic with triggers, but it looks ugly and isfairly brittle. Is there a better way or is this the dark side ofnormalization?Thanks.
View 6 Replies View RelatedIs there a way to create tables on the sql ce db that matches the tables I'm pulling from the main sql server?
example;
if I do a pull from Customers and ID is a primary key, how can i create this table on my handheld and have the ID set to primary key as well?
I want to do this on all my tables I need to pull from and create on my sql ce db
Guys,
I have 600 tables in my database, out of which 40 tables are look up value tables. I want generate truncate scripts which truncates all the tables in order of Parent child relationship excluding lookup tables. Is there any way to do this apart from figuring out Parent Child relationship and then writing the truncate statements for each of the table.
For example
EmployeeDetail table references Employee table
DepartmentDetail table references Department table
Department table references Employee table
My truncate script should be
TRUNCATE TABLE DEPARTMENTDETAIL
TRUNCATE TABLE EMPLOYEEDETAIL
TRUNCATE TABLE DEPARTMENT
TRUNCATE TABLE EMPLOYEE
Is there any automated way to figure out parent and child tables and generate truncate script for the same.
Thanks
Hey there,
Trying to build a temporary table based on a parent table such that:select * into #staging from tbl_parent where 1=0
The temp table (#staging) picks up any NOT NULL constraints from the parent (tbl_parent) table and frankly, it doesn't meet my needs.I also tried to build a view of the parent (tbl_parent) table from which to base my SELECT INTO and still, the constraints followed.
I'm thinking perhaps it possble to iterate though the temp table using syscolumns sysobjects and and set the column NULL properties on the temp table AFTER its been created but in this approach, I'm working to avoid directly referencing the columns by name.
Is this possible? A better answer perhaps?
Purpose of exercie is to populate a record in memory before it hits the table.I'll be constructing the record on the fly so that at first, I won't have all the fields to de-Null the columns.Also, I don't wish to lose the fact that some of my fields are Null - once I've fully populated my temp record, I'll insert into my 'real' table and will depend on constraints to throw the appropriate error. In this I won't have to built custom error checking in the stored procedure itself but instead depend on SQL SERVERS built capacity to the throw the error. This opens a bunch of possibilities for extensibility since if at a later date a constraint rule changes, one need only change the parent table definition rather than cracking open the stored procedure.
Thank you for reading and a big thanks to you for replying :)
Also,
Any tool, script, procedure, or otherwise that will solve this problem?
CREATE TABLE [Table 1] (
Id varchar,
TableTwoId varchar,
OtherData varchar )
CREATE TABLE [Table 2] (
Id varchar,
MoreData varchar )
What is the link between these two tables?
I have databases that have zero keys defined, no foreign key constraints, no unique value constraints. I cannot assume that an Identity column is the Id. The end game is be able to output that [Table 1].[TableTwoId] = [Table 2].[Id] but cannot assume that all linkage in the database will be as simple as saying "if the field name contains a table name + Id then it is the Id in that table."
Currently have written a script that will cycle through all columns and start identifying keys in singular tables based on the distinctness of the values in each column (i.e. Column1 is 99% distinct so is the unique value). It will also combine columns if the linkage is the combination of one or more columns from Table 1 = the same number of columns in Table 2. This takes a long time, and it somewhat unreliable as IDENTITY columns may seem to relate to one another when they don't.
CREATE TABLE [Table 1] (
Id varchar,
TableTwoId varchar,
OtherData varchar )
CREATE TABLE [Table 2] (
Id varchar,
MoreData varchar )
What is the link between these two tables?
I have databases that have zero keys defined, no foreign key constraints, no unique value constraints. I cannot assume that an Identity column is the Id. The end game is be able to output that [Table 1].[TableTwoId] = [Table 2].[Id] but cannot assume that all linkage in the database will be as simple as saying "if the field name contains a table name + Id then it is the Id in that table."
Currently have written a script that will cycle through all columns and start identifying keys in singular tables based on the distinctness of the values in each column (i.e. Column1 is 99% distinct so is the unique value). It will also combine columns if the linkage is the combination of one or more columns from Table 1 = the same number of columns in Table 2. This takes a long time, and it somewhat unreliable as IDENTITY columns may seem to relate to one another when they don't.
I know this is probably a flick of a switch but I cannot figure out which switch. Setup is SQL Server / Stored Procedures / DAL / BLL(skipped for testing) / PL. The stored procedure queries from only one table and two columns are ignored because they are being phased out. I can run the stored procedure and preview the data in the DAL but when I create a page with an ODS linked to the DAL and a GridView I get this error. I checked every column that does not allow nulls and they all have values. I checked unique columns (ID is the only unique and is Identity=Yes in the table definition). I checked foreign-key columns for values that are not in the foreign table and there are none. Any ideas why do I get this?
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Hi,
I am getting the above error when trying to load a report into my Web Application, I have tracked the error down to one specific field in my database. Even though this field is a NVarChar field and is of size 30 it would seem that there is an issue returning the value from the field. I can write it into the database no problems but when I try to get it out of the database it returns the above error.
e.g
MOB 401.908.804 - Fails
0401.907.324 - okay
8239 9082 (pager) - fails
Anyone got an idea on how to fix this????
Regards..
Peter.
I am using the "Transfer SQL Server Objects Task" to copy some tables from database A to database B including data.
The tables, primary key constraints, Foreign key, data and all transfers nicely except for "DEFAULT CONSTRAINTS" on the tables.
I have failed to find any option in the "Transfer SQL Server Objects Task" task to explicitly say "copy default constraints". So I guess logically it should happen automatically but it doesn't. I hope it is not a bug :-)
Any option anyone knows will help.
Thanks.
I need to disable a trigger for a week w/o droping it. Is there a way in sql ?
Thanks and nice week-end.
Ivan
Please help if you can! I have setup a job that runs every day on an hourly basis. Every morning I find that it's been disabled. The funny thing is is that the schedule is disabled, but the job is not (you see 'Enabled' in the jobs list in EM, but when you view the schedules tab, it's disabled.) Also, it runs a several times before becoming disabled.
TIA,
Colleen
I have a server that was being used for logshipping and had
replication set up at some point as well. One of the databases got out
of sync in the logshipping process so I removed logshipping and was
going to reinitialize the database and set up the logshipping again.
The database is in read only mode and when I try to take it out of
read only I get the following message:
Error 5063: Database 'XXXXXXXX' is in warm standby. A warm stanby
database is read-only. ALTER DATABASE statment failed. sp_dboption
command failed.
I have tried to disable replication on the server but get the
following error message:
SQL Server Enterprise Manager could not disable 'SRVXXXX' as a
publisher.
Error 3906: Could not run BEGIN TRANSACTION in database 'XXXXXXX'
because the database is read only.
So my problem is that I can't take the database out of read-only mode
because of replication and I can't disable replication because the
database is in read-only mode.
Has anyone come across this before and how should I resolve it? I
tried dropping the database as well and that didn't work either.
Any ideas or help would be greatly appreciated.
Thanks in advance,
Brad
Hi,
I have a update trigger on a table. Can I disable this trigger for duration of a job which updates this table.
Thanks in advance ...j
Is there a way to disable a trigger when performing a transaction besides dropping and recreating the trigger? I am trying to perform an insert on a table, and this keeps firing a trigger that I want to disable.
Any help is appreciated.
I have accidentally registered an existing database as a distribution database, which made it a system database.
the data itself is safe and sound, but I want to undo the whole thing.
How can I do this?
I have an SQL job that runs a .vbs script. It has the following code:
Cmd2.ActiveConnection = Conn2
Cmd2.CommandText = "USE msdb EXEC sp_update_job @job_name = 'Email Download',@enabled = 0"
Cmd2.CommandType = 1
Cmd2.Execute()
It's not disabling the job. If I execute in Query Analyzer, it works. I'm thinking it might be a permissions issue. Any suggestions?
TIA
Hii have a table with primary key defined on col1 and col2. now i want tohave col3 also included in primary key. when i alter the table it givesme error for duplicate rows. there is an option for 'with nocheck' butit only works with check or foreign key constraint. is there any optionin sql server like in oracle 'no validate' which doesnt validate theexisting data and force the data validation from new records.thanxFarid*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 5 Replies View RelatedHi all,
1) Can anybody provide me the script for DISABLING all the jobs in the server and vice versa (i.e.) for enabling also.
2) Script for DISABLING LOGINS and script for ENABLING all Logins.
Its Sql Server 2005
Thanks... Any Help Greatly appreciated.
HI There
My activated proc is rolling back the transaction and putting the message abck on the queue infinately ?
Normally it disabled the queue after a few rollbacks, i can see in the sql log that it just keeps rolling back and re-activating thousands of times.
It only stops when i disable activation on the queue.
WHy is the queue not disabling ?
Thanx
I have an SSIS task that transforms 70 million rows of data nightly.
The data is dumped and imported fresh every day.
Obviously I don't want my production Transaction Log getting cluttered up with 70 million rows of transaction logs daily.
Is there any way to avoid logging to the transaction log with an SSIS task?
Hi,
Is there any way to disable/enable constraints on the table?
Command "ALTER TABLE table_name {CHECK|NOCHECK} CONSTRAINT ALL" produces error.
Hi,
I'm using DMO (SQLOLE65.dll) to programmatically replicate selected publications. The Publication object supports a property called Enabled, which can be set to FALSE. I'm setting the enabled property to TRUE for those publications that need be replicated, and make the others FALSE. Still, all publications get replicated. [I give DoAlter to commit the changes i made.]
Any solution,Please mail me ASAP to venkateswaranb@synectics.soft.net
Thanks,
Venkat
sql 2000, northwind databse
code in sql analyzer:
Code:
ALTER TABLE categories NOCHECK CONSTRAINT ALL
TRUNCATE TABLE categories
ALTER TABLE categories CHECK CONSTRAINT ALL
but still i am getting the error:
Quote: Server: Msg 4712, Level 16, State 1, Line 2
Cannot truncate table 'categories' because it is being referenced by a FOREIGN KEY constraint.
what i need to do is write a query that just cleans all the tables of all data.
In the process of doing some routine monitoring/clean-up we'vediscovered that several (many?) users are apparently set to access ourSQL Server 2000 database instances via the Named Pipes protocol. Inreadings and recommendations we've decided that our WAN would be bestserved if we use the less "chatty" TCP/IP.As such we've also decided to try to enforce this decision to useTCP/IP exclusively using the domain login script used by all of ourend-users.Question: does anyone know what registry entries are created/used toindicate that TCP/IP is enabled and is the default protocol for SQLServer 2000? Our environment is: XP Pro SP2 and SQL Server 2000(typically SP3).TIAGlenn - newbie DBA
View 5 Replies View RelatedHi all,
Here is my situation. I need to disable a task at runtime. I have a script task that configures a boolean variable at runtime and sets its value to either true or false based on a condition. And also i have already set "disable" property of the component to get value from the boolean variable. The problem here is that the component gets the default value which we give during variable creation instead of getting the configured value.
Am i missing out anything. thanks in advance.
Regards,
Praveen
I'm currently using SQL Server 2000 and SSRS 2000 with the latest Service Packs.
I need to disable Excel Exports for a single report.
I've found a way to disable Exporting Formats but it disables for ALL REPORTS on the server.
This involves changing the rsreportserver.config file.
http://blogs.digineer.com/blogs/jasons/archive/2006/05/10/93.aspx
Another site mentions a way to disable Exporting Formats for a single Report.
http://mikemason.ca/2007/04/30/
Code Snippetusing System.Reflection;
using Microsoft.Reporting.WebForms;
using Microsoft.SqlServer.ReportingServices2005.Execution;
namespace MyProject
{
public class ServerReportDecorator
{
private readonly ServerReport serverReport;
public ServerReportDecorator(ReportViewer reportViewer)
{
this.serverReport = reportViewer.ServerReport;
}
public ServerReportDecorator(ServerReport serverReport)
{
this.serverReport = serverReport;
}
public void DisableUnwantedExportFormats()
{
foreach(RenderingExtension extension in serverReport.ListRenderingExtensions())
{
if(extension.Name == "XML" || extension.Name == "IMAGE"
|| extension.Name == "MHTML")
ReflectivelySetVisibilityFalse(extension);
}
}
private void ReflectivelySetVisibilityFalse(RenderingExtension extension)
{
FieldInfo info = extension.GetType().GetField("m_serverExtension",
BindingFlags.NonPublic
| BindingFlags.Instance);
if (info != null)
{
Extension rsExtension = info.GetValue(extension) as Extension;
if(rsExtension != null)
{
rsExtension.Visible = false;
}
}
}
}
}
I'm looking for further clarification of this process.
1. Where I can download the Microsoft.SqlServer.ReportingServices2000 dll?
2. Does anyone have a project example of the process? How/When to actually use the code quoted?