net send problem..plz help

Question

net send problem..plz help

Noman Tariq

SSCommitted

Points: 1750
More actions
March 18, 2006 at 2:06 pm

#170937

Hi all,
I have a problem while using master.xp_cmdshell
to send the mssg by using net send..
the script is like this.
------------------------------------------
CREATE PROC sp_netsend_test2
(
@notification VARCHAR(8000) = 'Please save your transactions and close the MTBC-SOFT now, till the next msg. Server will be unavailable due to Maintainance. Otherwise you will be disconnected forcefully.'
)
AS
BEGIN
/*******************************************************************************************************
Written by: Noman Tariq
Date written: Jan 23 2006
Purpose: To send NET SEND messages to all the connected SQL Server users in the MTBC LAN
*******************************************************************************************************/

SET NOCOUNT ON

DECLARE @msg VARCHAR(250)
DECLARE @hostname sysname
SELECT @hostname= min(RTRIM(hostname))
FROM
master.dbo.sysprocesses (NOLOCK)
WHERE
hostname <> ''

WHILE @hostname is not null
    BEGIN
set @msg='exec master.dbo.xp_cmdshell "net send ' + RTRIM(@hostname) + ' ' + RTRIM(@notification) + ' "'
        EXEC (@msg)
SELECT @hostname= min(RTRIM(hostname))
FROM
  master.dbo.sysprocesses (NOLOCK)
WHERE
  hostname <> ''
  and hostname > @hostname
    END

SET NOCOUNT OFF

END
GO
--------------
it gives me an error like :
The identifier that starts with 'net send computer_name Please save your transactions and close the Application now, till the next msg. Server will be unavailable due t' is too long. Maximum length is 128.
--
???? whats this .. seems like the length problem, but how can i increase it??

Viewing 2 posts - 1 through 2 (of 2 total)

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

K. Brian Kelley SSC Guru Points: 114704 More actions · Answer 1

From Books Online about error code 103:

If you enclose a character string that is more than 128 characters in double quotation marks, the application may receive this error. When the QUOTED_IDENTIFIERS option is set ON (SET QUOTED_IDENTIFIERS ON), Microsoft® SQL Server™ expects quoted identifiers to be enclosed in double quotation marks (") and data values to be enclosed in single quotation marks ('). In the case of character parameters of stored procedures, SQL Server accepts data values enclosed in double quotation marks if the character string is less than 128 characters. They should be considered syntax errors by SQL Server and generate an error.

The trick is to replace your double-quotes with two single quotes every place they occur. So " becomes ' repeated twice.

K. Brian Kelley
@kbriankelley