1st you need to create a function in sql server db Alter FUNCTION mySplitString ( @myValue nvarchar(Max), @SplitChar nvarchar(1), @NoOfPositions int ) RETURNS nvarchar(1000) AS BEGIN — Declare the return variable here DECLARE @name NVARCHAR(500) Declare @ReturnValue nvarchar(1000); DECLARE @pos INT set @ReturnValue=” SELECT @pos = CHARINDEX(@SplitChar, @myValue) — if no split char exist if […]
Shrink SQL Database in Using SQL Query
How to fix this ‘The transaction log for database ‘Search_Service_DB’ is full due to ‘LOG_BACKUP’.? On your SQL Server, open the SQL Server Management Studio. Connect to the local SQL Server. Right click Search_Service_DB -> Properties -> Options. Change Recover Model from Full to Simple. Click OK. Right click Search_Service_DB, go to Tasks -> Shrink -> Shrink Files. […]
How get list of Category with Parent Name ( Recursive Loop)
WITH Hierarchy AS ( SELECT Id, CategoryName,ParentId, Cast(CategoryName as nvarchar(Max)) as hierarchy FROM invCategoryHead AS FirtGeneration WHERE ParentId IS NULL Or ParentId=0 UNION ALL SELECT NextGeneration.Id, NextGeneration.CategoryName, NextGeneration.ParentId , Parent.hierarchy + ‘ -> ‘ + Cast(NextGeneration.CategoryName as Nvarchar(Max)) as hierarchy FROM invCategoryHead AS NextGeneration INNER JOIN Hierarchy AS Parent ON NextGeneration.ParentId = Parent.Id ) SELECT […]
xp_cmdshell Server Configuration Option
APPLIES TO: SQL Server Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse The xp_cmdshell option is a SQL Server server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system. By default, the xp_cmdshell option is disabled on new installations. Before enabling this option, it is important to consider the potential security implications […]
Find Column’s Table name from DB
SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME like ‘comment%’