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.

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' ;

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?

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

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

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.

Now, test it as normal user by update a record in the AdventureWorks2012 database.

SQL in the City

Last Friday, I attended "SQL in the City," a free SQL training workshop held in Los Angeles, California. Unlike other free workshops or conferences that mostly about sales marketing, this workshop had a lot of useful technical sessions presented by well known gurus in SQL community. Just few days before the event, I changed my mind. I thought that I will not go since two hours driving to Los Angeles was an inconvenience.

At the event, the speaker asked us about whether we were developers, DBA, or both. I thought I was a developer; that is what I have done my whole career. Actually, I was both when I working for a small company: Developer and DBA. There were many DBA sessions I attended more than Developer sessions since they are my interesting issues.

On the way home, I was so happy that I did not skip this great event. I appreciated Red Gate provided me and others a chance to learn new things and refresh our knowledge.

Playing with Microsoft SQL Server 2008

First, try to download SQL Express 2008 from Microsoft web site. Tired of waiting, click cancel. Back again to the Microsoft SQL Server homepage to looking the DVD. After logged in, filedl out some information, abort when the USD$ 5.00 shipping charge appeared.

Let fool around the other place!

From Microsoft web site:

Which edition of SQL Server 2008 Express is right for you?

SQL Server 2008 Express is available in the following 3 editions (each is available from the Install Wizard):

SQL Server 2008 Express 

SQL Server database engine - create, store, update and retrieve your data 

SQL Server 2008 Express with Tools 

SQL Server database engine - create, store, update and retrieve your data 

SQL Server Management Studio Basic - visual database management tool for creating, editing and managing databases

Microsoft® SQL Server® 2008 Express with Advanced Services 

SQL Server database engine - create, store, update and retrieve your data 

SQL Server Management Studio Basic - visual database management tool for creating, editing and managing databases 

Full-text Search - powerful, high-speed engine for searching text-intensive data 

Reporting Services - integrated report creation and design environment to create reports

TechNet Evaluation Center 

SQL links

Put some SQL articles and links here so I can easy to look up.

http://vyaskn.tripod.com/
http://www.sqldev.net/xp/xpsmtp.htm
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
http://www.lazydba.com/mssqldba.pl
http://www.sqldts.com/default.aspx
http://sqlservercode.blogspot.com/

XPSMTP.DLL - SQL Server SMTP Mail XP
INF: How to Configure SQL Mail
Sending Mail from SQL Server
Named Pipes Provider, error: 40 - Could not open a connection to SQL Server - MSDN Forums
KB 321185: How to identify your SQL Server version and edition
KB 884525: Additions to the SQL Server 2000 Service Pack 4 readme files
Named Pipes Provider, error: 40 - Could not open a connection to SQL Server