MSBI

This error message is due to the server setting of Remote Connection Query Timeout. The default is 600 seconds, or 10 minutes. We can change the Default value from 600 to any value as we wish, either by using SQL Server Management Studio GUI or by querying system Stored Procedure. By Query: EXEC   sp_configure   'show advanced options' ,  1 ; GO RECONFIGURE ; GO EXEC   sp_configure   'Remote Query Timeout' ,  1200 ; GO RECONFIGURE ; By SSMS GUI: We can Achieve this by using SSMS GUI. Follow these steps in SQL Server 2012. Step 1) On Server, Right Click and Go for properties. Step 2) Click in Connections as Shown here and C hange the  Remote Query Timeout value as required.

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". If you get this error while Loading Data From Excel to SQL Server, then, close the Excel sheet opened and try to run queries again.

 As Explained in my  Previous Blog , we can just view all the data from Excel sheet in SQL Server Data output Viewer. Now there was the question, How to Map Each Column from Excel Sheet to SQL Server Database Table to load the data. A little Modification from my previous blog will do the trick of mapping. For Example, Let me create a table in SQL Server DB. USE [Test] GO CREATE TABLE [dbo] . [Management] ( [ID] [int] NULL, [Name] [varchar] ( 100 ) NULL, [Designation] [varchar] ( 100 ) NULL ) ON [PRIMARY] GO Now My Excel sheet and Data Looks like this which I kept it on my desktop. I want to load one of column say Name into table Management Table. The Query to Map only column 'NAME' From Excel Sheet to 'Name' column of Table Management is as follows: Insert into   dbo.Management ( NAME ) SELECT Name FROM OPENROWSET ...

In my previous post as shown  here , I have shown how to load data from a excel sheet into SQL DB. In this post, let me show query to load data from SQL DB to Excel sheet. As in earlier post, we can use OPENROWSET Command to load SQL Server DB into Excel sheet. I have table 'Management' table in 'Test' Database and I wanted to load Management Table Data into a excel sheet. Snippet shown below shows Management table's data: My Excel sheet 'Dest' which carries column name and snippet of the same is shown below: The Query to load SQL Server DB Data into Excel sheet is below: INSERT INTO OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0' , 'Excel 12.0 Xml;Database=C:\Users\dhinakaran\Desktop\Dest.xlsx;' , 'SELECT * FROM [Sheet1$]' ) SELECT   ID , NAME , DESIGNATION FROM Test . dbo . Management After running this query, excel sheet is loaded with Management table Data. The snippet is as shown below: ...

To overcome this issue, There are two methods, One is by using SQL Server Management Studio GUI interface and Other is by running Stored Procedures to enable program. Method 1 Run Following Stored Procedures: EXEC   sp_configure   'show advanced options' ,  1 ; GO RECONFIGURE ; GO EXEC   sp_configure   'Ad Hoc Distributed Queries' ,  1 ; GO RECONFIGURE ; Explanation: Here  sp_configure   displays or changes global configuration settings for the current server.   By setting  Show Advanced options  to 1, we  can list the advanced options by using  sp_configure . The default is 0. By Enabling  Show Advanced options , we can Enable  Ad Hoc Distributed Queries  options. Ad Hoc Distributed Queries:   By default, SQL Server does not allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. When this option is set to 1, SQL Server allows ad hoc access. ...

Here is my Excel sheet, I wanted to load into SQL Server DB. So before loading into SQL Server DB, I wish to see all data in SQL Server output Viewer. This can be achieved by OPENROWSET Command provided with SQL Server.  OPENROWSET: -  Includes all connection information that is required to access remote data from an OLE DB data source.   More Details can be found   here . OPENROWSET also supports bulk operations through a built-in BULK provider that enables data from a file to be read and returned as a rowset. Since If you don't have Microsoft Office installed, then, you can download Microsoft Access Engine which will help to load Excel sheet Data into SQL Server DB. Query to view Excel sheet data in SQL Server output is as follows: SELECT * FROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0' , 'Excel 12.0 Xml;HDR=YES; Database=C:\Users\dhinakaran\Desktop\Sampleformslist.xlsx' , 'SELECT * FROM [Sheet1$]' ); HRD = YES , i...

Hi Everyone, We in general use SSIS to load Flat file Data to SQL Server DB. But this can be achieved even by using T-SQL Command. Bulk Insert is a command used to import Text File data into SQL Server DB. For Example , Say I'm having a flat File called Source on my desktop. My Text File looks like : Now To load this data into my SQL Server DB, Let me create a table in SQL Server. Use Test Go Create Table Management ( ID INT , Name Varchar ( 100 ), Designation Varchar ( 100 )) Go Now Below is the Query to load the Flat File Data into SQL Server  using Bulk Insert: Bulk Insert Management From 'C:\Users\dhinakaran\Desktop\Source.txt' WITH ( FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n' ) This will insert all the data into my SQL Server DB. Find complete Result in below snippet: We can also get many more options other than FieldTerminator and RowTerminator. We can use th...