Jul 24, 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 we have created a table tblPrice and inserted some data into in it in sql server:

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

INSERT tblPrice VALUES(1,'UK',30),(2,'UK',40) ,(1,'USA',50),(2,'USA',70)

Now if we will execute following sql query:

SELECT Location,SUM(price)
FROM tblPrice
WHERE SUM(price) > 100
GROUP BY Location

We will get error message like:

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: We cannot use aggregate functions like COUNT, SUM, MAX, MIN, AVG etc directly in WHERE clause of a SELECT statement.

Solution:

We can write above query in alternative ways:

1.

SELECT Location,SUM(price)
FROM tblPrice
GROUP BY Location
HAVING SUM(price) > 100

2.

SELECT Location,SUM(price)
FROM tblPrice o
WHERE (SELECT SUM(i.price) FROM tblPrice i
     WHERE i.Location = o.Location
     GROUP BY i.Location ) > 100
GROUP BY Location

Output:

Location
(No column name)
USA
120.00

Jul 21, 2013

How to execute SSIS package by sql query in sql server


We can execute a SSIS package and pass the parameters by sql query in sql sever.

Suppose we have created a SSIS package with:
Name of package with path: C:\SSIS\ImportData.dtxs

Suppose it has two user defined variables:

1. ConnStr
2. FilePath

Now we are creating a stored procedure to execute this SSIS package:

CREATE PROCEDURE uspImportdata(
    @ConnStr AS NVARCHAR(2000),
    @FilePath AS NVARCHAR(2000),
    @PackageName AS NVARCHAR(2000)
)
AS
BEGIN

    SET NOCOUNT ON
   
    DECLARE @StatusCode INT
    DECLARE @Params AS NVARCHAR(2000)
    DECLARE @SSISCommand NVARCHAR(4000)
    DECLARE @ErrMsg AS VARCHAR(MAX) = ''
   
    BEGIN TRY
        
         SET @ConnStr = '/set \package.variables[ConnStr].Value;"\"' + @ConnStr + '\"" '
         SET @FilePath = '/set \package.variables[FilePath].Value;"\"' + @FilePath + '\"" '
   
         SET @Params =  @Params + @ConnStr + @FilePath
         SET @SSISCommand = 'DTEXEC /f ' + @PackageName + ' ' + @Params
        
         CREATE TABLE #ErrTbl(
                 ErrMsg VARCHAR(2000)
         )
            
        INSERT INTO #ErrTbl
         EXEC @StatusCode = xp_cmdshell @SSISCommand
        
         IF @StatusCode = 0
             SELECT 'Success'
         ELSE BEGIN
             SELECT @ErrMsg = @ErrMsg + ISNULL(ErrMsg,'')  FROM #ErrTbl WHERE ErrMsg LIKE '%Description%'
             RAISERROR(@ErrMsg,16,1)
         END
        
    END TRY
    BEGIN CATCH
         SELECT ERROR_NUMBER()
    END CATCH
END

To execute it:

EXECUTE uspImportdata
    'Data Source=ServerName;Initial Catalog=DbName;Integrated Security=True;',
    'C:\SSIS\demo.txt',
    'C:\SSIS\ImportData.dtxs'

Jul 15, 2013

How to check whether a given column exists in given table in sql server?

In sql server information about all the columns and its properties of a table is stored in the system view sys.Columns.

Suppose we have table named Student. We want to check int this table, column name Roll_No is present or not. Sql qurey for this:

IF EXISTS(SELECT 1 FROM Sys.columns WHERE object_id = OBJECT_ID('Student') AND name = 'Roll_No')
    SELECT 'Roll_No column in the Student table is present.'
ELSE
    SELECT 'Roll_No column in the Student table is not present.'

Jul 14, 2013

Column '' has invalid data type for attribute-centric XML serialization in FOR XML PATH sql server


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

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

INSERT INTO tblOrder(OrderID,Location)
VALUES(1,'<Root>China</Root>'),
(2,'<Root>USA</Root>')

If we will execute following sql queries :
SELECT
    Location "@Country",
    OrderID,
    OrderDate
FROM tblOrder FOR XML PATH

We will get error message:

Column '' has invalid data type for attribute-centric XML serialization in FOR XML PATH.

Cause:  XML data type cannot be used as attribute.  

Solution: We have to type case into varchar.

SELECT 
    CAST(Location AS VARCHAR) "@Country",
    OrderID,
    OrderDate
FROM tblOrder FOR XML PATH

Output:

<row Country="&lt;Root&gt;China&lt;/Root&gt;">
  <OrderID>1</OrderID>
  <OrderDate>2012-04-18T00:31:55.590</OrderDate>
</row>
<row Country="&lt;Root&gt;USA&lt;/Root&gt;">
  <OrderID>2</OrderID>
  <OrderDate>2012-04-18T00:31:55.590</OrderDate>
</row>

Jul 11, 2013

Sql script to get the CPU busy time in sql server


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

SELECT @@CPU_BUSY AS [Busy Time]

Sample Output:

Busy Time
120


To get in seconds :

SELECT @@CPU_BUSY * CAST(@@TIMETICKS AS FLOAT) / 1000000 AS [Busy Time(Second)]

To get the CPU busy time during the execution of some sql queries:

DECLARE @BusyTime AS INT
SET  @BusyTime = @@CPU_BUSY

/*
    Sql statements
*/

SELECT @@CPU_BUSY -  @BusyTime AS [Busy Time]

Jul 8, 2013

Xml data type methods are not supported in computed column definitions of table variables and return tables of table-valued functions. The error occurred at column "", table "", in the statement. Xml data type methods are not supported in computed column definitions. Create a scalar user-defined function to wrap the method invocation. The error occurred at column "", table "", in the CREATE TABLE statement.


We want to create a table which has a computed column which will keep the value form XML data.

CREATE TABLE  tblXML(
    XML_ID BIGINT IDENTITY,
    Data XML,
    Name AS Data.value('(/Root/Id)[1]', 'int' )
)

Now if will execute above sql statement in sql server, we will get error message :


Xml data type methods are not supported in computed column definitions of table variables and return tables of table-valued functions. The error occurred at column "", table "", in the statement. Xml data type methods are not supported in computed column definitions. Create a scalar user-defined function to wrap the method invocation. The error occurred at column "", table "", in the CREATE TABLE statement.

Cause: we cannot use XML data type methods in computed columns.

Solution:

Instead of directly using the XML methods in computed column we can use user defined function. For example:

 CREATE TABLE  tblXML(
    XML_ID BIGINT IDENTITY,
    Data XML,
    Name AS dbo.GetName(Data)
)

Where GetName is user defined function:

CREATE FUNCTION GetName(
    @Data AS XML
)
RETURNS VARCHAR(100)
AS
BEGIN
   
    RETURN @Data.value('(/Root/Name)[1]', 'VARCHAR(100)' )
   
END

Jul 5, 2013

Cannot execute query. There is more than one TABLE HINT clause specified for object ''. Use at most one such TABLE HINT clause per table reference.


If we will execute following sql query in sql server:

SELECT *
FROM Employee
WITH(FASTFIRSTROW,NOLOCK)
OPTION(TABLE HINT(Employee,FASTFIRSTROW),TABLE HINT(Employee,NOLOCK))

We will get error message like:

Cannot execute query. There is more than one TABLE HINT clause specified for object ''. Use at most one such TABLE HINT clause per table reference.

Cause: We cannot use more than one table hints in OPTION clause. It is syntax error.

Solution:

Correct syntax is:

SELECT *
FROM Employee
WITH(FASTFIRSTROW,NOLOCK)
OPTION(TABLE HINT(Employee,FASTFIRSTROW,NOLOCK))