pick up random records from a table

Question:
How to pick up random records from a table?

Gabbar:

SELECT TOP 5
*
FROM
tblName
ORDER BY
right(convert(nvarchar(100),((row_number() over (order by ColumnName)) * rand())),3)

This will work in SQL 2005. To make it work in 2000, use any numeric column instead of row_number.

Truncate log and shrink all the database

Question:
How to truncate log and shrink the ALL the databases on a SQL Server 2005?

Gabbar:
Create the job with following script....
USE [msdb]
GO
/****** Object: Job [TruncateLogAndShrinkAllDatabases] Script Date: 02/06/2007 15:39:03 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 02/06/2007 15:39:03 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'TruncateLogAndShrinkAllDatabases',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [TruncateLogShrink] Script Date: 02/06/2007 15:39:03 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TruncateLogShrink',
@step_id=1,
@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'DECLARE @var nvarchar(max)
SET @var = ''''

SELECT
@var = @var +
''
backup log '' + name + '' with truncate_only
DBCC SHRINKDATABASE('''''' + name + '''''')
''
FROM
sys.databases
WHERE
is_read_only = 0
AND database_id > 4

EXEC sp_executesql @var
',
@database_name=N'master',
@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_jobschedule @job_id=@jobId, @name=N'MidNight',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20070206,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
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:

याद रक्खूंगा.....

When Gabbar is arrested and being sent to the jail, he meets Thakur on the way and says...
याद रक्खूंगा.....तुझे याद रक्खूंगा.....

The tone of his voice and the hatred in his eyes....लाजवाब!!

अनुस्वार

अरे ओ सांभा.....

मराठीत लिखाण

मी आता मराठी लिहीण्यात चान्गलाच पटाईत झालो आहे.
सुरुवातीला जरा हळू हळू लिहाव लागल, पण आता मात्र वेग आला आहे.
अजून अनुस्वार देता येत नाही.

Excellent tool for stock market

found this...
Market Heat Map

A very good tool to look at performances of major stocks.
Don't forget to click on options and check the color changes on selection of time periods.

Happy B'Day BigB

Our own BigB reaches 64 today.
Wishing him a very happy B'day and a great year ahead!!!


Excited to watch him play Gabbar.