.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

DDL Trigger Question - ADD_ROLE_MEMBER - Error regarding synchronous trigger registration

Posted By:      Posted Date: September 15, 2010    Points: 0   Category :Sql Server
I am getting the following error when trying to create the DDL trigger below.  Why? Msg 1082, Level 15, State 1, Procedure DDL_tr_wb_Add_DB_Role_Member, Line 7 "ADD_ROLE_MEMBER" does not support synchronous trigger registration. Create Trigger DDL_tr_wb_Add_DB_Role_Member ON DATABASE FOR ADD_ROLE_MEMBER AS DECLARE @data XML SET @data = EVENTDATA() Insert Into Security_Audit ( PostTime, LoginName, UserName, [HostName], [Application], [Event], [Command] ) VALUES ( GETDATE(), -- The time of the event CONVERT(nvarchar(100), SYSTEM_USER), -- Current Login CONVERT(nvarchar(100), CURRENT_USER), -- The database user against whose session the event occurred CONVERT(nvarchar(20), HOST_NAME()), -- The workstation name CONVERT(nvarchar(100), APP_NAME()), -- Application name of the current session @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), -- The type of event @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') -- The Transact-SQL command that comprised the event ) Thanks,   Dave

View Complete Post

More Related Resource Links

CLR Trigger with stored procedures - transaction error

Hi, I've developed sample CLR trigger which call stored procedure but if execution of stored procedure fail I got following exception The context transaction which was active before entering user defined routine, trigger or aggregate "CskTest" has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting. The statement has been terminated.  My trigger [SqlTrigger(Event = "FOR INSERT", Name = "CskTest", Target = "Requirement")] public static void HandleRequirement() { using ( SqlConnection conn = new SqlConnection("Context Connection=true") ) { SqlCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "Test"; conn.Open(); try { cmd.ExecuteNonQuery(); } catch ( Exception ex ) { SqlContext.Pipe.Send(ex.Message); } } } I wanna handle this exception in my catch block. How can I do this?

MSMQ Trigger Question

Hi    I am developing a C#  application (Framework ver 3.0) using MSMQ Queues (target windows 2003 server). To quickly summarize, the application first checks for the presence of a specific private queue and will create it if it doesn't exist. If it exists, it will write the information to the queue. What I need to do programatically is set a trigger and rule to run an EXE to manage the created QUEUE. Since we don't know all the queues at design time and also want to eliminate setup issues if we did ,manual queue trigger setup is not an option. I haven't been able to find any guidance or examples on the web for doing this, can someone point me in the right direction.   Regards,    Jim

After INSERT Trigger question - how to use value from last added record

Hi, I have a table where one of the fields is defined like [barCodeID] int IDENTITY(1, 1) NOT NULL - so the value in this column is generated automatically. What I want is, after each INSERT, to write this automatically generated value to another table. The simplest would be to do it with an after insert trigger, but don't know how. How to refer to a value from the last added record in a trigger? Thanks

Error in trigger...


Hello everyone.

I am working on a project using SqlSiteMapProvider.  I have two tables Articles and SiteMap

Articles table:

CREATE TABLE [dbuser].[Articles](
	[ArticleId] [uniqueidentifier] NOT NULL,
	[Title] [nvarchar](500) NULL,
	[Description] [ntext] NULL,
	[CategoryId] [int] NULL,
	[pubDate] [datetime] NULL,
	[Author] [nvarchar](255) NULL,
	[Hit] [int] NULL,
	[Auth] [bit] NULL

And SiteMap Table:

CREATE TABLE [dbuser].[SiteMap](
	[ID] [int] IDENTITY(0,1) NOT NULL,
	[Title] [nvarchar](50) NULL,
	[Description] [nvarchar](512) NULL,
	[Url] [nvarchar](512) NULL,
	[Roles] [nvarchar](512) NULL,
	[Parent] [int] NULL,

When I insert an Article my asp.net page also inserts that articles url and such information into SiteMap table. What I am trying to do is when I delete an Article from my Articles table (from asp.net page) the related row from SiteMap table with a trigger.

My asp.net page inserts the Article info into Sitmap table in this format: 

Dim SMUrl As String = "~/c.aspx?g=" & ddlCategoryId.SelectedValue & "&k=" & BaslikNo.ToString

I mean there is no one column exactly matchin in two

SQL Server 2008 Error creating trigger


I am working in sqlserver 2008 with SP1.  DB mail is setup and works with no issues.  When creating a trigger am getting the following error:

 Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42 profile name is not valid

Not sure where this error is comming from.  Thanks in advance for any help.  The code is below: 

CREATE Trigger TR_BLCargo ON BLCargo


Creating Trigger error


I declared for both @begin_time and @end_time as you see, but why do I get an error saying, 

Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@begin_time".
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@end_time".


DECLARE @begin_time datetime
DECLARE @end_time datetime
DECLARE @from_lsn binary(10)
DECLARE @to_lsn binary(10)

-- Set the start time for the CDC query to 2 minutes ago
SET @begin_time = DATEADD(MI, -2, GETDATE())

-- Make a change to the Production.Product table
UPDATE Production.Product
SET Name = 'AWC Logo Cap (XL)'
WHERE ProductID = 712

-- Get the end time for the CDC query
SET @end_time = GETDATE()

-- Wait for 10 seconds to allow the CDC process to record the change
WAITFOR DELAY '00:00:10'

-- Map the time intervals to log sequence numbers for CDC
SELECT @from_lsn = sys.fn_cdc_map_time_to_lsn_(
    'smallest greater than or equal', @begin_time

SELECT @to_lsn = sys.fn_cdc_map_time_to_lsn(
    'largest less than or equal', @end_time

SQL SErver 2008 Merge Replication: Alter Trigger cause syntax error on subscriber site


I have 2 clustered instances running on SQL Server 2008 SE-64 patch level 10.0.2531.0. These is one DB on these 2 instances (compatibility_level=80)under merge replication. now I need to change one trigger to add "NOT FOR REPLICATION". One publisher site all is ok but on subscriber site it causes Error 102 Severity 15 State 1 Incorrect Syntax near 'dbo'.

After tracing the error in profiler, I captured the incorrect syntax as below:

exec('ALTER TRIGGER [dbo].[trgBusinessEntityAllocationUpdate] on [dbo].[BusinessEntityAllocation] 

obviously, there is an duplicated part of object name. but the script was generated by replication engine. How could it happened? can anyone help?



George the DBA

Trying to trigger workflow for Survey got error" Failed to Start(retrying)"


I created an survey for the department i am working for. I did that at sharepoint designer 2007, then i created workflow and attached that with the survey page, i added conditions and actions when workflow is started upon item is created. But it throws me an error saying" Failed to Start(retrying)".

I dont know what going on , I saw in ablog saying windows sharepoint services 3.0 doesnt support attaching workflow into surveys. I dont know what else can be done to make this possible.

Can you please give me some advices, i am new on this field.

Thank You


Navin Pokhrel

Very Basic Question - Error message running first page


Hi Everyone,

I am running Windows 7 and I have gone into Control Panel and activated all of the IIS Functions.

The web application I am running was downloaded from the net (and has worked on other machines previously).  Once I downloaded it, I moved it into C:/inetpub/wwwroot/code/main.asp

I am a bit of a novice at this!  But I am hoping someone can point me in the right direction!


I get the following error when I try and run the web application through IE:

"An error occurred on the server when processing the URL. Please contact the system administrator. If you are the system administrator please click here to find out more about this error."

(When i click the "click here" button it just goes to the IIS site).


Appreciate any help I can get!




Using a trigger or anything else to populate two tables


Hi! I'm creating an application that's supposed to first add a record to table1, and then get the ID from that record to use when adding a record to table2, to be able to associate these two records with eachother.

The user gets to type in some values that goes to table1, and some values that goes to table2, but before the insert statement for table2 is executed i need the ID from the recently added record in table1. Some dude told me to use a trigger for the autopopulate purpose, but does that really work when i also need to save some values that's user input, and when those values doesn't get saved in table1?

Are there any other way to do this or can i send values to a trigger? I'm new to triggers and stored procedures, i don't have any particular knowledge of this, any help is appreciated!


Regards, Monsterbadboll

how to get id value from the row inserted in a table with thr trigger.


I have a reports table with column report id and and report version. I created a trigger on this table to execute an application to do some processing each time a row is inserted in reports table. I need to pass the new report id execute this application from the trigger. How do I get the report id from the row that was inserted?


execute update timedout expires , table with trigger due to recovery of database


I have table A in database A  , in table A i have trigger to insert record to database B table A, each update and insert of database A -table A, triiger fire and insert record in database B table A, when i try to update some times asp.net application gives error

"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

and then error log says System.Data.SqlClient.SqlException: Database 'database B is being recovered. Waiting until recovery is finished.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean ret

Trying to set update panel trigger programmatically


 I have a recursive sub that finds a dropdown list and sets up an AsyncPostbackTrigger for an update panel. I have tried calling this sub both from page_init and page_load 

If the dropdownlist is within the update panel, eveything works fine. If the dropdown list is not in the updatepanel I get error

A control with ID 'ddlPersonFilter' could not be found for the trigger in UpdatePanel 'up1'. In both cases the sub does find the dropdown list and executes the code to set up the trigger. Thanks for your help.

    Private Sub SetUpdatePanelTrigger(ByVal oControl As Control)
        For Each ctrl As Control In oControl.Controls
            If TypeOf ctrl Is DropDownList Then
                If CType(ctrl, DropDownList).ID = "ddlPersonFilter" Then
                    Dim thisddl As New DropDownList
                    Dim thistrigger As New AsyncPostBackTrigger
                    thisddl = ctrl
                    thistrigger.EventName = "SelectedIndexChanged"
                    thistrigger.ControlID = thisddl.ID
                    Exit Sub
                End If
                If ctrl.HasControls Then
                End If
            End If

web service soap mapper error question

Hi,I'm trying to connect to a web service using excel.I have currently no issues logging into the web service, and running one of the other functions to fetch a full list of data.  Once I am able to log in, the function returns a token, which I use to fetch data.But Currently, I'm getting the following errors one two of the functions:SoapMapper:Restoring data into SoapMapper ViewListResult failed HRESULT=0x80020009: Exception occurred. - Client:Unspecified client error. HRESULT=0x80020009: Exception occurred.Run-time error '-2147221504 (80040000)':SoapMapper:Restoring data into SoapMapper ViewListResult failedAny idea why I might be getting this error?  And also how I can fix it?  Thanks.

Generic Audit Trigger CLR C#(Works when the trigger is attached to any Table)

This Audit Trigger is Generic (i.e. non-"Table Specific") attach it to any tabel and it should work.  Be sure and create the 'Audit' table first though. The following code write audit entries to a Table called'Audit' with columns 'ActionType'    //varchar'TableName'    //varchar'PK'    //varchar'FieldName'    //varchar'OldValue'    //varchar'NewValue'    //varchar'ChangeDateTime'    //datetime'ChangeBy'    //varchar using System;using System.Data;using System.Data.SqlClient;using Microsoft.SqlServer.Server; public partial class Triggers{//A Generic Trigger for Insert, Update and Delete Actions on any Table[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")] public static void AuditTrigger(){SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Contextstring TName; //Where we store the Altered Table's Namestring User; //Where we will store the Database UsernameDataRow iRow; //DataRow to hold the inserted valuesDataRow dRow; //DataRow to how the deleted/overwritten valuesDataRow aRow; //Audit DataRow to build our Audit entry withstring PKString; //Will temporarily store the Primary Key Column Names and Values hereusing (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection{conn.Open();//Open the Connection//Build the AuditAdapter an

How to avoid network problems while calling a webservice in a SQL CLR trigger?

Hi All, My Goal: DB synchronization between SQL server 2005 and Mysql database via web services. I have created a SQLCLR trigger, in which i'm calling a web service to sync/update remote (MYSQL) DB over a specific constraints. After getting the acknowledgment from web service i'm updating the sync flag to success (In SQL SERVER2005). If any network delay happens i am unable to know whether sync has done successfully or not. How to avoid network dependency here? Is there any reliable queuing mechanism available in sql server 2005 to eliminate dependency over network? I am looking any suitable service/approach in sql server 2005 that can take care of calling web services as asynchronously and update the status of sync flag irrespective of network. Thanks in advance if anyone provide the good approach as step by step in detail.

Trigger that deletes another table's row

Hi, I'm new to making triggers. I have the following trigger wich should delete a row in the Activity_Log table when I delete a row in the Poll_Comments table.... I have an error in my where clause - because it don't delete the specific row.... My trigger:ALTER TRIGGER [dbo].[deletePollCommentsTrigger] ON [dbo].[Poll_Comments] FOR DELETE AS BEGIN DECLARE @intRowCount int SELECT @intRowCount = @@RowCount IF @intRowCount > 0 BEGIN DELETE Activity_Log WHERE id IN (SELECT questionId FROM deleted) AND activityDateCreated IN (SELECT commentsCreateDate FROM deleted) AND activitySectionPId = 8 END END If I change the statement to: DELETE Activity_Log WHERE id IN (SELECT questionId FROM deleted) AND activitySectionPId = 8 It correctly deletes all rows with that specific questionId and where activitySectionPId = 8... But I want also to have a where clause on, that ask for commentsCreateDate = activityDateCreated - so I only delete one row and not all rows... How can I do that? Kind regards,simsen :-) 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend