Monday, 30 November 2015

Dynamic Query in SQL Server

Sometimes we need to modify query based on different condition , scenarios etc.
To avoid such long length and repeated code , we build dynamic query. Here output same as normal query execution.

Syntax -

declare @var1 varchar(max) or nvarchar(max)
Select @var1 = N'<query to be written>'

Exec <default sp name> @var1



Following points need to consider for writing dynamic query :

1.  Combination of methods and declaration
 
Suppose we are declare in following way as -

declare @var varchar(max)
select @var = N'select * from datUsers'
select @var
execute sp_executesql @var

Here error occur as
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Follow below combination based on variable datatypes

1.  using VARCHAR -

declare @var varchar(max)
select @var = N'select * from datUsers'
select @var
execute(@var)

OUTPUT - (1 row(s) affected)

2.  using NVARCHAR -

declare @var1 nvarchar(max)
select @var1 = N'select * from datCustomerGroups'
select @var1
execute sp_executesql @var1


OUTPUT - (1 row(s) affected)




Thursday, 15 October 2015

SQL SERVER – Using PowerShell and Native Client to run queries in SQL Server

Many time I heard about Powershell , but never go thorogh it. I tried it at basic level to connect to sql server from SQL Native Client. Coding looks similar to web developers  (like some properties as  'ExecuteScalar' ,'ExecuteNonQuery' ).
Its very interesting which comes with SSMS (SQL Server Management Studio) .
I already have sample database created on my local , which I would like to connect through powershell.
/*Create table and keep some records for fetching from SQL Query*/
CREATE TABLE [dbo].[tbl_Employee](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [FirstName] [varchar](max) NULL,
 [LastName] [varchar](max) NULL,
 [Address] [varchar](max) NULL)

 









"1" indicates record inserted successfully !!
   PowerShell very useful any kind of deployment on remote servers  .
  Share your comments with addition information .

Hope u will try it . You might get few error , but its interesting.





Ways to SELECT XML string using Nodes and OPENXML()

XML reading is most probably way of reading data from file. Only for knowledge we are checking performance by two ways OPENXML() and Nodes().
Check following the queries at your end as part of "Performace Tuning ".
OpenXML() -
                      OpenXML() is a rowset provider. OpenXML can be used in T-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear.
Example :
DECLARE @idoc int, @doc varchar(1000),
@XMLString xml
SET @doc ='
<ROOT>
<Customer CustomerID="1" ContactName="Nihar">
<Order CustomerID="1" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="2" ContactName="HR">
<Order CustomerID="2" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>';
--Create an internal representation of the XML document.EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
-- Execute a SELECT statement that uses the OPENXML rowset provider.SELECT distinct *
FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',1)
WITH (CustomerID varchar(10) '../@CustomerID',
ContactName varchar(20) '../../@ContactName',
EmployeeID varchar(20) '../@EmployeeID',
ProductDetails varchar(20) '@ProductID'
);


Output
CustomerIDContactNameEmployeeIDProductDetails
1Nihar511
1Nihar542
2HR372


Here we represent same output with XML.Node() which prepares output faster in complex queries
DECLARE @idoc int, @doc varchar(1000),
@XMLString xml
SET @doc ='
<ROOT>
<Customer CustomerID="1" ContactName="Nihar">
<Order CustomerID="1" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="2" ContactName="HR">
<Order CustomerID="2" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>';
SELECT @XMLString = cast(@doc as xml)
;WITH CTE AS (
SELECT
CustomerID = Events.value('../@CustomerID', 'varchar(120)'),
ContactName = Events.value('../../@ContactName', 'varchar(120)'),
Employeeid = Events.value('../@EmployeeID','varchar(120)'),
ProductDetails =events.value('@ProductID','varchar(20)')
FROM
@XMLString.nodes('/ROOT/Customer/Order/OrderDetail') AS XTbl(Events)
)
select distinct * from cte
Output -
CustomerIDContactNameEmployeeIDProductDetails
1Nihar511
1Nihar542
2HR372


You can try with realtime example and check performance with Node() and OpenXML()

Error Message Severity Levels in SQL Server

Sample Code:
BEGIN TRY
    SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT ERROR_SEVERITY() AS ErrorSeverity;
END CATCH;
GO

Output :
16

###############################################################
Here we didnt understand what exactly severity occurs in SQL Expression
Find descriptions here for each severity level.
Severity Levels 0 through 19 :
Error messages with a severity level of 10 are informational. Error messages with severity levels from 11 through 16 are generated by the user and can be corrected by the user. Severity levels from 17 and 18 are generated by resource or system errors; the user's session is not interrupted.

Severity Level 10: Status Information
This is an informational message that indicates a problem caused by mistakes in the information the user has entered. Severity level 0 is not visible in SQL Server.
Severity Levels 11 through 16
These messages indicate errors that can be corrected by the user.
Severity Level 17: Insufficient Resources
These messages indicate that the statement caused SQL Server to run out of resources (such as locks or disk space for the database) or to exceed some limit set by the system administrator.
Severity Level 18: Nonfatal Internal Error Detected
These messages indicate that there is some type of internal software problem, but the statement finishes, and the connection to SQL Server is maintained. For example, a severity level 18 message occurs when the SQL Server query processor detects an internal error during query optimization. The system administrator should be informed every time a severity level 18 message occurs.
Severity Level 19: SQL Server Error in Resource
These messages indicate that some nonconfigurable internal limit has been exceeded and the current batch process is terminated. Severity level 19 errors occur rarely; however, they must be corrected by the system administrator or your primary support provider. The administrator should be informed every time a severity level 19 message occurs.

Severity Level 20: SQL Server Fatal Error in Current Process
These messages indicate that a statement has encountered a problem. Because the problem has affected only the current process, it is unlikely that the database itself has been damaged.
Severity Level 21: SQL Server Fatal Error in Database (dbid) Processes
These messages indicate that you have encountered a problem that affects all processes in the current database; however, it is unlikely that the database itself has been damaged.
Severity Level 22: SQL Server Fatal Error Table Integrity Suspect
These messages indicate that the table or index specified in the message has been damaged by a software or hardware problem.
Severity Level 23: SQL Server Fatal Error: Database Integrity Suspect
These messages indicate that the integrity of the entire database is in question because of a hardware or software problem.
Severity Level 24: Hardware Error
These messages indicate some type of media failure. The system administrator might have to reload the database. It might also be necessary to call your hardware vendor.

Sharing above information ,found to be informative , !!Let me know if any correction required

Wednesday, 3 December 2014

Fix a Problem with Aliasing

We are aware of aliasing in SQL Server , but its impact is huge.

You are presented with another grouped query that fails, this time because of
an aliasing problem. As in the first exercise, you are provided with instructions on how to fix
the query.
              1. Clear the query window, type the following query, and execute it.
               
                   SELECT OrgID, SUM(SaleOrders) AS SaleOrders
                   FROM Sales.Orders
                   WHERE  SaleOrders > 20000
                   GROUP BY OrgID;

The query was supposed to return only shippers for whom the SaleOrders value is
greater than 20,000, but instead it returns an empty set. Try to identify the problem in
the query.

2. Remember that the WHERE filtering clause is evaluated per row—not per group. The
query filters individual orders with a freight value greater than 20,000, and there are
none. To correct the query, you need to apply the filter per each shipper group—not
per each order. You need to filter the total of all freight values per shipper. This can be
achieved by using the HAVING filter. You try to fix the problem by using the following
query.
              SELECT OrgID,  SUM(SaleOrders) AS TotalSaleOrders
              FROM Sales.Orders
             GROUP BY OrgID
             HAVING TotalSaleOrders > 20000
But this query also fails. Try to identify why it fails and what needs to be revised to
achieve the desired result

The problem now is that the query attempts to refer in the HAVING clause to the alias
totalfreight, which is defined in the SELECT clause. The HAVING clause is evaluated
before the SELECT clause, and therefore, the column alias isn’t visible to it. To fix the
problem, you need to refer to the expression SUM(freight) in the HAVING clause, as
follows.

SELECT Orgid, SUM(SaleOrders) AS TotalSaleOrders
FROM Sales.Orders
GROUP BY Orgid
HAVING SUM(SaleOrders) > 20000.00

[Note : Reference - http://blog.sqlauthority.com/author/pinaldave/ ]

Sunday, 20 July 2014

Show animated wait window ,while processing request

Its a major problem faced while processing DB request else functions etc .

The problem is when we perform command on database it takes time of few seconds, at that time my applications main window becomes halted.
when i performs command on database execution returns on main application when whole command is executed on database.
Just Like "NonQuery()"
Lets assume we are working on asp.net 4.0 and sending request to view.  i am showing a form before it and closing that form after the command, but problem is i am putting a GIF image on the form, because exection is halted on main application, the gif don't shows animation until the command is executed.
so that i putted it on a BackGroundWorker, but main thread is halted so its child tread also become halt.
we can not put database transaction in background worker, becuase sometime working is depended on result retrieved from database, and we want to halt application untill data is not fetched, but in this case also we want that on wait form, the gif image should show its animation.
What to do in this case, and how to do ?
For that no need of large block of code.
<asp:UpdateProgress ID="updateProgress1" runat="server">'
 <ProgressTemplate>
       <div id="progressBackgroundFilter">
                 </div>
           <div id="processMessage" style="text-align: center">
            <asp:Image ID="imgLoader" runat="server" ImageUrl="~/Images/loader.gif" />    <br />
           <b>Loading...</b>
  </div>
</ProgressTemplate>
Same problem I faced while processing request from Windows application.
That time , we normally used Thread class and its methods
Thread query = new Thread(() => Method("Method", "Parameters", Blah));
  query.Start();    //start your animation
  while (query.IsAlive)   {
    Application.DoEvents();
    Thread.Sleep(10);    }
I don't have any idea about  BackgroundWorker  in windows form. But you can try with it.

Saturday, 12 July 2014

Dynamic query in SQL

Dynamic query which nothing but conditional query which help to optimize query and improve performance of query.

Currently using  [AdventureWorksLT2008R2] datbase as following :

Try first

EXEC ('SELECT * FROM SalesLT.Product')

You can try following just for idea

DECLARE @SQL NVARCHAR(max), @ParmDefinition NVARCHAR(1024)
DECLARE @ListPrice money = 2000.0, @LastProduct varchar(64)
SET @SQL =       N'SELECT @pLastProduct = max(Name)
                   FROM SalesLT.Product
                   WHERE ListPrice >= @param1'
SET @ParmDefinition = N'@param1 money,
                        @pLastProduct varchar(64) OUTPUT'
EXECUTE sp_executeSQL      -- Dynamic T-SQL
            @SQL,
            @ParmDefinition,
            @param1 = @ListPrice,
            @pLastProduct=@LastProduct OUTPUT
            
SELECT [ListPrice >=]=@ListPrice, LastProduct=@LastProduct

Tips :

If you tried it, then also tried for Temp Table and Table variable for knowledge.

Query :
1)  DECLARE @T AS MyTable;

     INSERT INTO @T VALUES (1,2), (2,3)

     SELECT * FROM @T

     EXEC sp_executesql   N'SELECT *  FROM @T'

Error :  Declare Scalar variable @T

2)   Use  TYPE feature of SQL Server  for Table variable . Refer following query to execute sql statement with table variable

CREATE TYPE MyTable AS TABLE 
Foo int,
Bar int
);
GO

DECLARE @T AS MyTable;

INSERT INTO @T VALUES (1,2), (2,3)

SELECT *  FROM @T

EXEC sp_executesql
  N'SELECT *  FROM @T',
 N'@T MyTable READONLY',
  @T=@T 

3)