Categories
MSSQL

Fix User Server Principal authentication error after db restore

use database_name; — ALTER DATABASE database_name SET READ_WRITE ——– Reset SQL user account guids ——————— DECLARE @UserName nvarchar(255) DECLARE orphanuser_cur cursor for SELECT UserName = su.name FROM sysusers su JOIN sys.server_principals sp ON sp.name = su.name WHERE issqluser = 1 AND (su.sid IS NOT NULL AND su.sid <> 0x0) AND suser_sname(su.sid) is null ORDER BY […]

Categories
C# Notes MSSQL Projects Sample Codes

Getting Started with .Net MVC c#

Using DB First Approach Database Part MSSQL Management Studio Create DB Tables, Views and Stored Procedure as requirement Visual Studio Application Part Create New Project Create Separate Projects for DAL Add Reference of DAL Project to main project Add Nuget package of Entity Framework to both projects [DAL and Main] Create EDMX Model from Database […]

Categories
MSSQL

TSQL Clear All Database objects

use dbname; DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR SET @Cursor = CURSOR FAST_FORWARD FOR SELECT DISTINCT sql = ‘ALTER TABLE [‘ + tc2.TABLE_NAME + ‘] DROP [‘ + rc1.CONSTRAINT_NAME + ‘]’ FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql WHILE (@@FETCH_STATUS = 0) BEGIN Exec […]

Categories
MSSQL

MSSQL Check Running Queries

SELECT sqltext.TEXT, req.session_id, req.status, req.command, req.cpu_time, req.total_elapsed_time FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Categories
MSSQL

TSQL Min, Max, AVG and Last Value in single query

declare @MyTable table ( SN INT ,[Month] INT ,Acc INT ,Bal INT ) — GO INSERT INTO @MyTable VALUES (1, 7, 101, 1000), (2, 7, 101, 800), (3, 7, 101, 1700), (4, 8, 101, 1200), (5, 8, 101, 900), (6, 9, 101, 2500) — GO select distinct Acc, [Month], MinBal, AvgBal, MaxBal, LastVal from ( […]

Categories
MSSQL

Some useful Commands for Check and Maintain MSSQL Database

— Shrink Database DBCC SHRINKDATABASE (AdventureWorks2008R2, TRUNCATEONLY); — Rebuild All Index on a table ALTER INDEX ALL ON Production.Product REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON); — Reorganize all indexes on the HumanResources.Employee table. ALTER INDEX ALL ON HumanResources.Employee REORGANIZE ; — Check DB for database consistency DBCC CHECKDB — Reclaims […]