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

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'

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.'

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>

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]

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

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))

Index in sql sever PPT