SQLCMD mode in SSMS and Creating WMI Alerts

Ask a question

Monday, February 04, 2008 8:46 PM

0

First of all, I'm not sure that this posting belongs here, but as far as I can tell this forum is the best match. I am running a script in SQLCMD mode in SSMS. Part of the script creates a WMI alert and a job to fire upon receiving the alert. The pertinent part of sp_add_jobstep is as follows:

Code Snippet

@command=N'INSERT INTO LogGrowth

(AlertTime, [Filename], PagesGrown, Duration)

VALUES (

GETDATE(),

N''$(ESCAPE_SQUOTE(WMI(FileName)))'',

$(ESCAPE_NONE(WMI(IntegerData))),

$(ESCAPE_NONE(WMI(Duration)))

)',

The script, when run with SQLCMD mode on produces the error:

A fatal scripting error occurred.

Variable WMI(FileName) is not defined.

Unfortunately it seems like SQLCMD mode and accessing WMI data is incompatible. Is there a way to escape the WMI tokens to insulate them from SQLCMD? Or perhaps a way to turn off the SQLCMD mode from within the script, and then turn it back on? Any suggestions would be helpful.

Thanks

John T
- Reply
- Quote

All Replies

Tuesday, February 05, 2008 2:49 AM

Moderator

0

You can use Tokens in SQL Agent Jobs per the BOL, but I have never seen a reference for using them elsewhere in SQL.

http://technet.microsoft.com/en-us/library/ms175575.aspx

If this is in the Agent as a job step that is response to a SQL Alert then remove the @command =, and all the quotes to just be

Code Snippet

INSERT INTO LogGrowth

(AlertTime, [Filename], PagesGrown, Duration)

VALUES (

GETDATE(),

$(ESCAPE_SQUOTE(WMI(FileName))),

$(ESCAPE_NONE(WMI(IntegerData))),

$(ESCAPE_NONE(WMI(Duration)))

)

So long as you WMI returns the FileName, IntegerData, and Duration, then it should work just like this. Also select your correct database in the dropdown.
- Reply
- Quote
Tuesday, February 05, 2008 6:31 PM

0

In SSMS connect to a DB engine, and open up a new query window. Press [ALT Q] + M (Query -> SQLCMD Mode). This allows you to use tokens as you would running SQLCMD from the command line. The problem is that creating WMI alerts also uses tokens. The end result is a 'clash', where the SQLCMD interpreter >also< tries to replace the tokens for the WMI alert. In the code sample below, if you comment out the last proc (sp_add_alert), this script runs in SQLCMD mode. If you try to run the script in its entirety, it will fail with the error I indicated before. I can't be the only person to have ever witnessed this behavior. Has anybody else seen this?

Code Snippet

:setvar Operator_Name "Operator Name"

:setvar Operator_Email_Address "someone@somewhere.com"

:setvar Operator_Pager_Email_Address "5555555555@somesmsgateway.net"

:setvar Path_To_WMI_Namespace "\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER"

USE [msdb]

GO

/****** Object: Operator [Operator Name] Script Date: 02/04/2008 09:36:57 ******/

IF NOT EXISTS(SELECT * FROM msdb.dbo.sysoperators WHERE NAME = 'Operator Name')

EXEC msdb.dbo.sp_add_operator @name=N'$(Operator_Name)',

@enabled=1,

@weekday_pager_start_time=0,

@weekday_pager_end_time=235959,

@saturday_pager_start_time=90000,

@saturday_pager_end_time=180000,

@sunday_pager_start_time=90000,

@sunday_pager_end_time=180000,

@pager_days=62,

@email_address=N'$(Operator_Email_Address)',

@pager_address=N'$(Operator_Pager_Email_Address)',

@category_name=N'[Uncategorized]'

GO

USE [msdb]

GO

/****** Object: Job [Capture Log Growth Event] Script Date: 02/04/2008 09:36:12 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 02/04/2008 09:36:12 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Capture Log Growth Event',

@enabled=1,

@notify_level_eventlog=2,

@notify_level_email=2,

@notify_level_netsend=0,

@notify_level_page=2,

@delete_level=0,

@description=N'Job for responding to LOG_FILE_AUTO_GROW events',

@category_name=N'[Uncategorized (Local)]',

@owner_login_name=N'SA',

@notify_email_operator_name=N'$(Operator_Name)',

@notify_page_operator_name=N'$(Operator_Name)', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [Insert Event Record Into LogGrowth] Script Date: 02/04/2008 09:36:12 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert Event Record Into LogGrowth',

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0,

@subsystem=N'TSQL',

@command=N'INSERT INTO LogGrowth

(AlertTime, [Filename], PagesGrown, Duration)

VALUES (

GETDATE(),

N''$(ESCAPE_SQUOTE(WMI(FileName)))'',

$(ESCAPE_NONE(WMI(IntegerData))),

$(ESCAPE_NONE(WMI(Duration)))

)',

@database_name=N'LogGrowthAlertTestDB',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [Check sqlperf for High PCT usage] Script Date: 02/04/2008 09:36:13 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check sqlperf for High PCT usage',

@step_id=2,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'CREATE TABLE #logspace([dbname] varchar(50), logsize int, space_used_pct float, status bit)

INSERT INTO #logspace

EXEC (''dbcc sqlperf(logspace)'')

IF EXISTS(SELECT * FROM #logspace WHERE dbname = ''LogGrowthAlertTestDB'' AND space_used_pct > 50)

RAISERROR(''Excessive Log Growth in LogGrowthAlertTestDB'', 16, 1)',

@database_name=N'LogGrowthAlertTestDB',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

USE [msdb]

GO

/****** Object: Alert [Respond to LOG_FILE_AUTO_GROW] Script Date: 02/04/2008 09:37:33 ******/

EXEC msdb.dbo.sp_add_alert @name=N'Respond to LOG_FILE_AUTO_GROW',

@message_id=0,

@severity=0,

@enabled=1,

@delay_between_responses=0,

@include_event_description_in=7,

@category_name=N'[Uncategorized]',

@wmi_namespace=N'$(Path_To_WMI_Namespace)' --N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',

@wmi_query=N'SELECT * FROM LOG_FILE_AUTO_GROW WHERE DatabaseID = 8',

@job_id=N'aaa586e9-b9c8-4f05-a643-43c5baa614a9'

GO
- Reply
- Quote
Friday, January 07, 2011 3:58 PM

0

I am with you ....We are creating an automation toolkit and have around 50 such scripts ...If I run the scripts in QA everything works fine ....But If I use SQLCMD (the application uses it)...

I get the warning sort of messages in the command prompt :

'WMI (ObjectName)' scripting variable not defined

The job gets created with all the steps but with disappeared tokens .What I mean to say is that if my actual job step is :

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert graph into LogEvents',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'

INSERT INTO [dbo].[Alterprocevents](
AlertTime,
object_name,
login_name,
user_name
)
VALUES (
GETDATE(),
N''$(ESCAPE_NONE(WMI(objectname)))'',
N''$(ESCAPE_NONE(WMI(loginname)))'',
N''$(ESCAPE_NONE(WMI(username)))''
)',
@database_name=N' jony',
@flags=0

And what I get in the job step is :

INSERT INTO [dbo].[Alterprocevents](
AlertTime,
object_name,
login_name,
user_name
)
VALUES (
GETDATE(),
N''$((WMI(objectname)))'',
N''$((WMI(loginname)))'',
N''$((WMI(username)))''
)',
@database_name=N' jony',
@flags=0

So the tokens disappear ....

The workaround is to use OSQL , which is still used but not recommended by Microsoft for future use...
Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
- Reply
- Quote
Friday, January 07, 2011 5:27 PM

0

Filed the bug (not sure if it is a bug or by design or if i am missing something.)There is a file attached as well.

https://connect.microsoft.com/SQLServer/feedback/details/635082/sqlcmd-bug-scripting-variable-not-defined

Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
- Reply
- Quote
Wednesday, May 23, 2012 6:47 PM

0

Try this Microsoft KB patch which works as a retrofit to reinstate the ESCAPE_SQUOTE after the token wrappers for the ESCAPE_SQUOTE sections are removed.

http://support.microsoft.com/kb/915845

Fixing issues with missing: ESCAPE_SQUOTE ESCAPE_NONE and SQL Job Agents
- Reply
- Quote

SQLCMD mode in SSMS and Creating WMI Alerts

All Replies

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support by product

Other support links

Not an IT pro?