SQL Server Innovations

How to Login to as Different User or How to RunAs in Command Line Problem: One of the common problem is, how to login to SQL Server tools using different credentials and save it so, don't have to manually enter user name and password again and again Solution: Method1: If you are starting from the Desktop in Windows Vista, Windows 7, or Windows Server 2008 and Windows Server 2008 R2, you can do the following: Click  Start  (also shown as the circular Windows logo in the Start menu). Type  Command Prompt  and right-click  Command Prompt  when it appears in the Start menu. From the resulting context menu, click  Run as administrator . As another example, assume that you are logged on as a typical domain user to a workstation in the domain, but you needed to run several tools using a more privileged account. You could do the following: Open a Command Prompt window. Click  Start , click  Run , type  cmd  and press ENTER. In the Command Prompt window you just

How to find and Delete Duplicate records in SQL Server table Solution: Step1: Find duplicate records by using Group by OR CTE (Common Table Expression) /* Using GroupBY */ SELECT FirstName ,              LastName ,              COUNT (*) AS RecordCnt      FROM   dbo.Customer      GROUP  BY FirstName ,              LastName      HAVING COUNT (*) > 1 or /* Using CTE */ WITH CTE              AS (      SELECT FirstName ,                  LastName ,                  Row_number ()                  OVER (                      PARTITION BY FirstName , LastName              ORDER BY (              SELECT 1 ) ) AS RowNo          FROM   dbo.Customer )        SELECT *      FROM   CTE      WHERE   RowNo > 1 Step2: Delete duplicate records by using Group by OR CTE (Common Table Expression) /* Delete duplicate records by using Group By */ DELETE FROM    FROM dbo.Customer C          LEFT JOIN      (    SELECT FirstName ,              LastName ,        

How to run sql script using command prompt or How to deploy database in production environment or  How to create database using batch file or  How to run sql scripts stored in a folder or  Automating Database Creation or How to call various SQL Scripts from Master SQL Script (Master - Child Scripts) I have came across various clients, where they would not be have SQL Server Management Studio installed on their production box and if you want to run SQL Scripts using command prompt then you can follow below example Step1 - Create a batch file and when you execute this batch file then it will run create_db.sql file against Master database which is stored in C:\Mihir\Scripts folder and creates an empty database SQLCMD - E - dmaster - i C: \ Scripts \ create_db . sql PAUSE Step2 - Here I have created a Master SQL Script (Create_DB.sql), which will call various child Scripts /* BUILD A DATABASE */ -- This is the Master Script SET NOCOUNT ON GO P