Disabling Triggers

Feb 15, 2001

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.

View 3 Replies


ADVERTISEMENT

Disabling Triggers From Stored Procedures

Mar 8, 2004

Hi,

Is it possible to disable a trigger from a Stored Procedure? If it is, how do you do it?


Thanks,


Federico

View 4 Replies View Related

Multiple Triggers On A Table Or Encapsulated Triggers

May 12, 2008

This isn€™t an problem as such, it€™s more of a debate.

If a table needs a number of update triggers which do differing tasks, should these triggers be separated out or encapsulated into one all encompassing trigger. Speaking in terms of performance, it doesn€™t make much of an improvement doing either depending upon the tasks performed. I was wondering in terms of maintenance and best practice etc. My view is that if the triggers do totally differing tasks they should be a trigger each on their own.

www.handleysonline.com

View 12 Replies View Related

Trigger - Disabling

May 18, 2001

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

View 2 Replies View Related

Job Is Disabling Every Night

Apr 12, 2002

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

View 2 Replies View Related

Disabling Replication

Jan 9, 2003

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

View 1 Replies View Related

Disabling The Trigger

Mar 25, 2003

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

View 1 Replies View Related

Disabling Distribution DB

May 28, 2008

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?

View 1 Replies View Related

Disabling Job Using VBScript

Jun 6, 2008

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

View 10 Replies View Related

Disabling Primary Key

Jul 20, 2005

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 Related

Disabling All Logins

Dec 4, 2007

Hi 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.

View 6 Replies View Related

Queue Not Disabling

Oct 11, 2006

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

View 3 Replies View Related

Disabling The TransactionLog

Sep 20, 2006

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?

View 5 Replies View Related

Constraint Disabling

Dec 20, 2006

Hi,

Is there any way to disable/enable constraints on the table?

Command "ALTER TABLE table_name {CHECK|NOCHECK} CONSTRAINT ALL" produces error.

View 8 Replies View Related

Disabling Foreign Key Constraints

Dec 22, 2000

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.

View 1 Replies View Related

Disabling Publication Using SQL DMO. URGENT!!!!!!!!

Oct 29, 1999

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

View 2 Replies View Related

Disabling FOREIGN KEY Constraint...

Jan 11, 2007

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.

View 4 Replies View Related

Disabling Named Pipes

Jul 23, 2005

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 Related

Disabling A Task At Runtime

Oct 1, 2007

Hi 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

View 12 Replies View Related

Disabling Export Formats

Apr 2, 2008



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?

View 2 Replies View Related

Any Script For Disabling All The Constraints On All The Tables In A Db?

Apr 21, 2004

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,

View 4 Replies View Related

Disabling Constraint For Bulk Loading

Dec 7, 2005

Greetings,I want to bulk load data into user defined SQL Servertables. For this i want to disable all the constraints on all the userdefined tables.I got solution in one of the thread and did the following:declare @tablename varchar(30)declare c1 cursor for select name from sysobjects where type = 'U'open c1fetch next from c1 into @tablenamewhile ( @@fetch_status <> -1 )beginexec ( 'alter table ' + @tablename + ' check constraint all ')fetch next from c1 into @tablenameenddeallocate c1goNow when i try to truncate one of the tables (say titles) it gives methe following error:Cannot truncate table 'titles' because it is being referenced by aFOREIGN KEY constraint.Can anyone show me the right path? I am working on ASE 12.5TIA

View 4 Replies View Related

Disabling The AUTOCOMMIT Option In MSSQLServer

Jun 8, 2006

Hello,Is there anyway.. we can disable AUTOCOMMIT option in MSSQL serverwhile executing the SQL queries via SQL ANALYZER

View 2 Replies View Related

Enabling Or Disabling Parameters (URGENT)

Nov 13, 2007



I have a report that can accept two parameters name or address. So if the user selects the name the dropdown for address should be disabled. if the user select an address the name dropdown should be disables?

is it possible to do something like this in SQL Report

Thanks

View 3 Replies View Related

SQL Server Express SP2 Disabling SA Account

Apr 16, 2007

I recently updated a default instance of SQL Express to Service Pack 2. When I tried to attach my database I got an error "Login failed for user SA. The user is not associated with a trusted SQL server connection".



I found that my SA account for the instance was locked out. I unlocked it and everything worked again.


This happened to my on two different PC's using a default instance of SQL Express. Does anybody know why SA is being locked out? Thanks in advance.

View 4 Replies View Related

Programmatically Disabling/enabling Drilldowns

Nov 2, 2007



I have a report where a particular row may or may not need to drill down, depending on whether or not there is more than one row grouped within it. I was wondering if there is some sort of property I can access that will enable or disable a drill down. I know where to go to do it in the IDE, but this is something that needs to happen at runtime.

Thanks!

View 2 Replies View Related

Help W/Disabling FK Constraints For Batch Operations

May 2, 2006

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.

View 4 Replies View Related

Selectively Disabling Trigger Recursion

May 3, 2008

I have a bit of an issue with an app I'm working on. The app integrates two different SQL Server applications - both of which employ recursive triggers to some extent. My integration basically serves to establish a link between different tables in the applications, and maintains consistency between the two applications (where common fields exist) by employing INSERT, UPDATE, and DELETE triggers.

In order to prevent infinitely recursive triggers (AppA.TableA's update trigger updates AppB.TableA. AppB.TableA's update trigger updates AppA.TableA...and so on, and so on) I need to be able to somehow selectively prevent these triggers only from executing recursively. For example, if the trigger in AppA is what calls the trigger in AppB, I do not want AppB's trigger to fire (and vice versa).

Further Information:


The apps may be on the same, or different, SQL servers.

I'm fully aware of the database-wide options to disable trigger recursion (ALTER DATABASE), but I can't disable recursion for the balance of the triggers in the databases.

The integration will run on either SQL 2000 or SQL 2005 - and perhaps one server on 2000, and one on 2005 (depending upon the deployment).

I'm perfectly amenable to handling it in the trigger code, if possible, but I'm at a bit of a loss as to how to properly and efficiently manage that.

I know that SQL Server will kill infinitely recursive triggers once it detects them, but that doesn't exactly solve my original problem.
Thanks very much for any input you can offer.

View 3 Replies View Related

Disabling Repl Error (22538)

Feb 2, 2007

Posted - 02/01/2007 : 22:35:02





one week ago, for testing purpose, I set up trans repl, trans repl with updateable, snapshot repl using the same instance.

configuration is like this,

publisher and distributor are on the same server, 2 remote subscribers, one is 2000, the other is 2005.

It works OK, today I am trying to disable the replicaiton, clean up the machine. keep getting the errors:

an exception occurred while executing a T-SQL statement or batch
only replicaiton jobs,or job schedules can be added, modified,dropped or viewed through replicaiton SPs
could not update the distribution database subscription table, the subscription status could not be changed.
changed database context to 'master',(MSSQL SERVER error 22538)


Please help


publisher: 2005 Standard SP2 CTP

View 3 Replies View Related

Disabling Drill Down Functionality For A Column

Dec 12, 2006

Hi,

I am creating an adhoc report using report builde in RS2005, now the created report has few columns and some if them have functonality of drillng diwn but is it possible to have a column where a particular column can not be drilled into >



prashant

View 1 Replies View Related

SQL Server 2005 - Disabling Dependency Check?

Nov 15, 2007

Hi,I have over 200 stored procedures and they have dependencies on each other (not necessarily a cyclic dependency).Is there a way in SQL Server 2005 that I can disable a dependency check while I am executing a script that creates those stored procedures, and then re-enable it after?Thanks. 

View 2 Replies View Related

Disabling A Trigger From A Stored Procedure In Another Database

Nov 16, 2000

I want to disable a trigger on a table in a database from inside a stored procedure in another database.
Can I disable then enable? Do I have to drop then recreate the trigger?
How do I code it? I've tried several ways but I can't get it right...

View 1 Replies View Related

SQL 2012 :: Disabling Indexes For Load Performance?

Sep 22, 2015

I'm trying to improve the loading of some tables with large amounts of data that forms part of an ETL. I was going to try removing any indexes before the inserting to speed up the process, but I had some questions on whether or not I should include the clustered index (assuming one exists).

I was originally planning on including a step to disable all indexes on the destination table using the following:

ALTER INDEX ALL ON MyTable DISABLE

Once the load had finished I'd simply rebuild all the indexes.

should I simply disable the non-clustered indexes?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved