SQL Server Express LocalDB

Intro

1.     ItIs installed by VS 2012 Pro

2.     This is execution mode of SQL Express (Not a service), so can do everything of
Express without having to install Express externally. To confirm this you can see
the sqlserver.exe process in the Task Manager

3.
Go to SQL Server Object Explorer Window in VS 2012 and you’ll see it
under servers with Name: (localdb)\Projects:

a.       Location:
C:\Users\khematar\AppData\Local\Microsoft\VisualStudio\SSDT

b.      Connection
String:
Data Source=(localdb)\Projects;Initial Catalog=master;Integrated
Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False

c.       Version
: 11

d.      OS:
Microsoft Windows NT 6.1 (7601)

e.      Product:
Microsoft SQL Server Express Edition (64-bit)

4.
When we create a new Project of type SQL Server Database Project (after
installing SSDT), we can see that in project properties, the target is SQL
Server 2012 only and the connection strin (in Debug) is set to the Local DB :
Data Source=(localdb)\Projects;Initial Catalog=Database1;Integrated
Security=True;Pooling=False;Connect Timeout=30

Advertisements

SQL Server Coding Tips

 

Note

Use ANSI syntax for Joining tables

Remove references to undocumented system
tables

VARCHAR / NVARCHAR and other datatypes
should always be created with the size

No use of defauly keywords as column/table
Names

No Variables declared with @@ prefix

Wildcard pattern usage based on the
documented recommendations

Usage of SCHEMABINDING in UDF without table
access

Add exception handling in the SPs and
functions

Avoid usage of nolock/readpast hint

Use Isnull over coalesce wherever possible

Length and data type of procedure
parameters should match with the base comparison tables

Trim and Len function needs to be used as
appropriate. 

Join needs to be improved and needs to have
all the relevant columns from the joined columns.

Repetitive queries need to be converted
into the temporary tables/table variable to avoid running the queries
multiple times

Make the transaction as small as possible
to avoid any blocking deadlocking and have better concurrency

String breaking functions should be
executed once and values should be stored in a table variable for the further
reuse in the stored procedure

Opportunity to split the big query into
smaller chunks.

Use Quotename function instead of manually
putting Square brackets around the columns.

 

 

–>

Writing SQL Queries: Tips and Tricks


1.
Intellisense in VS 2012 using SSDT: http://msdn.microsoft.com/en-us/library/vstudio/ee362021(v=vs.100).aspx

2.
Using
Transact-SQL Code Snippet:
http://msdn.microsoft.com/en-us/library/vstudio/ee362022(v=vs.100).aspx

Note: Shortcut is ^KX

3.
sql query
designer in visual studio 2012

a.
http://www.youtube.com/watch?v=791aVp4o2gM

b.
http://stackoverflow.com/questions/12229731/visual-studio-2012-database-designer-has-the-functionality-changed

4.
View the Table
Designer and Data from rt clk on table in Object Explorer (
http://technet.microsoft.com/en-us/library/ms190180.aspx)

5.
When creating a
Project from Database, it always executes queries on SqlServer Local DB (verify
from the connection string the the Project Properties). So you have to publish
it back in the Server at the end.

6.
How to: Design a
Query by Using the Query Designer:
http://msdn.microsoft.com/en-us/library/vstudio/ff852016.aspx#filters
and http://msdn.microsoft.com/en-us/library/ms172013.aspx

a.
Issue: My Toolbar is greyed out…(http://stackoverflow.com/questions/13087965/visual-query-designer-not-accessible-in-when-connecting-to-sql-server-2012-in-vi)
and http://msdn.microsoft.com/en-us/library/ms172011.aspx

7.
Use the Table
Designer to Manage Tables and Relationships:
http://msdn.microsoft.com/en-us/library/hh272680(v=vs.103).aspx

8.
Another issue:
When I create a .sql Query and run it, it says Successful, but I don’t see any
result, so the Q is : how to see the result of sql select * .sql query in
visual studio 2012. Ok so what is happening is that the query is not getting
executed at all as I was creating a stored proc :

CREATE PROCEDURE [dbo].[GetAllAddSEC]

 

AS

         SELECT  * from Person.Password

RETURN 0

. But when I run SELECT  * from Person.Password in Management Studio I see
the result

Now running SELECT  * from Person.Password  in VS 2012 gives this error:

/*————————

SELECT  * from Person.Password

————————*/

Msg 233, Level 20, State 0, Line 0

A transport-level error has occurred when sending the request to the server. (provider: Named Pipes Provider, error: 0 – No process is on the other end of the pipe.)

 

 

 

–>

SQL Server Getting Started

Download Here: http://technet.microsoft.com/en-US/sqlserver/ff898410

Getting Started

1.
Lesson
1: Basic Navigation in SQL Server Management Studio

2.
http://www.quackit.com/sql_server/tutorial/

3.
http://www.wikihow.com/Write-Basic-Sql-Statements-in-Sql-Server

4.
http://www.discoverthat.co.uk/tech/program/sql/various_sql.html

Startup ERROR RESOLVING:

a.
In the Management studio object window, I tried to create a new Database
and got this error:

—————————

Microsoft Visual Studio

—————————

A network-related or
instance-specific error occurred while establishing a connection to SQL Server.
The server was not found or was not accessible. Verify that the instance name
is correct and that SQL Server is configured to allow remote connections.
(provider: Shared Memory Provider, error: 40 – Could not open a connection to
SQL Server)

I found (using this ) that my svc was
stopped. But I get this error when trying to start it:

—————————

SQL Server Configuration
Manager

—————————

The request failed or the
service did not respond in a timely fashion. Consult the event log or other
applicable error logs for details.

3.
Also tired to start svc using:

·         Goto Start > All Programs
> SQL Server 2008 > Configuration Tools > SQL Server Configuration
Manager > SQL Server Services
.

And here you’ll see all the instances and their state.

·         The state
of the instance you were trying to connect can be stopped here.

·         Double
click on the instance and then click on connect.

·         It will
connect and now go back and run your application, you will be able to connect
with no error.

But  got the same error and in eventvwr it says:

The
SQL Server (SQLSERVER) service terminated unexpectedly.  It has done this
6 time(s).

Further
below it showed Acess Issues

But
I am launching it as admin, by launching mmc.exe from C:\Windows\System32 as
Admin and from there opening the Computer Management snap-in.

 

4.
I also
tried all stuff mentioned here:
http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

5.
Default Port for SQL is 1433, open this in inbound rules

6.
And tried this too: http://www.codeproject.com/Questions/335874/A-network-related-or-instance-specific-error-occur

 RESOLUTION:

.       Well, my
Studio is unable to connect to the DB so I downloaded the SP1 from http://www.microsoft.com/en-us/sqlserver/get-sql-server/try-it.aspx#tab2

I installed it on top of the existing
installation, though with a different server name and it worked.

 

 

–>

SQL Server Flavors

 

1.      SQL Server Compact Edition

a.
Is installed by VS 2012 Pro

2.      SQL Server Express Local DB

a.
Is installed by VS 2012 Pro

b. This is execution mode of SQL Express (Not a service),
so can do everything of Express without having to install Express externally

3. SQL Server Express

                 a. This is the

4.      SQL Server 2012

a.      Separate installation

SQL Server Management Studio

1.       Can connect to any version/ DB

 

 

–>