Disabling/enabling of trigger & distributed transaction

I've 2 Windows 2000 server running each own instance of SQL2000. I've setup
both linked servers @. both end.
At server A, it'll call a sp in server B, whereby this sp will update server
B tables based on server A's data. And the server A table A will trigger
back to server B.
However due to some business logic and distributed transaction don't allow a
loopback operation, is there any method the trigger can be bypassed?
I can't drop this trigger as it need to be maintained when other process
access this table.
My question is, can a disable/enable trigger be issued in this sp?
What if another process updating this table at the same time and need the
trigger to be enabled? Will this process be queued after this completion of
sp?
Please advice on how feasible can trigger be controlled from firing..
Server_A call ServerB.sp
|
V
store procedure @. serverB
begin transaction
-- disable trigger
insert into serverB.dbB.dbo.tableB
select * from serverA.dbA.dbo.tableA where key=X
commit transaction
-- enable trigger
|
V
serverB.dbB.dbo.tableB trigger back to serverA
if record not found in serverA
insert into serverA
else
update into server A
Thanks in advance
KristeHi
You can do an ALTER TABLE with the ENABLE/DISABLE TRIGGER, but, the
disabling is for all connections.
The only way you could do this is to have the trigger look up in a table
what connections it can fire for. Not elegant, but might be workable.
Regards
Mike
"Kriste L" wrote:

> I've 2 Windows 2000 server running each own instance of SQL2000. I've setu
p
> both linked servers @. both end.
> At server A, it'll call a sp in server B, whereby this sp will update serv
er
> B tables based on server A's data. And the server A table A will trigger
> back to server B.
> However due to some business logic and distributed transaction don't allow
a
> loopback operation, is there any method the trigger can be bypassed?
> I can't drop this trigger as it need to be maintained when other process
> access this table.
> My question is, can a disable/enable trigger be issued in this sp?
> What if another process updating this table at the same time and need the
> trigger to be enabled? Will this process be queued after this completion o
f
> sp?
> Please advice on how feasible can trigger be controlled from firing..
> Server_A call ServerB.sp
> |
> V
> store procedure @. serverB
> begin transaction
> -- disable trigger
> insert into serverB.dbB.dbo.tableB
> select * from serverA.dbA.dbo.tableA where key=X
> commit transaction
> -- enable trigger
>
> |
> V
> serverB.dbB.dbo.tableB trigger back to serverA
> if record not found in serverA
> insert into serverA
> else
> update into server A
>
> Thanks in advance
> Kriste
>
>

Disabling triggers with alter table, execute as?

I'm disabling some triggers in SQL Server 2000 with the following command
launched from another trigger:
ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
I want normal users to be able to run that command. Is there a kind of
"execute as" I can use in SQL Server 2000 to do it?
Thanks.Before I answer: are you sure you want to do this? The entire point of a
trigger is to fire regardless of user control. Be very careful here. I'm
going to lay out an option in 2000, but I don't endorse it's usage (I'm
presenting it simply because I don't know exactly what you're business goal
is - it may be perfectly valid).
Technically you could create a job that disables the trigger, then give
users access to the job and let them call it from within a stored procedure
or such. Be sure not to create a schedule for the job. With a SQL Agent job,
you can control (somewhat) the execution authority.
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||Is that wise?
For the user to execute an ALTER table, they will have to be provided table
ownership privileges. That means that the users can alter the table anyway
they desire -DROP columns, ADD columns, DROP TABLE, etc.
If you are trying to stop nested Triggers, there is another way. Use
sp_configure to turn on/off the Using Nested Triggers configuration option.
(Or use Server properties in Enterprise Manager.)
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||b3nny80y wrote:
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.
Why would you want to do this? Please explain further what you're
trying to accomplish. If you're trying to disable the triggers for
specific users, you can accomplish the same effect by putting IF/ELSE
code in the trigger that checks the current username, and simply doesn't
execute the code in the trigger.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Disabling triggers with alter table, execute as?

I'm disabling some triggers in SQL Server 2000 with the following command
launched from another trigger:
ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
I want normal users to be able to run that command. Is there a kind of
"execute as" I can use in SQL Server 2000 to do it?
Thanks.Before I answer: are you sure you want to do this? The entire point of a
trigger is to fire regardless of user control. Be very careful here. I'm
going to lay out an option in 2000, but I don't endorse it's usage (I'm
presenting it simply because I don't know exactly what you're business goal
is - it may be perfectly valid).
Technically you could create a job that disables the trigger, then give
users access to the job and let them call it from within a stored procedure
or such. Be sure not to create a schedule for the job. With a SQL Agent job,
you can control (somewhat) the execution authority.
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||Is that wise?
For the user to execute an ALTER table, they will have to be provided table
ownership privileges. That means that the users can alter the table anyway
they desire -DROP columns, ADD columns, DROP TABLE, etc.
If you are trying to stop nested Triggers, there is another way. Use
sp_configure to turn on/off the Using Nested Triggers configuration option.
(Or use Server properties in Enterprise Manager.)
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||b3nny80y wrote:
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.
Why would you want to do this? Please explain further what you're
trying to accomplish. If you're trying to disable the triggers for
specific users, you can accomplish the same effect by putting IF/ELSE
code in the trigger that checks the current username, and simply doesn't
execute the code in the trigger.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Disabling triggers not involved in replication in SQL 2000

I am experimenting with replication and recently set up a prototype
transactional replication with about a dozen publications and perhaps twice
as many articles. All has gone smoothly so far. One item that is causing
much gnashing of teeth is the fact that I cannot disable a pre-existing
trigger that has nothing to do with the replication.
Msg 4929, Level 16, State 2, Line 1
Cannot alter the table 'XXXXX' because it is being published for replication.
Any help greatly appreciated.
Craig
Craig,
you won't be able to do this sort of statement: "alter table suppliers
disable trigger all" but you can issue an alter trigger statement. If there
are only a few triggers this might be practical for you.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Disabling triggers from stored procedures

Hi,
Is it possible to disable a trigger from a Stored Procedure? If it is, how do you do it?
Thanks,
FedericoALTER TABLE [MyTable] DISABLE TRIGGER trMyTrigger|||Just curious as to why you would want to do this?|||Yes, it is possible to disable the trigger, but keep in mind that you are disabling that trigger for everyone using the table, not just for what the trigger is doing. I don't know of any way to allow a trigger to function for some users/spids and not for others, except to include code within the trigger to make it decide that it shouldn't run under some conditions.

-PatP|||If SQL Server 2000 and one can alter the trigger then one can use SET CONTEXT_INFO in SP and then check master..sysprocesses from trigger.

Might also insert DBCC INPUTBUFFER(@.@.SPID) results into temporary table in trigger to check for proc name in EventInfo column. Would work in SQL Server 7 and would not require any additional code in SP, but would hurt trigger performance, be kludgy, and require hard-coding proc name(s) in trigger.

Disabling triggers

Hi,
Does ALTER TABLE ... DISABLE TRIGGER ALL guarantee that disabled triggers
won't fire under any circumstances?
MS SQL Server 2000 SP3.
-- Many thanks.Yes, did work for me all the time.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--

Disabling triggers

Hi,
Does ALTER TABLE ... DISABLE TRIGGER ALL guarantee that disabled triggers
won't fire under any circumstances?
MS SQL Server 2000 SP3.
-- Many thanks.Yes, did work for me all the time.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

Disabling Trigger within a trigger?

Hi all,

I would like to disable a trigger within a trigger using...

ALTER TABLE RESIDENTIAL DISABLE TRIGGER UPDATE_SYSTEMS_RES

However it tells me that I cant disable a trigger within one. How can I get around this or is there another way of disabling a trigger.

Thanks
AnthonyYou will need to alter the code of your trigger to test if the trigger should do it's thing or bail. To my knowledge there is no way to disable a trigger from fireing while inside a trigger.

Disabling the TransactionLog

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?Are you using bulk insert or are you running 70 million insert statements? There is a varitety of ways to truncate / shrink the transaction log after you are done processing records.|||

Greg Van Mullem wrote:

Are you using bulk insert or are you running 70 million insert statements? There is a varitety of ways to truncate / shrink the transaction log after you are done processing records.

I am going to be using bulk inserts.|||

You can either put your database into simple recovery which will provide 0 recovery and then put back into "full" mode and do a full backup after your finished.

With "simple" you will only be able to recover from your previous backup up to the point you flipped into simple mode

The other option is to use BULK Logged mode. This mode logs extent allocations, once completed you should put your database back into full mode at which point when you back up the log all pages/extents changed during the bulk logged period are written to the backup.

Your transaction log will always grow until a in simple mode a checkpoint occurs at which point it is emptied of commited transactions, or in bulk logged/full the transaction log is backed up. If you have 1 transaction for all the work then the transaction log will need to be big enough to handle all the changes made in the transaction

|||Have you consided solving this problem with a hardware purchase instead of software tweaks? Buy big disk drives and let the transaction log explode in size. If it's too slow then buy faster disk drives or servers.

This is often the most cost effective approach these days. More often than not, when I run the numbers on an issue like this, an expensive hardware purchase turns out be cheaper, faster and easier! Just a thought.

Later,
Greg Van Mullem|||

Use the batchsize parameter (I hope bulkinsert has one, as the bcp utility has one). This will leasd into a checkpoint. If you now define an alert for the database (log is used by e.g. 75 percent) and you define a job which will dump the transaction log, you should have no problems with it.

Maybe you have to deal with the percent value. That's it.

The advantage is, that you do not change the recovery model of the database and even your inserts will be part of the backup.

Regards

Norbert

disabling the SQLAgentCmdExec account

Is it ok to disable the SQLAgentCmdExec account' would the sql service
run nonetheless' I'm looking for better ways of securing this
account... I already changed the password to a strong one.
ThanksIn SQL2K I think the SQLAgentCmdExec account is used for both xp_cmdshell an
d
the global proxy account in Agent. You could disable this and the service
should still run but you might get errors when using xp_cmdshell or executin
g
a command exec job when your not a sysadmin.
Rob Walters
Program Manager - SQL Server
"cesar.ponce@.gmail.com" wrote:

> Is it ok to disable the SQLAgentCmdExec account' would the sql service
> run nonetheless' I'm looking for better ways of securing this
> account... I already changed the password to a strong one.
> Thanks
>

disabling the SQL Server Service (2005)

Hello:
I know that there are times when certain SQL services, like the browser
service, can be disabled when the service is not needed.
I recently read somewhere that there are times when one would want to
actually disable the SQL Server service. Why is that? Isn't the Server
service the service that runs the database engine?
Thanks!
childofthe1980s> Isn't the Server
> service the service that runs the database engine?
Correct. The only reason I can see is if you don't *want* to start the SQL S
erver instance for some
reason, and don't want to be able to start it by mistake...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in messa
ge
news:26F95D22-EFA0-485F-8E29-A426AF4122EC@.microsoft.com...
> Hello:
> I know that there are times when certain SQL services, like the browser
> service, can be disabled when the service is not needed.
> I recently read somewhere that there are times when one would want to
> actually disable the SQL Server service. Why is that? Isn't the Server
> service the service that runs the database engine?
> Thanks!
> childofthe1980s

disabling the SQL Server Service (2005)

Hello:
I know that there are times when certain SQL services, like the browser
service, can be disabled when the service is not needed.
I recently read somewhere that there are times when one would want to
actually disable the SQL Server service. Why is that? Isn't the Server
service the service that runs the database engine?
Thanks!
childofthe1980s> Isn't the Server
> service the service that runs the database engine?
Correct. The only reason I can see is if you don't *want* to start the SQL Server instance for some
reason, and don't want to be able to start it by mistake...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:26F95D22-EFA0-485F-8E29-A426AF4122EC@.microsoft.com...
> Hello:
> I know that there are times when certain SQL services, like the browser
> service, can be disabled when the service is not needed.
> I recently read somewhere that there are times when one would want to
> actually disable the SQL Server service. Why is that? Isn't the Server
> service the service that runs the database engine?
> Thanks!
> childofthe1980s

Disabling the identity column

I have a table that I want to disable the identity column that will be
disabled across different sessions. I cannot use SET IDENTITY_INSERT
table off as it does not persist across columns.
I also do not want to create a tmp table without the identity being
turned on, populate it and then rename the table as this seems a bit
time consuming on a large recordset.
Is there another way?
I will be using SQL server 2000 and/or ado.net
Thanks
Rippo
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!The only way is to drop the column.
AMB
"Rippo" wrote:

> I have a table that I want to disable the identity column that will be
> disabled across different sessions. I cannot use SET IDENTITY_INSERT
> table off as it does not persist across columns.
> I also do not want to create a tmp table without the identity being
> turned on, populate it and then rename the table as this seems a bit
> time consuming on a large recordset.
> Is there another way?
> I will be using SQL server 2000 and/or ado.net
> Thanks
> Rippo
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>|||If you can't SET IDENTITY_INSERT for each session then you'll have to
use a non-identity column instead. Why are you using an IDENTITTY in
this situation? The point of an auto-generated surrogate key is that
you shouldn't care what value is inserted. If you want to control the
values externally then IDENTITY isn't the right solution.
--
David Portas
SQL Server MVP
--

Disabling the flat file destination at run-time?

I noticed that The flat file destination that I am using, creates a blank file everytime even if there are no records processed by the data flow.

I just wanted to know if there is any way to stop this. I dont want to see a chunk of blank files on the disk. (I create the files with a timestamp in the file name so every run creates a new file.)

So I want to implement a logic to stop the creation of these blank files in case there are no records to write into it.

Any suggestion would be of great help.

Regards

Saurabh

One option is to use an Execute SQL in the control flow before the data flow to check the record count, and use a condition on a precedence constraint to the data flow to prevent it from running at all.

Another option is to use a Row Count component in the data flow to capture the number of rows written to the flat file. Set a File System task to delete the file after the data flow, with a precedence constraint that uses the row count to determine whether the File System task should execute.

|||

well the first option is not suitable for me because I cannot determine the number of records to be written beforehand.

second option looks good. I'll do that but again that will be having to do one extra task. if at all there is any way to stop the file creation then that will be great. (I am playing around with a script component with no luck)

Thanks for your response John!

Please get back if you find out any other way...

|||

jwelch wrote:

One option is to use an Execute SQL in the control flow before the data flow to check the record count, and use a condition on a precedence constraint to the data flow to prevent it from running at all.

Another option is to use a Row Count component in the data flow to capture the number of rows written to the flat file. Set a File System task to delete the file after the data flow, with a precedence constraint that uses the row count to determine whether the File System task should execute.

Or to adapt a theme, push the data into a raw file or recordset (http://blogs.conchango.com/jamiethomson/archive/2006/06/28/SSIS_3A00_-Comparing-performance-of-a-raw-file-against-a-recordset-destination.aspx) and then use a conditional precedence constraint (as suggested by John) to decide whether to push the data into your flat file or not.

Plenty of ways anyway.

-Jamie

|||

Sounds good! though me too didn't like to push the intermediate data to the disk, the performance analysis shown by Jamie is really helpful in making a decision.

Thanks for the response. I shall go with this solution insead of adding more complications to my short and sweet package!

Regards

Saurabh

disabling the export option

Hi,

As we can disable the toolbar by url access like '&rc:toolbar=false', I would like to know whether there is any way to disable the export control.

The reason for this is, I dont want to show the exporting option in my reports and I want to show the print and other options in the toolbar.

No, that's not possible, using the "rc" parameter.

Ayzan

|||Is there any other way to do this , I'm opening the report through HTML hyperlink.|||

No,

the export functionality can only be disabled within the config file, disabling the rendering extensions. But this can only be done per se, not per user, per permission set etc, only per Reporting Server Instance.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Disabling the AUTOCOMMIT option in MSSQLServer

Hello,

Is there anyway.. we can disable AUTOCOMMIT option in MSSQL server
while executing the SQL queries via SQL ANALYZERbegin transaction|||On 8 Jun 2006 08:01:15 -0700, Uncle Sam wrote:

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

Hi Sam,

In Query Analyzer (for SQL Server 2000): Tools / Options / Connection
Properties / Set implicit_transactions to ON. This affects only new
connections.
Also in Query Analyzer: Query / Current coonnection pproperties / Set
implicit_transactions ON to affect the current connection.

In SQL Server Management Studio (for SQL Server 2005): Tools / Options /
Query Execution / SQL Server / ANSI / SET IMPLICIT_TRANSACTIONS ON (for
new connections), and Query / Query Options / Execution / ANSI / SET
IMPLICIT_TRANSACTIONS ON (for the current connection).

--
Hugo Kornelis, SQL Server MVP

Disabling SQLISPackage start/finish events sent to eventlog

I'm running my SSIS packages from a scheduler (WindowsService) that I wrote in .Net. For the logging of the SSIS events I only use te SSIS Log provider for SQL Server. Nevertheless, the package run start and stop events (and execution failures) are still logged in the Windows Event Log. Is there a way to stop SSIS from writing this event-log entries?

Hi,

i have a scheduled package that runs every one minute and my eventviewer/application is full of these start/finish events.

i would like to see the resolution for this issue.

TIA

Disabling SQLISPackage start/finish events sent to eventlog

I'm running my SSIS packages from a scheduler (WindowsService) that I wrote in .Net. For the logging of the SSIS events I only use te SSIS Log provider for SQL Server. Nevertheless, the package run start and stop events (and execution failures) are still logged in the Windows Event Log. Is there a way to stop SSIS from writing this event-log entries?

Hi,

i have a scheduled package that runs every one minute and my eventviewer/application is full of these start/finish events.

i would like to see the resolution for this issue.

TIA

Disabling SQL jobs using command line

Hi,
Is it possble to disable a SQL using command line?
If so, pls show me how or direct me to somewhere where I can find the
information.
Thanks in advance
Simply stop the service, see the Screencast on my website for more
detailed information.
In summary you just use the NET STOP command.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Sorry did not read the "job" in the subject:
UPDATE sysjobs
SET enabled = 0
WHERE jobId = 'SomeId'
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Or
calling sp_update_job via SQLCMD or OSQL.
(No I think I am done. :-) )
|||Sure. try:
exec msdb..sp_update_job @.job_name = 'yourjobname', @.enabled=0
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Thanks everyboday. It worked great!!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OmCG5gCUHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Sure. try:
> exec msdb..sp_update_job @.job_name = 'yourjobname', @.enabled=0
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>

Disabling SQL jobs using command line

Hi,
Is it possble to disable a SQL using command line?
If so, pls show me how or direct me to somewhere where I can find the
information.
Thanks in advanceSimply stop the service, see the Screencast on my website for more
detailed information.
In summary you just use the NET STOP command.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Sorry did not read the "job" in the subject:
UPDATE sysjobs
SET enabled = 0
WHERE jobId = 'SomeId'
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Or
calling sp_update_job via SQLCMD or OSQL.
(No I think I am done. :-) )|||Check out sp_update_job.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AppleDopod" <hk_007@.hotmail.com> wrote in message news:OgWwQxBUHHA.4832@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Is it possble to disable a SQL using command line?
> If so, pls show me how or direct me to somewhere where I can find the information.
> Thanks in advance
>|||Sure. try:
exec msdb..sp_update_job @.job_name = 'yourjobname', @.enabled=0
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Thanks everyboday. It worked great!!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OmCG5gCUHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Sure. try:
> exec msdb..sp_update_job @.job_name = 'yourjobname', @.enabled=0
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>