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.
Nothing loose by learning again. Definitely gain If you just don't let it go away. - Bvsc.
Friday, January 23, 2015
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:
-- 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
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.
-- 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.
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.
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'
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
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
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
Subscribe to:
Posts (Atom)