Repeate String N Times using REPLICATE function

In sql-server we have a function which repeats the string n number of times.

Function : REPLICATE(string,int)

Example : Select REPLICATE('rahul ',5)

Result : rahul rahul rahul rahul rahul 

Length of LOB data () to be replicated exceeds configured maximum 65536

Whenever you try to replicate data from a database that saves the images(filestream) in the database, and you have included those images in your replication, then you might get this error

Length of LOB data (583669) to be replicated exceeds configured maximum 65536

using TSQL

sp_configure 'max text repl size', '2147483647'

GO

RECONFIGURE

Find the statictics on an Object and Drop them

Hi,

With the below query we can get the statictics detail of an object and drop them

SELECT name, OBJECT_NAME(OBJECT_ID) AS ObjectName
FROM sys.stats
WHERE auto_created = 1
and OBJECT_NAME(OBJECT_ID) = '<OBJECT_NAME>';

DROP STATISTICS <OBJECT_NAME>.PrimaryKey

Get the active connection of SQL-Server

Hi,

Below query will help to get all the active connection list in SSMS.

SELECT des.program_name,
des.login_name,
des.host_name,
COUNT(des.session_id) [Connections]
FROM sys.dm_exec_sessions des
INNER JOIN sys.dm_exec_connections DEC
ON des.session_id = DEC.session_id
WHERE des.is_user_process = 1
AND des.status != 'running'
GROUP BY des.program_name,
des.login_name,
des.host_name
--,der.database_id
HAVING COUNT(des.session_id) > 2
ORDER BY COUNT(des.session_id) DESC