Hey!
Thank you for your answer.
I decided to create a trigger which catches all inserts and on an insert into geminiissues with priority "emergency" the trigger will execute a vbsript (which will then send the email).
But:
Everytime I active the trigger, the issue creation popup of the gemini user interface does not close when I click on "Create". The ticket is created but the window does not close. I investigated a ticket creation process with SQL Profiler and figured out that:
After this command:
exec spexecutesql N'INSERT INTO geminiissues (projectid, issuetypeid, issuepriorityid, issueseverityid, issuestatusid, issueresolutionid, reportedby, fixedinversionid, summary, longdesc, revised, startdate, duedate,
resolveddate, closeddate, issuerisklevelid, userdata1, userdata2, userdata3, percentcomplete, estimatedays, estimatehours, estimateminutes, visibility, visibilitymembertype, parentissueid, originatortype, originatorreply,
originatordata, originatorprocessed) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29); select
SCOPEIDENTITY()',N'@p0 int,@p1 int,@p2 int,@p3 int,@p4 int,@p5 int,@p6 int,@p7 int,@p8 nvarchar(19),@p9 nvarchar(19),@p10 datetime,@p11 datetime,@p12 datetime,@p13 datetime,@p14 datetime,@p15 int,@p16 nvarchar(4000),@p17
nvarchar(4000),@p18 nvarchar(4000),@p19 nvarchar(4000),@p20 int,@p21 int,@p22 int,@p23 int,@p24 int,@p25 int,@p26 int,@p27 int,@p28 nvarchar(4000),@p29
datetime',@p0=165,@p1=9,@p2=3,@p3=2,@p4=17,@p5=1,@p6=1,@p7=NULL,@p8=N'TEST TICKET TOMMY 2',@p9=N'TEST TICKET TOMMY 2',@p10='2010-04-27 18:57:02:000',@p11=NULL,@p12=NULL,@p13='2010-04-27
18:57:02:000',@p14=NULL,@p15=1,@p16=N'',@p17=N'',@p18=N'',@p19=N'',@p20=0,@p21=0,@p22=0,@p23=1,@p24=2,@p25=NULL,@p26=0,@p27=0,@p28=NULL,@p29=NULL
The following error appears when I active the trigger:
exec geminilogmessage @messagetype='Error',@messagesource='Gemini',@logmessage='null identifier
at NHibernate.Engine.EntityKey..ctor(Object identifier, String rootEntityName, String entityName, IType identifierType, Boolean batchLoadable, ISessionFactoryImplementor factory, EntityMode entityMode)
at NHibernate.Engine.EntityKey..ctor(Object id, IEntityPersister persister, EntityMode entityMode)
at NHibernate.Event.Default.AbstractSaveEventListener.PerformSaveOrReplicate(Object entity, EntityKey key, IEntityPersister persister, Boolean useIdentityColumn, Object anything, IEventSource source, Boolean
requiresImmediateIdAccess)
at NHibernate.Event.Default.AbstractSaveEventListener.PerformSave(Object entity, Object id, IEntityPersister persister, Boolean useIdentityColumn, Object anything, IEventSource source, Boolean requiresImmediateIdAccess)
at NHibernate.Event.Default.AbstractSaveEventListener.SaveWithGeneratedId(Object entity, String entityName, Object anything, IEventSource source, Boolean requiresImmediateIdAccess)
at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.SaveWithGeneratedOrRequestedId(SaveOrUpdateEvent event)
at NHibernate.Event.Default.DefaultSaveEventListener.SaveWithGeneratedOrRequestedId(SaveOrUpdateEvent event)
at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.EntityIsTransient(SaveOrUpdateEvent event)
at NHibernate.Event.Default.DefaultSaveEventListener.PerformSaveOrUpdate(SaveOrUpdateEvent event)
at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.OnSaveOrUpdate(SaveOrUpdateEvent event)
at NHibernate.Impl.SessionImpl.FireSave(SaveOrUpdateEvent event)
at NHibernate.Impl.SessionImpl.Save(Object obj)
at CounterSoft.Gemini.DataProvider.x73a34067f4cd229a.xf606eb5e1032d933(BaseEN x6fcb9c1c812a0558, Boolean x8b15d0e18e6888d9, ISession x45b53be31e53d68b)
at CounterSoft.Gemini.DataProvider.x73a34067f4cd229a.xf606eb5e1032d933(BaseEN x6fcb9c1c812a0558, Boolean x8b15d0e18e6888d9)
at CounterSoft.Gemini.DataProvider.x73a34067f4cd229a.xf606eb5e1032d933(BaseEN x6fcb9c1c812a0558)
at CounterSoft.Gemini.DataProvider.xc0186e148e957052.x0dd7228622330700(IssueEN x22c77d60925997e9)
at CounterSoft.Gemini.Business.x95da312ae9372c2e.x0dd7228622330700(IssueEN x22c77d60925997e9)
at CounterSoft.Gemini.Web.Controls.IssueUC.AddIssue()',@username=''
Can you help me to get the meaning out of this error so that I am able to see what the problem is within my trigger? I do not understand why it is disturbing the whole gemini functionality although I am just executing a cmdshell command... doing no inserts, updates, deletes, truncates...just selects and this cmd execution...
My trigger is a table trigger "on insert" in geminiissues.
Thank you very much in advance!
Cheers
jarhead104
Here is the trigger if you need the whole code...
USE [Gemini]
GO
/* Object: Trigger [dbo].[Gemini_SendMailOnTicket] Script Date: 04/29/2010 12:09:36 */
SET ANSINULLS ON
GO
SET QUOTEDIDENTIFIER ON
GO
ALTER TRIGGER [dbo].[GeminiSendMailOnTicket]
ON [dbo].[geminiissues]
FOR INSERT
AS
BEGIN
IF (SELECT issuepriorityid FROM INSERTED) = 3
BEGIN
DECLARE @vPjctCode NVARCHAR(10)
DECLARE @vPjctName NVARCHAR(100)
DECLARE @vID INT
DECLARE @vIssue NVARCHAR(255)
DECLARE @vStatus NVARCHAR(200)
DECLARE @vComment NVARCHAR(3990)
DECLARE @vPriority NVARCHAR(9)
DECLARE @vAssigned NVARCHAR(255)
DECLARE @vStartDate DATETIME
DECLARE @vDueDate DATETIME
DECLARE @vCreated DATETIME
DECLARE @vLink NVARCHAR(255)
DECLARE @HelpProjectID int
SET @vPjctCode = (SELECT projectcode FROM INSERTED INNER JOIN geminiprojects ON INSERTED.projectid = geminiprojects.projectid)
SET @vPjctName = (SELECT projectname FROM INSERTED INNER JOIN geminiprojects ON INSERTED.projectid = geminiprojects.projectid)
SET @vID = (SELECT issueid FROM INSERTED)
SET @vIssue = (SELECT summary FROM INSERTED)
SET @vStatus = (SELECT statusdesc FROM INSERTED INNER JOIN geminiissuestatus ON INSERTED.issuestatusid = geminiissuestatus.statusid)
SET @vComment = (SELECT geminicustomfielddata.fielddata FROM INSERTED INNER JOIN geminicustomfielddata ON INSERTED.issueid = geminicustomfielddata.issueid INNER JOIN geminiprojects ON geminiprojects.projectid = geminicustomfielddata.projectid WHERE geminicustomfielddata.customfieldid = 10)
SET @vPriority = SUBSTRING((SELECT prioritydesc FROM INSERTED INNER JOIN geminiissuepriorities ON INSERTED.issuepriorityid = geminiissuepriorities.priorityid) , 1, 9)
SET @vAssigned = REPLACE((SELECT username FROM INSERTED INNER JOIN geminiissueresources ON INSERTED.issueid = geminiissueresources.issueid INNER JOIN geminiusers ON geminiusers.userid = geminiissueresources.userid),'domain\','')
SET @vStartDate = (SELECT startdate FROM INSERTED)
SET @vDueDate = (SELECT duedate FROM INSERTED)
SET @vCreated = (SELECT created FROM INSERTED)
SET @HelpProjectID = (SELECT projectid FROM INSERTED)
SET @vLink = 'http://some.url/issue/ViewIssue.aspx?id=' + CAST(@vID AS NVARCHAR) + '&PROJID=' + CAST(@HelpProjectID AS NVARCHAR)
IF @vComment IS NULL OR LEN(@vComment) < 1 BEGIN SET @vComment = ' ' END
IF @vAssigned IS NULL OR LEN(@vAssigned) < 1 BEGIN SET @vAssigned = ' ' END
IF @vStartDate IS NULL OR LEN(@vStartDate) < 1 BEGIN SET @vStartDate = ' ' END
IF @vDueDate IS NULL OR LEN(@vDueDate) < 1 BEGIN SET @vDueDate = ' ' END
DECLARE @cmd NVARCHAR(1000)
SET @cmd = 'C:\scripts\GeminiSendMailOnTicket.vbs '
+ '"' + @vPjctCode + '"' + ' '
+ '"' + @vPjctName + '"' + ' '
+ '"' + CAST(@vID AS NVARCHAR) + '"' + ' '
+ '"' + @vIssue + '"' + ' '
+ '"' + @vStatus + '"' + ' '
+ '"' + @vComment + '"' + ' '
+ '"' + @vPriority + '"' + ' '
+ '"' + @vAssigned + '"' + ' '
+ '"' + CAST(@vStartDate AS NVARCHAR) + '"' + ' '
+ '"' + CAST(@vDueDate AS NVARCHAR) + '"' + ' '
+ '"' + CAST(@vCreated AS NVARCHAR) + '"' + ' '
+ '"' + @vLink + '"' + ' '
+ '//B'
EXEC XPCMDSHELL @cmd
--Debugging
Print @cmd
END
END
