how to write in excel file of sql query result

i need to write certain sql result in excel file

like i run select * from products where name='xyz' when it fetch result it should write in excel file..

how can do this

Possibly useful links:

http://developmentnow.com/2008/04/07...ults-to-excel/

https://stackoverflow.com/questions/...-data-to-excel

i have below stuff an i write each result in excel sheet . i have an issue to write in excel sheet of each sql result

drop table #table_Northwind
create table #table_Northwind
(

RowID int not null identity(1,1) primary key,
column_name varchar(50)

)

insert into #table_Northwind (column_name)
(SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='Northwind' )

--select * from #table_Northwind
declare @tbl_name nvarchar(50)
declare @i int
select @i = min(RowID) from #table_Northwind
declare @max int
select @max = max(RowID) from #table_Northwind


while @i <= @max begin

SELECT (select column_name from #table_Northwind where RowID=@i) 'TableName' , o.name, [Scehma]=schema_name(o.schema_id), o.type
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON o.object_id = m.object_id
WHERE m.definition like +'%'+ (select column_name from #table_Northwind where RowID=@i) +'%'

set @i = @i + 1
end

How to you intend on creating the .XLSX file?

I am assuming you are creating this type of EXCEL file and not the older .XLS type.

I am also assuming you are not creating a .TDF file - which will also open in EXCEL. These are text files with TAB delimiters, very easy to make.

Please confirm the type of EXCEL file you want to make and then tell us what code you can use - VB6? VB.Net?

want to create from database command/stored procedure ?? and also i mentioned my problem above ,want to create .xls file and want some one to write few basic lines

like excel in msoffice

Ok - so you limit yourself when you say you want an older version .XLS format file.

And I cannot imagine how a STORED PROCEDURE would interact with EXCEL.

Now you can flip the issue and manually create an EXCEL file - and create in that spreadsheet a datasource and pull from a query. That can be saved as an older EXCEL 1997 .XLS file and given to another user. I've done this trick.

Otherwise, I use VB.Net and the Open XML SDK to create spreadsheets. These are .XLSX excel files.

https://docs.microsoft.com/en-us/off...l/spreadsheets

I could share code to do that - but it's not going to go into a SPROC or run in the database.

right click on export database

i want to exec below line in above while loop
INSERT INTO OPENROWSET
('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\Test.xls;','SELECT query')

Quote:

---

Originally Posted by erum_mirza

i want to exec below line in above while loop
INSERT INTO OPENROWSET
('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\Test.xls;','SELECT query')

---

I've never even seen this type of OPENROWSET use to create XLS files. That was a link given to you by JDC2000 in the second post here in this thread.

Code:

---

while @i <= @max begin

    Set @tbl_name = (SELECT (select column_name from #table_Northwind where RowID=@i) 'TableName' , o.name, [Scehma]=schema_name(o.schema_id), o.type
          FROM sys.sql_modules m
          INNER JOIN sys.objects o
          ON o.object_id = m.object_id
          WHERE m.definition like +'%'+ (select column_name from #table_Northwind where RowID=@i) +'%')

    INSERT INTO OPENROWSET
          ('Microsoft.Jet.OLEDB.4.0',
          'Excel 8.0;Database=c:\Test' + Cast(@i as varchar(100)) + '.xls;','SELECT * From ' + @tbl_name )

set @i = @i + 1
end

---

drop table #table_Northwind
create table #table_Northwind
(

RowID int not null identity(1,1) primary key,
column_name varchar(50)

)

insert into #table_Northwind (column_name)
(SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='Northwind' )
declare @tbl_name nvarchar(50)
declare @i int
select @i = min(RowID) from #table_Northwind
declare @max int
select @max = max(RowID) from #table_Northwind


while @i <= @max begin

Set @tbl_name = (SELECT (select column_name from #table_Northwind where RowID=@i) 'TableName' , o.name, [Scehma]=schema_name(o.schema_id), o.type
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON o.object_id = m.object_id
WHERE m.definition like +'%'+ (select column_name from #table_Northwind where RowID=@i) +'%')

INSERT INTO OPENROWSET
('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=d:\book2' + Cast(@i as varchar(100)) + '.xls;','SELECT * From ' + @tbl_name )

set @i = @i + 1
end

showing errorr

Msg 102, Level 15, State 1, Line 32
Incorrect syntax near '+'.

Where did you get this? And what is it supposed to do?

Code:

---

Set @tbl_name = (SELECT (select column_name from #table_Northwind where RowID=@i) 'TableName' , o.name, [Scehma]=schema_name(o.schema_id), o.type
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON o.object_id = m.object_id
WHERE m.definition like +'%'+ (select column_name from #table_Northwind where RowID=@i) +'%')

---

And please put things in [code] blocks!

You are still not giving much information.

Which database are you talking about?
Do you actually need to create it from a stored procedure or can you also do it with an additional program (written in VB.Net or VB6)?

A sample:
https://www.sqlservercentral.com/for...e#post-3736926

sql server 2008 R2, from stored prcoedure

its my code

This should work

Code:

---

insert into #table_Northwind (column_name)
(SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='Northwind' )
declare @tbl_name nvarchar(50)
declare @i int
select @i = min(RowID) from #table_Northwind
declare @max int
select @max = max(RowID) from #table_Northwind
Declare @sqlstr nvarchar(max)

while @i <= @max begin

Set @tbl_name = (SELECT (select column_name from #table_Northwind where RowID=@i) 'TableName'-- , o.name, [Scehma]=schema_name(o.schema_id), o.type
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON o.object_id = m.object_id
WHERE m.definition like '%'+ (select column_name from #table_Northwind where RowID=@i) +'%')

Set @SQLSTR='INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=d:\book2' + Cast(@i as varchar(100)) + '.xls;'',''SELECT * From ' + @tbl_name + ')'
select @SQLSTR
Exec(@SqlStr)

set @i = @i + 1
end

---

Might be missing a ' or two in the SQL string - fix it first!

i have below updated code

Code:

---

--drop table #table_Northwind
create table #table_Northwind
(

RowID int not null identity(1,1) primary key,
column_name varchar(50)

)

insert into #table_Northwind (column_name)
(SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='Northwind' )
declare @tbl_name nvarchar(50)
declare @i int
select @i = min(RowID) from #table_Northwind
declare @max int
select @max = max(RowID) from #table_Northwind
Declare @sqlstr nvarchar(max)

while @i <= @max begin

Set @tbl_name = (SELECT (select column_name from #table_Northwind where RowID=@i)) --'TableName'

--Set @tbl_name = (select column_name as TableName from #table_Northwind where RowID=@i)-- 'TableName'-- , o.name, [Scehma]=schema_name(o.schema_id), o.type
 SELECT o.name, (select column_name from #table_Northwind where RowID=@i) 'TableName' , o.name, [Scehma]=schema_name(o.schema_id), o.type
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON o.object_id = m.object_id
WHERE m.definition like +'%'+ (select column_name from #table_Northwind where RowID=@i) +'%'
Set @SQLSTR='INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=d:\book2' + Cast(@i as varchar(100)) + '.xls;'',''SELECT * From ' + @tbl_name + ''')'
select @SQLSTR
Exec(@SqlStr)
set @i = @i + 1
end


drop table #table_Northwind

---

have below error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.

unable to find ,can help me

You should first check your OLEDB providers installed on the SQL Server machine, use xp_enum_oledb_providers (extended) stored procedure for this

EXEC master.dbo.xp_enum_oledb_providers

. . . to get results similar to this:

Code:

---

Provider Name                        Parse Name                                Provider Description
SQLOLEDB                        {0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}        Microsoft OLE DB Provider for SQL Server
MSOLAP                                {10154F28-4979-4166-B114-3E7A7926C747}        Microsoft OLE DB Provider for Analysis Services 10.0
SSISOLEDB                        {1AD8F6DD-9411-4908-BF39-DAE7696EB426}        OLE DB Provider for SQL Server Integration Services
SQLNCLI11                        {397C2819-8272-4532-AD3A-FB5E43BEAA39}        SQL Server Native Client 11.0
Microsoft.ACE.OLEDB.12.0        {3BE786A0-0366-4F5C-9434-25CF162E475E}        Microsoft Office 12.0 Access Database Engine OLE DB Provider
ADsDSOObject                        {549365d0-ec26-11cf-8310-00aa00b505db}        OLE DB Provider for Microsoft Directory Services
MSOLEDBSQL                        {5A23DE84-1D7B-4A16-8DED-B29C09CB648D}        Microsoft OLE DB Driver for SQL Server
SQLNCLI10                        {8F4A6B68-4F36-4e3c-BE81-BC7CA4E9C45C}        SQL Server Native Client 10.0
Search.CollatorDSO                {9E175B8B-F52A-11D8-B9A5-505054503030}        Microsoft OLE DB Provider for Search
MSDASQL                                {c8b522cb-5cf3-11ce-ade5-00aa0044773d}        Microsoft OLE DB Provider for ODBC Drivers
MSDAOSP                                {dfc8bdc0-e378-11d0-9b30-0080c7e9fe95}        Microsoft OLE DB Simple Provider

---

Notice that Microsoft.Jet.OLEDB.4.0 provider is 32-bit only so if you are using an x64 version of SQL Server you cannot possible find it in the list above and cannot possible use it with OPENROWSET and/or linked servers per se.

What you need to work with xls and newer xlsx/xlsb files in x64 environment is the x64 version of Microsoft.ACE.OLEDB.12.0 provider. This is Microsoft Access Database Engine 2010 which you have to first download and then install on your SQL Server machine.

Another caveat you have to consider is that with OPENROWSET you cannot *create* new xls files. What you can do is provide a pre-created template xls file and insert/append new data into a sheet of a copy of your template workbook.

Your original query can be dumped into the target/copy xls file like this

Code:

---

INSERT OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=C:\TEMP\testing.xls', [Sheet1$])
SELECT * FROM products WHERE name='xyz'

---

Keep in mind that the OPENROWSET above returns a resultset with certain columns so your SELECT * FROM products should return the same number and match the columns from OPENROWSET otherwise the INSERT statement will fail.

Professional Solution to I/O of external for SQL Server data

Here are two utility stored procedures that we are using to deal with external data in SQL Server. These are handling data in xls, xlsx, xlsb and csv files for us by first creating "linked" views in tempdb.

For instance using something like

EXEC dbo.usp_adm_CreateLinkView 'C:\TEMP\testing.xls', NULL, 'v_testing'

. . . will create a permanent tempdb.dbo.v_testing "linked" view (in tempdb database) to the first sheet of your xls file. Such regular view can be used to SELECT from or INSERT into with regular T-SQL syntax like

INSERT tempdb.dbo.v_testing(Col1, Col2) SELECT code, name FROM products WHERE name='xyz'

Once done working with the external data in the xls file just use

EXEC dbo.usp_adm_DropLinkView 'v_testing'

. . . to drop the "linked" view and the resource lock used internally.

cheers,
</wqw>

i find below
Provider Name Parse Name Provider Description
SQLOLEDB {0C7FF16C-38E3-11d0-97AB-00C04FC2AD98} Microsoft OLE DB Provider for SQL Server
MSOLAP {10154F28-4979-4166-B114-3E7A7926C747} Microsoft OLE DB Provider for Analysis Services 10.0
SQLNCLI11 {397C2819-8272-4532-AD3A-FB5E43BEAA39} SQL Server Native Client 11.0
ADsDSOObject {549365d0-ec26-11cf-8310-00aa00b505db} OLE DB Provider for Microsoft Directory Services
SQLNCLI10 {8F4A6B68-4F36-4e3c-BE81-BC7CA4E9C45C} SQL Server Native Client 10.0
Search.CollatorDSO {9E175B8B-F52A-11D8-B9A5-505054503030} Microsoft OLE DB Provider for Search
MSDASQL {c8b522cb-5cf3-11ce-ade5-00aa0044773d} Microsoft OLE DB Provider for ODBC Drivers
MSDAOSP {dfc8bdc0-e378-11d0-9b30-0080c7e9fe95} Microsoft OLE DB Simple Provider
MSIDXS {F9AE8980-7E52-11d0-8964-00C04FD611D7} Microsoft OLE DB Provider for Indexing Service

You have to install AccessDatabaseEngine_X64.exe on your SQL Server to have an x64 OLEDB provider capable of accessing Excel files.

cheeers,
</wqw>

installed 32 bit,as it was supported not 64

have 32 bit office products

Quote:

---

Originally Posted by erum_mirza

installed 32 bit,as it was supported not 64

---

You need the x64 OLEDB provider. Might want to try x64 version from Microsoft Access Database Engine 2016 Redistributable if you are using newer Office.

Use xp_enum_oledb_providers to make sure Microsoft.ACE.OLEDB.12.0 provider is accessible.

Until resolving OLEDB provider problem you cannot make any progress on your original issue.

cheers,
</wqw>