Viet: how to

Showing posts with label how to. Show all posts

Sunday, May 15, 2016

Manage SQL Server Services from the Command Line

From https://technet.microsoft.com/en-us/magazine/dd421654.aspx

NET START MSSQLSERVER Starts SQL Server as a service.
NET STOP MSSQLSERVER Stops SQL Server when running as a service.

Sunday, June 28, 2015

Using WHERE with LIKE and ESCAPE syntax

Note to myself. From https://msdn.microsoft.com/en-us/library/ms173545.aspx

USE AdventureWorks2012 ;
GO
SELECT *
FROM Production.ProductPhoto
WHERE LargePhotoFileName LIKE '%greena_%' ESCAPE 'a' ;

Monday, May 25, 2015

Attach Sample DB AdventureWorksLT2012_Data

On this post Error 5120 When Attached Database, the attached database has two files the MDF and the LDF. But if you have only one MDF file, how do you attach?

Download from http://msftdbprodsamples.codeplex.com/releases/view/55330
Open MSSQL Management Studion as local administrator
Attach the file AdventureWorksLT2012_Data.mdf
High light the LDF files row to delete and click Ok button at the end

Sunday, June 1, 2014

How to mount an ISO image file on Windows 7

There is a free unsupported utility from Microsoft ( http://www.microsoft.com/en-us/download/details.aspx?id=38780 ) called Virtual CD-ROM Control Panel that enables users of Windows XP, Windows Vista, and Windows 7 to mount ISO disk image files as virtual CD-ROM drives.

I downloaded it, unzipped, moved driver to Windows System folder, configured it and attempted to use. Finally, remove it from my test computer.

Next, go to http://www.slysoft.com/en/virtual-clonedrive.html to get SlySoft Virtual Clone Drive, a freeware. After installed it, I can open the ISO image disk file from Windows Explorer. Virtual Clone Drive is easy to install and use.

Sunday, November 17, 2013

Check for existing record before insert

To avoid duplicate record in database, it is better to check for duplicate certain column data before insert. Primary key is a good mechanic to prevent duplicate row but don't count on it. Take a look the following SQL statements.

-- Creating temporary table (Notice the primary key is set to identity)
CREATE TABLE #tblTemp
    ( ID int identity primary key,
           ColA nvarchar(30),
           ColB nvarchar(50)
        );
GO

-- Inserting new record into the temporary table tblTemp
-- If execute this insert statement twice, you will have two row have same data in ColA and ColB
-- with difference ID
INSERT INTO #tblTemp VALUES ('Test A1', 'Test B1');

-- This is better
IF NOT EXISTS (SELECT 1 FROM #tblTemp WHERE #tblTemp.ColA='Test A1')
    INSERT INTO #tblTemp VALUES ('Test A1', 'Test B1')
SELECT * FROM #tblTemp

Another example using sample database AdventureWorks2012.
USE AdventureWorks2012;
GO
SELECT * FROM AdventureWorks2012.Person.Address WHERE AddressLine1='1970 Napa Ct.';
GO

-- We don't want a duplicate record for AddressLine1 '1970 Napa Ct.' and PostalCode 1597
IF NOT EXISTS (SELECT 1 FROM AdventureWorks2012.Person.Address WHERE AddressLine1='1970 Napa Ct.' AND PostalCode=1597)
    INSERT INTO AdventureWorks2012.Person.Address (AddressLine1, City, StateProvinceID, PostalCode)
    VALUES ('1970 Napa Ct.', 'Lane Cove', 50, 1597);

SELECT * FROM AdventureWorks2012.Person.Address WHERE AddressLine1='1970 Napa Ct.';
GO

Tuesday, October 8, 2013

Error 5120 When Attached Database

I got error 5120 when attempted to attach sample database AdventureWorks2012 to Microsoft SQL Server 2012 ran on Microsoft Windows 8.1 Preview.

Solution to this problem is simple: Close the current Microsoft SQL Server Management Studio and run it as administrator to attach database. Also, do not use 'sa'. Use Windows Authentication instead.