Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Windza
Yak Posting Veteran
61 Posts |
Posted - 2013-07-21 : 21:54:16
|
I have a server that is used very sporadically so I've created a scheduled task that calls a very basic stored procedure to put the server to sleep if no one is logged in (by means of an IF() function)... this is the stored procedure;/****** Object: StoredProcedure [dbo].[sp_GoToSleep] Script Date: 07/21/2013 13:53:12 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================ALTER PROCEDURE [dbo].[sp_GoToSleep]ASBEGIN SET NOCOUNT ON;IF NOT EXISTS ( SELECT a.loginame AS LoginName FROM master.sys.sysprocesses AS a WHERE loginame NOT LIKE '%XXXX%' AND hostname <> '' GROUP BY db_name(dbid) , loginame , hostname UNION ALL SELECT a.LogonName AS LoginName FROM XXXXDB.dbo.CurrentUser AS a) BEGIN EXEC xp_cmdshell "rundll32.exe powrprof.dll,SetSuspendState 0,1,0" END ENDThe problem I'm having is the EXEC statement getting called when users are logged in and data should exist. There is nothing else putting the PC to sleep (stays on when the script is disabled).Everything is being run from the same local account that has sufficient permissions which I verified by logging in via RDP under that account and checking that I get expected results when a user is logged in (just me at the moment).The task is set to run when the local account is logged off and that appears to be when the issue starts...Is there something here I'm missing? The IF function HAS to be initiating the EXEC statement but I KNOW the there should be data that would otherwise stop it - unless SQL is somehow returning different results when the account is logged off?Would love some suggestions on this because it's got me tearing my hair out :-) |
|
Windza
Yak Posting Veteran
61 Posts |
Posted - 2013-07-21 : 23:05:46
|
Welp, nevermind... triple checking permissions on the XXXXDB I realised the account didn't have access which would likely explain the issue...Testing again now - don't mind me |
|
|
Windza
Yak Posting Veteran
61 Posts |
Posted - 2013-07-22 : 08:53:34
|
Gah... I thought that'd be an obvious solution - apparently not... behaviour is still the same as before.One last appeal for solutions before I scrap the idea completely! Energy saving be darned ;-) |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-07-22 : 21:26:19
|
This is quite an unusual requirement. Does it means, when the user need to use the application, they must physically go to the server and power it up ? KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|