Net Send

Question

Post reply

Net Send

sam.marsden

Old Hand

Points: 332
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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

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: 332 More actions · Answer 2

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 · Answer 3

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: 332 More actions · Answer 4

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 · Answer 5

Ian Scarlett

SSC-Insane

Points: 23197

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: 332 More actions · Answer 6

sam.marsden

Old Hand

Points: 332

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: 332 More actions · Answer 7

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 · Answer 8

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