AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of qprsoft
qprsoft
Flag for Finland asked on

SQL Insert into giver error: Subquery returned more than 1 value.

Hi Guys,

Im trying to update a table with the following SQL script but I keep getting an error. Any ideas what might be causing this?

Query:

insert into [LMS Notification]
select     AccountBase_dw.Name,Accountbase_dw.AccountID, QPR_DW_2007.LMS_activations_2007.LicenseType, SystemUserBase_dw.InternalEMailAddress,  
                      QPR_DW_2007.LMS_activations_2007.QPRComments, QPR_DW_2007.LMS_activations_2007.UsedBclients,
                      QPR_DW_2007.LMS_activations_2007.UsedDclients, QPR_DW_2007.LMS_activations_2007.UsedPGDclients,
                      QPR_DW_2007.LMS_activations_2007.UsedSCDclients, QPR_DW_2007.LMS_activations_2007.WLT,
                      QPR_DW_2007.LMS_activations_2007.ContactFirstName, QPR_DW_2007.LMS_activations_2007.ContactLastName,
                      QPR_DW_2007.LMS_activations_2007.ContactEmailAddress, AccountBase_dw.StatusCode,QPR_DW_2007.LMS_activations_2007.ProductCode,QPR_DW_2007.LMS_activations_2007.Remote_addr
FROM         AccountBase_dw INNER JOIN
                      SystemUserBase_dw ON AccountBase_dw.OwningUser = SystemUserBase_dw.SystemUserId INNER JOIN
                      QPR_DW_2007.LMS_activations_2007 ON AccountBase_dw.AccountNumber = QPR_DW_2007.LMS_activations_2007.CNumber


Error message:
sg 512, Level 16, State 1, Procedure Licensingevents, Line 21
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

The select query returns multiple lines, but this is needed as the resultset is over 100000 lines .


EDIT: Found out it is the triggers in the database causing the error. Once I disabled them, import worked OK. How could I get around this?
DatabasesSQL
Avatar of undefined
Last Comment
qprsoft
8/22/2022 - Mon
Guy Hengel [angelIII / a3]
2 options:
?  the table LMS_Notification has a trigger (on insert) that raises the error (in which case, post the triggers code)
?  you have some views you query from, and the error is there.

please clarify
Guy Hengel [angelIII / a3]
site note: try to learn/use table aliases in your query:
it won't solve that problem, but it makes queries much more readable
insert into [LMS Notification] 
select ab.Name
   , ab.AccountID
   , qa.LicenseType
   , sub.InternalEMailAddress
   , qa.QPRComments
   , qa.UsedBclients
   , qa.UsedDclients
   , qa.UsedPGDclients
   , qa.UsedSCDclients
   , qa.WLT
   , qa.ContactFirstName
   , qa.ContactLastName
   , qa.ContactEmailAddress
   , ab.StatusCode
   , qa.ProductCode
   , qa.Remote_addr
FROM         AccountBase_dw ab
INNER JOIN SystemUserBase_dw sub 
  ON ab.OwningUser = sub_dw.SystemUserId 
INNER JOIN QPR_DW_2007.LMS_activations_2007 qa 
  ON ab.AccountNumber = qa.CNumber

Open in new window

Guy Hengel [angelIII / a3]
>>>EDIT: Found out it is the triggers in the database causing the error. Once I disabled them, import worked OK. How could I get around this?

please post the trigger code.
Your help has saved me hundreds of hours of internet surfing.
fblack61
qprsoft
ASKER
USE [LMS notifications]
GO
/****** Object:  Trigger [dbo].[Licensingevents]    Script Date: 03/15/2009 17:22:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Licensingevents] ON [dbo].[LMS Notification] AFTER INSERT AS
 
DECLARE @Name varchar(100)
DECLARE @internalEmailaddress varchar(200)
DECLARE @WLT varchar(200)
DECLARE @QPRcomments varchar(200)
DECLARE @Contactfirstname varchar(200)
DECLARE @contactlastname varchar(200)
DECLARE @Contactemailaddress varchar(200)
DECLARE @Licensetype varchar(200)
DECLARE @usedbclients varchar(100)
DECLARE @useddclients varchar(100)
DECLARE @usedscdclients varchar(100)
DECLARE @usedpgdclients varchar(100)
DECLARE @accountid varchar(100)
DECLARE @HEADER varchar(100)
DECLARE @Productcode varchar(254)
DECLARE @Remote_addr varchar(254)


SET @name  = (SELECT name FROM inserted)
SET @internalemailaddress = (SELECT internalemailaddress FROM inserted)
SET @usedbclients = (SELECT usedbclients FROM inserted)
SET @useddclients = (SELECT useddclients FROM inserted)
SET @usedscdclients = (SELECT usedscdclients FROM inserted)
SET @usedpgdclients = (SELECT usedpgdclients FROM inserted)
SET @WLT = (SELECT WLT FROM inserted)
SET @QPRcomments = (SELECT QPRcomments FROM inserted)
SET @Contactfirstname = (SELECT Contactfirstname FROM inserted)
SET @contactlastname = (SELECT contactlastname FROM inserted)
SET @Contactemailaddress = (SELECT Contactemailaddress FROM inserted)
SET @Licensetype = (SELECT Licensetype FROM inserted)
SET @accountid = (SELECT accountid FROM inserted)
SET @Remote_addr = (SELECT remote_addr FROM inserted)
SET @header = ('New QPR Server Licensing event from organization:'+@name+'')
SET @productcode = (SELECT ProductCode FROM inserted)
If @Productcode =0
  BEGIN
    DECLARE @msg varchar(1500)
    SET @msg =    
         
    --// CHANGE THE VALUE FOR @recipients
    EXEC msdb.dbo.sp_send_dbmail @recipients=@internalemailaddress, @body= @msg,  @subject = @header, @profile_name = 'dw'
  END
qprsoft
ASKER
site note: try to learn/use table aliases in your query:
it won't solve that problem, but it makes queries much more readable

OK, I will do so in the future.
qprsoft
ASKER
I removed the @msg, @subject and @body parts from the trigger as most likely the error is not due to those.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Guy Hengel [angelIII / a3]
your trigger code will not "loop" on all the inserted rows, but would only take 1 single row (as in mssql server, the INSERTED table contains ALL of the inserted /updated rows)

to solve, you need to loop:
ALTER TRIGGER [dbo].[Licensingevents] ON [dbo].[LMS Notification] AFTER INSERT AS
SET NOCOUNT ON
DECLARE @work_table TABLE ( r INT identity
, Name varchar(100)
, internalEmailaddress varchar(200)
, WLT varchar(200)
, QPRcomments varchar(200)
, Contactfirstname varchar(200)
, contactlastname varchar(200)
, Contactemailaddress varchar(200)
, Licensetype varchar(200)
, usedbclients varchar(100)
, useddclients varchar(100)
, usedscdclients varchar(100)
, usedpgdclients varchar(100)
, accountid varchar(100)
, HEADER varchar(100)
, Productcode varchar(254)
, Remote_addr varchar(254)
)
 
DECLARE @r int 
DECLARE @Name varchar(100)
DECLARE @internalEmailaddress varchar(200)
DECLARE @WLT varchar(200)
DECLARE @QPRcomments varchar(200)
DECLARE @Contactfirstname varchar(200)
DECLARE @contactlastname varchar(200)
DECLARE @Contactemailaddress varchar(200)
DECLARE @Licensetype varchar(200)
DECLARE @usedbclients varchar(100)
DECLARE @useddclients varchar(100)
DECLARE @usedscdclients varchar(100)
DECLARE @usedpgdclients varchar(100)
DECLARE @accountid varchar(100)
DECLARE @HEADER varchar(100)
DECLARE @Productcode varchar(254)
DECLARE @Remote_addr varchar(254)
 
INSERT INTO DECLARE @work_table 
( Name 
, internalEmailaddress  
, WLT  
, QPRcomments  
, Contactfirstname  
, contactlastname  
, Contactemailaddress  
, Licensetype  
, usedbclients  
, useddclients  
, usedscdclients  
, usedpgdclients  
, accountid  
, HEADER 
, Productcode 
, Remote_addr 
)
SELECT, Name 
, internalEmailaddress  
, WLT  
, QPRcomments  
, Contactfirstname  
, contactlastname  
, Contactemailaddress  
, Licensetype  
, usedbclients  
, useddclients  
, usedscdclients  
, usedpgdclients  
, accountid  
, 'New QPR Server Licensing event from organization:' + name
, Productcode 
, Remote_addr 
FROM INSERTED
WHERE ProductCode = 0
 
WHILE @@ROWCOUNT > 0
  SELECT TOP 1 @r = r
  , @Name = Name 
  , @internalEmailaddress = internalEmailaddress  
  , @WLT = WLT  
  , @QPRcomments = QPRcomments  
  , @Contactfirstname = Contactfirstname  
  , @contactlastname = contactlastname  
  , @Contactemailaddress = Contactemailaddress  
  , @Licensetype = Licensetype  
  , @usedbclients = usedbclients  
  , @useddclients = useddclients  
  , @usedscdclients = usedscdclients  
  , @usedpgdclients = usedpgdclients  
  , @accountid = accountid  
  , @HEADER = HEADER 
  , @Productcode = Productcode 
  , @Remote_addr = Remote_addr 
  FROM @work_table 
 
  IF @@ROWCOUNT > 0
  BEGIN
    DECLARE @msg varchar(1500)
    SET @msg =   'xxx' 
         
    EXEC msdb.dbo.sp_send_dbmail @recipients=@internalemailaddress, @body= @msg,  @subject = @header, @profile_name = 'dw'
 
  END
  DELETE @work_table WHERE r = @r
 
END

Open in new window

qprsoft
ASKER
Im still getting this error,

Msg 156, Level 15, State 1, Procedure Licensingevents, Line 40
Incorrect syntax near the keyword 'DECLARE'.

Trigger now looks like this:

USE [LMS notifications]
GO
/****** Object:  Trigger [dbo].[Licensingevents]    Script Date: 03/15/2009 17:22:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Licensingevents] ON [dbo].[LMS Notification] AFTER INSERT AS
SET NOCOUNT ON
DECLARE @work_table TABLE ( r INT identity
, Name varchar(100)
, internalEmailaddress varchar(200)
, WLT varchar(200)
, QPRcomments varchar(200)
, Contactfirstname varchar(200)
, contactlastname varchar(200)
, Contactemailaddress varchar(200)
, Licensetype varchar(200)
, usedbclients varchar(100)
, useddclients varchar(100)
, usedscdclients varchar(100)
, usedpgdclients varchar(100)
, accountid varchar(100)
, HEADER varchar(100)
, Productcode varchar(254)
, Remote_addr varchar(254)
)
 
DECLARE @r int
DECLARE @Name varchar(100)
DECLARE @internalEmailaddress varchar(200)
DECLARE @WLT varchar(200)
DECLARE @QPRcomments varchar(200)
DECLARE @Contactfirstname varchar(200)
DECLARE @contactlastname varchar(200)
DECLARE @Contactemailaddress varchar(200)
DECLARE @Licensetype varchar(200)
DECLARE @usedbclients varchar(100)
DECLARE @useddclients varchar(100)
DECLARE @usedscdclients varchar(100)
DECLARE @usedpgdclients varchar(100)
DECLARE @accountid varchar(100)
DECLARE @HEADER varchar(100)
DECLARE @Productcode varchar(254)
DECLARE @Remote_addr varchar(254)
 
INSERT INTO DECLARE @work_table
 (Name
, internalEmailaddress  
, WLT  
, QPRcomments  
, Contactfirstname  
, contactlastname  
, Contactemailaddress  
, Licensetype  
, usedbclients  
, useddclients  
, usedscdclients  
, usedpgdclients  
, accountid  
, HEADER
, Productcode
, Remote_addr
)
SELECT, Name
, internalEmailaddress  
, WLT  
, QPRcomments  
, Contactfirstname  
, contactlastname  
, Contactemailaddress  
, Licensetype  
, usedbclients  
, useddclients  
, usedscdclients  
, usedpgdclients  
, accountid  
, 'New QPR Server Licensing event from organization:' + name
, Productcode
, Remote_addr
FROM INSERTED
WHERE ProductCode = 0
 
WHILE @@ROWCOUNT > 0
  SELECT TOP 1 @r = r
  , @Name = Name
  , @internalEmailaddress = internalEmailaddress  
  , @WLT = WLT  
  , @QPRcomments = QPRcomments  
  , @Contactfirstname = Contactfirstname  
  , @contactlastname = contactlastname  
  , @Contactemailaddress = Contactemailaddress  
  , @Licensetype = Licensetype  
  , @usedbclients = usedbclients  
  , @useddclients = useddclients  
  , @usedscdclients = usedscdclients  
  , @usedpgdclients = usedpgdclients  
  , @accountid = accountid  
  , @HEADER = HEADER
  , @Productcode = Productcode
  , @Remote_addr = Remote_addr
  FROM @work_table
 
  IF @@ROWCOUNT > 0
  BEGIN
    DECLARE @msg varchar(1500)


Thanks for your help!
qprsoft
ASKER
site note: try to learn/use table aliases in your query:
it won't solve that problem, but it makes queries much more readable


Im not sure I know how to do this :)
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Guy Hengel [angelIII / a3]
I see:
ALTER TRIGGER [dbo].[Licensingevents] ON [dbo].[LMS Notification] AFTER INSERT AS
BEGIN
 
 .... all the code
 
END

Open in new window

Guy Hengel [angelIII / a3]
ps: just to explain the original error:

SET @name  = (SELECT name FROM inserted)

if INSERTED contains more than 1 row, you will get that error.
qprsoft
ASKER
I was able to modify the code, for the trigger creation to go trough OK, but mow the insert into freezes on the first line. When I press cancel after 1 minute, it says one line affected.. 
USE [LMS notifications]
GO
/****** Object:  Trigger [dbo].[Licensingevents]    Script Date: 03/15/2009 17:22:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Licensingevents] ON [dbo].[LMS Notification] AFTER INSERT AS
SET NOCOUNT ON
DECLARE @work_table TABLE ( r INT identity
, Name varchar(100)
, internalEmailaddress varchar(200)
, WLT varchar(200)
, QPRcomments varchar(200)
, Contactfirstname varchar(200)
, contactlastname varchar(200)
, Contactemailaddress varchar(200)
, Licensetype varchar(200)
, usedbclients varchar(100)
, useddclients varchar(100)
, usedscdclients varchar(100)
, usedpgdclients varchar(100)
, accountid varchar(100)
, HEADER varchar(100)
, Productcode varchar(254)
, Remote_addr varchar(254)
)
 
DECLARE @r int 
DECLARE @Name varchar(100)
DECLARE @internalEmailaddress varchar(200)
DECLARE @WLT varchar(200)
DECLARE @QPRcomments varchar(200)
DECLARE @Contactfirstname varchar(200)
DECLARE @contactlastname varchar(200)
DECLARE @Contactemailaddress varchar(200)
DECLARE @Licensetype varchar(200)
DECLARE @usedbclients varchar(100)
DECLARE @useddclients varchar(100)
DECLARE @usedscdclients varchar(100)
DECLARE @usedpgdclients varchar(100)
DECLARE @accountid varchar(100)
DECLARE @HEADER varchar(100)
DECLARE @Productcode varchar(254)
DECLARE @Remote_addr varchar(254)
 
INSERT INTO @work_table 
 (Name 
, internalEmailaddress  
, WLT  
, QPRcomments  
, Contactfirstname  
, contactlastname  
, Contactemailaddress  
, Licensetype  
, usedbclients  
, useddclients  
, usedscdclients  
, usedpgdclients  
, accountid  
, Productcode 
, Remote_addr 
)
SELECT Name 
, internalEmailaddress  
, WLT  
, QPRcomments  
, Contactfirstname  
, contactlastname  
, Contactemailaddress  
, Licensetype  
, usedbclients  
, useddclients  
, usedscdclients  
, usedpgdclients  
, accountid  
, Productcode 
, Remote_addr 
FROM INSERTED
WHERE ProductCode = 0
 
WHILE @@ROWCOUNT > 0
  SELECT TOP 1 @r = r
  , @Name = Name 
  , @internalEmailaddress = internalEmailaddress  
  , @WLT = WLT  
  , @QPRcomments = QPRcomments  
  , @Contactfirstname = Contactfirstname  
  , @contactlastname = contactlastname  
  , @Contactemailaddress = Contactemailaddress  
  , @Licensetype = Licensetype  
  , @usedbclients = usedbclients  
  , @useddclients = useddclients  
  , @usedscdclients = usedscdclients  
  , @usedpgdclients = usedpgdclients  
  , @accountid = accountid  
  , @Productcode = Productcode 
  , @Remote_addr = Remote_addr 
  FROM @work_table 
 
  IF @@ROWCOUNT > 0
  BEGIN
    DECLARE @msg varchar(1500)
    SET @msg = xxx
   
    --// CHANGE THE VALUE FOR @recipients
    EXEC msdb.dbo.sp_send_dbmail @recipients=@internalemailaddress, @body= @msg,  @subject = @header, @profile_name = 'dw'
    DELETE @work_table WHERE r = @r
  END

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Guy Hengel [angelIII / a3]
well, that might be the simple problem that when you insert 10000 rows into the table, your code is to send out 10000 emails ... can take some time...
qprsoft
ASKER
Yep, could be . But im testing with only 10 rows :)
Guy Hengel [angelIII / a3]
I see the problem:

IF @@ROWCOUNT > 0
  BEGIN
    DECLARE @msg varchar(1500)
    SET @msg = xxx
   
    --// CHANGE THE VALUE FOR @recipients
    EXEC msdb.dbo.sp_send_dbmail @recipients=@internalemailaddress, @body= @msg,  @subject = @header, @profile_name = 'dw'
    DELETE @work_table WHERE r = @r
  END

the DELETE must be AFTER that END:
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
qprsoft
ASKER
Still the same problem occurs, even I change the order or DELETE / END.. :/

IF @@ROWCOUNT > 0
  BEGIN
    DECLARE @msg varchar(1500)
    SET @msg = xxx
   
    --// CHANGE THE VALUE FOR @recipients
    EXEC msdb.dbo.sp_send_dbmail @recipients=@internalemailaddress, @body= @msg,  @subject = @header, @profile_name = 'dw'
  END
DELETE @work_table WHERE r = @r

qprsoft
ASKER
One thing I noticed, in your original code, you had:

  END
  DELETE @work_table WHERE r = @r
 
END


If I add the last END, it throws me an error:

Msg 102, Level 15, State 1, Procedure Licensingevents, Line 119
Incorrect syntax near 'END'.


Sharath S
The last 'END' is not required. 
USE [LMS notifications]
GO
/****** Object:  Trigger [dbo].[Licensingevents]    Script Date: 03/15/2009 17:22:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Licensingevents] ON [dbo].[LMS Notification] AFTER INSERT AS
SET NOCOUNT ON
DECLARE @work_table TABLE ( r INT identity
, Name varchar(100)
, internalEmailaddress varchar(200)
, WLT varchar(200)
, QPRcomments varchar(200)
, Contactfirstname varchar(200)
, contactlastname varchar(200)
, Contactemailaddress varchar(200)
, Licensetype varchar(200)
, usedbclients varchar(100)
, useddclients varchar(100)
, usedscdclients varchar(100)
, usedpgdclients varchar(100)
, accountid varchar(100)
, HEADER varchar(100)
, Productcode varchar(254)
, Remote_addr varchar(254)
)
 
DECLARE @r int 
DECLARE @Name varchar(100)
DECLARE @internalEmailaddress varchar(200)
DECLARE @WLT varchar(200)
DECLARE @QPRcomments varchar(200)
DECLARE @Contactfirstname varchar(200)
DECLARE @contactlastname varchar(200)
DECLARE @Contactemailaddress varchar(200)
DECLARE @Licensetype varchar(200)
DECLARE @usedbclients varchar(100)
DECLARE @useddclients varchar(100)
DECLARE @usedscdclients varchar(100)
DECLARE @usedpgdclients varchar(100)
DECLARE @accountid varchar(100)
DECLARE @HEADER varchar(100)
DECLARE @Productcode varchar(254)
DECLARE @Remote_addr varchar(254)
 
INSERT INTO @work_table 
 (Name 
, internalEmailaddress  
, WLT  
, QPRcomments  
, Contactfirstname  
, contactlastname  
, Contactemailaddress  
, Licensetype  
, usedbclients  
, useddclients  
, usedscdclients  
, usedpgdclients  
, accountid  
, Productcode 
, Remote_addr 
)
SELECT Name 
, internalEmailaddress  
, WLT  
, QPRcomments  
, Contactfirstname  
, contactlastname  
, Contactemailaddress  
, Licensetype  
, usedbclients  
, useddclients  
, usedscdclients  
, usedpgdclients  
, accountid  
, Productcode 
, Remote_addr 
FROM INSERTED
WHERE ProductCode = 0
 
WHILE @@ROWCOUNT > 0
  SELECT TOP 1 @r = r
  , @Name = Name 
  , @internalEmailaddress = internalEmailaddress  
  , @WLT = WLT  
  , @QPRcomments = QPRcomments  
  , @Contactfirstname = Contactfirstname  
  , @contactlastname = contactlastname  
  , @Contactemailaddress = Contactemailaddress  
  , @Licensetype = Licensetype  
  , @usedbclients = usedbclients  
  , @useddclients = useddclients  
  , @usedscdclients = usedscdclients  
  , @usedpgdclients = usedpgdclients  
  , @accountid = accountid  
  , @Productcode = Productcode 
  , @Remote_addr = Remote_addr 
  FROM @work_table 
 
  IF @@ROWCOUNT > 0
  BEGIN
    DECLARE @msg varchar(1500)
    SET @msg = xxx
   
    --// CHANGE THE VALUE FOR @recipients
    EXEC msdb.dbo.sp_send_dbmail @recipients=@internalemailaddress, @body= @msg,  @subject = @header, @profile_name = 'dw'
    
  END
  DELETE @work_table WHERE r = @r

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
qprsoft
ASKER
OK, thanks. Any ideas why the insert into script keeps freezing? ¨

I've tried to figure out the logic in the script provided by angelIII but I dont see the loop there, or I just dont understand the logic behind it...Any help to get this working is much appriciated! :)

If I run this insert into script below with the trigger disabled, it works and updates the table. Then trigger is enabled, it freezes to executing.

insert into [LMS Notification] (name, internalemailaddress, UsedBclients, UsedDclients , UsedPGDclients , UsedSCDclients, ContactEmailAddress,ContactFirstName,ContactLastName,WLT,LicenseType,QPRComments,AccountID,productcode , remote_addr ) values (xxx,'jarkko@test.com,'10','22','33','44',test@test.com,'test,'man','8.1.0','1','test line','A30E7A1C-E45C-DC11-B5B8-0015C5FF9D1F','0','127.0.0.1')

Here is the full script:

Big thanks for the help!
USE [LMS notifications]
GO
/****** Object:  Trigger [dbo].[Licensingevents]    Script Date: 03/15/2009 20:56:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Licensingevents] ON [dbo].[LMS Notification] AFTER INSERT AS
SET NOCOUNT ON
DECLARE @work_table TABLE ( 
  r INT identity
, Name varchar(100)
, internalEmailaddress varchar(200)
, WLT varchar(200)
, QPRcomments varchar(200)
, Contactfirstname varchar(200)
, contactlastname varchar(200)
, Contactemailaddress varchar(200)
, Licensetype varchar(200)
, usedbclients varchar(100)
, useddclients varchar(100)
, usedscdclients varchar(100)
, usedpgdclients varchar(100)
, accountid varchar(100)
, Productcode varchar(254)
, Remote_addr varchar(254)
)
 
DECLARE @r int 
DECLARE @Name varchar(100)
DECLARE @internalEmailaddress varchar(200)
DECLARE @WLT varchar(200)
DECLARE @QPRcomments varchar(200)
DECLARE @Contactfirstname varchar(200)
DECLARE @contactlastname varchar(200)
DECLARE @Contactemailaddress varchar(200)
DECLARE @Licensetype varchar(200)
DECLARE @usedbclients varchar(100)
DECLARE @useddclients varchar(100)
DECLARE @usedscdclients varchar(100)
DECLARE @usedpgdclients varchar(100)
DECLARE @accountid varchar(100)
DECLARE @Productcode varchar(254)
DECLARE @Remote_addr varchar(254)
 
INSERT INTO @work_table 
 (Name 
, internalEmailaddress  
, WLT  
, QPRcomments  
, Contactfirstname  
, contactlastname  
, Contactemailaddress  
, Licensetype  
, usedbclients  
, useddclients  
, usedscdclients  
, usedpgdclients  
, accountid  
, Productcode 
, Remote_addr 
)
SELECT Name 
, internalEmailaddress  
, WLT  
, QPRcomments  
, Contactfirstname  
, contactlastname  
, Contactemailaddress  
, Licensetype  
, usedbclients  
, useddclients  
, usedscdclients  
, usedpgdclients  
, accountid  
, Productcode 
, Remote_addr 
FROM INSERTED
WHERE ProductCode = 0
 
WHILE @@ROWCOUNT > 0
  SELECT TOP 1 @r = r
  , @Name = Name 
  , @internalEmailaddress = internalEmailaddress  
  , @WLT = WLT  
  , @QPRcomments = QPRcomments  
  , @Contactfirstname = Contactfirstname  
  , @contactlastname = contactlastname  
  , @Contactemailaddress = Contactemailaddress  
  , @Licensetype = Licensetype  
  , @usedbclients = usedbclients  
  , @useddclients = useddclients  
  , @usedscdclients = usedscdclients  
  , @usedpgdclients = usedpgdclients  
  , @accountid = accountid  
  , @Productcode = Productcode 
  , @Remote_addr = Remote_addr 
  FROM @work_table 
 
  IF @@ROWCOUNT > 0
  BEGIN
    DECLARE @msg varchar(1500)
    SET @msg = 
    --// CHANGE THE VALUE FOR @recipients
    EXEC msdb.dbo.sp_send_dbmail @recipients=@internalemailaddress, @body= @msg,  @subject = 'TEST', @profile_name = 'dw'
       END
       DELETE @work_table WHERE r = @r

Open in new window

Guy Hengel [angelIII / a3]
can you please post the procedure, as is (apart from the sensitive data)
qprsoft
ASKER
Thanks for your help angel. The Procedure is below:


IF @@ROWCOUNT > 0
  BEGIN
    DECLARE @msg varchar(1500)
    SET @msg = 'New event has occured from '+@contactfirstname + ' '+@contactlastname+', (mailto:'+@contactemailaddress+') from the company: ' + @Name + '. The following details were detected:
    
    version: '+@WLT+'
    
    Amount of licenses:
    Licensed Portal viewers' + @usedbclients + '
    Licensed Portal editors: '+ @Useddclients + '
    Licensed SC D clients: ' + @Usedscdclients + '
    Licensed PG D clients:' + @Usedpgdclients + '
    
    License type:'+ @licensetype+ ' (1=Full license, 2=evaluation license, 3=Transient license)
    
    Link to the organisation: http://crm/xxx/sfa/accts/edit.aspx?id={'+@accountid+'}
    
    Comments: ' +@QPRCOMMENTS+'
    
     Click the link to analyse the licensing address : http://www.db.ripe.net/fcgi-bin/whois?form_type=simple&full_query_string=&searchtext='+@remote_addr+''
     
    --// CHANGE THE VALUE FOR @recipients
    EXEC msdb.dbo.sp_send_dbmail @recipients=@internalemailaddress, @body= @msg,  @subject = 'TEST', @profile_name = 'dw'
       END
       DELETE @work_table WHERE r = @r

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
qprsoft
ASKER
Sorry, here is the full procedure
USE [LMS notifications]
GO
/****** Object:  Trigger [dbo].[Licensingevents]    Script Date: 03/15/2009 20:56:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Licensingevents] ON [dbo].[LMS Notification] AFTER INSERT AS
SET NOCOUNT ON
DECLARE @work_table TABLE ( 
  r INT identity
, Name varchar(100)
, internalEmailaddress varchar(200)
, WLT varchar(200)
, QPRcomments varchar(200)
, Contactfirstname varchar(200)
, contactlastname varchar(200)
, Contactemailaddress varchar(200)
, Licensetype varchar(200)
, usedbclients varchar(100)
, useddclients varchar(100)
, usedscdclients varchar(100)
, usedpgdclients varchar(100)
, accountid varchar(100)
, Productcode varchar(254)
, Remote_addr varchar(254)
)
 
DECLARE @r int 
DECLARE @Name varchar(100)
DECLARE @internalEmailaddress varchar(200)
DECLARE @WLT varchar(200)
DECLARE @QPRcomments varchar(200)
DECLARE @Contactfirstname varchar(200)
DECLARE @contactlastname varchar(200)
DECLARE @Contactemailaddress varchar(200)
DECLARE @Licensetype varchar(200)
DECLARE @usedbclients varchar(100)
DECLARE @useddclients varchar(100)
DECLARE @usedscdclients varchar(100)
DECLARE @usedpgdclients varchar(100)
DECLARE @accountid varchar(100)
DECLARE @Productcode varchar(254)
DECLARE @Remote_addr varchar(254)
 
INSERT INTO @work_table 
 (Name 
, internalEmailaddress  
, WLT  
, QPRcomments  
, Contactfirstname  
, contactlastname  
, Contactemailaddress  
, Licensetype  
, usedbclients  
, useddclients  
, usedscdclients  
, usedpgdclients  
, accountid  
, Productcode 
, Remote_addr 
)
SELECT Name 
, internalEmailaddress  
, WLT  
, QPRcomments  
, Contactfirstname  
, contactlastname  
, Contactemailaddress  
, Licensetype  
, usedbclients  
, useddclients  
, usedscdclients  
, usedpgdclients  
, accountid  
, Productcode 
, Remote_addr 
FROM INSERTED
WHERE ProductCode = 0
 
WHILE @@ROWCOUNT > 0
  SELECT TOP 1 @r = r
  , @Name = Name 
  , @internalEmailaddress = internalEmailaddress  
  , @WLT = WLT  
  , @QPRcomments = QPRcomments  
  , @Contactfirstname = Contactfirstname  
  , @contactlastname = contactlastname  
  , @Contactemailaddress = Contactemailaddress  
  , @Licensetype = Licensetype  
  , @usedbclients = usedbclients  
  , @useddclients = useddclients  
  , @usedscdclients = usedscdclients  
  , @usedpgdclients = usedpgdclients  
  , @accountid = accountid  
  , @Productcode = Productcode 
  , @Remote_addr = Remote_addr 
  FROM @work_table 
 
  IF @@ROWCOUNT > 0
  BEGIN
    DECLARE @msg varchar(1500)
    SET @msg = 'New licensing event has occured from '+@contactfirstname + ' '+@contactlastname+', (mailto:'+@contactemailaddress+') from the company: ' + @Name + '. The following activations details were detected:
    
    Licensed version: '+@WLT+'
    
    Amount of licenses:
    Licensed portal Viewers: ' + @usedbclients + '
    Licensed Portal editors: '+ @Useddclients + '
    Licensed SC D clients: ' + @Usedscdclients + '
    Licensed PG D clients:' + @Usedpgdclients + '
    
    License type:'+ @licensetype+ ' (1=Full license, 2=evaluation license, 3=Transient license)
    
    Link to the organisation: http://crm/xxx/sfa/accts/edit.aspx?id={'+@accountid+'}
    
    Comment provided by Activation: ' +@QPRCOMMENTS+'
    
     Click the link to analyse the licensing address : http://www.db.ripe.net/fcgi-bin/whois?form_type=simple&full_query_string=&searchtext='+@remote_addr+''
     
    --// CHANGE THE VALUE FOR @recipients
    EXEC msdb.dbo.sp_send_dbmail @recipients=@internalemailaddress, @body= @msg,  @subject = 'TEST', @profile_name = 'dw'
       END
       DELETE @work_table WHERE r = @r

Open in new window

Guy Hengel [angelIII / a3]
there is some code missing, obviously:
USE [LMS notifications]
GO
/****** Object:  Trigger [dbo].[Licensingevents]    Script Date: 03/15/2009 20:56:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Licensingevents] ON [dbo].[LMS Notification] AFTER INSERT AS
BEGIN
SET NOCOUNT ON
DECLARE @work_table TABLE ( 
  r INT identity
, Name varchar(100)
, internalEmailaddress varchar(200)
, WLT varchar(200)
, QPRcomments varchar(200)
, Contactfirstname varchar(200)
, contactlastname varchar(200)
, Contactemailaddress varchar(200)
, Licensetype varchar(200)
, usedbclients varchar(100)
, useddclients varchar(100)
, usedscdclients varchar(100)
, usedpgdclients varchar(100)
, accountid varchar(100)
, Productcode varchar(254)
, Remote_addr varchar(254)
)
 
DECLARE @r int 
DECLARE @Name varchar(100)
DECLARE @internalEmailaddress varchar(200)
DECLARE @WLT varchar(200)
DECLARE @QPRcomments varchar(200)
DECLARE @Contactfirstname varchar(200)
DECLARE @contactlastname varchar(200)
DECLARE @Contactemailaddress varchar(200)
DECLARE @Licensetype varchar(200)
DECLARE @usedbclients varchar(100)
DECLARE @useddclients varchar(100)
DECLARE @usedscdclients varchar(100)
DECLARE @usedpgdclients varchar(100)
DECLARE @accountid varchar(100)
DECLARE @Productcode varchar(254)
DECLARE @Remote_addr varchar(254)
 
INSERT INTO @work_table 
 (Name 
, internalEmailaddress  
, WLT  
, QPRcomments  
, Contactfirstname  
, contactlastname  
, Contactemailaddress  
, Licensetype  
, usedbclients  
, useddclients  
, usedscdclients  
, usedpgdclients  
, accountid  
, Productcode 
, Remote_addr 
)
SELECT Name 
, internalEmailaddress  
, WLT  
, QPRcomments  
, Contactfirstname  
, contactlastname  
, Contactemailaddress  
, Licensetype  
, usedbclients  
, useddclients  
, usedscdclients  
, usedpgdclients  
, accountid  
, Productcode 
, Remote_addr 
FROM INSERTED
WHERE ProductCode = 0
 
WHILE @@ROWCOUNT > 0
BEGIN
  SELECT TOP 1 @r = r
  , @Name = Name 
  , @internalEmailaddress = internalEmailaddress  
  , @WLT = WLT  
  , @QPRcomments = QPRcomments  
  , @Contactfirstname = Contactfirstname  
  , @contactlastname = contactlastname  
  , @Contactemailaddress = Contactemailaddress  
  , @Licensetype = Licensetype  
  , @usedbclients = usedbclients  
  , @useddclients = useddclients  
  , @usedscdclients = usedscdclients  
  , @usedpgdclients = usedpgdclients  
  , @accountid = accountid  
  , @Productcode = Productcode 
  , @Remote_addr = Remote_addr 
  FROM @work_table 
 
  IF @@ROWCOUNT > 0
  BEGIN
    DECLARE @msg varchar(1500)
    SET @msg = 'New licensing event has occured from '+@contactfirstname + ' '+@contactlastname+', (mailto:'+@contactemailaddress+') from the company: ' + @Name + '. The following activations details were detected:
    
    Licensed version: '+@WLT+'
    
    Amount of licenses:
    Licensed portal Viewers: ' + @usedbclients + '
    Licensed Portal editors: '+ @Useddclients + '
    Licensed SC D clients: ' + @Usedscdclients + '
    Licensed PG D clients:' + @Usedpgdclients + '
    
    License type:'+ @licensetype+ ' (1=Full license, 2=evaluation license, 3=Transient license)
    
    Link to the organisation: http://crm/xxx/sfa/accts/edit.aspx?id={'+@accountid+'}
    
    Comment provided by Activation: ' +@QPRCOMMENTS+'
    
     Click the link to analyse the licensing address : http://www.db.ripe.net/fcgi-bin/whois?form_type=simple&full_query_string=&searchtext='+@remote_addr+''
     
    --// CHANGE THE VALUE FOR @recipients
    EXEC msdb.dbo.sp_send_dbmail @recipients=@internalemailaddress, @body= @msg,  @subject = 'TEST', @profile_name = 'dw'
  END -- IF @@ROWCOUNT
  DELETE @work_table WHERE r = @r
END -- WHILE @@ROWCOUNT
        
END -- trigger

Open in new window

qprsoft
ASKER
The trigger works now correctly, and if I run a insert into from the query analyser, notification is sent!!

Last problem I still need to figure out is when I run the update from a job : the whole body of the email is blank.. from query analyser adding one line it works correctly and the email has the body included.. If you still have some ideas to this, very much appriciated!

Anyhow, big thanks for the assistance, could not have done without your help!

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
qprsoft
ASKER
If I could, I would by you the biggest pint or beer available ! :) it's working like a charm now. a Big Thanks for your help angel.
qprsoft
ASKER
Once more, I Really appriciate the help you provided to me! Beers are defenitely on me if we ever should run into each others :)
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent