{"id":3164,"date":"2019-03-25T17:43:00","date_gmt":"2019-03-25T20:43:00","guid":{"rendered":"http:\/\/xexeu.elipse.com.br\/pt\/kb47758-exporting-data-from-sql-server-to-excel\/"},"modified":"2019-09-30T09:07:04","modified_gmt":"2019-09-30T12:07:04","slug":"kb47758-exporting-data-from-sql-server-to-excel","status":"publish","type":"post","link":"https:\/\/kb.elipse.com.br\/en\/kb47758-exporting-data-from-sql-server-to-excel\/","title":{"rendered":"KB-47758: Exporting data from SQL Server to Excel."},"content":{"rendered":"
\n

Question:<\/b><\/p>\n

How can a table be exported from SQL Server to Excel?<\/p>\n

Solution:<\/b><\/p>\n

To do so, follow these procedures:<\/p>\n

1. Create a spreadsheet whose first row works as a column reader.<\/p>\n

\"\"<\/div>\n

2. Create a Query in Elipse E3, executing SQL’s OPENROWSET <\/span>method to open a connection to the desired Excel spreadsheet. Then, use SELECT <\/span>method to select the columns in the table. Example:
\n
\n<\/span><\/p>\n

INSERT INTO OPENROWSET<\/span> (‘Microsoft.ACE.OLEDB.12.0’, ‘Excel 12.0; DATABASE=E:\\RelatorioNiveis.xlsx;’, ‘SELECT Data, Nivel1, Nivel2 FROM [Plan1$]’<\/span>)<\/span>
\nSELECT <\/span>E3TimeStamp, Nivel1, Nivel2 FROM <\/span>Niveis<\/span><\/div>\n

If the Server is not set up to allow executing AD Hoc queries, the following access error can be generated: “SQL Server blocked access to STATEMENT ‘OpenRowset\/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’… <\/span>“. To fix this, you must execute the code below in a query at the SQL Server Studio:<\/p>\n

EXEC <\/span>sp_configure ‘show advanced options’<\/span>, 1;<\/span>
\nRECONFIGURE<\/span>;<\/span>
\nEXEC <\/span>sp_configure ‘Ad Hoc Distributed Queries’<\/span>, 1;<\/span>
\nRECONFIGURE<\/span>;<\/span><\/div>\n

In case it generates an error informing you that it was not possible to create an instance of OLE DB 12 server to the bound server, you will need to download the AccessDatabaseEngine.exe<\/a> application, which contains the Microsoft.ACE.OLEDB.12.0 driver.<\/p>\n

If this issue persists, execute the code below in a query at the SQL Server Studio:<\/p>\n

USE <\/span>[master]<\/span><\/span>
\nGO<\/span>
\nEXEC master<\/span>.dbo<\/span>.sp_MSset_oledb_prop<\/span> N’Microsoft.ACE.OLEDB.12.0′,N’AllowInProcess’<\/span>, 1<\/span>
\nGO<\/span>
\nEXEC master<\/span>.dbo<\/span>.sp_MSset_oledb_prop<\/span> N’Microsoft.ACE.OLEDB.12.0′,N’DynamicParameters’<\/span>, 1<\/span>
\nGO<\/span><\/div>\n

NOTE<\/b>: This demo application was developed with SQL Server Express 2008 R2<\/b> and Microsoft Excel 2010<\/b>.<\/p>\n<\/div>\n

Attachment:<\/h3>\n

SQLExcel<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

Question: How can a table be exported from SQL Server to Excel? Solution: To do so, follow these procedures: 1. Create a spreadsheet whose first row works as a column…<\/p>\n","protected":false},"author":18,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0},"categories":[735,763],"tags":[],"yoast_head":"\nKB-47758: Exporting data from SQL Server to Excel. - Elipse Knowledgebase<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/kb.elipse.com.br\/en\/kb47758-exporting-data-from-sql-server-to-excel\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"KB-47758: Exporting data from SQL Server to Excel.\" \/>\n<meta property=\"og:description\" content=\"Question: How can a table be exported from SQL Server to Excel? Solution: To do so, follow these procedures: 1. Create a spreadsheet whose first row works as a column…\" \/>\n<meta property=\"og:url\" content=\"https:\/\/kb.elipse.com.br\/en\/kb47758-exporting-data-from-sql-server-to-excel\/\" \/>\n<meta property=\"og:site_name\" content=\"Elipse Knowledgebase\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/elipsesoftware\" \/>\n<meta property=\"article:published_time\" content=\"2019-03-25T20:43:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-09-30T12:07:04+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/kb.elipse.com.br\/pt-br\/images\/ID4847\/Picture_en.jpg\" \/>\n<meta name=\"author\" content=\"Felipe Gabriel dos Santos\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Felipe Gabriel dos Santos\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/kb.elipse.com.br\/en\/kb47758-exporting-data-from-sql-server-to-excel\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/kb.elipse.com.br\/en\/kb47758-exporting-data-from-sql-server-to-excel\/\"},\"author\":{\"name\":\"Felipe Gabriel dos Santos\",\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/person\/33472b114af7aa3a770b87462ed1e7c7\"},\"headline\":\"KB-47758: Exporting data from SQL Server to Excel.\",\"datePublished\":\"2019-03-25T20:43:00+00:00\",\"dateModified\":\"2019-09-30T12:07:04+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/kb.elipse.com.br\/en\/kb47758-exporting-data-from-sql-server-to-excel\/\"},\"wordCount\":266,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/kb.elipse.com.br\/#organization\"},\"articleSection\":[\"DataBases\",\"Scripts\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/kb.elipse.com.br\/en\/kb47758-exporting-data-from-sql-server-to-excel\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/kb.elipse.com.br\/en\/kb47758-exporting-data-from-sql-server-to-excel\/\",\"url\":\"https:\/\/kb.elipse.com.br\/en\/kb47758-exporting-data-from-sql-server-to-excel\/\",\"name\":\"[:pt]KB-47758: Exporting data from SQL Server to Excel.[:] - Elipse Knowledgebase\",\"isPartOf\":{\"@id\":\"https:\/\/kb.elipse.com.br\/#website\"},\"datePublished\":\"2019-03-25T20:43:00+00:00\",\"dateModified\":\"2019-09-30T12:07:04+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/kb.elipse.com.br\/en\/kb47758-exporting-data-from-sql-server-to-excel\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/kb.elipse.com.br\/en\/kb47758-exporting-data-from-sql-server-to-excel\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/kb.elipse.com.br\/en\/kb47758-exporting-data-from-sql-server-to-excel\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"In\u00edcio\",\"item\":\"https:\/\/kb.elipse.com.br\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"KB-47758: Exporting data from SQL Server to Excel.\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/kb.elipse.com.br\/#website\",\"url\":\"https:\/\/kb.elipse.com.br\/\",\"name\":\"Elipse Knowledgebase\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/kb.elipse.com.br\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/kb.elipse.com.br\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/kb.elipse.com.br\/#organization\",\"name\":\"Elipse Software\",\"url\":\"https:\/\/kb.elipse.com.br\/\",\"sameAs\":[\"http:\/\/www.facebook.com\/elipsesoftware\"],\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/kb.elipse.com.br\/wp-content\/uploads\/2019\/05\/schererelipse-com-br\/logoElipse.png\",\"contentUrl\":\"https:\/\/kb.elipse.com.br\/wp-content\/uploads\/2019\/05\/schererelipse-com-br\/logoElipse.png\",\"width\":161,\"height\":58,\"caption\":\"Elipse Software\"},\"image\":{\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/person\/33472b114af7aa3a770b87462ed1e7c7\",\"name\":\"Felipe Gabriel dos Santos\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/kb.elipse.com.br\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/69a6f83b6fc2b0aed7ba3f8cb3cb019f?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/69a6f83b6fc2b0aed7ba3f8cb3cb019f?s=96&d=mm&r=g\",\"caption\":\"Felipe Gabriel dos Santos\"},\"url\":\"https:\/\/kb.elipse.com.br\/en\/author\/gabriel\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"KB-47758: Exporting data from SQL Server to Excel. - Elipse Knowledgebase","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/kb.elipse.com.br\/en\/kb47758-exporting-data-from-sql-server-to-excel\/","og_locale":"en_US","og_type":"article","og_title":"[:pt]KB-47758: Exporting data from SQL Server to Excel.[:] - Elipse Knowledgebase","og_description":"Question: How can a table be exported from SQL Server to Excel? Solution: To do so, follow these procedures: 1. Create a spreadsheet whose first row works as a column…","og_url":"https:\/\/kb.elipse.com.br\/en\/kb47758-exporting-data-from-sql-server-to-excel\/","og_site_name":"Elipse Knowledgebase","article_publisher":"http:\/\/www.facebook.com\/elipsesoftware","article_published_time":"2019-03-25T20:43:00+00:00","article_modified_time":"2019-09-30T12:07:04+00:00","og_image":[{"url":"http:\/\/kb.elipse.com.br\/pt-br\/images\/ID4847\/Picture_en.jpg"}],"author":"Felipe Gabriel dos Santos","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Felipe Gabriel dos Santos","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/kb.elipse.com.br\/en\/kb47758-exporting-data-from-sql-server-to-excel\/#article","isPartOf":{"@id":"https:\/\/kb.elipse.com.br\/en\/kb47758-exporting-data-from-sql-server-to-excel\/"},"author":{"name":"Felipe Gabriel dos Santos","@id":"https:\/\/kb.elipse.com.br\/#\/schema\/person\/33472b114af7aa3a770b87462ed1e7c7"},"headline":"KB-47758: Exporting data from SQL Server to Excel.","datePublished":"2019-03-25T20:43:00+00:00","dateModified":"2019-09-30T12:07:04+00:00","mainEntityOfPage":{"@id":"https:\/\/kb.elipse.com.br\/en\/kb47758-exporting-data-from-sql-server-to-excel\/"},"wordCount":266,"commentCount":0,"publisher":{"@id":"https:\/\/kb.elipse.com.br\/#organization"},"articleSection":["DataBases","Scripts"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/kb.elipse.com.br\/en\/kb47758-exporting-data-from-sql-server-to-excel\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/kb.elipse.com.br\/en\/kb47758-exporting-data-from-sql-server-to-excel\/","url":"https:\/\/kb.elipse.com.br\/en\/kb47758-exporting-data-from-sql-server-to-excel\/","name":"[:pt]KB-47758: Exporting data from SQL Server to Excel.[:] - Elipse Knowledgebase","isPartOf":{"@id":"https:\/\/kb.elipse.com.br\/#website"},"datePublished":"2019-03-25T20:43:00+00:00","dateModified":"2019-09-30T12:07:04+00:00","breadcrumb":{"@id":"https:\/\/kb.elipse.com.br\/en\/kb47758-exporting-data-from-sql-server-to-excel\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/kb.elipse.com.br\/en\/kb47758-exporting-data-from-sql-server-to-excel\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/kb.elipse.com.br\/en\/kb47758-exporting-data-from-sql-server-to-excel\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"In\u00edcio","item":"https:\/\/kb.elipse.com.br\/en\/"},{"@type":"ListItem","position":2,"name":"KB-47758: Exporting data from SQL Server to Excel."}]},{"@type":"WebSite","@id":"https:\/\/kb.elipse.com.br\/#website","url":"https:\/\/kb.elipse.com.br\/","name":"Elipse Knowledgebase","description":"","publisher":{"@id":"https:\/\/kb.elipse.com.br\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/kb.elipse.com.br\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/kb.elipse.com.br\/#organization","name":"Elipse Software","url":"https:\/\/kb.elipse.com.br\/","sameAs":["http:\/\/www.facebook.com\/elipsesoftware"],"logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/kb.elipse.com.br\/#\/schema\/logo\/image\/","url":"https:\/\/kb.elipse.com.br\/wp-content\/uploads\/2019\/05\/schererelipse-com-br\/logoElipse.png","contentUrl":"https:\/\/kb.elipse.com.br\/wp-content\/uploads\/2019\/05\/schererelipse-com-br\/logoElipse.png","width":161,"height":58,"caption":"Elipse Software"},"image":{"@id":"https:\/\/kb.elipse.com.br\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/kb.elipse.com.br\/#\/schema\/person\/33472b114af7aa3a770b87462ed1e7c7","name":"Felipe Gabriel dos Santos","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/kb.elipse.com.br\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/69a6f83b6fc2b0aed7ba3f8cb3cb019f?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/69a6f83b6fc2b0aed7ba3f8cb3cb019f?s=96&d=mm&r=g","caption":"Felipe Gabriel dos Santos"},"url":"https:\/\/kb.elipse.com.br\/en\/author\/gabriel\/"}]}},"_links":{"self":[{"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/posts\/3164"}],"collection":[{"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/users\/18"}],"replies":[{"embeddable":true,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/comments?post=3164"}],"version-history":[{"count":3,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/posts\/3164\/revisions"}],"predecessor-version":[{"id":8899,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/posts\/3164\/revisions\/8899"}],"wp:attachment":[{"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/media?parent=3164"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/categories?post=3164"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kb.elipse.com.br\/en\/wp-json\/wp\/v2\/tags?post=3164"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}