SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SP giving Different Results?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Windza
Yak Posting Veteran

Australia
61 Posts

Posted - 07/21/2013 :  21:54:16  Show Profile  Reply with Quote
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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_GoToSleep]
AS
BEGIN

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

END


The 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

Australia
61 Posts

Posted - 07/21/2013 :  23:05:46  Show Profile  Reply with Quote
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
Go to Top of Page

Windza
Yak Posting Veteran

Australia
61 Posts

Posted - 07/22/2013 :  08:53:34  Show Profile  Reply with Quote
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 ;-)
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17586 Posts

Posted - 07/22/2013 :  21:26:19  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000