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
Printable View
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
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')
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?
And please put things in [code] blocks!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) +'%')
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
Might be missing a ' or two in the SQL string - fix it first!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
i have below updated codehave below errorCode:--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
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:
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.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
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
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.Code:INSERT OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=C:\TEMP\testing.xls', [Sheet1$])
SELECT * FROM products WHERE name='xyz'
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
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>