Search This Blog

Loading...

Tuesday, April 28, 2015

Display comma Separated Values in SSRS Report

Display comma Separated Values in SSRS Report

Issue-How to Display comma Separated Values in SSRS Report.
Being a SQL and BI Developer I am quite active in various SQL and BI related groups including my own SQL and BI group.So there one of the member of the group asked be the below Question.

IN SSRS I have a table like this 

Id cellNo 
1 9xxx
1 8xxx
1 7xxx
2 9xxx
2 8xxx
3 7xx
4 9xxx

 output Required.

Id cellNo
1 9xxx,8xxx,7xxx
2 9xxx,8xxx
3 7xxx
4 9xxx

Ast it’s a common Requirement with lots of Geeks who are working with SSRS so I thought of writing a post on this  in my Site.

Solutions-In order to get the values as comma separated first we will create a test table with above records.
CREATE TABLE Number(Id int, CellNo varchar(100))
 INSERT INTO Number VALUES
( 1, '9xxx'),
(1, '8xxx'),
(1, '7xxx'),
(2, '9xxx'),
(2,' 8xxx'),
(3, '7xxx'),
(4,'9xxx')
           
 SELECT   * FROM  Number 

So once we have created a table we will create a Datasource and dataset in the SSRS Report and will show the same table in SSRS Report
.
Now in order to get the same format as required go to design view of Report.

Write the below Expression in the cell highlighted in Yellow.


=Join (LookUpSet (Fields!Id.Value, Fields!Id.Value, Fields!CellNo.Value, "DataSet1"), ",")

Preview the Report

Now you are able to see the desired Result with comma separated values Group on Id.But you will notice that there are duplicates rows coming .

So, In order to Remove duplicates Records in SSRS .

Go to design view of Report

Select the second Row

Click on Row Visibility

Select show and hide based on Expression as in Screenshot.

Write the below Expression


=iif(Fields!Id.Value = Previous(Fields!Id.Value), True, False)

Click ok

Preview the Report.

We will get the exact desired format without any duplicate Records.


Note:If you want to Get the same format in SQL check my older post on same.
http://www.sqlandssrssolutions.com/search?q=comma+separated 





Tuesday, March 31, 2015

SQL Server Job-Creating and Scheduling a SQL Job with an example

SQL Server Job-Creating and Scheduling a SQL Job with an example

In my previous post I have showed how to execute a sql script using a batch file. This post is about executing and automating  sql script using SQL Jobs
.
First we will create a test table and we will truncate the table using the SQL Job.

We can schedule a SQL  job to run at specific point or to run recursively after some time frame or we can schedule it daily, weekly, Monthly  quarterly and so on.

Below is an example :-

First we will create a database and a table and we will truncate that table using SQL  Job
--Below is the table and test script

create database test

create table test
(id int )

insert into test values (1),(2),(3)

Now we will truncate the table using SQL Job and will automate it to run on daily basis. For that we will write the truncate query as per the requirement. Here I will show an example of truncating table by scheduling a job.

We can consider a scenario where we need to truncate the table automatically on daily basis at 8 P.M so we will create a Job that will run on daily basis and will truncate the data daily daily at 8 p.m.

First Step

In Object Explorer Go to SQL Server Agent-Jobs
Note : Make Sure SQL Server Agent is running on your instance otherwise you won’t be able to see Jobs.
Right click on Job
New Job as in below screenshot.


Second Step
Give a Name to a Job and add a description as below Screenshot


Third Step
Go to Steps add a new step as below screenshot
-Give Step a Name
-Type-T-SQL  (As we are directly running a sql script in the job)
-Database-Select the database in which we are truncating table
-Command-Write or paste your SQL Code as in below screenshot.

Truncate table test



Step 4-Schedule the Job

-Click on New Schedule
-Give Job Schedule a Name
-Schedule Type-Recurring as we want to truncate the table daily at 8.PM
-Recurs every-1 Day
-Occur Once at -8 P.M as in below screenshot.



Schedule Type Description as below. 

Click Start automatically when SQL Server Agent starts to start the job when the SQL Server Agent service is started. 

Click Start whenever the CPUs become idle to start the job when the CPUs reach an idle condition. 

Click Recurring if you want a schedule to run repeatedly. To set the recurring schedule, complete the Frequency, Daily Frequency, and Duration groups on the dialog. 

Click One time if you want the schedule to run only once. To set the One time schedule, complete the One-time occurrence group on the dialog

Now you are done with your Job If you want any Alerts if Job fails and Notification to be send after completion of Job then you can add those steps also.

Target- If in case you want to target the Job in more than once server you can configure that also

A Target server allows the job to be run against a different server than the one it is defined on. 

Now once done click ok

In order to verify Job Right click on Job and run job you will get success message as below.

Now after testing schedule your job as per requirement

NOTE: SQL Jobs are not only limited to SQL Scripts you can create jobs to run SSIS Packages, analysis Services queries, Power shell commands and many others.














Saturday, February 28, 2015

Execute SQL Script using batch file

Execute SQL Script using batch file

A batch file is a type of script file, a text file containing a series of commands to be executed by the command line interpreter. A batch file may contain any command the interpreter accepts interactively at the command prompt
.
Below is an example of how to run a set of SQL queries using batch file

First we will create a sample database and a table and we will truncate that table using a batch file.

--Below is the table and test script
create database test

create table test
(id int )

insert into test values (1),(2),(3)

Now we will truncate the table using batch file. For that we will write the query as per the requirement. Here I will show an example by truncating table.

truncate table test

First Step

Save the above truncate query to any of your folder with .sql extension in my case I am saving the sql file in C drive CDE folder.

Note: Don’t save the SQL file directly into C drive as some time it can throw an error due to permission issue in C drive.

Second Step

Create a notepad file write the below code.

echo off
sqlcmd -E -S localhost -d test -i C:\CDE\Truncate_Test.sql
set /p delExit=Press the ENTER key to exit...

Save the above notepad file in any name and save it to any location with .bat extension.

Explanation of above script

-S    Server Name which is Localhost-Local SQL Server instance Name in our case.
-E   Trusted Connection or Windows Connection as I am using window connection.
-D   Database name Test-DB Name
-I     Input file C:\CDE\Truncate_Test.sql is the path where you have saved your SQL Code.

In case you are using mix mode authentication then use below one.

-U login Id /User Name
-P Password

Third Step

Double click on your batch file and it will run the SQL file and will truncate your table with the below screenshot.

Advantages of Batch file


You can perform many operations using Batch file like Running SQL Scripts using SQL Files like deleting data from tables clearing log files on monthly basis, copy files and a lot.


Monday, January 26, 2015

Transfer table from one schema to another Schema in SQL

Transfer table from one schema to another Schema in SQL

Issue-Moving table from one schema to another schema in SQL

Issue Description- A user created a table in default schema (dbo) but as per the requirement the new table should be part of another Schema (Emp).So the new table created in dbo schema need to be moved to another Schema Emp.

Issue Solution- Table Employee created in dbo schema need to be moved in Emp Schema


Case I –When you want to move only one table from one schema to another

So below is the query       
          
Alter Schema Emp --The Target schema Name where you are moving your table
    Transfer  [dbo].[employee]  --The Source schema Name along with table name from  where you are moving your table Employee

Case II –When you want to move all tables from one schema to another for eg. From dbo schema to Emp Schema

exec sp_MSforeachtable "ALTER SCHEMA Emp TRANSFER ?"




Tuesday, December 30, 2014

Alternate of Union in SQL

Alternate of Union in SQL 

Alternate of Union –How to get Same Result as union without using Union in SQL

Issue-One of my colleague asked me -can we achieve the union result without using union or is there any alternative way to get same result as union without using Union.

Issue Description-He told me that he went for an interview in some ‘XYZ’ company there he was asked with this question that How to get result same as what union does without using SQL.

Issue Solutions: So there are two ways which i know to do this but  there can be other ways also add in comments if you know more.

Below are two test table structures

create table Emp (id int, Name nvarchar(100), EmpAddress nvarchar(100))
Insert into Emp values (1,'A','AP'),(2,'B','MP'),(3,'C','JK'),(4,'D','TN')

--Second Table
create table Emp1(id int, Name nvarchar(100),EmpAddress nvarchar(100))

Insert into Emp1 values (5,'E','Kar'),(6,'F','Kerl'),(7,'G','UP'),(8,'H','Delhi'),(9,'I','Haryana'),(10 ,'J','UK')

Output Required. Same as union but without using Union
select * from Emp
union--without using Union
select * from Emp1


Method 1-Create a temp table and insert both table Record into temp table as below

--Select * into to create a new temp table employee and inserting first table record ---into it
select *   into #employee from Emp

--once first table record gets inserted into temp table insert second table records
insert into #employee
select * from Emp1

--Output verification:
Select * from #employee

Method 2-By using a full outer joins and coalesce like below:

with cte as
 (
select a.id as AID,a.name AName,a.EmpAddress AEmpAddress ,b.* from emp a full outer join emp1 b on 1=2
)
--as we have given condition 1=2 so it will show all records from both the table
--Now we will use coalesce to show value instead of null in first table record
Select coalesce(aid,id)ID,coalesce(aname,name)Name,coalesce(aEmpaddress,EmpAddress)EmpAddress
from cte

So I have showed two ways to achieve the result without using Union if you know more write in comments.







.

Sunday, November 30, 2014

Table as Input Parameter of Stored Procedure using Table-Valued Parameters in SQL

Table as Input Parameter of Stored Procedure using Table-Valued Parameters in SQL

Table-Valued Parameters are used as Input parameter of stored procedure, For that first we need to create Table type.Table Type are best used when we want to insert multiple records in a table using a stored procedure table valued Parameter and executing the procedure once.

Table valued Parameters  is a new parameter type in SQL SERVER 2008 that provides efficient way of passing the table type variable than using the temporary table.

Below is the example showing how to take table as Input Parameter of Stored Procedure using table valued Parameter in SQL

In order to explain this I will create a table, Table type and a Stored Procedure with table valued parameter.

--table Script
create table Emp
(
 Empid nvarchar(30),
empName nvarchar(30)
)


--Script to create a Table Type with Name Employee 
Create type Employee as Table
( Empid nvarchar(30),
empName nvarchar(30)
)

Note: The scope of Table Type is limited to Batch only and Input parameter of procedure should be read only when using Table Type as Input Parameter of Stored Procedure.

Now we will create a stored Procedure and we will define input parameter of stored procedure
as table type

--Script to  create a procedure with Input parameter table type
create procedure proc_x
 @x as employee readonly
as 
begin 
insert into Emp 
select * from @x;--this will insert record from table variable @x into Emp table 
end


Above Procedure will take table variable @x as input type which is using table type Employee 

To execute this Procedure we will declare a variable and will pass the variable to insert multiple rows to Emp table in a single execution of Procedure as below.


Declare @x as Employee 
--will insert all values in table varirable @x using insert script which will insert all record into Emp table
insert into @x values('1','rack'),('2','mack'),('3','Rex'),('4','Peter')

--Executing Procedure
Exec   proc_x @x

Verify Output

select * from Emp 

Output

Empid empName
1 rack
2 mack
3 Rex
4 Peter









Friday, October 17, 2014

Operand data type nvarchar is invalid for subtract operator.

Operand data type nvarchar is invalid for subtract operator.

Issue- In one of the SQL Groups forum one of the user asked a question that we want the diff of Max value of one column and min value of other column

 Difference=Max(Column1)-Min(Column2)

Below is the user Original question

Question-i am facing a small problem .kindly help me
select min(low) as low, max(high) as high from <table_name>
now i want to find the difference between two results 
i used 
select (select max(high)from <table_name>)-(select min(low)from <table_name>) as diff
This is not working ... kindly help me
Table screenshot


Solution-So for this I created a test script with same table structure so below is the script with output

create table x1
(name nvarchar(100),
high float,
low float
)

insert into x1 values ('xyz', 50.5, 48),('xyz', 60.2 ,50.3),('xyz', 55, 45),('xyz' ,62.9 ,47.6)



select max(high)MaxHigh,min(low)MinLow ,max(high)-min(low) differences from x


Output

MaxHigh           MinLow differences
62.9                  45                     17.9

After giving the Query to him he informed me that he is getting below error

Msg 8117, Level 16, State 1, Line 1
Operand data type nvarchar is invalid for subtract operator.

 In case if you are applying multiplication then you will get the below error.

Msg 8117, Level 16, State 1, Line 1

Operand data type nvarchar is invalid for multiply operator

In case if you are applying divide then you will get the below error.

Msg 8117, Level 16, State 1, Line 1

Operand data type nvarchar is invalid for divide operator.


These Errors means columns are not int or float type they are varchar type so i casted them into float to give him result without any error.

Solution with casting the varchar field to float.

select max( cast(high as float))-min(cast(low as float)) diff from x

Output

diff
17.9

or as per his query

select (select max( cast(high as float))from x)-(select min(cast(low as float)) from x) as diff

Output

diff
17.9


NOTE: Always keep in mind that applying any arithmetic operator or function in any varchar field you need to first cast or convert it into int, float or decimal.