Remove “Restricted User” in SQL Server 2008

October 10, 2016

ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE DatabaseName SET MULTI_USER
GO


Sqlserver notes : Issue – The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows).

October 19, 2015

http://allenbuckley.com/blog/the-row-values-updated-or-deleted-either-do-not-make-the-row-unique-or-they-alter-multiple-rows/

SET ROWCOUNT 1
DELETE FROM Table1
WHERE col1 = ‘0001’


Sqlserver notes

October 16, 2015

Before deleting data from production server follow these steps. Its a good practice

select * from WriteOff_ReworkUsers
where Ref_ID in (189538,201177,201185,203533,203534,203563,203567)

begin tran
delete from WriteOff_ReworkUsers
where Ref_ID in (189538,201177,201185,203533,203534,203563,203567)
rollback tran

begin tran
delete from WriteOff_ReworkUsers
where Ref_ID in (189538,201177,201185,203533,203534,203563,203567)
commit tran


Sqlserver notes: Minus operator in Oracle – Sqlserver Except or Not Exists

September 30, 2015

http://www.sqlservercurry.com/2011/01/minus-keyword-in-sql-server.html

MINUS operator (in oracle) is used to subtract the rows which are available in the second result, from the first result set. SQL Server does not have a built-in MINUS keyword, but in SQL Server, it’s equivalent is the EXCEPT operator or using NOT EXISTS

Here’s an example. Consider the following tables

DECLARE @table1 table(a int, b int)
DECLARE @table2 table(a int, b int)

TEST DATA

INSERT INTO @table1
SELECT 1 as a, 2 as b
UNION ALL
SELECT 1 as a, 2 as b
UNION ALL
SELECT 11 as a, 12 as b
UNION ALL
SELECT 13 as a, 12 as b

INSERT INTO @table2
SELECT 1 as a, 21 as b
UNION ALL
SELECT 1 as a, 12 as b
UNION ALL
SELECT 1 as a, 112 as b
UNION ALL
SELECT 13 as a, 12 as b

Using EXCEPT

SELECT * FROM @table1
EXCEPT
SELECT * FROM @table2
Using NOT EXISTS

SELECT DISTINCT t1.* FROM @table1 as t1
WHERE NOT EXISTS
(SELECT * from @table2 as t2
WHERE t1.a=t2.a and t1.b=t2.b)

NOT EXISTS also has the same functionality of EXCEPT operator i.e. retrieving the rows from the first table, which are not available in the second result set


SqlServer notes : sqlserver script to find a string in entire database

April 1, 2015

http://www.sqlmatters.com/Articles/Searching%20all%20columns%20in%20all%20tables%20in%20a%20database.aspx

DECLARE @SQL VARCHAR(MAX)
DECLARE @SearchString VARCHAR(100)
SET @SQL=”

— ——————————————
— Enter the string to be searched for here :

SET @SearchString=’bracket’
— ——————————————

SELECT @SQL = @SQL + ‘SELECT CONVERT(VARCHAR(MAX),COUNT(*)) + ” matches in column ”+”’
+ C.name + ”’+” on table ” + ”’ + SC.name + ‘.’ + T.name +
”’ [Matches for ”’+@SearchString+”’:] FROM ‘ +
QUOTENAME(SC.name) + ‘.’ + QUOTENAME(T.name) + ‘ WHERE ‘ + QUOTENAME(C.name) +
‘ LIKE ”%’ + @SearchString +
‘%” HAVING COUNT(*)>0 UNION ALL ‘ +CHAR(13) + CHAR(10)
FROM sys.columns C
JOIN sys.tables T
ON C.object_id=T.object_id
JOIN sys.schemas SC
ON SC.schema_id=T.schema_id
JOIN sys.types ST
ON C.user_type_id=ST.user_type_id
JOIN sys.types SYST
ON ST.system_type_id=SYST.user_type_id
AND ST.system_type_id=SYST.system_type_id
WHERE SYST.name IN (‘varchar’,’nvarchar’,’text’,’ntext’,’char’,’nchar’)
ORDER BY T.name, C.name

— Strip off the last UNION ALL
IF LEN(@SQL)>12
SELECT @SQL=LEFT(@SQL,LEN(@SQL)- 12)

EXEC(@SQL)

–PRINT @SQL


SqlServer Notes : How to RESET identity columns in SQL Server

August 27, 2014

DBCC CHECKIDENT(‘[table_name]’, RESEED, [new_reseed_value])
— for example
DBCC CHECKIDENT(‘product’, RESEED, 0)

Here product is the table name


SQL Server – Finding TCP Port Number SQL Instance is Listening on

August 13, 2014

http://sqlandme.com/2013/05/01/sql-server-finding-tcp-port-number-sql-instance-is-listening-on/

By default SQL Server listens on TCP port number 1433, and for named instances TCP port is dynamically configured. There are several options available to get the listening port for SQL Server Instance.

Here are a few methods which we can use to get this information.

Method 1: SQL Server Configuration Manager

Method 2: Windows Event Viewer

Method 3: SQL Server Error Logs

Method 4: sys.dm_exec_connections DMV

Method 5: Reading registry using xp_instance_regread

Let’s see how you can use each of these methods in detail:

Method 1: SQL Server Configuration Manager:

Step 1. Click Start > All Programs > Microsoft SQL Server 2012 > Configuration Tools > SQL Server Configuration Manager

Step 2. Go to SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for

Step 3. Right Click on TCP/IP and select Properties

image

Step 4. In TCP/IP Properties dialog box, go to IP Addresses tab and scroll down to IPAll group.

image

If SQL Server if configured to run on a static port it will be available in TCP Port textbox, and if it is configured on dynamic port then current port will be available in TCP Dynamic Ports textbox. Here my instance is listening on port number 61499.

Method 2: Windows Event Viewer:

When SQL Server is started it logs an event message as ‘Server is listening on [ ‘any’ ‘ in windows event logs. Here will be actual port number on which SQL Server is listening.

To view this using Event Viewer:

Step 1. Click Start > Administrative Tools > Event Viewer.

Note: If Administrative Tools are not available on Start menu, go to Start > Control Panel > System and Maintenance > Administrative Tools > View event logs

Step 2. Navigate to Event Viewer > Windows Logs > Application

Step 3. Since huge amount of event are logged, you need to use filtering to locate the required logs. Right click on Application and select Filter Current Log…

image

Step 4. You can filter the events by Event ID and Event source. The event we are interested in has Event ID of 26022, and it’s source is SQL Server Instance. You need to filter by both Event ID and SQL Server Instance if you have multiple instances installed, for a single instance you can filter by Event ID only. Click on OK to apply the filter.

image

Step 5. Once the filter is applied, Locate message ‘Server is listening on [ ‘any’ …’. As we can see from below screenshot that SQL Server Instance is running on TCP Port 61499.

image

Method 3: SQL Server Error Logs:

When SQL Server is started it also logs an message to SQL Server Error Logs. You can search for port number in SQL Server Error Logs by opening SQL Server Error Log in notepad or via T-SQL using extended stored procedure xp_ReadErrorLog as below:

EXEC xp_ReadErrorLog 0, 1, N’Server is listening on’, N’any’, NULL, NULL, ‘DESC’
GO

Result Set:

LogDate ProcessInfo Text
2013-03-21 13:34:40.610 spid18s Server is listening on [ ‘any’ 61499].
2013-03-21 13:34:40.610 spid18s Server is listening on [ ‘any’ 61499].

(2 row(s) affected)

As we can see from the output that SQL Server Instance is listening on 61499.

Note: This method does not work if SQL Server Error Logs have been cycled. See sp_Cycle_ErrorLog for more information.

Method 4: sys.dm_exec_connections DMV:

DMVs return server state that can be used to monitor SQL Server Instance. We can use sys.dm_exec_connections DMV to identify the port number SQL Server Instance is listening on using below T-SQL code:

SELECT local_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
GO

Result Set:

local_tcp_port
61499

(1 row(s) affected)

As we can see from the output… same as above Smile

Method 5: Reading registry using xp_instance_regread:

Port number can also be retrieved from Windows Registry database.

We can use extended stored procedure xp_instance_regread to get port number information using below T-SQL code:

DECLARE @portNumber NVARCHAR(10)

EXEC xp_instance_regread
@rootkey = ‘HKEY_LOCAL_MACHINE’,
@key =
‘Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll’,
@value_name = ‘TcpDynamicPorts’,
@value = @portNumber OUTPUT

SELECT [Port Number] = @portNumber
GO

Result Set:

Port Number
61499

(1 row(s) affected)

As we can see … same as above Smile Smile

Note: The above code will only work if SQL Server is configured to use dynamic port number. If SQL Server is configured on a static port, we need to use @value_name = ‘TcpPort’ as opposed to @value_name = ‘TcpDynamicPorts’.