Tables are a userbasic b userlmits c groupauthclass -- 2 Table Inner Join SQL Server -- -- 3 Table Inner Join SQL Server --
Uncategorized
Generate HTML Report from Table in SQL Server
Truncate Log in Database Mirroring SQL Server 2008 2012
select log_reuse_wait_desc from sys.databases where name = 'AdventureWorks' If Output is "LOG_BACKUP" -- Execute 2 Times just in case -- BACKUP LOG AdventureWorks TO DISK='NUL:' -- Now Truncate the Log -- USE AdventureWorks GO ALTER DATABASE AdventureWorksSET RECOVERY SIMPLE WITH NO_WAIT; GO DBCC SHRINKFILE(AdventureWorks_LOG, 100); GO ALTER DATABASE AdventureWorks SET RECOVERY FULL WITH NO_WAIT; GO
Delete Duplicate Rows SQL Server
1. Add an Identity Column to the Table alter table MASTER add identity_col int identity(1,1); 2. Delete duplicate data using Below Query (In the GROUP BY CLAUSE you can specify multiple columns if the row is indetified using more than one field) delete from MASTER where identity_col not in ( select Min(identity_col) from MASTER group … Continue reading Delete Duplicate Rows SQL Server
Stored Procedure to Insert Data SQL Server
CREATE TABLE [dbo].[MASTER]( [BANK_CODE] [varchar](30) NULL, [TRNX_UID] [float] NULL, [CARD_TYPE] [varchar](30) NULL, [DIR_FLAG] [varchar](30) NULL, [ACQ_CODE] [varchar](30) NULL, [ACQ_DESC] [varchar](200) NULL, [ISS_CODE] [varchar](30) NULL, [ISS_DESC] [varchar](200) NULL, [TRNX_STATUS] [varchar](30) NULL, [TRNX_TYPE] [varchar](30) NULL, [TERMINAL_ID] [varchar](200) NULL, [CARD_NO] [varchar](100) NULL, [CURRENCY_CODE] [varchar](30) NULL, [AMOUNT_FC] [numeric](20, 6) NULL, [AMOUNT_LC] [numeric](20, 2) NULL, [CURRENCY_RATE] [numeric](10, 6) NULL, [TRNX_DATE] … Continue reading Stored Procedure to Insert Data SQL Server
Enable XP_CMDSHELL SQL Server 2008 2012
EXEC sp_configure 'show advanced option',1 GO RECONFIGURE GO EXEC sp_configure 'xp_cmdshell', 1 GO RECONFIGURE GO
Run Powershell Script from Task Scheduler
Action : Start a Program Program : powershell.exe Add Arguements (Optional) "& 'c:\checkDS\CheckDiskSpace_post.ps1'"
Using PowerShell to send a Windows Service Recovery Email Alert
Create Indexed Views in SQL Server; SQL Equivalent of Oracle Materialized Views.
SQL Server supports creating indexes on a view. It is the same concept as Materialized Views in Oracle. The main difference in the syntax in normal views and indexed views is the clause "with schemabinding" CREATE VIEW [dbo].[ViewName] with schemabinding AS SELECT id, activityID, userID, value, date FROM dbo.TableName WHERE (field = 'activityID') Now you … Continue reading Create Indexed Views in SQL Server; SQL Equivalent of Oracle Materialized Views.
SQL Server – How to Move Database Files – Data File and Log File – From One Drive Location to the Other
Most of the DBAs at some point of time in their career should have faced at-least one of the below situation(s):
1. The disk/SAN where the database files are located is going to be replaced
2. Disk is full and no more free space available
3. Data files and/or log files are not located on standard drives
There can be even more secnarios like the above where we may need to move the database files from current location to a new location. Starting SQL Server 2005 this can be simply achieved by using ALTER DATABASE T-SQL command
Let us take the below scenario for step-by-step Demo:
Database: AdventureWorks2012
Current Files Location: C:Disk1
NewTarget Files Location: C:Disk2
Step 1:Get the current database files Logical Name and Physical Location
USE master GO SELECT name AS LogicalFileName, physical_name AS FileLocation , state_desc AS Status FROM sys.master_files WHERE database_id =
View original post 229 more words
SimpleSQLDBA | Shadab Mohammad 