Wednesday, April 13, 2016

What is – DML, DDL, DCL,TCL and DQL





What is DDL Command  : (Data Definition Language)

It is used to create and modify the structure of database objects in database.

Command
Description
CREATE
Creates a new table, a view of a table, or other object in database
ALTER
Modifies an existing database object, such as a table
DROP
Deletes an entire table, a view of a table or other object in the database



What is DML Command  : (Data Manipulation Language)

It is used to retrieve, store, modify, delete, insert and update data in database.

Command
Description
INSERT
Creates a record
UPDATE
Modifies records
DELETE
Deletes records


What is DCL Command  : (Data Control Language)

It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

Command
Description
GRANT
Gives a privilege to user
REVOKE
Takes back privileges granted from user


What is DQL Command  : (Data Query Language)

It is used to fetch the records from Physical files


Command
Description
SELECT
Retrieves certain records from one or more tables

What is TCL Command  : (Transaction Control Language)

It is used to manage different transactions occurring within a database.

Command
Description
COMMIT
To commit the bunch of SQL Statements
ROLLBACK 
To revert a transaction


Tuesday, April 5, 2016

Issue in expanding MSDB packages in SSIS


When you are trying to expand MSDB folder in Integration services. if you are facing below error.




TITLE: Import Package
------------------------------

The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in %SQL_PRODUCT_SHORT_NAME% Books Online.

Login timeout expired
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
Named Pipes Provider: Could not open a connection to SQL Server [2].  (MsDtsSrvr)

------------------------------
ADDITIONAL INFORMATION:

Login timeout expired
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
Named Pipes Provider: Could not open a connection to SQL Server [2].  (Microsoft SQL Server Native Client 11.0)


Solution : you need to configure Integration Services service 

you will have to modify the configuration file if your packages are stored in a named instance or a remote instance of Database Engine.

Configuration file : MsDtsSrvr.ini.xml

Location :  %ProgramFiles%\Microsoft SQL Server\120\DTS\Binn




Monday, April 4, 2016

which version of Windows you are running



To know which version of windows os you are using enter the following commands in the Command Prompt

wmic os get caption

wmic os get osarchitecture


Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists

If you received below error while attaching a .mdf file in cluster environment please follow below steps to resolve the issue ERROR Ca...