Friday, January 23, 2015

SQL Puzzle

 My friend came to me and can you find this word 'S L U N O I O T'. I said 'SOLUTION'.
Then I thought why can't I create a script which will jigjag a input word.

Of course you may do so many other ways.. I have developed like below. If you want to use this for longer strings please change the string length appropriately...


SET NOCOUNT ON
GO
Declare @str VARCHAR(20) = 'SOLUTION' -- You can pass your own word here
DECLARE @val CHAR(1)

DECLARE @valstr VARCHAR(20)
DECLARE @len INT = LEN(@str)
Declare @mytab Table (ID INT IDENTITY,Value char(1),OrderID INT)
DECLARE @i int =1
    WHILE (@len >= @i)
    BEGIN
        SET @val = SUBSTRING(@str,@i,1)
        INSERT INTO @mytab VALUES (@val,rand()*10)
        SET @valstr = SUBSTRING(@str,@i,@len)
        --SET @len = Len(@valstr)
        SET @i = @i +1
    END

SELECT *, row_number() OVER (ORDER BY orderID) RowID into #temp from @mytab


DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + value
FROM #temp
 SELECT replace(@names,',','') As WhatIsThisWord
 drop table #temp

Note: Please acknowledge.

Monday, January 5, 2015

Database Snapshot

                                                --Database Snapshot
A database snapshot is a read-only, static view of a SQL Server database. A database snapshot always resides on the same server instance as its source database.


USE master
GO
-- Drop database snapshot if it already exists
IF  EXISTS (
         SELECT name
         FROM sys.databases
         WHERE name = 'MYDB_snapshot'

                   )
DROP DATABASE MYDB_snapshot
GO
-- Create the database snapshot
CREATE DATABASE MYDB_snapshot ON
( NAME = MYDB , -- FileName(Data)
FILENAME =
'C:\PYamani\SQL_Archives\MYDB_snapshot.ss' )
AS SNAPSHOT OF NJSBCL;
GO


-- Dropping the SnapShort database

DROP DATABASE NJSBCL_snapshot1


Notes:

  • The source database must be in Online.
  • To create a database snapshot on a mirror database, the database must be in the SYNCHRONIZED mirroring state.
  • All recovery models support database snapshots.
  • You cannot Drop a database,Detached or restored which holds the at least one Snapshot.
  • Files cannot be dropped from the source database or from any snapshots.
  • You cannot take a backup for the snapshot database.
  • You can have 'N' number of snapshots on a single database, It is depends on the disc space.
    Each database snapshot persists until it is explicitly dropped by the database owner.

-- Testing snapshot by creating new tables
    Once you create a snapshot, you can create a table on source/base database. You can refresh the snapshot tables now to see the new table.
        Create table TestTable (Sno INT)
        Drop table TestTable

Tuesday, December 23, 2014

Disconnect/Kill all the SPID's in the database

Some times a situation like to restore the database immediately.
When you are trying to do that, It will not let you install and throwing the error saying Database is in use....
Once you find the SPID's running under the database, It may be hard to disconnect all of them in single short (Of course you can use Single user mode, It has its own problems though).

By running the following script you can kill all the SPID's running under the desired database and you can restore the database by adding restore database script.
Please take a look at it,


DECLARE @ID INT
DECLARE @MSG CHAR(8), @DataName SYSNAME = 'newdb'

WHILE 1 = 1
BEGIN
 SET ROWCOUNT 1
 SELECT  @id = spid
        FROM Master..Sysprocesses P,
                Master..Sysdatabases D
         WHERE D.Name=@DataName AND
                D.dbid = P.dbid
IF @@rowcount = 0
 BREAK

 SELECT @msg = 'KILL ' + CONVERT(CHAR(8) ,@ID)

PRINT @msg
EXEC( @msg)

END

-- Please continue your restore script here.

Tuesday, December 9, 2014

How can we stop pulling data from a table in SQL server ?

It looks funny when you think about it but its true.
One of the way  you can stop users to pull data from a table is not a Heap should be disable Cluster Index.

If you disable the Cluster Index as it has the data, You can't be accessible though you hold required permissions.

ALTER INDEX IndexName ON TableName DISABLE.

How to find all the users in my windows group?

 You have an extended stored procedure to get the information, which you have requested.

xp_logininfo 'Your windows Group','members'

Database Read_Only Vs Read_Write

Some times, You may get a situation like need to make your database as read_only time being...
Please follow the below..

If you want to make you database set to read only from (READ_WRITE) Normal DB,Then you can follow below
Note: Conditions apply, You will have to disconnect users from respected database.
USE [master]
GO
ALTER DATABASE [DatabaseName] SET  READ_ONLY WITH NO_WAIT
GO
If you want to make your database set to REGULAR(READ and WRITE), Then you can follow below...
Note: Conditions apply, You will have to disconnect users from respected database.

USE [master]
GO
ALTER DATABASE [DatabaseName] SET  READ_WRITE WITH NO_WAIT
GO

Thursday, November 20, 2014

SQL Commands related to Database Mail

Basic SQL commands to know related to Database Mail, I am just keeping them for quick reference.

SELECT * FROM sysmail_log
           
-- Get all Sys mail accounts by running the below statement       
        SELECT * FROM sysmail_account
       
--If you want to find all the mails which by a particular Login...
        SELECT * FROM sysmail_sentitems
        WHERE send_request_user = 'LoginName'

        -- You can use above for today item List too..

-- If you want to see all failed items through Database mail                        
        SELECT * FROM sysmail_faileditems
        --WHERE send_request_user = 'LoginName'

       

-- List all the principle profiles in the server
SELECT * FROM sysmail_principalprofile
-- List all the profiles in the server
SELECT * FROM sysmail_profile

SELECT * FROM sysmail_profileaccount
-- You can see the existed SMPT server names, Port number by running the following
SELECT * FROM sysmail_server