Aug 26, 2013

Sql script to know or set first day of week in sql server


We can get the first day of any week by following sql query:

SELECT @@DATEFIRST AS FirstDay

Or

SELECT
    CASE @@DATEFIRST
         WHEN 1 THEN 'Monday'
         WHEN 2 THEN 'Tuesday'
         WHEN 3 THEN 'Wednesday'
         WHEN 4 THEN 'Thursday'
         WHEN 5 THEN 'Friday'
         WHEN 6 THEN 'Saturday'
         WHEN 7 THEN 'Sunday'
    END AS FirstDay

Default value of first day of week varies according to language. For us_english is 7 that is Sunday.

We can set first day of week by following sql script:

SET DATEFIRST 5

Aug 22, 2013

Server was unable to process request. ---> Object reference not set to an instance of an object.detail: System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> Object reference not set to an instance of an object. at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

When I was calling SSRS report in sql server 2005. I get error message something like this:

Server was unable to process request. ---> Object reference not set to an instance of an object.detail: System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> Object reference not set to an instance of an object. at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

Then I debugged my code and found I was passing report parameters incorrectly. For example:

Dim strArr() As String
Dim vcReportParamter As String = "ID=5"

strArr = vcReportParamter.ToString.Split(";")
Dim objParams(strArr.Length + 1) As MYReport.ParameterValue

For count = 0 To strArr.Length - 1

If strArr(count) <> String.Empty Then

Dim objParam As New MYReport.ParameterValue
Dim strTempArr() As String

strTempArr = strArr(count).Split("=")
objParam.Name = strTempArr(0).Trim
objParam.Value = strTempArr(1).Trim

objParams(count) = objParam

End If

Next

Mistake was here: 

Dim objParams(strArr.Length + 1) As MYReport.ParameterValue
That is size of objParams more than actual numbers of parameter.

So I changed my code to :
Dim objParams(strArr.Length - 1) As MYReport.ParameterValue

which fixed my issue :)


If you get same error but due to some other reason put a comment. 

Aug 20, 2013

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Suppose I have table named tblEmp:

CREATE TABLE [dbo].[tblEmp](
  [ntEmpID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
  [vcName] [varchar](100) NULL,
  [vcMobieNumer] [varchar](15) NULL,
  [vcSkills] [varchar](max) NULL,
  [moSalary] [money] NOT NULL,
  [ntLevel] [bit] NOT NULL
)

If we will execute this query:

SELECT
   moSalary,
   (SELECT TOP(1) vcName FROM tblEmp WHERE moSalary = MAX(moSalary))
FROM tblEmp E
GROUP BY moSalary

We will get error message:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Cause: If we don't reference any column in inner query then it is considered and column of inner table and we cannot user aggregate function MAX with the column of inner query:

SELECT TOP(1) vcName
FROM tblEmp 
WHERE moSalary = MAX(moSalary)

Since there is not any group by clause in the inner query.

Solution:

SELECT
   moSalary,
   (SELECT TOP(1) vcName FROM tblEmp WHERE moSalary = MAX(E.moSalary))
FROM tblEmp E

GROUP BY moSalary 

Aug 19, 2013

Sql script to get Microsoft version of sql server


We can get Microsoft versions of sql sever by following sql query:

SELECT  @@MICROSOFTVERSION / 0x01000000 AS MSVersion

Value
Meaning
9
Sql server 2005
10
Sql server 2008

Aug 16, 2013

Sql script to get the current database in sql server


Sql query to get current working database:

SELECT DB_NAME() AS DatabaseName, DB_ID() DataBaseID

Sample output:

DatabaseName
DataBaseID
master
1

Aug 15, 2013

Attribute-centric column '' must not come after a non-attribute-centric sibling in XML hierarchy in FOR XML PATH.


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

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

INSERT INTO tblOrder(OrderID,Location) 
VALUES(1,'China'),(2,'USA')

If we will execute following sql queries :

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

We will get error message:

Attribute-centric column '' must not come after a non-attribute-centric sibling in XML hierarchy in FOR XML PATH.

Cause:  In SELECT Clause OrderId column must come first since it is attribute centric.  

Solution: correct order will be:

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

Output:

<row Country="1">
  <Location>China</Location>
  <OrderDate>2012-04-18T09:09:37.680</OrderDate>
</row>
<row Country="2">
  <Location>USA</Location>
  <OrderDate>2012-04-18T09:09:37.680</OrderDate>
</row>

Aug 12, 2013

How to know total numbers of rows in a cursor in sql server


In sql server we can know total numbers of rows or records only in static cursor. For example:

CREATE TABLE Employee(
    EmpID BIGINT IDENTITY PRIMARY KEY,
    EmpName VARCHAR(100)
)

INSERT Employee VALUES('Scott'),('Greg'),('Davis')

DECLARE EmpCursor CURSOR LOCAL STATIC FOR
SELECT EmpName FROM Employee

OPEN EmpCursor

SELECT @@CURSOR_ROWS AS TotalRows

Output:

TotalRows
3

The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.


Suppose we have created a table tblOrder and inserted some data into it in sql server

CREATE TABLE tblOrder(
     OrderId INT PRIMARY KEY,
     OrderName VARCHAR(100)
)

INSERT tblOrder VALUES(1,'PC'),(2,'Laptop')

There is another table tblOrderHistory:

CREATE TABLE tblOrderHistory(
     OrderId INT,
     OrderName VARCHAR(100),
     HistoryDate DATETIME DEFAULT(GETDATE())
)

Now if we will execute following sql query:

INSERT INTO tblOrderHistory(OrderId,OrderName,HistoryDate)
SELECT * FROM tblOrder

We will get error message like:

The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

Cause: Select statement from tblOrder selecting only two columns OrderId and OrderName while we are trying to insert in the three columns. So it is syntax error.

Solution:

Correct syntax is:

INSERT INTO tblOrderHistory(OrderId,OrderName)
SELECT * FROM tblOrder

Aug 11, 2013

Incorrect syntax near ''. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.


Suppose we have created a table tblPrice in sql server:

CREATE TABLE tblPrice(
     ItemID INT,
     Location VARCHAR(50),
     Price MONEY
)

Now we are creating a non-clustered index on table tblPrice

CREATE INDEX IX_Price ON tblPrice(Location)

Now if we will execute following sql query:

SELECT * FROM tblPrice (INDEX = IX_Price)

We will get error message like:

Incorrect syntax near ''. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

Cause: It necessary to use WITH clause when we want use table hints INDEX.

Solution:

SELECT * FROM tblPrice WITH(INDEX = IX_Price)

Aug 7, 2013

The target table '' of the OUTPUT INTO clause cannot have any enabled check constraints or any enabled rules. Found check constraint or rule ''.


Suppose we have created a table tblOrder and inserted some data into it in sql server

CREATE TABLE tblOrder(
     OrderId INT PRIMARY KEY,
     OrderName VARCHAR(100)
)

INSERT tblOrder VALUES(1,'PC'),(2,'Laptop')

Now we are creating other table tblOrderHistory with check conrstaint on column OrderName:

CREATE TABLE tblOrderHistory(
     OrderId INT,
     OrderName VARCHAR(100) CHECK(LEN(OrderName) > 2),
     HistoryDate DATETIME DEFAULT(GETDATE())
)

If we will insert records into the tblOrderHistory by executing following sql query:

DELETE FROM tblOrder
OUTPUT deleted.OrderId,DELETED.OrderName
INTO tblOrderHistory(OrderId,OrderName)

We will get error message like:

The target table '' of the OUTPUT INTO clause cannot have any enabled check constraints or any enabled rules. Found check constraint or rule ''.

Cause: It is due to check constraint on table tblOrderHistory. OUTPUT INTO clause doesn't insert records in table with check constraint.

Solution:

1. Drop the check constraint on table tblOrderHistory
2. In OUTPUT into clause first insert into temp table or table variable then insert the data of temp table or table variable into the tblOrderHistory

How to use database engine tuning advisor to get recommendations in sql server


How to get recommendation from sql server tuning advisor to improve query performance?

Sql server tuning advisor in tools which accept a trace file and suggest missing indexes, statistics etc and generate various type of report like costliest queries etc. Today we are performing a demo work to know about how to use and get suggestions from sql server tuning advisor.

Step 1: 

We are creating a new database named Exact_Help

CREATE DATABASE Exact_Help

In the Exact_Help database we are creating a table tblEmployee and inserting records into it:

--Creating a new table in Exact_Help database
CREATE TABLE tblEmployee(
    ntEmployeeID BIGINT IDENTITY PRIMARY KEY,
    vcName VARCHAR(100),
    ntAge INT,
    moSalary MONEY
)

--Inserting records into the table tblEmployee
INSERT tblEmployee
SELECT
    LEFT([Text],20),
    severity,
    language_id 
FROM Sys.messages

Step 2: Now we are opening sql server profile to create a trace file. To open it, click on Tools -> Sql Server Profiler.


We will get the trace properties window:

Here,

Trace Name: You can write any trace name.
Use the Template: Choose the Tuning in drop down since we are creating trace for tuning purpose.
Save to File: To specify the path where we want to save the trace file. We can also save in the sql server table.


Now click on Event Selection Tab:
I want to restrict the tuning process for Exact_Help database. For this click on Columns Filters button and in DatabaseName write Like Exact_Help. Now Run the Profiler.

Step 3:

Now we are executing few sql queries in SSMS for which we want to get query optimization recommendations.

SELECT vcName, ntAge
FROM  tblEmployee
WHERE ntAge = 16

SELECT vcName, moSalary
FROM  tblEmployee
WHERE vcName LIKE 'a%'

SELECT *
FROM  tblEmployee
WHERE moSalary > 1000

We are executing each sql queries 3 times one by one. Now we are going to stop the profiler.


In this example we will get the Optimization_Demo.trc trace file at desktop since we have saved at this path.

Step 4: Now we are going to open Database Engine Tuning Advisor. We can get it from Tools -> Database Engine Tuning Advisor.


Then connect to database engine.


Session Name: You can write any session name.
Workload File: Specify the path of your trace file.
Database for workload analysis: Specify the database name where you want to analyze the database.
Select databases and tables to tune: Specify for which tables you want to get optimization suggestions.

In tuning option tab there any many options which we can set according to our requirements.

Step 5: Now click on start analysis button (at the top position)


After sometime we will get recommendation of missing indexes, statistics etc.


At the top we will get estimated imprudent which means if we will follow its recommendation query performance may increase by that percentage. Recommendations are:

--Index Suggestion 1
CREATE NONCLUSTERED INDEX [_dta_index_tblEmployee_14_2105058535__K3_2]
ON [dbo].[tblEmployee] (
    [ntAge] ASC
)
INCLUDE ( [vcName])
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
ON [PRIMARY]

--Index Suggestion 2
CREATE NONCLUSTERED INDEX [_dta_index_tblEmployee_14_2105058535__K2_4]
ON [dbo].[tblEmployee] (
    [vcName] ASC
)
INCLUDE ( [moSalary])
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
ON [PRIMARY]

Apart from this tuning advisor generates different reports to analyze the query. For example: