SQL server 100% CPU usage fix

This issue is very common and commonly faced by DBA, developers and server management team on production server i.e. ... Read more »

SQL for Beginners

SQL (Structured Query Language)

• It is high level language which provide you capability to query data from any structured data source (Tables). 
• High level language means language which is used by us to communicate (English). 
• For learning, we will be using online SQL editor provided by W3C.
• http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

SQL script:

SQL Queries:

Using Wild Card (*) :
Selecting all data 
SELECT * FROM CUSTOMERS

Specific Columns:
SELECT CustomerName, ContactName,Address,
 City FROM CUSTOMERS

Click to continue

SSIS Row Number

Generating Row Number in SSIS for each record. 

Steps for Generating Row Numbers are: Click to Download Project.

PIVOT:
•Convert your output from (values)vertical order to horizontal(making columns) one

Topic to cover:
•PIVOT example
•PIVOT syntax
•Interview Question
•PIVOT on northwind
•Dynamic PIVOT query

Click to continue

SQL VIEWS and Security

What View do ?
• First,  It help you break down complex queries.
• Secong, Query reusability.
• Third, Security.(not fully recommendable)

What is View ?
View is just a stored query. Can also be said as virtual 
table. They don't hold any data into them.Data comes from 
real tables.SQL select query operator (WHERE, ORDER BY, etc)
 can be used with it.

Script:

File 1:

... Read more »

Sub Queries-

Three basic rules only.

Where you can place sub query?
3 places where you can place your sub query:
SELECT ----- FROM
FROM   ----- WHERE
WHERE  ----->

SELECT ----- FROM
_____________________
(sub query can only return single value & no alias to value returned is required)

Correct:
_________

(select max(val) from abc where ---) Ex output: 10
(select top 1 val from abc where ----) Ex output: abctext

Wrong:
_________
(select val from abc where ----) and result is returning multple rows:

Ex. ouput of above query:
val
___
12
32
45
6
45

Click here to view complete article

LINQ for Beginners

Topic covered are :

• WHERE clause in LINQ
• LEFT JOIN in LINQ
• INNER JOIN in LINQ
• GROUP BY in LINQ
• ORDER BY in LINA

To download script click here.

SQL XML:

You can generate XML from SQL queries without even doing single line of code in your programming language. Below is the script used in above video:

--Query 1

select 1 as tag,null as parent,
c.customerid as [Customers!1!Customerid],
null as [order!2!orderid]
from customers c inner join orders o
on o.customerid=c.customerid
union
select 2 as tag,1 as parent,
c.customerid,
o.orderid
from customers c inner join orders o
on o.customerid=c.customerid
order by [Customers!1!Customerid],[order!2!orderid]
for xml auto

--Query 2

select 1 as tag,null as parent,
c.customerid as [Customers!1!Customerid],
null as [order!2!orderid]
from customers c inner join orders o
on o.customerid=c.customerid
union
select 2 as tag, ... Read more »

Below is script you can use to get scheduled records:

-- Table you requre to hold schedule time and other information
-- you can add other columns too to suite your requirements. 
-- Main columns are scheduletype :  it hold information if schedule is daily, weekly or monthly
-- Scheduletime : Time when you need to send report
-- Scheduleday : Day of week or month you want to fire report
-- for week Sunday holds value 1, monday 2, ... saturday equal 7 and repeat for next week from 1 to 7
-- for month value for scheduleday is for day of month means 1 is 1st day of month 
-- for daily its value doesn't matter
-- other columns are just dependent on requirement
create table #scheduletable
(
reportname varchar(100),
scheduletime time,
scheduletype varchar(10),
scheduledate date,
scheduleday int,
userid int
)
--Note: I am using temporary table. Use table ac ... Read more »

IE11 ssrs report viewer OR report viewer internet explorer 11

If you are doing this in ASP.net. Then add ASP NET Folder App_Browser. Then add Browser File and replace all text with text below or download file attached below:

<browsers>
 <browser id="IE11Preview" parentID="Mozilla">
 <identification>
 <userAgent match=
		... 
		
			Read more »