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.

My Notes on Microsoft Access

How to open Microsoft Access file in design mode?
Double-clicking the .mdb file while holding the shift key

How to show Navigation Pane?
Press F11
or
File -> Options -> Current Database -> Check the check box "Display Navigation Pane"

How to show these tabs: Create, External data, and Database Tools?
File -> Options -> Current Database -> Check the check box 'Allow Full Menus' in 'Ribbon and Toolbar Options' area

How to show the item menu when right click on it on Navigation Pane?
File -> Option -> Current Database ->  Check both "Allow Default Shortcut Menus" and "Allow Built-in Toolbars"

How to change the View?
On Ribbon select the Home tab and View
or
click View on Quick Access toolbar

How to switch between codes and form?
Press ALT F11

Link table manager:
Use file DSN file
http://office.microsoft.com/en-us/access-help/use-the-upsizing-wizard-HP005273009.aspx

How do I view the SQL for EXISTING queries and edit it in Access?
http://www.gcflearnfree.org/access2013
http://office.microsoft.com/en-us/access-help/introduction-to-queries-HA102749599.aspx
http://office.microsoft.com/en-us/access-help/view-modify-or-copy-a-query-s-sql-statement-mdb-HP005188056.aspx

How to create new file DSN?
Control Panel -> System and Security -> Administrative Tools -> Data Sources (ODBC)
Select "File DSN" and click on "Add" button
Select "SQL Server" on "Create New Data Source" and click "Finish" button
Continue to specify SQL Server and database to connect

Office 2013 developer training (Module 21)
http://dev.office.com/training

Unable to view hidden objects in Microsoft Access 2007
http://answers.microsoft.com/en-us/office/forum/office_2007-access/unable-to-view-hidden-objects-in-microsoft-access/

How to toggle between tabs in Access
http://answers.microsoft.com/en-us/office/forum/office_2007-access/how-to-toggle-between-tabs-in-access/c955f3e0-4ad0-46ca-bbfd-0a0713537c42
Ctrl+F6 will switch between all open tabs (or windows) in the database, regardless of type.

How to view documents in tab
Change Document Windows Option to "Tab Documents" using File -> Privacy Options

Sub-Forms and Sub-Reports
http://www.functionx.com/access/topics/sfsr.htm

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

How to edit an existing append query in Microsoft Access 2013?‏

If you would like to view or edit the SQL codes of an existing append or delete query from Navigation pane in Microsoft Access 2013, you may not open that query or right-click to see the menu since it's not in design mode.

To open an existing append, update or delete query in design mode: 

1. Open the Microsoft Access 2003 .mdb file while holding the shift key, Access 2013 will open with Navigation pane in design mode. 
2. Select the append query. 
3. Right click. 
4. Select Design View. 
5. Change to SQL View to see SQL statements.

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.

Microsoft SQL vs MySQL

I'm working with both databases and love them. Here are my notes their SQL syntax difference.

Select first 10 records from table:
In Microsoft SQL:

SELECT TOP 10 SchoolID, SchoolName
FROM schools
ORDER BY SchoolID;

In MySQL:
SELECT SchoolID, SchoolName

ORDER BY SchoolID

LIMIT 10

Create new table and insert data from existing tables
Microsoft SQL:
SELECT * INTO Schools_Bak20130916 FROM Schools;

MySQL:
CREATE TABLE Schools_Bak20130916 SELECT * FROM Schools;

Transact-SQL statement terminator

Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version. For more information, see Deprecated Database Engine Features in SQL Server 2008.

Read more at http://msdn.microsoft.com/en-us/library/ms177563(v=sql.100).aspx

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.

Microsoft SQL Server

When someone mentioned about Microsoft SQL Server, we thought about money. How much to pay?  Actually Microsoft offers two free versions: Microsoft SQL 2008 R2 Express Edition and Microsoft SQL Compact Edition (CE) 4.0. Developers can use Microsoft SQL 2008R2 Express Edition or Microsoft SQL CE 4.0 for prototype, development, and testing.

Microsoft SQL CE 4.0 is an embedded SQL server database engine. It can use to build standalone and occasionally connected applications for mobile devices, desktops, and Web clients. The maximum database size is 4GB per file.

Microsoft SQL 2008 R2 Express is a free edition of SQL Server ideal for developing and powering desktop, web and small server applications. It can support up to 10GB, 1 CPU and 1GB RAM.

Other paid Microsoft SQL 2008 R2 editions such as Standard, Enterprise, or Data Center support up to 524PB, more than 1 CPU and 64GB RAM, and much more features.

Reference:
Compare Microsoft SQL Server Editions
VS 2010 SP1 and SQL CE - ScottGu's Blog
Everything SQL Server Compact

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