Disabling Triggers through VB6 & MS SQL 7

[seosamh]

Hi

I need to temporarily disable and then reenable some triggers. This is not a problem when done through the MSSQL 7 query analyzer. However, when I attempt to disable these through an application written in VB6 I am presented with

run-time error '-2147217900 (80040e14)'... Only the owner or members of the sysadminrole can alter table 'xxxx'

I have used "sp_addsrvrolemember 'domain\user', 'sysadmin'", but to no avail. I have given every permission possible, but no joy either. What do I need to do?

Any help would be much appreciated

Seosamh

 

[Bernadette]

HI Seosamh,

You will have to use SQLDMO to do this.
Try the following from the following url

http://www.swynk.com/friends/warren/dmotrigger.asp

....

Enable Trigger
---------------
Dim oServer
Dim oTable
Dim oTrigger
dim DBName

'change this to your database name
DBName="PUBS"

Set oServer = createobject("SQLDMO.SQLServer&quot

oServer.LoginSecure = True
oServer.Connect

For Each oTable In oServer.Databases(DBName).Tables
For Each oTrigger In oTable.Triggers
oTrigger.enabled=true
Next
Next

oServer.DisConnect
Set oServer = Nothing


Disable Trigger
-----------------
Dim oServer
Dim oTable
Dim oTrigger
dim DBName

'change this to your database name
DBName="PUBS"

Set oServer = createobject("SQLDMO.SQLServer&quot

oServer.LoginSecure = True
oServer.Connect

For Each oTable In oServer.Databases(DBName).Tables
For Each oTrigger In oTable.Triggers
oTrigger.enabled=false
Next
Next

oServer.DisConnect
Set oServer = Nothing


Bernadette