Author: Ahmed Mujtaba

How to Insert from Joins

Here is an example of inserting into a temporary table from multiple joins:

select a.col,b.col,c.col into #temp1

from table1 a

join table2 b on a.column1ID= b.column2ID

join table3 c on a.column1ID= c.column3ID

where c.col= ‘anydata’

Connectivity – Network Related Error :

Today while connecting my MS SQL 2012 Server after a long time , i came across an error :
A network-related or instance-specific error occurred while establishing a connectionto 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: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

Solution :

I checked services of my pc , by typing the “services.msc” in the run , which opens the services dialog box .Then started the services of SQL Server which were not in running state.

I was then able to connect

Best Regards,

Working With Indexing -Part 1

OVERVIEW :

An index is a database’ essential structure and a component which makes faster , quicker and easier to find data based on more than one columns.

We can think of an index in database just like of searching a word in a dictionary .

For example : If you want to search a word : “Computer” , you have two paths to go to that word : First , go page by page in sequential word by word order , second path is : You quickly start searching through the Alphabet , like first in C then : ->o->m->p->u->t->e->r .

The first path will take for example : 1 minute to reach the word , the second path will take 10 seconds to reach the word . This is the beauty of Indexing , it reduces the path to the best optimal path and saves time.

Useful Script for checking SQL job History from Query

USE msdb
Go
SELECT j.name JobName,h.step_name StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT(‘000000′ + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,’:’),3,0,’:’) RunTime,
h.run_duration StepDuration,
case h.run_status when 0 then ‘failed’
when 1 then ‘Succeded’
when 2 then ‘Retry’
when 3 then ‘Cancelled’
when 4 then ‘In Progress’
end as ExecutionStatus,
h.message MessageGenerated,*
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id
where j.name =’jobname’
ORDER BY j.name, h.run_date, h.run_time
GO

What is Stored Procedure (Part 1)

Stored Procedure

The Stored procedure is actually a piece of code that can be saved in order to reuse it and saves a lot of time instead of writing the queries over and over again.

Depending on the need , SQL stored procedure can also use parameters as Input and Output.

It is a secure place to write your code , for instance : A stored procedure can be encrypted which secures it from unauthorized access.

Permissions can be given to only particular users to access the stored procedure to have a better control of security .

When a stored procedure is executed , it gets stored in the cache memory which increases performance and efficiency.

They are executed after writing execute statement.

Better error handling can be done in the stored procedure.

Working on Sets 1

The difference between UNION and UNION ALL Operator :

There is a difference between union and a union All operator which users are not aware of:

The union all operator will result in a set which will not eliminate the duplicates from the resulting set , means it will return the duplicate rows if residing in the set .

On the other hand union operator will eliminate the duplicates from the resulting set .

Whenever , you see a ALL keyword with union or intersect or except operators , it means it will give all the results including the duplicate records in the set .

How to Find All Sql Servers Installed on a Network

To check the number of SQL Servers installed on a LAN or network , we can use the following Commands on your commandline(cmd) :

osql -L
sqlcmd -L

Also , we can run the following command on Powershell to check the SQL Servers installed on a network with detailed results :

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

Creating SQL Tables

Tables are the database structures where your data actually gets stored.

Now , we shall create a database table ,you can write the create table below script to create :

use mydatabase; –assuming this is the name of your database

CREATE TABLE dbo.Student — dbo is schema & student is table name
(
studentid INT NOT NULL,
firstname VARCHAR(20) NOT NULL,
lastname VARCHAR(20) NOT NULL,
admissiondate DATE NOT NULL,
phoneNumber VARCHAR(18) NOT NULL,
TeacherId INT NULL
);

Connecting to SQL -2 (Errors – Named Pipes)

Named PIPES:

Named Pipes Provider, error: 40 – Could not open a connection to SQL Server

Users / developers often come across this type of errors while connecting /opening MS SQL Server. Here is quick review of how to overcome this issue.

To connect to MS SQL Server , we need network protocols , which connect and communicate with SQL. There are normally four Network Protocols to communicate to MS SQL .

Shared Memory
Named Pipes
TCP/IP
VIA

To overcome the above error of Named Pipes , Follow the instructions below :

Go to Start Menu of your windows, open the Microsoft SQL Server tab , find the SQL Server Configuration Manager .
Go to the protocols of SQL Server in the SQL Server Configuration Manager And enable the Name Pipes on the right window .Find the below Screen Clipping for reference :

Connecting to SQL

People face many connectivity issues while connecting to different SQL versions (MS SQL 2005,2008,2012). We shall be providing quick review of how to connect to different Versions on your local machine.

1- Connecting SQL 2005 :