Search This Blog

Loading...

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





 



Monday, August 25, 2014

Converting Column Values as Comma Separated String based on condition in SQL

Requirement- Our Requirement was Employee which are working on same Projects OR Employee who are assigned to same projects should come as a comma separated string in a single row for each Project from Project table  like below..

Output Required

ProjectName           name
Project1                rakesh, mukesh, sukesh
Project2                ajit, surjit


Below is test Project  table structure.

create table Projects
(Projectname varchar(200),Empname varchar(200)
)

insert into Projects values ('Project1','rakesh'),('Project1','mukesh'),('Project1','sukesh'),('Project2','ajit'),('Project2','surjit')

select * from Projects

Projectname      Empname
Project1            rakesh
Project1            mukesh
Project1            sukesh
Project2            ajit
Project2            surjit

In the above Projects table

For Project1-Employee assigned-Rakesh,  Mukesh,  Sukesh
For Project2 –Employee assigned-Ajit, Surjit

Each Row in Project table is showing Project Name and employee name assigned to that project.

We want the output like all employee names assigned to same project  should come as comma separated value as an string in a single row for each project.

So below is the Script  that convert column values as comma separated values

--create table Projects
--(Projectname varchar(200),Empname varchar(200)
--)

--
--insert into Projects values ('Project1','rakesh'),('Project1','mukesh'),('Project1','sukesh'),('Project2','ajit'),('Project2','surjit')

--Below script will show column values as comma separated string based on condition

declare @ProjectName nvarchar(100)
declare @name nvarchar(200)
declare @newProjectName nvarchar(100)
declare @newname nvarchar(100)
set @newProjectName='0'
set @newname ='0'

create table #tbl (ProjectName varchar(200) ,name varchar(100))

declare C cursor  for select Projectname,Empname from Projects
open  c
fetch next from c into @ProjectName,@name
set @newProjectName=@ProjectName
while @@FETCH_STATUS =0
begin
if @newProjectName= @ProjectName
begin
  if (@newname ='0')
 begin
   set @newname =@name
 end
   else
   begin
   set @newname =@newname  + ', '+ @name
end
end

if @newProjectName<>@ProjectName
begin
insert into #tbl values(@newProjectName,@newname )
set @newProjectName=@ProjectName
set @newname =@name

end
fetch next from c into @ProjectName,@name
end

if @newProjectName=@ProjectName
begin
insert into #tbl values(@newProjectName,@newname )

end

close c
deallocate c
select * from #tbl
drop Table #tbl


Script Output 

ProjectName                 Name
Project1                        rakesh, mukesh, sukesh
Project2                        ajit, surjit




Saturday, July 26, 2014

We cannot locate a server to load the workbook data model

We cannot locate a server to load the workbook data model

Issue-The Error was coming in SharePoint 2013 site when refreshing or clicking on any slicer of  the Power table Report in excel 2013 workbook.

Issue Cause-This issue occurs because the Analysis Services instance has not been configured in the Central Administration site.

Issue Solutions-Restart your SQL Server Analysis Server (PowerPivot) and Reset the IIS.


In most of the case above will fix your above Issue.

If in case the above method will not fix the Issue then configure the Excel Services service application on the SharePoint server. To do this, follow these steps:

1.       In the Application Management  section of the Central Administration home page, click Manage service applications.
2.       On the Manage Service Applications page, click the Excel Services service application that you want to configure.
3.       On the Manage Excel Services page, click Data Model.
4.       Click Add Server.
5.       In the Server Name box, type the name of the Analysis Services instance that you want to add.
6.       Click OK.


Monday, July 21, 2014

Unable to open the physical file in SQL

                Unable to open the physical file in SQL

Issue-Issue in SQL while Attaching MDF File or Restoring Backup file from some location.

Error1
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\ DB \AdventureWorksDW2012_Data (1).mdf". Operating system error 2: "2(The system cannot find the file specified.)".

Error 2
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\ DB \AdventureWorksDW2012_Data.mdf". Operating system error 32: "32(failed to retrieve text for this error. Reason: 15105)".


Issue Description-While attaching the Database  mdf file in sql server we were getting these above errors.

--Attaching MDF File without ldf file
CREATE DATABASE AdventureWorksDW2012
ON (FILENAME = 'C:\DB\AdventureWorksDW2012_Data.mdf')
FOR ATTACH_REBUILD_LOG ;

Error

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\DB\AdventureWorksDW2012_Data (1).mdf". Operating system error 2: "2(The system cannot find the file specified.)".

Issue Solution

-Run SSMS as an Administrator
-Restart SQL Server

Now you will be able to attach the DB Successfully.

Run your query again 

--Attaching MDF File without ldf file
CREATE DATABASE AdventureWorksDW2012
ON (FILENAME = 'C:\DB\AdventureWorksDW2012_Data.mdf')
FOR ATTACH_REBUILD_LOg

Output.It will attach the mdf file and will create a new ldf (log file) in same location 
with the message below.

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2012_log.ldf" may be incorrect.
New log file 'C:\DB\AdventureWorksDW2012_log.ldf' was created.

NOTE: If after Running SQL as Administrator and Restarting the sql server won’t work for you then check for permission in your folder where you have kept your mdf or back file then go to “Sharing and Security of that folder and Allow-Full control.

Tuesday, July 8, 2014

T-SQL Script to perform DML Operations (Select, Insert, Update and Delete) based on DB Name

T-SQL script that will Perform DML Operations (Select, Insert, Update and Delete) based on DB Name


Requirement-We were working with Different customers DB where different customers were having Different DB Names but the table name and Structure of Tables were same.

So our requirement was through same web application we need to perform DML Operations (Select, Insert, Update and Delete) based on DB Name passed from front end (i.e. web application).
For Eg

DB-Customer-X
Tables-Employee, Products, Product_Description, Customers……….

DB-Customer-Y
Tables-Employee, Products, Product_Description, Customers……….

DB-Customer-Z
Tables-Employee, Products, Product_Description, Customers……….

So below I created a Stored Procedure Script that used to perform DML in different db based on DB Name passed from Front end

So Below is the Script with two test DB and one Employee Table

--Creating two test DB
Create database Test1
Create Database Test2

--Run Table Script in both the Database
CREATE TABLE [dbo].[Employee1](
     [Empid] [int] IDENTITY(1,1) NOT NULL,
     [EmpName] [nvarchar](50) NULL,
     [EmpSalary] [float] NULL,
 CONSTRAINT [PK_Employee1] PRIMARY KEY CLUSTERED
(
     [Empid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Stored Procedure Script that will will Perform DML Operations (Select, Insert, Update and Delete) based on DB Name Passed.

 --Script Code
--Run this Procedure in DB with which your web application is connected
Create PROCEDURE [dbo].[proc_db_test]
 (
 --Passing DB Name
@dbname varchar(100)=null,
--For Passing Operation insert update delete select
@Operation varchar(100)=null,
--Passing table values
@EmpId int =null,
@EmpName nvarchar(200)=null,
@EmpSalary float=null
 )
 AS
 BEGIN
IF db_id(@dbname) IS NULL
 BEGIN
RAISERROR ('Database %s does not exists!', 16,1, @dbname)
RETURN
END

if @Operation='INSERT'
begin

  EXEC('INSERT INTO '+@dbname+' .dbo.Employee1(EmpName,EmpSalary)
        VALUES('''+@EmpName+''','''+@EmpSalary+''')')

END
else if @Operation='UPDATE'
begin
 exec('update ' +@dbname+'.dbo. Employee1
set EmpName= '''+@EmpName+'''
,EmpSalary= '''+@EmpSalary+''' where EmpId='''+@EmpId+'''')
END
else if  @Operation='DELETE' 
begin
exec('Delete  from  ' +@dbname+'.dbo.Employee1  where EmpId='''+@EmpId+'''')
end
else  if @Operation='SELECTDETAILS'
begin
exec('select * from   ' +@dbname+'.dbo.Employee1  where EmpId='''+@EmpId+'''')
end
else if @Operation='SELECTALL'
begin
exec('select * from ' +@dbname+'.dbo.Employee1 order by Empid')
end
End


Test the Script

Insert Operation in both DB
--Procedure will have following inputs
--Proj_DB_Test DBName OPeration,EmpID,EMPName,EmpSalary

Exec  [dbo].[proc_db_test] 'Test1','Insert',1,'RK','15000'

--Test1 DB Test
Select * from test1.dbo.Employee1

Output
Empid  EmpName       EmpSalary
1          RK        15000

--Procedure will have following inputs
--Proj_DB_Test DBName OPeration,EmpID,EMPName,EmpSalary

Exec  [dbo].[proc_db_test] 'Test2','Insert',1,'RK1','20000'

--Test2 DB Test
Select * from test2.dbo.Employee1


Output
Empid EmpName    EmpSalary
1    RK1  20000


Test Update Operation in both DB

--Updating Name in Test1 DB
Exec  [dbo].[proc_db_test] 'Test1','UPdate',1,'RK_new','15000'

Output
Empid EmpName    EmpSalary
1    RK_new     15000



Test Delete Operation in both DB
--For Testing Delete Operation we will insert one more record in any of DB

--Insert
Exec  [dbo].[proc_db_test] 'Test1','Insert',2,'RK_1','10000'

Output

Empid EmpName    EmpSalary
1    RK_new     15000
2    RK_1 10000

--Deleting above newely added Record
Exec  [dbo].[proc_db_test] 'Test1','Delete',2

Output

Empid EmpName    EmpSalary
1    RK_new     15000


Test Select Operations in both DB
--Test Specific Records of Both DB Employee Table by passing Employee ID

Exec  [dbo].[proc_db_test] 'Test1','SELECTDETAILS',2

Output

Empid EmpName    EmpSalary
1    RK_new     15000



Exec  [dbo].[proc_db_test] 'Test2','SELECTDETAILS',1

Output 
Empid EmpName    EmpSalary
1    RK1  20000

Test Select all Operation in both DB
--Test all Records of Both DB Employee Table

Exec  [dbo].[proc_db_test] 'Test1','SELECTALL'

Output

Empid EmpName    EmpSalary
1    RK_new     15000

Exec  [dbo].[proc_db_test] 'Test2','SELECTALL'

Output

Empid EmpName    EmpSalary
1    RK1  20000


Testing by Passing DB Name that doesn't exist in server-Will Raise the below error

Exec  [dbo].[proc_db_test] 'Test3','SELECTALL'

Msg 50000, Level 16, State 1, Procedure proc_db_test, Line 16
Database Test3 does not exists!

NOTE:The best use of this script when you have multiple customers having different Databases having same table Names and Same table Structure.