Dec 31, 2013

Sql query to get script or text of any stored procedures without modifying in sql server


Sql query to get script or text of any stored procedures in sql server

We can get the script or text of stored procedure without modifying the procedure by using the system stored procedure sp_helptext. Synatx:

EXECUTE sp_helptext <ProcedureName>

For example:

EXECUTE sp_helptext 'usp_getdata'

Other sql script:

SELECT m.definition 
FROM sys.sql_modules m INNER JOIN Sys.procedures p
ON m.object_id = p.object_id AND p.name = 'AppProc'

Sql query for DELETE all the records or rows of all the tables in database in sql server


Sql query for DELETE all the records or rows of all the tables in database in sql server

We can delete all the records or rows of all tables of sql of given database using system stored procedure sp_MSforeachtable. For example:

EXECUTE sp_MSforeachtable N'SELECT * FROM ?'

If you have any quires you can ask here.

Sql query for SELECT all the records or rows of all tables in database in sql server


Sql query for SELECT all the records or rows of all tables in database in sql server

We can select all the records or rows of all tables of sql of given database using system stored procedure sp_MSforeachtable. For example:

EXECUTE sp_MSforeachtable N'SELECT * FROM ?'

If you have any quires you can ask here.

Dec 30, 2013

Cannot create nonunique clustered index on view '' because only unique clustered indexes are allowed. Consider creating unique clustered index instead.


Cannot create nonunique clustered index on view '' because only unique clustered indexes are allowed. Consider creating unique clustered index instead.

We cannot create the non-unique clustered  index on view. That is we must have to create clustered index using UNIQUE keyword. If we will create index like

CREATE CLUSTERED INDEX CI_ViewStu
ON dbo.view_StuInfo(RollNo)

We will get error message:

Cannot create nonunique clustered index on view 'dbo.view_StuInfo' because only unique clustered indexes are allowed. Consider creating unique clustered index instead.

Solution:

CREATE UNIQUE CLUSTERED INDEX CI_ViewStu
ON dbo.view_StuInfo(RollNo)

Cannot create index on view ''. It does not have a unique clustered index.


Cannot create index on view ''. It does not have a unique clustered index.

We cannot create the non-clustered index on view untill we have not first created the unique clustered index on view. For example:
First create unique clustered index on view:

CREATE UNIQUE CLUSTERED INDEX CI_ViewStu
ON dbo.view_StuInfo(RollNo)

Now we can create any numbers of non-clustered index on view.

CREATE NONCLUSTERED INDEX NCI_ViewStu
ON dbo.view_StuInfo(Age)

Cannot create index on view '' because the view is not schema bound.


Cannot create index on view '' because the view is not schema bound.

When we will create an index on view

CREATE UNIQUE CLUSTERED INDEX CI_ViewStu
ON dbo.view_StuInfo(Age)

We may get error message like:
Cannot create index on view 'view_StuInfo' because the view is not schema bound.

Since we can create an index on only those view which are schema bind with base table. Scheama bind means we cannot change the schema of the base objects unless and unill we don't remove the schema binding view  or alter or drop the view.

Solution:

DROP VIEW view_StuInfo
CREATE VIEW view_StuInfo WITH SCHEMABINDING
AS
SELECT Age,Name FROM dbo.Student

Note: It is necessay to specify the schema name (dbo) in the object.

Dec 23, 2013

Sql script to get the version of sql server


Sql query to get the version operating system where it has installed:

SELECT @@VERSION AS [Version]

Sample output:

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)   Mar 29 2009 10:27:29   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition on Windows NT 6.1 <X86> (Build 7600: )

Sql script to get the IO time in sql server


Sql script to get the input output busy time in sql server since last time sql server had started.

SELECT @@IO_BUSY AS [IO Time]

Sample Output:

IO Time
120

To get in second:

SELECT @@IO_BUSY * CAST(@@TIMETICKS AS FLOAT) / 1000000 AS [IO Time(Second)]

To get the IO time during the execution of some sql queries:

DECLARE @IOTime AS INT
SET  @IOTime = @@IO_BUSY

/*
    Sql statements
*/

SELECT @@IO_BUSY -  @IOTime AS [IO Time]

Dec 15, 2013

FOR XML PATH error in column '' - '//' and leading and trailing '/' are not allowed in simple path expressions sql server


I am creating a tblOrder table in sql server and inserting few records into it:

CREATE TABLE tblOrder(
    OrderID INT IDENTITY PRIMARY KEY,
    Location VARCHAR(50),
    OrderDate DATETIME DEFAULT(GETDATE())
)

INSERT INTO tblOrder(Location) VALUES('China'),('USA'),('India'),('UK'),('India')

If we will execute following sql queries :

1.

SELECT
    OrderID "@Id",
    Location "/Country/Location",
    OrderDate
FROM tblOrder FOR XML PATH

2.


SELECT
    OrderID "@Id",
    Location "//Country/Location",
    OrderDate
FROM tblOrder FOR XML PATH

3.

SELECT
    OrderID "@Id",
    Location "Country/Location/",
    OrderDate
FROM tblOrder FOR XML PATH
We will get error message:

FOR XML PATH error in column '' - '//' and leading and trailing '/' are not allowed in simple path expressions.

Cause:  It is due in incorrect XML path in column Location.

Solution: Correct XML path can be:

SELECT
    OrderID "@Id",
    Location "Country/Location",
    OrderDate
FROM tblOrder FOR XML PATH

Output:


<row Id="AA==">
  <Country>
    <Location>China</Location>
  </Country>
  <OrderDate>2012-04-18T00:09:56.953</OrderDate>
</row>
<row Id="AQ==">
  <Country>
    <Location>USA</Location>
  </Country>
  <OrderDate>2012-04-18T00:09:56.953</OrderDate>
</row>
<row Id="Cg==">
  <Country>
    <Location>India</Location>
  </Country>
  <OrderDate>2012-04-18T00:09:56.953</OrderDate>
</row>
<row Id="Cw==">
  <Country>
    <Location>UK</Location>
  </Country>
  <OrderDate>2012-04-18T00:09:56.953</OrderDate>
</row>
<row Id="ZA==">
  <Country>
    <Location>India</Location>
  </Country>
  <OrderDate>2012-04-18T00:09:56.953</OrderDate>
</row>

Dec 14, 2013

Unable to open the physical file "". Operating system error 2: "2(The system cannot find the file specified.)". (Microsoft SQL Server, Error: 5120)


When we will try to attach AdventureWorks database in sql server we may get error message like:

Unable to open the physical file "". Operating system error 2: "2(The system cannot find the file specified.)". (Microsoft SQL Server, Error: 5120)

Cause of this error is path of physical and log file. To solve this problem correct the physical path of data file and remove the log file as show in the following figure:


I hope this will solve this issue.