MS SQL 2005 : To Check the size of the Database

Use the Below Query to Check the Database Size with all *.mdf & log.*ldf file sizes

****QUERY****

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'Database_Name'

Size of DB - shyamb4u

Generating CSV file using SQL Server 2005

A lot of times it is our inability to learn some of the simple techniques present in the tools that we use make us even more productive.

Here are the steps to achieve the same. 

Go to SQL Server Management Studio ---> Choose Tools from Menu Bar 
--> Click on Options.
 Go to “Query Results” ---> “SQL Server” ---> Click on “Results to Text”

shyamb4u

Learn Access 2007 in 30 Days.

Access 2007 is the database software in the Microsoft 2007 Office suite that allows you to order, manage, search, and report large amounts of information.

The below link provides complete source and learning process of Access 2007

CLICK TO DOWNLOAD - Access 2007

What is SCHEMABINDING?

SCHEMABINDING with a Query...

***Table***

CREATE TABLE Sampletbl ( col1 DATETIME ) 

***Data***

INSERT  INTO Sampletbl
VALUES  ( GETDATE() + 1 )

***Function***

CREATE FUNCTION SampleFunction ( )
RETURNS DATETIME
    WITH SCHEMABINDING
AS 
    BEGIN 

        DECLARE @t DATETIME

        SELECT  @t = col1
        FROM    dbo.Sampletbl

        RETURN @t

    END

MS SQL - Standby Servers and Types of Standby Servers

Standby servers - Standby Server is a type of server that can be brought online in a situation when Primary Server goes offline and application needs continuous (high) availability of the server. There is always a need to set up a mechanism where data and objects from primary server are moved to secondary (standby) server. This mechanism usually involves the process of moving backup from the primary server to the secondary server using T-SQL scripts. Often, database wizards are used to set up this process.

Languages for BI – MDX, DMX, XMLA

BI stands for Business Intelligence 

What languages BI uses to achieve the goal? 

MDX – Multidimensional Expressions. This language is used for retrieving data from SSAS cubes. It looks very similar to T-SQL, but it is very different in the areas of conceptualization and implementations.

DMX – Data Mining Extensions. This is again used for SSAS but rather than cubes it is used for data mining structures. This language is more complicated than MDX. Microsoft has provided lots of wizards in its BI tools, which further reduced experts for learning this language which deals with data mining structures.

XMLA – XML for Analysis. This is mainly used for SSAS administrative tasks. It is quite commonly used in administration tasks such as backup or restore database, copy and move database or learning meta data information. Again, MS BI tools provide lots of wizards for the same.

Android 5.0 Lollipop VS Android 4.4 Kitkat

The announcement of Android 5.0 Lollipop has, indeed, incorporated another achievement in the kitty of Android in terms of technological advancement. Android L is, certainly, considered to be one of the biggest updates by Android as of now due to the incredible changes and upgraded progressions. The new version of Android was announced recently at a conference in San Francisco with tons of changes, especially for developers. At present, it is available only in Google Nexus 6 and Google Nexus 9 tablet. However, there is a lot of chit-chat and gossip in the market about the features that both Kitkat and Android L provide to the users, and which is the best. Have a look at what more or less Android L is providing, and whether it is better than Android 4.4 Kitkat or not.

What are the difference between DDL, DML and DCL commands?

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object

RAID Concept & Levels

RAID (originally redundant array of inexpensive disks; now commonly redundant array of independent disks) is a data storage virtualization technology that combines multiple disk drive components into a logical unit for the purposes of data redundancy or performance improvement.

Displays a list of files and sub directories in a directory in CMD Prompt.

 DIR [ drive:][path][file-name] [/A[[:]attributes]] [/B] [/C] [/D] [/L] [/N]
  [/O[[:]sortorder]] [/P] [/Q] [/R] [/S] [/T[[:]timefield]] [/W] [/X] [/4]

  [drive:][path][filename]
          Specifies drive, directory, and/or files to list.

  /A          Displays files with specified attributes.
           D  Directories                R  Read-only files
           H  Hidden files               A  Files ready for archiving
           S  System files               I  Not content indexed files
           L  Re parse Points        -  Prefix meaning not

Just set type of desired file attribute, in your case /A:D (directory)

dir /s/b/o:n/A:D > filename.txt

Source: Link

CASCADE in SQL Server (Foreign & Primary Key Constraints)

We have several tables in an SQL database referencing each other with foreign key constraints. If we need to delete a row in one of these tables as well as all other rows that reference it, then we have two options. The first option is to execute an explicit DELETE statement for each table that contains referencing rows and then finish by deleting the referenced row (this order is important if we don’t want to violate any foreign key constraints). The other option is to declare our foreign keys as ON DELETE CASCADE. This clause tells the database to automatically delete the referencing row if the row it references is deleted.

Select * from dbo.ProductDetails
Select * from dbo.Products

Suspect Query for MS SQL 2005

How To Repair A Suspect Database In MSSQL 2005:

Issue
You have a database in MSSQL that is tagged as (suspect) and you are unable to connect to the database.

Possible Causes
The database could have become corrupted.
There is not enough space available for the SQL Server to recover the database during startup.
The database cannot be opened due to inaccessible files or insufficient memory or disk space.
The database files are being held by operating system, third party backup software etc.
There was an unexpected SQL Server Shutdown, power failure or a hardware failure.

Get Client IP Address in SQL Server..

Execute the below function and call this function through a Stored Procedure. It will return the IP address of the client system.

CREATE FUNCTION [dbo].[GetCurrentIP] ()
RETURNS varchar(255)
AS
BEGIN
    DECLARE @IP_Address varchar(255);

    SELECT @IP_Address = client_net_address
    FROM sys.dm_exec_connections
    WHERE Session_id = @@SPID;

    Return @IP_Address;
END


Source