SQL SERVER – 2005 – Export Data From SQL Server 2005 to Microsoft Excel Datasheet

Enable Ad Hoc Distributed Queries. Run following code in SQL Server Management Studio – Query Editor.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

Create Excel Spreadsheet in root directory c:\contact.xls (Make sure you name it contact.xls). Open spreadsheet, on the first tab of Sheet1, create two columns with FirstName, LastName. Alternatively you can download sample Spreadsheet from here.

Run following code in SQL Server Management Studio – Query Editor.
USE [AdventureWorks];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\contact.xls;',
'SELECT * FROM [Sheet1$]')
SELECT TOP 5 FirstName, LastName
FROM Person.Contact
GO

Open contact.xls spreadsheet you will see first five records of the Person.Contact inserted into the first two columns.

Make sure your spreadsheet is closed during this operation. If it is open it may thrown an error. You can change your spreadsheet name as well name of the Sheet1 to your desired name.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

216 thoughts on “SQL SERVER – 2005 – Export Data From SQL Server 2005 to Microsoft Excel Datasheet

  1. Hi, this codes work great but how about it done by reverse..
    like i have database in Access file in my C: drive then i want this to insert my SQL server database..

    how could i do this..?

    please help…

    • HI Pinal,

      I have an issue with SSIS package, I have designed a package and schedule it in the prod server, this package delete the exsiting detstination file through Script task and pump the data every time as I need to overwrite the existing data with new data.

      The issue is, if anybody open the destination excel file, the pakcage is failing and I am unbale to track those details via send mail task.

      Can any body help on this please..

      Thanks,
      Rams.

  2. when ever I tried to execute the below query it gives me an error.

    INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\contact.xls;’,
    ‘SELECT * FROM [Sheet1]‘)
    SELECT top 5 TXEM_Name as FirstName,TXEM_TaxPercentage as LastName
    FROM ACCTaxMaster where txem_id>32

    The error is :

    OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “The Microsoft Jet database engine could not find the object ‘Sheet1′. Make sure the object exists and that you spell its name and the path name correctly.”.
    Msg 7350, Level 16, State 2, Line 1
    Cannot get the column information from OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

    Pls reply.

  3. thanks for advice.. all i saw there is inserting data using excel to SQL server. my question how about inserting data using access to SQL SERVER….

    HOW COULD I DO IT..?

    PLEASE HELP..

  4. hi, i tried this codes to transfer my data from access to sql server…
    but it’s not working…
    can someone modify my codes please..?

    Insert into magpatoc.dbo.RSOTransfer
    Select * FROM (‘Provider=Microsoft.Jet.OLEDB.4.0;’,
    ‘Data Source=c:\CopyOfRSODB.mdb;User Id=admin;Password=;’,
    ‘SELECT * FROM FinalCustItemRSO’)

    Help please..

  5. when ever I tried to execute the below query it gives me an error.

    INSERT INTO OPENROWSET (’Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\contact.xls;’,
    ‘SELECT * FROM [Sheet1]’)
    SELECT top 5 TXEM_Name as FirstName,TXEM_TaxPercentage as LastName
    FROM ACCTaxMaster where txem_id>32

    The error is :

    Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘Person.Contact’.

    • hi.use it-
      ==========
      insert into P
      values(‘gt’,’gt’,’gst1@in.com’)
      INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;Database=c:\contact.xls;’,
      ‘SELECT * FROM [Sheet1$]‘)
      SELECT top 5 FirstName,LastName,email from P
      where FirstName>email
      =========

  6. hi Dave
    Trying to export a lot of data into Excel file but I get this error:
    Msg 8152, Level 16, State 4, Line 1
    String or binary data would be truncated.
    The statement has been terminated.

    I think it’s because there are line breaks and other characters in the data. Is there any way to get around this?
    Thanks a lot!

  7. TITLE: Microsoft SQL Server Management Studio
    ——————————

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ——————————
    ADDITIONAL INFORMATION:

    String or binary data would be truncated. (Microsoft SQL Server, Error: 8152)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3033&EvtSrc=MSSQLServer&EvtID=8152&LinkId=20476

    ——————————
    BUTTONS:

    OK
    ——————————

  8. OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “The Microsoft Jet database engine could not find the object ‘Sheet1′. Make sure the object exists and that you spell its name and the path name correctly.”.
    Msg 7350, Level 16, State 2, Line 1
    Cannot get the column information from OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

  9. Hi – I would like to do something very similar…

    I want all of the databases tables and their columns inserted into an excel spreadsheet. So schema rather than data.

    Do you know how this can be done?

  10. Sean, see the SQL Server help for “Information Schema”, it’s a set of “meta-tables” (or views) which contain information about your database’s schema.

  11. Puinal Hi:

    Is there a way to use query like below………..BUT NOT give column names hardcoded in the excel file….

    The reason being my sql is dynamically being passed and I donot want to have column names hardcoded into my excel template.

    USE [AdventureWorks];
    GO
    INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\contact.xls;’,
    ‘SELECT * FROM [Sheet1$]‘)
    SELECT TOP 5 FirstName, LastName
    FROM Person.Contact
    GO

  12. Hi,
    I used your code

    USE [AdventureWorks];
    GO
    INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\contact.xls;’,
    ‘SELECT * FROM [Sheet1$]‘)
    SELECT TOP 5 FirstName, LastName
    FROM Person.Contact
    GO

    to export data from sql server to excel but i am getting this error
    OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “The Microsoft Jet database engine could not find the object ‘SheetData1$’. Make sure the object exists and that you spell its name and the path name correctly.”.
    Msg 7350, Level 16, State 2, Line 1
    Cannot get the column information from OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

    can you help me out to get rid of this?
    Thanks!
    Monika

        • @Damodharan.

          There is a very complex process to create Excel Sheet, which I do not suggest to anyone.

          Simplest way to create new Excel sheet is to follow below steps,

          1. Create a Template/Model Excel Sheet (Empty) in any folder.
          2. Use Xp_Cmdshell copy command to copy this file from above folder to another folder.

          EXEC master.dbo.xp_cmdshell ‘copy Folderlocation\ExcelFileTemplate.xls new_folder_location\ExcelFileTemplate.xls’

          Now you have a new Excel File.

          Perform you action, Write to Excel Sheet. Once done with your task.

          3. Using Xp_Cmdshell you can also rename Excel Sheet.

          ~IM.

  13. Hi,
    I have problem with Excel when I using Openrowset in sql 2k.
    When I export sql table witch has column type decimal in excel I have column type string. I would like decimal in excel too.
    What I can do?

    THX

  14. Hi,
    This Query runs successfully
    SELECT *
    FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,’c:\mydb.mdb';’Admin';”, Table1)

    But this doesnt because it has password
    SELECT *
    FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,’c:\ACCESS.mdb';’Admin';’mypwd’, Table1)

    Before it both queries giving authentication error but after setting the permissions i am able to run the first query, but i am still having problem to run second query

  15. Hi,
    I am trying to create SSIS to export data to excel sheets(to multible sheets from different queries using views)
    I tried to run one query and export to one of the sheets.
    I got this error
    [Excel Destination [153]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
    [Excel Destination [153]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “input “Excel Destination Input” (164)” failed because error code 0xC020907B occurred, and the error row disposition on “input “Excel Destination Input” (164)” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
    and more…

    Pinal or some Gurus ,
    do you know how should I export data to excel from
    2005 using SSIS?

    priya

  16. when ever I tried to execute the below query it gives me an error.

    USE [TEST];
    GO
    INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\TEST.xls;’,
    ‘SELECT * FROM [Sheet1$]‘)
    SELECT * FROM t11
    GO

    The error is :

    OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “The Microsoft Jet database engine could not find the object ‘Sheet1′. Make sure the object exists and that you spell its name and the path name correctly.”.
    Msg 7350, Level 16, State 2, Line 1
    Cannot get the column information from OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

    Pls reply.

  17. Hello,

    “OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “The Microsoft Jet database engine could not find the object ‘Sheet1′. Make sure the object exists and that you spell its name and the path name correctly.”. ”

    this error means your excel file do not have a sheet named Sheet1, so check the excel file

  18. This is not still working anybody know, how to overcome on this error
    “OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “The Microsoft Jet database engine could not find the object ‘Sheet1′. Make sure the object exists and that you spell its name and the path name correctly.”. ”

    Regards
    Shyam

  19. OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “Could not find installable ISAM.”.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

  20. We use SQL Server 2005 & SQL Server Express to keep our Management Software data.

    My question is simple, but may not be so easily implemented. I would like to take our data (*.mdf) files and export it to a different work station that has also SQL Server Express. From my understanding this is not easily done thru SQL Server 2005. Does anyone have any suggestions?

  21. hi, please help, i’m new to this sql…i’m running sql server 2005 on ms server 2003(64x). everytime i click on management studio this is the message i get “The file C:\windows\microsoft.net\framework\\mscorlib.tlb could not be loaded. An attempt to repair this condition failed because the file could not be found. Please reinstall this program.”

  22. USE [AdventureWorks];
    GO
    INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\contact.xls;’,
    ‘SELECT * FROM [Sheet1$]‘)
    SELECT TOP 5 FirstName, LastName
    FROM Person.Contact
    GO

    This code is working properly, but when i changed the path of excel file then it gives an error(Note:- excel file is on anthoer machine)

    path is like that :- \\bhavin\AX-Config\contact.xls

    Error :- Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” reported an error. The provider did not give any information about the error.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

  23. INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=D:\UserRights.xls;’,
    ‘SELECT * FROM [Sheet1]‘)
    select * from dbtemp.dbo.Operators

    here dbtemp is the database name and Operators is the table name

    i have already created the file ‘D:\UserRights.xls;’

    While using this query, i m getting following error whot should i do

    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider ‘Microsoft.Jet.OLEDB.4.0′ reported an error.
    [OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Sheet1'. Make sure the object exists and that you spell its name and the path name correctly.]
    OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].

    PLs Reply soon……..

  24. can anyone please post the solution to this problem
    Cannot get the column information from OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

  25. Hi,

    The code provided works fine, but when i run the same second time the data in excel gets appended, where i want it to copy to a new sheet or delete the existing data and insert new data.. Awaiting for the response…
    Thanks in advance

  26. Hi, I would like to export data from store procedure (which has two input parameter) to Excel. The store procedure will return multipal record sets.

    Please let me know if we have any way to do this.

  27. I have procudures that successfully download data into Excel worksheets but run into issues if a field on the SQL Server has more than 255 characters it fails. I am using Excel 2003 and have found some post regarding “Extended Properties” of the connection string however I cannot get this to work correctly.

    Do you have any suggestions on how to get around this type of issue?

  28. I tried u r code
    It is showing Error

    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider ‘Microsoft.Jet.OLEDB.4.0′ reported an error.
    [OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Sheet1'. Make sure the object exists and that you spell its name and the path name correctly.]

    what is exact prob
    is it with Excel Or with Sql
    plz help

  29. The reason many are getting this message:
    [ “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” ] is because Excel must exist on the same machine you have the xls file on. The spreadsheet itself does not carry the Jet database engine.

  30. hi dave i tried above code its working for excel but i want to export data from access data base to sql server and vise versa how can do this

    pls help me i am a ferser……………..

  31. Hi,

    INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=C:\temp\test.xls;’,
    ‘SELECT aftercare_id,participant_id,r_followup_period_id FROM [Sheet1$]‘)
    SELECT [aftercare_id],[participant_id],[r_followup_period_id]
    FROM view_1
    go

    The above code works, but it will allow me to put only 7 columns, if i put ‘*’ it is not inserting data into excel, could you please tell me how to fix this problem

    i have 30 columns in my view. i want to use all columns, could you please respond to me as early as possible.

    Thanks,
    Roy

  32. Hi,

    I have a problem here. My codes works well. Everything seems fine. My problem is, whenever I upload large files like 18 mb .xls file, It shows page cannot be displayed. I’m working with a project for a large scale company. I really need this. this one works in SQLquery but not in my app. I hope someone would help me quick. INSERT INTO dbo.data SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,
    ‘Excel 8.0;Database=C:\Documents and Settings\…analysis.xls’, [data$])

    you can contact me here: jaythree03@gmail.com

  33. Error 0xc00470fe: Data Flow Task: The product level is insufficient for component “Destination – Configuration” (37).
    (SQL Server Import and Export Wizard)

  34. Hi I’ve tried this example with minor changes like:

    INSERT INTO OPENROWSET (‘Microsoft.ACE.OLEDB.12.0;Database=C:\Documents and Settings\Thato\My Documents\contact.xlsx;’,’SELECT * FROM [Sheet1$]‘)
    SELECT TOP 5 CallName, Surname
    FROM DTIHeadcount
    Where Period = 200904
    ——————–
    I get a syntax error:
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘)’.

    Please help

  35. In a simple way, we can export the resultset to .csv file using the following scenario:

    1. Go to Tools>>Options>>Query Results>>Sql Server>>Results to text: specify the output format to “Tab delimited”

    2.While executing the query – set Resultset to show as text instead of in grid (Ctrl + Q + T)

    3. Select all from the Resultset and save Results as “name.csv” or “name.xls”

    – Praveen

  36. Hi!!!

    Is possible to insert data from SQL Server to an Excel Spreadsheet that has a password protection? I need this because I want to prevent User Editing…

    Tks a lot!!!

  37. I can’t find the URL that I wanted to cite, but just to let folks know, the “Jet” drivers for this type of wonderful thing are not supported on the 64 bit version of SQL Server. Nice, huh?

  38. Error :- Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” reported an error. The provider did not give any information about the error.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

    Solution :

    Make sure your the file path and SQL server are in the same PC

  39. Hello sir,

    For the solution you gave regarding exporting data from SQL server to Excel sheet, is there any alternative that the file should be manually created along wid a header row.

    Is there any way the excel sheet too can be created with a header row defined by running a command line script from the sql promt so that the file along wid header is created at the same time just before data is inserted into that excel sheet i.e creating a dynamic query in which the file path , filename, column headers are specified in variables

    Please help out.

    Thanx

  40. USE [hrnew];
    GO
    INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=d:\12345.xls;’,’SELECT * FROM [Sheet1$]‘)
    SELECT TOP 5 EMP_NO,EMP_FULL_NAME_AR FROM dbo.EMPLOYEES
    GO

    after run your script with my data i get the next error messge.
    please help me

    Msg 213, Level 16, State 1, Line 1
    Insert Error: Column name or number of supplied values does not match table definition.

  41. Hi, to all above that got the error :
    The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” reported an error. The provider did not give any information about the error.

    Ensure that you have a space between Excel and 8.0 in the OPENROWSET section ie

    INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′,
    ‘Excel 8.0; Database=c:\somesheet.xls;’
    ,’SELECT * FROM [Sheet1$]‘)

  42. to mahmoud

    You generally see this type of error if you have not put the Column headers into Sheet1 of your spreadsheet .

    ColumnHeaders must be the same as the Field Names.

  43. Thanks this works great.

    One problem I am having. Numeric fields that I am exporting to excel are entered into the excel sheet as text.

    I have formatted the column in the excel sheet to number and I am still getting text. Any ideas how to fix this?

    Thanks

  44. Hi Every one,this good and simple. I tried this but it is not Working For me.after that working fine because the same syntax could not be worked for Excel 2007.Finally i got and if we use .xls format and also it will give error if an excel file is open and also for wrong name and also for columns not mentioned

  45. hi

    I tried the same but i get th following error
    OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “The Microsoft Jet database engine could not find the object ‘Sheet1$’. Make sure the object exists and that you spell its name and the path name correctly.”.
    Msg 7350, Level 16, State 2, Line 1
    Cannot get the column information from OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

    Can u please help me how to solve this ,
    can i use the same in server 2008.

    Please help me

  46. hi

    I tried the same but i get th following error

    OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “The Microsoft Jet database engine could not find the object ‘Sheet1$’. Make sure the object exists and that you spell its name and the path name correctly.”.
    Msg 7350, Level 16, State 2, Line 1
    Cannot get the column information from OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

    please give me solution

  47. dtswizard.exe .net framework initialization error unable to find a version at runtime t orun this application

    can u tell me the the posiple problem,when i try
    tnx for ur help

    • I have the same problem. I get the same error msg when i try to import/export data. That is “dts wizard.exe -.net framework initialisation error. Unable to find a version of the runtime to run this application” can any one help please.

  48. hi

    Using the example above – I have a situation, where i need to save each person’s detail as a seperate excel file. The thing is i am not sure how many rows are going to be returned by my query each time. so can some one please help me I have the following done –

    – I have the query
    – I have the rows being returned in ssms
    – the results are stored in a temp table
    – I would like to get each row saved at a location and the file name appened with the person’s name so I know who it is for to be emailed.

    Can some one please help me with the bit of storing each row as an excel file or flat file and adding the persons name to the file name..

    Thanks
    bubbles

  49. Hi,

    If you want to store results in text files thaeI would like then SQLCMD is an easy method.
    At first create a script to run on command prompt. customize the below statement:

    SELECT ‘SQLCMD -S server_name -Q “SELECT * FROM db.schema.Table WHERE personname = ”’ + personname + ”'”
    -o c:\’ + personname + ‘.txt’
    from db.schema.Table

    It would create a SQLCMD statement for each record. Then execute these SQLCMD command on command prompt. a new file would be created for each statement.

    Regards,
    Pinal Dave

  50. when ever I tried to execute the below query it gives me an error.

    INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\contact.xls;’,
    ‘SELECT * FROM [Sheet1]‘)
    SELECT top 5 TXEM_Name as FirstName,TXEM_TaxPercentage as LastName
    FROM ACCTaxMaster where txem_id>32

    The error is :

    OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “The Microsoft Jet database engine could not find the object ‘Sheet1′. Make sure the object exists and that you spell its name and the path name correctly.”.
    Msg 7350, Level 16, State 2, Line 1
    Cannot get the column information from OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

    Pls reply.

  51. hi
    i have use ur code to transfer data from execel sheet to sqlserver database with a little change
    the code are given billow
    USE [salary9]
    INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=D:\contact.xls;’,’SELECT * FROM [Sheet1$]‘)
    SELECT FirstName, LastName
    FROM testtable
    GO
    After executing this code in sql query editor
    the following err is fired:
    Msg 7399, Level 16, State 1, Line 2
    The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” reported an error. The provider did not give any information about the error.
    Msg 7303, Level 16, State 1, Line 2
    Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

    plz Help.

      • Dear Sir,

        I am using Siemens SCADA, WinCC V7.0 which uses SQL SERVER 2005 Database.
        I want to generate report in excel format.
        For this, want to export data from SQL SERVER DATABASE to excel.
        for report, client will select on SCADA runtime Screen , start date & time and end date & time, then tags logged during this time period should be exported to excel sheet.

        how and where to write script for this?
        please help.

        Warm regards,
        Damayanti

  52. i have 3 sheets in an excel with names sheet1,sheet2,sheet3..

    i have transfer all the columns in all the sheets into sqlserver
    table in data flow task..

    How can i do it??i need whole steps…

  53. Hi Suma

    I had this problem a few weeks ago. One of the best things to do is to run the Import Export Wizard or alternatively

    in the data flow task in the OLEDB destination or SQL Server Destination which ever you are using select the Source to be SQL command from the drop down box – and in the SQL box enter –

    select * from Sheet_Name;
    select * from Sheet_Name;

    and i think this should work.. try it out and let me know

  54. Hi,
    I am exporting data from a sql 2008 database using ssis import/export wizrd to excel. The columns in excel are truncated. How can I autofit the columns in sql?

    Please help.

  55. Is there any way where you can automatically enter in cells(1,1) on contact.xls with a tag like “XYZ Company” and
    cells(2,1) with “Location: texas”. I want to do this automatically when the scripts run.

  56. Hi Dave,

    Can I export the Stored Procedure (SQL Server 2005) result to Excel (version 2007), like the way we do for Tables and Views (Creating SQL Server connection from excel and export table and view).

  57. To elaborate it:

    I am using SQL Server (version 2005) data connection in Excel 2007 to retrieve bulk of records from SQL Server table. Currently, the number of records are around 400,000 and they keep on increasing further going down the line. The query is taking too long to execute while refreshing the data.

    I want to use a Stored Procedure instead of a SQL Query which helps in executing result faster and give me data without causing delays in my Excel spreadsheet.

    Is there any way by which I can import data using a Stored Procedure from SQL Server ?

    Any help would be appreciated.

    Many Thanks.

  58. I’ve tried the below script following your example:
    USE [EPATEST1];
    GO
    INSERT INTO OPENROWSET
    (‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;Database=C:\Dummy EPA Test Data1.xls’, ‘SELECT * FROM [Sheet1$]‘)

    …but I get the below error message on SQL Server 2005:
    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘)’.

    What is it that I’m doing wrong?

  59. Hi,

    I’m trying to run the simple Query below just to test the export operation, but getting an error.

    USE hd
    INSERT INTO OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,
    ‘Excel 8.0;Database=D:\TesteInsercao.xls;’,
    ‘SELECT * FROM [SheetName$]‘)
    SELECT * FROM helpdeskv2.dbo.rep

    Error: Msg 7403, Level 16, State 1, Line 2
    The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ has not been registered.

    Can anyone help, please.

    Thank You

  60. Hi Pinal

    i tired to insert data into Excel sheet from the below query

    INSERT INTO OPENROWSET
    (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\contact.xls;’,
    ‘SELECT * FROM [Sheet1$]‘)

    but it gives the following Errors

    The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” reported an error. The provider did not give any information about the error.
    Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

    Pls give me the Suggestion for this Query

  61. I need to query a SQL Server 2005 running on Server 2003 platform, for data to be put into an excel work book.
    I do not want to run the query from the Server. I would like to have the query located in excel. (transportability)

    example Server name Dell-2850SQL
    database name =ClinicalData

    I have the query I want to run and it returns the data I want. I just want it put in excel, and do not want anyone
    to have access to the server, besides the query being run from a desktop workstation having MS office 2003.

    I have tried this connection string am i on the right track?

    Driver={SQL Native Client};server=Dell-2850;database=ClinicalData;Trusted_Connection=yes;

  62. i want to Gathering the column that have the same ID in groups
    then get the max col between that group

    they told me use count distinct but i’m not sure for that

  63. The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” reported an error. The provider did not give any information about the error.
    Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

    Pls give me the Suggestion for this Query

  64. I want to have live currency exchange rates from dollar to euro, dollar to pound in my database in a separate table in SQL Server 2005 Management Studio. How can I do this? Any response would be highly appreciated..

  65. mr SAlly,

    Make sure that you had created the excel file in the server.

    When someone using sql server as client only gets this problem you get.

    So give the path in the sql server machine.

    i tried this way and solved

  66. hi every body ……i found another sol for exporting and importing to/from excel
    i hope that is useful and it work

    SQL Server Management Studio (SQL Server 2005)
    1. In SQL Server Management Studio, expand Server Objects in Object Explorer.
    2. Right-click Linked Servers, and then click New linked server.
    3. In the left pane, select the General page, and then follow these steps:
    a. In the first text box, type any name for the linked server.
    b. Select the Other data source option.
    c. In the Provider list, click Microsoft Jet 4.0 OLE DB Provider.
    d. In the Product name box, type Excel for the name of the OLE DB data source.
    e. In the Data source box, type the full path and file name of the Excel file.
    f. In the Provider string box, type Excel 8.0 for an Excel 2002, Excel 2000, or Excel 97 workbook.
    g. Click OK to create the new linked server.

    Import:-
    CREATE PROCEDURE Import_Data
    AS
    BEGIN
    Insert Customer
    SELECT * FROM OPENQUERY(EXCELIMPPRT, ‘SELECT * FROM [Sheet1$]‘)
    END

    to Export:
    Create PROCEDURE Export_Data
    AS
    BEGIN
    INSERT INTO OPENROWSET
    (‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;Database=C:\\sales.xls;’,’SELECT Customer_Key, First_Name,Middle_Name,Last_Name,Birthday, Marital_Status, Gender,Email_address, English_Education, Spanish_Education, Phone,Date_Frist_Purchase,Geography_Key FROM [Sheet1$]‘)
    SELECT Customer_Key, First_Name,Middle_Name,Last_Name,Birthday, Marital_Status, Gender,Email_address, English_Education, Spanish_Education, Phone,Date_Frist_Purchase,Geography_Key from Customer
    END

  67. OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′,
    ‘Excel 12.0;HDR=NO;Database=@C:\Documents and Settings\raise20\My Documents\Preadmission\PreadmissionDetail.xlsx’,
    ‘SELECT F7,F8,F9 FROM [PreadmissionDetail$] where F7”’)
    Give Error Like

    [OLE/DB provider returned message: Failure creating file.]
    Msg 7399, Level 16, State 1, Procedure Inser_PreAdmissionFromExcel, Line 16
    OLE DB provider ‘Microsoft.ACE.OLEDB.12.0′ reported an error.

    in SQl 2000 Database

  68. Hi Dave! Thanks for advice but i have a problem .İ tried many ways but i can’t solve it…

    I’m using

    Operating System Windows Server 2008 R2 Standart &
    SQL SERVER 2005

    I declared everything and i using this query;

    SET @SQLconnect = ‘SELECT * INTO ##TMP FROM OPENROWSET(”Microsoft.Jet.OLEDB.4.0”, ”Excel 8.0;Database=’ + @XLSFileName + ”’, ”SELECT * FROM [Sheet1$]”)’
    EXEC (@SQLconnect)

    i take this ERROR:
    Msg 7308, Level 16, State 1, Line 1
    OLE DB provider ‘Microsoft.Jet.OLEDB.4.0′ cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

    How can i solve it in 64 BIT OS ?

  69. i am able to Export data to text file but with columns i am not able to do using cmd shell bcp command please help me

  70. i am able to Export data to text file but with columns i am not able to do using cmd shell bcp command please help me

  71. I’m able to insert the ‘ID’ column into Excel, but when I try to input the ID, productname and productdescription I get this error:

    The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” supplied invalid metadata for column “ID”. The data type is not supported.

    The ID datatype is INT. I don’t know why it wouldn’t be supported.

    This is my insert statement:

    insert into OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,
    ‘Excel 8.0;Database=D:\Uploads\testing.xls;HDR=No;’,
    ‘SELECT ID,productname,productdescription
    FROM [Sheet1$]‘)
    select ID, productname,productdescription from tb_Products

    Any thoughts on what I’m missing would be appreciated.

    Thanks

  72. Guys ………………..!

    i found the solution ……

    create a new excel with some name along with columns than save as ……….Compatible with 97-2003 excel work book.

    Thanks,
    Deep

  73. Thanks for this code .
    it is working with .xls files , but i am working with .xlsx files.
    I am getting the error
    please paste the code for .xlsx file.

    Thank you
    Abhishek

  74. when i run the below query in SQL Server 2005 and MSOffice2007

    insert into OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\contact.xlsx;’, ‘SELECT * FROM [Sheet1]‘) select * from member

    i get following error,

    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” reported an error. The provider did not give any information about the error.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

    Member table, c:\contact.xlsx and Sheet1 exists. Iam running the query on a valid db.

    Please help me regarding…

  75. I am new to SQL Server.I have few questions.Any help would be highly appreciated.

    I have set of query and need to run as a schedule job.

    1.First,I want to know how to redirect the output of the query in Excel format to particular location.

    2.How to set a schedule task for that query.Need to be stored in the same folder.So i cant able to set a same file name for the result.

  76. yes its working but i want that the excel should be overwrite instead of just appending records to it .

    i mean every time i execute this query to insert records from sql table to excel it should fill with the 1st row . but its appending thats not desireable in my case.

    PLS HELP . M LOOKING FOR IT FOR QUIET A DAYS.

    THANKS

  77. My query is correct,but when I change the file xls (values from the collumns).My query keep the last result.Why happend this?
    Thanks for your attention!

  78. I ran this script as a select * successfully with no errors, but the spreadsheet is missing data past the 11th column?

  79. USE [Volkswagen1];
    GO
    INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\contact.xls;’,
    ‘SELECT * FROM [Sheet1$]‘)
    SELECT TOP 5 vLogin_UserID, VLogin_Password,vLogin_UserType,userCreationDate,lastLoginDate
    FROM Tbl_Login
    GO

    This code is run on local server successfully.But when i use Tbl_Login table which is present in server database (another PC) not work, error occurred.
    suggest the changes.

  80. I ran the query mentioned above and got this error.
    OLE DB provider ‘Microsoft.Jet.OLEDB.4.0′ cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
    I know it does not work on a 64 bit machine. Is there any work around to that.

    Thanks,

  81. what i can do for this error message :

    Msg 7399, Level 16, State 1, Line 2
    The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” reported an error. The provider did not give any information about the error.
    Msg 7303, Level 16, State 1, Line 2
    Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

    can any one know ple tell me …….

  82. Hi Pinal,
    I got error while trying to read excel file from network path

    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” reported an error. The provider did not give any information about the error.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

    here is my code

    insert INTO Temp_TS_Contract_Document_Staging
    SELECT *
    FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,
    ‘Excel 8.0;Database=\\computername\PDFAttachment\format_images1.xls’,’SELECT * FROM [Sheet1$]‘)

    while its works fine with the local system
    insert INTO Temp_TS_Contract_Document_Staging
    SELECT *
    FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,
    ‘Excel 8.0;Database=C:\format_images1.xls’,’SELECT * FROM [Sheet1$]‘)

  83. Hi

    I am stuck up with a problem. If I try

    SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;Database=E:\test.xls’, [Sheet1$])

    the query executes properly.

    But if i try

    SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,’E:\test.mdb';’Admin';”, tmptable)

    im getting the error

    OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “Cannot open database ”. It may not be a database that your application recognizes, or the file may be corrupt.”.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

    Please advice me what should i do.

  84. Hi,

    Found error when access from excel data.the error is…

    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” reported an error. The provider did not give any information about the error.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

    any one can healp on that…..

  85. Hi,

    I am using the same first code but I am getting the same error as

    Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

    Everything I have done according to the instruction and even the file is not opened while executing the commands.

    Please suggest the needful to be done.

  86. Select t1.account_no from newtest as t1 inner join
    (
    select * from Openrowset (‘Microsoft.Jet.OLEDB.4.0′,
    ‘Excel 8.0;Database=C:\Documents and Settings\kyros\Desktop\Delwise sub base.xls;HDR=YES’,
    ‘SELECT * FROM [Sheet1$]‘)
    ) as t2 on t1.account_no=t2.account_no

    Hi Thanks. It work fine.

    i need to insert a new table in SQL database that matching records. How to right Query

    Please Help me

    • insert into new_table (col_list)
      Select t1.account_no from newtest as t1 inner join
      (
      select * from Openrowset (‘Microsoft.Jet.OLEDB.4.0′,
      ‘Excel 8.0;Database=C:\Documents and Settings\kyros\Desktop\Delwise sub base.xls;HDR=YES’,
      ‘SELECT * FROM [Sheet1$]‘)
      ) as t2 on t1.account_no=t2.account_no

  87. Hi i need to less the amount for three column. Matching to Sql Table and Excel Sheet

    Ex: This is SQL Table Columns
    ———————————-

    month1 month2 month3 month4
    400 300 200 100

    This is Excel Sheet Table
    ——————————–
    Amount
    800

    i need to less Excel Amount(800) Column to sql table month column that is month1 month2 month3 month4

    inserting on New SQL Tables.

    Ex:

    Amount month1 month2 month3 month4
    0 200 0 0 0

    next day do the same process

    How to write the Query please help me.

  88. Hi Thanks. Its works fine. but the Account_no column show values

    for this format. i need to store whole numbers. Ex:9072790008

    9.07279e+008
    9.07299e+008
    9.07308e+008

    Please Help me

  89. Hi i need to less the amount for three column. Matching to Sql Table and Excel Sheet

    Ex: This is SQL Table Columns
    ———————————-

    month1 month2 month3 month4
    400 300 200 100

    This is Excel Sheet Table
    ——————————–
    Amount
    800

    i need to less Excel Amount(800) Column to sql table month column that is month1 month2 month3 month4

    inserting on New SQL Tables.

    Ex:

    Amount month1 month2 month3 month4
    0 200 0 0 0

    next day do the same process

    How to write the Query please help me.

  90. alter procedure [dbo].[SP_ExportExcelFromSql]
    as
    begin
    insert into OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,
    ‘Excel 8.0;Database=F:\SARAVANA\Test.xls;’,
    ‘SELECT * FROM [Sheet1$]‘) select * from usermas
    end

    After executing this i am facing the following error:

    OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “Cannot update. Database or object is read-only.”.
    Msg 7399, Level 16, State 1, Procedure SP_ExportExcelFromSql, Line 5
    The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” reported an error. The provider indicates that the user did not have the permission to perform the operation.
    Msg 7343, Level 16, State 2, Procedure SP_ExportExcelFromSql, Line 5
    The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” could not INSERT INTO table “[Microsoft.Jet.OLEDB.4.0]“.

    Could anyone help plz……

      • Hi Thanks for your response madhivanan,

        I checked but it has permission for Read/Write…

        Now im using my local database server….

        and my file path is my local system…

        whether this may give any problem?

        Thanks in Advance

        • Try this

          alter procedure [dbo].[SP_ExportExcelFromSql]
          as
          begin
          insert into OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,
          ‘Excel 8.0;IMEX=0;Database=F:\SARAVANA\Test.xls;’,
          ‘SELECT * FROM [Sheet1$]‘) select * from usermas
          end

          • Hi madhivanan,

            Thanks for your support

            I tried this one but it showing the same issue as :

            OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “Cannot update. Database or object is read-only

            But i saw the file by Right-Clicking and its properties

            and it is not a read only Mode

            moreover in the excel file(Test.xls) i added four column names in first line as
            and No records for that is it right?

            ———————————————————-
            Id User Name Password User Type
            ———————————————————-

            So Plz give some suggestions to fix this issue

            Thanks in Advance

  91. ALTER procedure [dbo].[SP_ExportExcelFromSql]
    as
    begin
    insert into OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,
    ‘Excel 8.0;Database=F:\SARAVANA\Test.xls;’,
    ‘SELECT * FROM [Sheet1$]‘) select * from usermas
    end

    OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “Cannot update. Database or object is read-only.”.
    Msg 7399, Level 16, State 1, Procedure SP_ExportExcelFromSql, Line 4
    The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” reported an error. The provider indicates that the user did not have the permission to perform the operation.
    Msg 7343, Level 16, State 2, Procedure SP_ExportExcelFromSql, Line 4
    The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” could not INSERT INTO table “[Microsoft.Jet.OLEDB.4.0]“.

    Still i am facing this error

    Can anyone help me to solve this problem

    Thanks in Advance

  92. Hi,

    I am getting problem at the time of uploading “The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ has not been registered” please help

  93. Hi ,
    I want to export sql server 2005 table to excel 2003(windows 7).
    i used this query:
    INSERT INTO OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=C:\n\testing.xls;’, ‘SELECT * FROM [Sheet1$]‘)
    SELECT * FROM dlr_master
    All column names in both sql server table and excel file are same.
    Excel file is not open.
    Path is perfect.

    Still I m getting the error,
    Cannot Initialize Datasource Object Of OLEDB Provider “Microsoft.Jet.OLEDB.4.0″ for Linked server “(null)”

    I feel its Windows 7 platform thats creating a conflict because this query runs well on Windows Xp.
    Please suggest the right query and versions required

    Thanks

  94. Hello ALL,

    I am getting this error, can any help to get rid this of

    OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” returned message “Could not find installable ISAM.”.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

    Regards,

    Shiv

  95. beautiful solution, thank you. The codes are fine. But I have problem here. After execute the insert statement, the data always appear starting different row. How I can do if I want the data always start at A2 since my data is linked to charts. I need to update the weekly.

  96. Hi Sir when i try to run yoour Script sql Server give me this Error
    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” reported an error. The provider did not give any information about the error.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)”.

    Note : im not open the Ecexl File its Close When i run this script

  97. Print ‘Creating Proc’
    GO
    Create Proc GetMonthlyDownloadLogDetails
    /* *****************************************************************************
    ** Name: GetMonthlyDownloadLogDetails
    ** Desc: To Get DownloadLogDetails Monthly Wise
    ** Auth: BalaKrishna Hari
    ** Date: 10/05/12
    ********************************************************************************/
    AS
    SET NOCOUNT ON;

    Select convert(varchar,reqtime,103)
    ,Appid,channel,bill_rescode
    ,Unitprice,count(*) Requests
    ,SUM(Case When dwnstatus=’Y’
    Then 1 Else 0 End) as Success
    ,SUM(Case When dwnstatus ‘Y’ Then 1 Else 0 End) as Failures
    From cms_download_logs (nolock)
    Where DATEPART(MM,reqtime) =DATEPART(MM,dateadd(MM,-1,getdate()))
    And DATEPART(MM,reqtime) = DATEPART(YY,getdate())
    And appid Not Like ‘%_FREE’
    Group By Convert(varchar,reqtime,103)
    ,appid
    ,channel
    ,bill_rescode
    ,unitprice
    Order By convert(varchar,reqtime,103),appid

    Go

    –Print ‘Creating Proc For BCP’

    –GO

    Declare @path varchar(100)
    ,@CMD varchar(1000)
    SELECT @path = ‘C:\MonthlyDownloadLogDetails\MonthlyDownloadLogDetails_’ + CONVERT(VARCHAR,GETDATE() ,110)
    SELECT @CMD = ‘bcp “GetMonthlyDownloadLogDetails” queryout ‘ + @path +’ -S.\sql2008r2 -T -c’
    SELECT @CMD
    EXEC XP_CMDSHELL @CMD

  98. If record are more than 65535 rows then csv can used.

    EXEC sp_configure ‘show advanced options’, 1;
    GO
    RECONFIGURE;
    GO
    EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1;
    GO
    RECONFIGURE;
    GO

    BULK
    INSERT CSVTest
    FROM ‘c:\csvtest.txt’
    WITH
    (
    FIELDTERMINATOR = ‘|’,
    ROWTERMINATOR = ‘\n’
    )
    GO

  99. Hi Gurus, when I have run the below query to export to excel ;
    USE [AdventureWorks];
    GO
    INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\test.xls;’,
    ‘SELECT * FROM [Sheet1$]‘)
    SELECT TOP 5 FirstName, LastName
    FROM Person.Contact
    GO

    I have got below given error..pls help me to get it done…

    “Msg 7308, Level 16, State 1, Line 1
    OLE DB provider ‘Microsoft.Jet.OLEDB.4.0′ cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.”

    Thanks..

    Chintesh Soni

  100. Pingback: SQL SERVER – Copy Data from One Table to Another Table – SQL in Sixty Seconds #031 – Video « SQL Server Journey with SQL Authority

  101. INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=c:\test.xls;’,
    ‘SELECT * FROM [Sheet1$]‘)
    SELECT * FirstName, LastName
    FROM Person.Contact

    It takes long time to execute.can u please tell me how to make it faster

  102. Pingback: SQL SERVER – Weekly Series – Memory Lane – #011 « SQL Server Journey with SQL Authority

  103. Hi All
    When I Execute above code got error

    Msg 7308, Level 16, State 1, Line 2
    OLE DB provider ‘Microsoft.Jet.OLEDB.4.0′ cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

    Any Suggest Please

  104. hi Pinal,

    i have question and maybe somebody or you can help me to solved this problem.
    if i have 1 files Excel,and 1 files excel have 3 sheet (sheet1,sheet2,and sheet3) with different column on every sheet,can you tell me how to import that file and create table based on how many sheet in that excel files (e.g i have 3 sheet), so if i have 3 sheet, i can create 3 table on 1 database.i suppose i can use foreach loop container for that,but how to create the 3 tables based on sheet ? (dynamic).
    i can do that if i use import data features on sql server,are ssis have item tool like that ?

    thank you very much for your help.

  105. Mr.Dave
    I have a problem in my store procedure. when execute sp, i have go this error
    “Msg 137, Level 15, State 2, Line 5 Must declare the scalar variable “@FDate”.
    I have tried may way, But I could not. but without date, it works fine. Pls
    Please advice me where I did wrong?
    Thank You
    Maideen

    it is My Store Procedure

    USE [collegeBac]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[usp_pivot_by_RCVD_Dynamic]

    @FDate datetime,
    @TDate datetime

    AS
    BEGIN
    SET NOCOUNT ON;
    DECLARE @PivotColumnHeaders VARCHAR(MAX)
    SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ‘,[' + CAST(RCVDFor AS VARCHAR) + ']‘,
    ‘[' + CAST(RCVDFor AS VARCHAR)+ ']‘ )FROM dbo.vw_PIVOT_RCVD_DISTINCT
    DECLARE @PivotTableSQL NVARCHAR(MAX)
    SET @PivotTableSQL = N’
    SELECT * FROM (SELECT dbo.vwRCHeadDetails.RCDate,dbo.vwRCHeadDetails.CourseCode,dbo.vw_PIVOT_RCVD_DISTINCT.RCVDFor,
    dbo.vwRCHeadDetails.Amount FROM dbo.vw_PIVOT_RCVD_DISTINCT INNER JOIN vwRCHeadDetails
    ON dbo.vw_PIVOT_RCVD_DISTINCT.rcvdfor = dbo.vwRCHeadDetails.rcvdfor
    WHERE dbo.vwRCHeadDetails.RCDate >= @FDate and dbo.vwRCHeadDetails.RCDate <= @TDate
    ) AS PivotData
    PIVOT ( SUM(Amount) FOR RCVDFor IN (' + @PivotColumnHeaders + ' )) AS PivotTable'

    EXECUTE(@PivotTableSQL)
    END

  106. hi Dave Sir
    greeting

    I have tried to copy the Sql server Query 2008 data to excel 10 ,
    to do such work i have run these statement
    “”
    EXEC sp_configure ‘show advanced options’, 1;
    GO
    RECONFIGURE;
    GO
    EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1;
    GO
    RECONFIGURE;
    GO

    INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 12.0;Database=c:\contact.xls;’,
    ‘SELECT * FROM [Sheet1$]‘)
    SELECT TOP 5 Name, Add1
    FROM Table Name

    “”
    After doing this i getting an error that is

    Msg 7308, Level 16, State 1, Line 1
    OLE DB provider ‘Microsoft.Jet.OLEDB.4.0′ cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode

    “”

    Please Give me Solution its very important to me,, any one can know it please let me know tha solution

  107. Thanks it works good one, but need to modify this query,suppose after generating the data in excel one time, if i run the query next time the existing data in excel should gets deleted or it should validate the data present in excel as well as the fresh data which is going to be uploaded.

  108. I’m Getting Error in SQL Server 2008

    Msg 7308, Level 16, State 1, Line 1
    OLE DB provider ‘Microsoft.Jet.OLEDB.4.0′ cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

    Please tell me the Solution

  109. Trying to get this working for importing sql table to an existing access table

    INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0′,’Access 9.0;Database=C:\Database1.mdb’,’SELECT * FROM [Code]‘);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s