Are the posted questions getting worse?

Question

Are the posted questions getting worse?

Viewing 15 posts - 51,526 through 51,540 (of 66,738 total)

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

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 1

Luis Cazares (11/19/2015)
You're missing the obvious way to get the current date. :hehe:
USE [msdb]
GO
DECLARE @jobId BINARY(16);
EXEC dbo.sp_add_job @job_name=N'GetCurrentDate',
@enabled=1, @job_id = @jobId OUTPUT;
EXEC dbo.sp_add_jobserver
@job_name = N'GetCurrentDate' ;
EXEC dbo.sp_add_jobstep
@job_name = N'GetCurrentDate',
@step_name = N'Simple Select',
@subsystem = N'TSQL',
@command = N'SELECT 1',
@retry_attempts = 5,
@retry_interval = 5 ;
EXEC dbo.sp_start_job N'GetCurrentDate';
WAITFOR DELAY '00:00:01';
SELECT TOP 1 dbo.agent_datetime(run_date, run_time)
FROM dbo.sysjobhistory h
WHERE h.job_id = @jobId;
EXEC dbo.sp_delete_job @job_name=N'GetCurrentDate';

Very nice Luis! 😎

Another, much less interestingly convoluted way:

SELECT

GETDATE(), --for comparison

start_time

FROM sys.dm_exec_requests

WHERE session_id=@@SPID

There are other fun ideas using dm_db_index_usage_stats and dm_exec_query_stats, but I've not bothered mocking them up yet 🙂

jasona.work SSC Guru Points: 50118 More actions · Answer 2

Ooo! An even better way (and gauarnteed to tick off people!)

exec sp_configure 'show advanced options', 1;

reconfigure;

exec sp_configure 'xp_cmdshell', 1;

exec sp_configure 'show advanced options', 0;

reconfigure;

exec xp_cmdshell 'echo %date%'

exec xp_cmdshell 'echo %time%'

exec sp_configure 'show advanced options', 1;

reconfigure;

exec sp_configure 'xp_cmdshell', 0;

exec sp_configure 'show advanced options', 0;

reconfigure;

I'm gonna get so yelled at for this one...

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 3

HA!

DECLARE @CurrentTime DATETIME, @TallyCount INT, @Adhoc_SQL NVARCHAR(50);

DECLARE Time_Cursor CURSOR FOR

SELECT DISTINCT N FROM dbo.Tally WHERE N < 500

-- Open Cursor of Users

OPEN Time_Cursor

FETCH NEXT FROM

Time_Cursor

INTO

@TallyCount

-- Process Each User and issue Fix command via Sp_Change_Users_Login

WHILE @@FETCH_STATUS = 0

BEGIN

-- Build Command String

SET @Adhoc_SQL = 'SELECT CONVERT(VARCHAR(35),SYSDATETIME()) ''' + CONVERT(VARCHAR(3),@TallyCount) + '''';

-- Display Command issued

PRINT @Adhoc_SQL

IF @TallyCount = 499

AND ( (SELECT CURRENT_TIMESTAMP) = (SELECT GETUTCDATE())

OR ( (SELECT SYSDATETIMEOFFSET()) = (SELECT SYSUTCDATETIME()) ) )

-- Execute Command

EXEC sp_ExecuteSQL @Adhoc_SQL

ELSE IF @TallyCount = 499 AND (SELECT CURRENT_TIMESTAMP) = (SELECT GETDATE())

EXEC sp_ExecuteSQL @Adhoc_SQL

ELSE

PRINT 'Did you really think that code would work?';

-- Get the next Row

FETCH NEXT FROM

Time_Cursor

INTO

@TallyCount

END

-- Cursor Cleanup

CLOSE Time_Cursor

DEALLOCATE Time_Cursor

EDIT: Stole the fix from Luis because that's apparently how I'm rolling today. @=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Of course you're getting yelled. Why would you want to turn off xp_cmdshell? 😀

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 5

Bah. I thought I was being clever, but I have an error I can't find.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Brandie Tarvin (11/19/2015)
Bah. I thought I was being clever, but I have an error I can't find.

I made the code work by adding 4 characters in different parts of the query.

DECLARE @CurrentTime DATETIME, @TallyCount INT, @Adhoc_SQL nVARCHAR(50);

DECLARE Time_Cursor CURSOR FOR

SELECT DISTINCT N FROM dbo.Tally WHERE N <= 500

-- Open Cursor of Users

OPEN Time_Cursor

FETCH NEXT FROM

Time_Cursor

INTO

@TallyCount

-- Process Each User and issue Fix command via Sp_Change_Users_Login

WHILE @@FETCH_STATUS = 0

BEGIN

-- Build Command String

SET @Adhoc_SQL = 'SELECT SYSDATETIME() ''' + CONVERT(VARCHAR(3),@TallyCount) + '''';

-- Display Command issued

PRINT @Adhoc_SQL

IF @TallyCount = 500

AND ( (SELECT CURRENT_TIMESTAMP) = (SELECT GETUTCDATE())

OR ( (SELECT SYSDATETIMEOFFSET()) = (SELECT SYSUTCDATETIME()) ) )

-- Execute Command

EXEC sp_ExecuteSQL @Adhoc_SQL

ELSE PRINT 'Did you really think that code would work?';

-- Get the next Row

FETCH NEXT FROM

Time_Cursor

INTO

@TallyCount

END

-- Cursor Cleanup

CLOSE Time_Cursor

DEALLOCATE Time_Cursor

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 7

Luis Cazares (11/19/2015)
Brandie Tarvin (11/19/2015)
Bah. I thought I was being clever, but I have an error I can't find.
I made the code work by adding 4 characters in different parts of the query.
DECLARE @CurrentTime DATETIME, @TallyCount INT, @Adhoc_SQL nVARCHAR(50);
DECLARE Time_Cursor CURSOR FOR
SELECT DISTINCT N FROM dbo.Tally WHERE N <= 500
-- Open Cursor of Users
OPEN Time_Cursor
FETCH NEXT FROM
Time_Cursor
INTO
@TallyCount
-- Process Each User and issue Fix command via Sp_Change_Users_Login
WHILE @@FETCH_STATUS = 0
BEGIN
-- Build Command String
SET @Adhoc_SQL = 'SELECT SYSDATETIME() ''' + CONVERT(VARCHAR(3),@TallyCount) + '''';
-- Display Command issued
PRINT @Adhoc_SQL
IF @TallyCount = 500
AND ( (SELECT CURRENT_TIMESTAMP) = (SELECT GETUTCDATE())
OR ( (SELECT SYSDATETIMEOFFSET()) = (SELECT SYSUTCDATETIME()) ) )
-- Execute Command
EXEC sp_ExecuteSQL @Adhoc_SQL
ELSE PRINT 'Did you really think that code would work?';
-- Get the next Row
FETCH NEXT FROM
Time_Cursor
INTO
@TallyCount
END
-- Cursor Cleanup
CLOSE Time_Cursor
DEALLOCATE Time_Cursor

Actually, you quoted before I changed my code. Take another look at it.

And in my defense, I'm the only DBA on call today and yesterday and I had a late night release last night and another one tonight. So YAY for working 16 hour shifts!

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Eirikur Eiriksson SSC Guru Points: 182917 More actions · Answer 8

Sean Lange (11/19/2015)
Jacob Wilkins (11/19/2015)
jasona.work (11/19/2015)
Luis Cazares (11/19/2015)
jasona.work (11/19/2015)
I was going to post this over in Steve's call for Good Basic T-SQL Exercises but decided not to pollute the topic with fluff...
So I'm posting it here...
Just for Jeff Moden:
How to get the date from SQL Server.
Sample data required: None
Queries:
SELECT SYSDATETIME();
SELECT SYSDATETIMEOFFSET();
SELECT SYSUTCDATETIME();
SELECT CURRENT_TIMESTAMP();
SELECT GETDATE();
SELECT GETUTCDATE();
:hehe:
Jason, you have failed this query.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.
Even better then!
The learner has to figure out what the problem is!
OK, I'll admit I had MSDN open so I could get *all* the date / time functions, I wasn't paying attention to the syntax as I went and just naturally presumed they all had the () in them...
Or if we want to make things difficult on ourselves, we can add this:
SELECT DATEADD(ms,ms_ticks%1000,DATEADD(ss,ms_ticks/1000,sqlserver_start_time))
FROM sys.dm_os_sys_info
Of course, we have to do it in this silly nested fashion to account for the possibility that the server's been up for more milliseconds than integer can handle (a little shy of 25 days). Obviously very useful :rolleyes:
Only works on 2008+ and it isn't really very accurate. I tried this out and found some variances between this and getdate() greater than 5 minutes. It is however pretty clever.
SELECT DATEADD(ms,ms_ticks%1000,DATEADD(ss,ms_ticks/1000,sqlserver_start_time))
, GETDATE()
FROM sys.dm_os_sys_info

How about this one (variation of Jacob's)

😎

SELECT DATEADD(MILLISECOND, (@@CPU_BUSY + @@IDLE) * SQRT(64),(SELECT sqlserver_start_time FROM sys.dm_os_sys_info))

Edit: Wrong credit 😉

Eirikur Eiriksson SSC Guru Points: 182917 More actions · Answer 9

So to recap, here are 9 different methods for getting the current time, any additions anyone?

😎

SELECT

GETDATE()

,SYSDATETIME()

,SYSDATETIMEOFFSET()

,CURRENT_TIMESTAMP

,SYSUTCDATETIME()

,GETUTCDATE()

,DATEADD(MILLISECOND, (@@CPU_BUSY + @@IDLE) * SQRT(64.2548),(SELECT sqlserver_start_time FROM sys.dm_os_sys_info))

,(SELECT MAX(start_time) FROM sys.dm_exec_requests)

,(SELECT DATEADD(ms,ms_ticks%1000,DATEADD(ss,ms_ticks/1000,sqlserver_start_time)) FROM sys.dm_os_sys_info)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 10

I feel excluded.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182917 More actions · Answer 11

Eirikur Eiriksson

SSC Guru

Points: 182917

November 19, 2015 at 1:59 pm

#1841171

Luis Cazares (11/19/2015)
I feel excluded.

How come?

😎

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 12

Eirikur Eiriksson (11/19/2015)
Luis Cazares (11/19/2015)
I feel excluded.
How come?
😎

Luis, I'll include you with me. 🙂

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Eirikur Eiriksson SSC Guru Points: 182917 More actions · Answer 13

Alvin Ramard (11/19/2015)
Eirikur Eiriksson (11/19/2015)
Luis Cazares (11/19/2015)
I feel excluded.
How come?
😎
Luis, I'll include you with me. 🙂

That looks way too close to a union all statement for comfort:-P

😎

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 14

Eirikur Eiriksson (11/19/2015)
Alvin Ramard (11/19/2015)
Eirikur Eiriksson (11/19/2015)
Luis Cazares (11/19/2015)
I feel excluded.
How come?
😎
Luis, I'll include you with me. 🙂
That looks way too close to a union all statement for comfort:-P
😎

Union all???? I didn't say I was including the rest of you! 😀

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 15

Eirikur Eiriksson (11/19/2015)
Luis Cazares (11/19/2015)
I feel excluded.
How come?
😎

You left my method out

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2