SP giving Different Results? - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

SP giving Different Results?

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 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

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]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources