Search This Blog

Loading...

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. 


Monday, September 29, 2014

Unable to retrieve list of databases. Reason: Failed to connect to the server. Reason: The 'SQLNCLI11' provider is not registered on the local machine.

Unable to retrieve list of databases. Reason: Failed to connect to the server. Reason: The 'SQLNCLI11' provider is not registered on the local machine.

Issue: while working on a Power Pivot Report we opened a power pivot report in Client Machine to as we need to change the Database Name of the Report.

Issue Description: After opening the Power Pivot window when we tried to connect to DB Server we got the below Error.



 Like in Screenshot the error indicates that SQLNClLI11 is not registered in your local machine.
You can also got an error if you are using sql 2008 then the Error will be SQLNCLI10 is not Registered on your local machine like below.



So below are link you can download the SQSQL Nativelient 10 and 11.

Download Native clin10 for SQL 2008 from below Link into your client System.

Download Native client11 for SQL 2012 from below link to Client System

Once you will download the appropriate Native clients as per your System i.e 32 or 64 bit you will be able to connect to DB Successfully.
In case after Installing the native client also you were still getting the Same Issues.

Then the  Best solution is 

Go to Advanced Section in the PowerPivot Data connection Window.
Providers- Change the Provider from 10.0 or 11.0 to Microsoft Ole Db provider for SQL Server
Test Connection
Ok


Now you will notice that you are able to connect to your database without installing Native clients also.



Tuesday, September 23, 2014

Sorting Months Name by calendar Order in Powerpivot Slicer

Sorting Months Name by calendar Order in Powerpivot Slicer

 When we add a column containing Month Name in Powerpivot Slicer fields it sort the data alphabetically like first (April, August, etc.) but we need the Month Name to be sorted Naturally
Like January, February, March….

There is no direct way to sort this you need to do workaround for slicers.

I am giving Example where I was using month Names from SQL query.
Sample Query that will fetch All MonthNames from Master database view spt_Values.

SELECT number, DATENAME(MONTH, '2014-' + CAST(number as varchar(2)) + '-1') monthname
FROM master..spt_values
WHERE Type = 'P' and number between 1 and 12
ORDER BY Number

We will insert this record in a temp table so that we can write a query to temp table
SELECT number, DATENAME(MONTH, '2014-' + CAST(number as varchar(2)) + '-1') monthname
 into #temp from  master.dbo.spt_values
WHERE Type = 'P' and number between 1 and 12
ORDER BY Number

Select * from #temp
Output
number            monthname
1          January
2          February
3          March
4          April
5          May
6          June
7          July
8          August
9          September
10         October
11         November
12         December

So if you will create a Slicer based on this Data you can produce the Issue you will have slicer sorted in alphabetical order like below.
Issue Screenshot



Solution: In order to sort in Natural Order you need to add a Prefix before your month name this is the only workaround that you can do with Slicer.

Note: Don't forget to add zero before 1,2….9 otherwise it will not sort your slicer as required.

So here is the SQL code to add prefix.
select case [MonthName] when 'January' then '01January'
when 'February' then '02February'

 when 'March' then '03March'
when 'April' then '04April'
when 'May' then '05May'
when 'June' then  '06June'
when 'July' then  '07July'
when 'August' then '08August'
when 'September' then '09September'
when 'October' then '10October'
when 'November' then '11November'
when 'December' then  '12December'
end [MonthName]
from #temp


Refresh your Report having MonthName Slicer and Sort from A to Z.The Slicer will get sorted in a Natural Sort from January to December as below.



Note : If you don't want to change anything in your sql code you can achieve the same result by following msdn blog-http://blogs.msdn.com/b/analysisservices/archive/2010/04/21/sorting-month-by-natural-sort-order.aspx 

Wednesday, August 27, 2014

SSRS Report Issue- Toggle Item Visibility default as Expanded with + sign

Issue- Toggled Item Text Box Sign in Report while giving Default as expanded showing + Sign instead of -.

Issue Description-We Created a SSRS Report which used to show Project Name and Employee assigned to the Projects .Default Nature of Report was expanded all but while previewing the Report the toggle sign appears as + sign in below Screenshot.

Test Report Highlighting Issue Screenshot.



Issue Solution. To fix this Issue below are the steps.

1. Select  your toggle Item (i.e. Text box  ) which is showing the + Sign.
           2.    Go to Properties of Selected Text box (Right Hand Side Properties ).
3.  Change the IntialToggleState Property from False to True as in below Screenshot.




4.       Preview the Report .Now on Default Expanded all  Report will show the correct Sign i.e (-).