-
Jan 12th, 2022, 04:32 AM
#1
Thread Starter
Junior Member
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
-
Jan 12th, 2022, 10:22 AM
#2
Re: how to write in excel file of sql query result
-
Jan 13th, 2022, 03:04 AM
#3
Thread Starter
Junior Member
Re: how to write in excel file of sql query result
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
-
Jan 13th, 2022, 07:16 AM
#4
Re: how to write in excel file of sql query result
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?
-
Jan 13th, 2022, 07:37 AM
#5
Thread Starter
Junior Member
Re: how to write in excel file of sql query result
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
-
Jan 13th, 2022, 07:39 AM
#6
Thread Starter
Junior Member
Re: how to write in excel file of sql query result
-
Jan 13th, 2022, 07:42 AM
#7
Re: how to write in excel file of sql query result
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.
-
Jan 13th, 2022, 07:54 AM
#8
Thread Starter
Junior Member
Re: how to write in excel file of sql query result
right click on export database
-
Jan 13th, 2022, 08:09 AM
#9
Thread Starter
Junior Member
Re: how to write in excel file of sql query result
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')
-
Jan 13th, 2022, 08:22 AM
#10
Re: how to write in excel file of sql query result
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
-
Jan 13th, 2022, 08:28 AM
#11
Thread Starter
Junior Member
Re: how to write in excel file of sql query 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' )
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 '+'.
-
Jan 13th, 2022, 08:38 AM
#12
Re: how to write in excel file of sql query result
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!
-
Jan 13th, 2022, 08:40 AM
#13
Re: how to write in excel file of sql query result
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
-
Jan 13th, 2022, 08:42 AM
#14
Thread Starter
Junior Member
Re: how to write in excel file of sql query result
sql server 2008 R2, from stored prcoedure
-
Jan 13th, 2022, 08:42 AM
#15
Thread Starter
Junior Member
Re: how to write in excel file of sql query result
-
Jan 13th, 2022, 08:50 AM
#16
Re: how to write in excel file of sql query result
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!
-
Jan 14th, 2022, 01:41 AM
#17
Thread Starter
Junior Member
Re: how to write in excel file of sql query result
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
-
Jan 14th, 2022, 03:18 AM
#18
Re: how to write in excel file of sql query result
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>
-
Jan 14th, 2022, 04:47 AM
#19
Thread Starter
Junior Member
Re: how to write in excel file of sql query result
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
-
Jan 14th, 2022, 05:27 AM
#20
Re: how to write in excel file of sql query result
You have to install AccessDatabaseEngine_X64.exe on your SQL Server to have an x64 OLEDB provider capable of accessing Excel files.
cheeers,
</wqw>
-
Jan 14th, 2022, 05:35 AM
#21
Thread Starter
Junior Member
Re: how to write in excel file of sql query result
installed 32 bit,as it was supported not 64
-
Jan 14th, 2022, 05:36 AM
#22
Thread Starter
Junior Member
Re: how to write in excel file of sql query result
have 32 bit office products
-
Jan 14th, 2022, 06:53 AM
#23
Re: how to write in excel file of sql query result
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>
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|