Unable to import float data type values from excel to sql server

Issue:
Unable to import float data type values from excel to sql server though the text in the column contains string values.

Description:
The issue with JET Engine is that it guesses the datatype of the column based on the majority of the type of values in that column.
After a long research i could come across a work around. Basically there is no direct solution as it seems that this is a bug with JET engine.
Use OpenRowSet command with IMEX=1 in connection string, to import the data to sql server as it is in the excel.

Process below:

First check if "Ad Hoc Distributed Queries" setting is enabled in sql server.
Run sp_configure in query window and check the list of the configuration. If there are 14 list items and there is no Ad Hoc Distributed Queries then it should be enabled.

To enable "Ad Hoc Distributed Queries" follow below steps:
Run
sp_configure 'show advanced options', 1
reconfigure
Then run
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

Now run below query to get the result set from excel
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Test.xls;IMEX=1;', 'SELECT * FROM [Sheet1$]')
Most important is that you should give IMEX=1 in the connection string since this is the one setting that is going to help us solve the issue. This command tells JET not to guess the datatype of the columns in the excel.

Now that you get proper data, you can insert the data into the required table using INSERT INTO ......... SELECT ...FROM format of sql

The cube should be active to make a call on this method

Hi All!

Recently came across an error "The cube should be active to make a call on this method" while using SQL Server Analysis Services (SSAS). I have set up the analysis services database and process it using visual studio. Everything was fine. Data was also available in the tables. But still unable to see the reports.

Here is the fix for the error:
Go to SQL Server Analysis services
Right Click on the Analysis Server and open Properties
Go to Security Tab. There you will find Users and Groups.
Click on Add and select ASPNET, IUSR, NETWORKSERVICE, NETWORK and Everyone and save.

Now you wil be able to see the reports.

Tags: SSAS, SQL Server Analysis Services, The cube should be active to make a call on this method, SSAS Reports, Cube Error, Cube Authentication Error

Increase your sql performance with DBCC DBREINDEX

Recently i have come across an issue with page time out. There was stored procedure that performs updates and inserts to 3 database tables on save. Usually this worked fine but gives a time out expired error when there are any sql jobs running in the background at the time of action. It was quite clear that this is a performance issue and will get solved if the stored procedure was optimized. But the thing is stored procedure was already optimized and there was nothing else to optimize it. I researched for about 1 week but no luck. Then i found out about DBCC DBREINDEX in sql that solved the issue at last. :-)

Those who are not aware of DBCC DBREINDEX, lets have some knowledge about it.

When you perform any data modification operations (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. When changes are made to the data that affect the index, index fragmentation can occur and the information in the index can get scattered in the database. Fragmented data can cause SQL Server to perform unnecessary data reads, so a queries performance against a heavy fragmented table can be very poor.

Rebuilding an index is a more efficient way to reduce fragmentation in comparison with dropping and re-creating an index, this is because rebuilding an index is done by one statement.

The DBCC DBREINDEX statement cannot automatically rebuild all of the indexes on all the tables in a database it can only work on one table at a time. You can write your own script to rebuild all the indexes on all the tables in a database

TableName - Is the name of the table in your DataBase.
Index_name - Is the name of the index to rebuild. (refer to image below)
Fillfactor - Is the percentage of space on each index page to be used for storing data when the index is created or rebuilt. Default is 100.

Syntax
DBCC DBREINDEX ('TableName','Index_Name',fillfactor)

Example
DBCC DBREINDEX ('Vendor')
DBCC DBREINDEX ('Vendor','IX_Vendor',80)

Believe me! this increases the performance of the actions that we perform on these tables.