SQL2000: Disable Trigger Not Allowed In Tables That Involved In Publication.
Apr 10, 2007
We have setup a replication in SQL2000:
We have DTS package automatically pouring data into the publishing database(source tables). During this process, we want to temporary disable certain triggers. However, the command
Alter table 'tbl' disable trigger 'abc' errored out. The error message said:
''Cannot alter the table 'tbl' because it is being published for replication."
I've digged more into this and found although it's not allowed to disable a triggers,
the SQLServer do allow delete the trigger and recreate them.
Is there any way to disable the trigger directly?
Thanks in advance,
Don
BTW:
I've used the following sql directly, however the trigger still fires.
UPDATE
sysobjects
SET
status = status|2048
WHERE
type = 'TR'
AND
parent_obj = OBJECT_ID (@table_name)
The only other way around now is to create stored procedures that dynamically create the trigger. Because our trigger is normmally larger than 8000 bytes. We have to create one stored procedure per trigger. This option is not acceptable because not only it takes quite a time, but also a maintainance nightmare.
Has anyone had problems with this limitation before or could explain to me what the purpose of this limit is for? It obviously does allow you to publish more than 246 (we have been publishing more than 700 for years). Is this new with SQL 2005? Is my publication going to stop one day and tell me there are too many articles?
Hi all, I am brandnew with SQL2000, so sorry in advance forstupidities....SQL2000, SP3, running on a SBS2003-server with WindowsSharePointServices.I want to make a dataconnection (from within the LAN with InfoPath) froma client to this server. I got the error that whether the server doesnotexists (it does) or no permission. Where can I find (in SQL2000) whichusers have access to the databases ???Help is appreciated, Ger.*** Sent via Developersdex http://www.developersdex.com ***
I'm experiencing a problem I think I should not in my COM+ application. I will describe the setup first and then will expose the problem.
It's a simple COM+ application (dll). Inside it, there's a method to save an object A. Object A is persisted in a table in SQL Server 2000 that uses an identity field for the primary key. What this method does is the following: 1) Insert the record for Object A via ADO 2) Retrieve the Id for the object using SCOPE_IDENTITY via ADO and set it on the object 3) Execute an UPDATE statement based on a certain condition via ADO (this UPDATE statement will fire a trigger, however the trigger will not do anything since the record does not answer the criteria for the trigger) 4) Insert a record for another Object A via ADO 5) Retrieve the Id for the object using SCOPE_IDENTITY via ADO and set it on the object
When I get to step (5), an error is raised because SCOPE_IDENTITY returns NULL. This is as if it was returning the Identity value for the trigger that did not cause any INSERT on the UPDATE statement in (3). All the steps are performed using a single connection.
The trigger will duplicate the updated record in another table if a certain flag is set, so in my case, it was not set yet.
It's just weird that this would happen. If I delete the trigger, everything works fine. @@IDENTITY gives me the same problem. It's really as if the trigger was taking over or something and unless I put something between the two steps I get this error. There's one thing though. In step (3), I was using the adCmdText flag for the ADO statement. If I use adExecuteNoRecords it works fine. However I don't understand why it would be this way, I'm trying to understand why it's not working to begin with, even though the sequence of the steps performed should.
I've got the following problem: Our product is delivered with SQL2000 and SQL2005.
Now, there are some schema changes, which I'd like to deploy with T-SQL on the publishers. With SQL2000 I do it with the sp_addmergecolumn etc and on SQL 2005 (if replication compatibility level is 90) with replication of DDL. So far so good.
But how can my T-SQL Script determine, wether the replication of DDL is on? I know there exists sp_helpmergepublication, but how do I get the column replicate_ddl of the result-set?
Say I have app, I want to know when I perform any operation like logging, changing username etc, can I get all the tables involved for that particular operation.
Hi,The following request select a constraint from TABLE_CONSTRAINT withthe name specified in the where clause:select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS whereconstraint_name = 'FK__51OtherParties__51Claims'It returns:http://graphicsxp.free.fr/constraint.JPGSo I have TABLE_NAME that correspond to the first table involved in theconstraint, but how do I get 51Claims ????Thank you
I have a query written that filters on joined table data. The SELECT looks like this:
SELECT * FROM tbl_bol AS a LEFT OUTER JOIN bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN tbl_carrier AS c ON c.carrier_id = a.carrier_id WHERE (a.carrier_name LIKE 'five%') AND (a.accrueamt = 0) AND (a.imported = 1) AND (b.description = 'tendered') AND (a.ship_date BETWEEN '9/1/13' AND '9/30/13') ORDER BY a.bol_number DESC
If I want to do an UPDATE query that uses those filters in the WHERE clause, how do I go about doing that? It doesn't look like you can used joined tables in the UPDATE line like this:
UPDATE tbl_bol AS a LEFT OUTER JOIN bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN tbl_carrier AS c ON c.carrier_id = a.carrier_id SET accrueamt='1348' WHERE (a.carrier_name LIKE 'five%') AND (a.accrueamt = 0) AND (a.imported = 1) AND (b.description = 'tendered') AND (a.ship_date BETWEEN '9/1/13' AND '9/30/13')
Salve, non riesco a disabilitare un trigger su sqlserver nč da queryanalyzer, nč da enterprise manager.In pratica tal cosa riuscivo a farla in Oracle con TOAD, mentre qui nonriesco.Mi interessa disattivarlo senza cancellarlo per poi riattivarlo al bisognosenza rilanciare lo script di creazione.Grazie a tuttiHi I need to disable a DB trigger and I'm not able to do this neither withquery analyzer, neither with enterprise manager.I remeber this job was quite simple using TOAd in Oracle.I'm interested in making it disabled not delete it, without run creationscript.Thanks a lot to everybody.
can I disable a trigger in Sqlserver 2000??? When i run a store procedure who works with one table i want that the trigger doesn“t work it. After that the trigger would be enabled again.
I know i can delete it and create it again but something like "ALTER TRIGGER DISABLED" would be ok.
Sorry to reopen a post, but I'm having the same problem. Worse, I can't change the trigger code right now.
My concerns with disabling triggers are:
1) DISABLE TRIGGER affects all sessions, not just the session doing the mass load 2) DISABLE TRIGGER is permanant until re-enabled, so if the mass load process fails (and if our TRY/CATCH blocks aren't perfect), then the triggers would remain disabled for normal OLTP use. Any ideas on ways around this? I can identify all the work the triggers would have done and do it on my own. The problem is getting them not to do it!
I have a dilema, I need to have a delete trigger enabled to track user deletes to update an external history table. However, when the posting process runs for the table for which the delete trigger runs, all the records from that table are deleted. Th end result is that instead of capturing the specific deletes, it shows all recods being deleted. I know you can disable foreign keys and triggers as a whole, can you do it for specific triggers?
There is a trigger to monitor the modification on a table, and it turn on. For a special duration, I need to turn off this trigger to modify the table. And then turn on the trigger again.
Howdy all. Got a q someone might know something about.
Assume TableA and TableB, identical structure. TableA has an insert trigger.
I want to insert the contents of B into A, but i do NOT want the trigger to fire for my particular insert. During the insert (might be millions of records coping from B to A) i would like the trigger to continue to fire for anyone else that inserts data.
My research suggests that I need to BCP out from B to a file, then BULK INSERT from the file back to A. Anyone have any other ideas?
This was my first thoght, but of course, this will disable the trigger for ALL insert operations, not just mine:
Code Block DISABLE MyInsertTrigger on TableA; GO insert into TableA select * from TableB GO ENABLE MyInsertTrigger on TableA; GO
So, I know, after searching these forums, that it is possible to disable a trigger before updating a table and then enabling it again afterwords, for instance, from a stored procedure.
I might be dealing with hypotheticals here, but when I do a ...
ALTER TABLE table { ENABLE | DISABLE } TRIGGER { ALL | trigger_name [ ,...n ] }
... from a stored procedure, will it not be database wide? Should I worry about another change to the table happening in the timespan in which the trigger is disabled (which it would be for during a single update), which the trigger should have caught?
Is it possible to run a trigger whenever a SQL user is disabled or enabled? From what I've seen of various sysusers and syslogins tables there isn't a column that represents enabled or disabled.
I have transaction replication setup on two SQL7 boxes and a nightly job runs a procedure that need to alter a table and disable a trigger. Since replication has been set up the disable doesn't work. Any way around this.
I have a trigger that executes AFTER INSERT, UPDATE, DELETE. Is there a way to disable and then reenable only the "AFTER DELETE", letting AFTER INSERT, UPDATE act normally?
the disable trigger will be enable again.Because I already disable the trigger on last month, but when I check through the database, it enable again.I understand that, when we restore the database, all the trigger will be enable.How about SQL Cluster? will it enable the trigger??
Is it possible to fire a update trigger from SQL6.5 table to update a SQL2000 server database table field value? (upgrade this 'legacy' to sql2k now is not an option)
If it is not possible, is there a way to accomplish it?
string fileName = "d:\shiporder.xml"; DataSet dataSet = new DataSet(); dataSet.ReadXML(fileName); //connection string string cmd = "INSERT INTO Orders (OrderID, OrderPerson) VALUES (dataSet.Tables[0].Row[0][0].ToString(),dataSet.Tables[0].Row[0][1].ToString); SqlConnection con = new SqlConnection(conection string); SqlCommand mycmd = new SqlCommand(cmd,con); con.Open(); mycmd.ExecuteNonQuery(); con.Close(); it gives me this error dataSet.Tables its not permitted in this context. valid expression are constants, constant expression, . Columns name are not permitted
Hi, I was trying to run an insert statement which has subquery, But it is returning the error like this.. Subqueries are not allowed in this context. Only scalar expressions are allowed. -------------- Is there a way to reparse the insert statement without have to assign the subquery to a temp value and insert it? thanks.. Here is my SQL.. insert into admincriteria values (nextID,(select id from nodetable where description like 'Example - Quality Analytics'),8071,2,'Failures by Customer',2,0,0)
I have 2 dbs (SQL 2012) - one contains a trigger that is enabled/disabled by a procedure in the other database. This all works fine.
If I create a Database Project solution in Visual Studio 2012 SSDT (or 2013) for both databases, the stored procedure generates a SQL71502 stating that my trigger name can't be resolved.
To recreate the issue:
CREATE DATABASE DbWithTrigger GO USE DbWithTrigger GO CREATE TABLE dbo.TblWithTrigger( Id int NULL, SomeValue varchar(30) NULL
1. Create a new solution with a project named DbWithTrigger 2. In project settings set the Target platform to SQL 2012 2. Import the DbWithTrigger db into this project 3. Create a new project named DbCallsTrigger 4. In project settings set the Target platform to SQL 2012 5. Import the DbCallsTrigger db into this project 6. Add a Database Reference in DbCallsTrigger for DbWithTrigger
When you build the solution both dbs build successfully, however there are two warnings. One is easily resolved by replacing DbWithTrigger in the body of the procedure with [$(DbWithTrigger)] (db variable name for the reference) but I can't find out how to get rid of the other. Is it a bug?
We have restored a database on new server without keeping replication settings. Now while creating the publication no tables are shown in new publication wizard window. In fact we have hundred of tables in database and included in replication on source server from where backup was taken.
What may be the reason that tables are not appearing?
Is there a way to disable to automatic renaming of source tables by the View editor? (SLQ Server 2008 R2)
I have a view that is using several sub-queries (including a "rank/partition") and even though each sub-query is contained in ( ) and given an alias, the view designer automatically adds the _1, _2 to all of the tables it thinks are duplicates, which then invalidates the and explicit field calls in CASE WHEN statements ...
