How to find the SQL Server Version?

Simply execute the below query to get the version of SQL Server you are running.



SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

How to change the server wide date format

Many times, when you install windows and sql server 2000 or 2005 in the fresh machine. generally application which is pointing to your database fails to insert the dd/mm/yyyy
date to your database. even you put the write syntex in your application program and the insert query. Why ?

It is due to server level date settings. As a default your server settings would be in mm/dd/yyyy format, which needs to be changed in the server.

How to do this?
Follow these simple steps...

1. Start -> Control Pannel -> Reginal settings ->Customize
after that go the date format and set the short date = dd/mm/yyyy
and long date format = dd mmmm, yyyy

After doing this, Your application will be able to insert the exact date to your database.As there is no sql server level settings to change the date format. But apart from thatyou can use SET option while firing the insert command to your database
Like this...

SET dd/mm/yyyy
Insert into table (date1) value ('29/04/2008')

SQL Magic

Execute the Below Query and enjoy the results that is obtained by this query every time when it is executed.
This Query gives a random list everytime u run it

select * from table_name order by newid()

Retrieve Processes Using Specified Database

If you want to know how many processes are there in any particular database, it can be retrieved querying sysprocesses in master database.
1st Method
USE master
GO
DECLARE @dbid INT
SELECT @dbid = dbid
FROM sysdatabases
WHERE name = ‘DBName’
IF EXISTS (SELECT spid
FROM sysprocesses
WHERE dbid = @dbid)
BEGIN
SELECT ‘These processes are using current database’ AS Note,
spid, last_batch,
status, hostname, loginame
FROM sysprocesses
WHERE dbid = @dbid
END
GO

2nd Method
SELECT 'These processes are using database ' AS Note,
[Database] =DB_NAME (dbid), spid, last_batch,
status, hostname, loginame
FROM sysprocesses
WHERE dbid = DB_ID (‘DBName')

Script to Script Out all Publication Stored Procs

You may come across a situation where in your replication is failing with the error “could not find the stored procedure ‘sp_MSupd_TableName’ ”
or
With the error “could not find the stored procedure ‘sp_MSins_TableName’ ”

To fix this replication errors run the following command in the publication database

sp_scriptpublicationcustomprocs ‘Publication Name’

After running the above stored proc, obtain the results from the result set and run them in the subscription database. Now restart the agent and this should fix the error.