Net Send

• sam.marsden

  Old Hand

  Points: 352

  More actions

  August 11, 2009 at 6:59 am

  #385157

  Hi Everyone...

  Back again...

  Hopefully just a simple one this time,

  what i would like to do is notify a certain user whever a record is added to a specific table

  i.e

  Create Table Table_2 (Call_Num int, Area_Code varchar(6), Schedular_PC nchar(10)

  Wherever a record is inserted into the table above a net send goes out (would build net send into a trigger...)

  something like this:

  DECLARE @CALL int

  SET @CALL = Call_Num From Table_2

  DECLARE @PC nchar(10)

  SET @PC = Schedular_PC From Table_2

  exec xp_cmdshell net send ' + @PC + ' Report Added To ' + @Call'"

  But i just can't get it to work... I'm sure i'm just dong something stupid..

  Any help would be greatly appreciated..

  Sam Marsden

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  August 11, 2009 at 7:10 am

  #1037377

  I presume you are getting syntax errors on the xp_cmdshell line.

  Try this instead

  DECLARE @CALL int

  SET @CALL = Call_Num From Table_2

  DECLARE @PC nchar(10)

  SET @PC = Schedular_PC From Table_2

  DECLARE @cmd nvarchar(255)

  set @cmd = 'net send ' + @PC + ' "Report Added To ' + convert(varchar(255),@Call) + '"'

  exec xp_cmdshell @cmd

• sam.marsden

  Old Hand

  Points: 352

  More actions

  August 11, 2009 at 7:17 am

  #1037383

  Ian Scarlett (8/11/2009)

  ---

  I presume you are getting syntax errors on the xp_cmdshell line.

  Try this instead

  DECLARE @CALL int

  SET @CALL = Call_Num From Table_2

  DECLARE @PC nchar(10)

  SET @PC = Schedular_PC From Table_2

  DECLARE @cmd nvarchar(255)

  set @cmd = 'net send ' + @PC + ' "Report Added To ' + convert(varchar(255),@Call) + '"'

  exec xp_cmdshell @cmd

  Thanks for the quick reply, tried the above as you said but getting errors as per below:

  Msg 156, Level 15, State 1, Line 2

  Incorrect syntax near the keyword 'From'.

  Msg 156, Level 15, State 1, Line 4

  Incorrect syntax near the keyword 'From'.

  Sam Marsden

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  August 11, 2009 at 7:23 am

  #1037390

  As you didn't post any schema details, I couldn't test the first part of your script. The SET statements should be SELECT statements. Try this.

  DECLARE @CALL int

  SELECT @CALL = Call_Num From Table_2

  DECLARE @PC nchar(10)

  SELECT @PC = Schedular_PC From Table_2

  DECLARE @cmd nvarchar(255)

  set @cmd = 'net send ' + @PC + ' "Report Added To ' + convert(varchar(255),@Call) + '"'

  exec xp_cmdshell @cmd

  Edited to stop line break in code

• sam.marsden

  Old Hand

  Points: 352

  More actions

  August 11, 2009 at 7:29 am

  #1037393

  Ian Scarlett (8/11/2009)

  ---

  As you didn't post any schema details, I couldn't test the first part of your script. The SET statements should be SELECT statements. Try this.

  DECLARE @CALL int

  SELECT @CALL = Call_Num From Table_2

  DECLARE @PC nchar(10)

  SELECT @PC = Schedular_PC From Table_2

  DECLARE @cmd nvarchar(255)

  set @cmd = 'net send ' + @PC + ' "Report Added To ' + convert(varchar(255),@Call) + '"'

  exec xp_cmdshell @cmd

  Sorry man my bad - all i'm getting now is..

  Msg 2812, Level 16, State 62, Line 7

  Could not find stored procedure 'xp_cmdshell'.

  Sam Marsden

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  August 11, 2009 at 7:32 am

  #1037398

  XP_cmdshell lives in the master database, so if you run this in the context of any other database, you have to specify

  exec master..xp_cmdshell .....

• sam.marsden

  Old Hand

  Points: 352

  More actions

  August 11, 2009 at 8:10 am

  #1037443

  Once Again SQL Server Central comes out on top!!

  Thanks for your help!

  Sam Marsden

• sam.marsden

  Old Hand

  Points: 352

  More actions

  August 11, 2009 at 8:14 am

  #1037449

  I do have another question along the same line... i need to add a where condition to my trigger but get the error below:

  Msg 156, Level 15, State 1, Procedure temptable, Line 4

  Incorrect syntax near the keyword 'Where'.

  When i run:

  USE [TESS42LIVE]

  GO

  /****** Object: Trigger [dbo].[temptable] Script Date: 08/11/2009 15:07:30 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  ALTER TRIGGER [dbo].[temptable]

  ON [dbo].[SCFSR]

  FOR INSERT

  Where FSR_Employ_Num = 'Samm'

  AS

  BEGIN

  INSERT INTO Table_2 (Call_Num, Area_Code, Schedular_PC)

  SELECT FSR_Call_Num, FSR_Area_Code, Area_Redirection

  From inserted

  INNER JOIN TESS42LIVE.dbo.SCArea SCArea ON FSR_Area_Code=SCArea.Area_Code

  END

  ?????

  Sam Marsden

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  August 11, 2009 at 1:16 pm

  #1037678

  Again, as you have posted no schema details, I can only guess, rather than test a solution.

  The WHERE clause is in the wrong place. So you will probably want something like this (assuming that the FSR_Employ_Num column is from the SCFSR table.

  USE [TESS42LIVE]

  GO

  /****** Object: Trigger [dbo].[temptable] Script Date: 08/11/2009 15:07:30 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  ALTER TRIGGER [dbo].[temptable]

  ON [dbo].[SCFSR]

  FOR INSERT

  AS

  BEGIN

  INSERT INTO Table_2 (Call_Num, Area_Code, Schedular_PC)

  SELECT FSR_Call_Num, FSR_Area_Code, Area_Redirection

  From inserted

  INNER JOIN TESS42LIVE.dbo.SCArea SCArea ON FSR_Area_Code=SCArea.Area_Code

  Where inserted.FSR_Employ_Num = 'Samm'

  END