SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Export to Excel
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 30

cengaver
Starting Member

30 Posts

Posted - 07/16/2009 :  02:41:22  Show Profile  Reply with Quote
i did but it didnt change. the error continue
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 07/16/2009 :  06:16:49  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
You dont need Linked Server when using OPENROWSET.
Try this and see what happens


ALTER PROCEDURE [dbo].[TEST]
AS
BEGIN

SET NOCOUNT ON;
DECLARE @RC int
DECLARE @server nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider nvarchar(128)
DECLARE @datasrc nvarchar(4000)
DECLARE @location nvarchar(4000)
DECLARE @provstr nvarchar(4000)
DECLARE @catalog nvarchar(128)

SET @server = 'ar-ge'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
SET @datasrc = 'c:\raporlar.xls'
SET @provstr = 'Excel 8.0'


INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\raporlar.xls;HDR=NO',
'SELECT * FROM [Sheet1$]')
SELECT * FROM personal

DECLARE @body VARCHAR(1024)
SET @body = 'dosyalar '+
CONVERT(VARCHAR, GETDATE())

EXEC omerDB..xp_sendmail
@recipients='omer_yalcin1985@hotmail.com',
@message = 'dosyalar' ,
@subject = 'dosyalar',
@attachment = 'c:\raporlar.xls'

exec xp_cmdshell 'del C:\raporlar.xls'

END



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

akpaga
Constraint Violating Yak Guru

USA
311 Posts

Posted - 08/17/2009 :  14:37:20  Show Profile  Reply with Quote
hi madhivannan,

i using your procedure for exporting data to excel file along with the cloumn names but the cloumnnames in the excel fiel is not in the same order as that of the table hence the data are being shown in the wrong header names in the excel file.
Please let me know how to correct this.


thank you and the rest of the function is working fine.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 08/18/2009 :  02:34:35  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by akpaga

hi madhivannan,

i using your procedure for exporting data to excel file along with the cloumn names but the cloumnnames in the excel fiel is not in the same order as that of the table hence the data are being shown in the wrong header names in the excel file.
Please let me know how to correct this.


thank you and the rest of the function is working fine.


If you have already column names in the EXCEL sheet, why do you need them again? Use Method 1 by selecting the columns from the table in the proper order

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

dpoirier
Starting Member

9 Posts

Posted - 10/14/2009 :  23:17:01  Show Profile  Reply with Quote
If anyone is interested, I created an SSMS 2005/2008 Addin that allows saving ad-hoc SQL query results as an XLS file, with header and value formats intact. You don't have to mess around with copying and pasting text, or wrapping your queries in OPENROWSET, etc.

http://www.SsmsXlsExport.com/

Cheers,
David
Go to Top of Page

DBConner
Starting Member

USA
2 Posts

Posted - 01/11/2010 :  14:57:21  Show Profile  Reply with Quote
As a very simplified solution to write to an 'Excel file' , perhaps you could benefit by "cheating" and using the simple command below to create a CSV text file...

Exec Master..xp_cmdshell 'bcp "Select * from db.dbo.SQLtable" queryout "C:\data\testing.csv" -c -T -t"," -SyourServer\SQL2008'

Default for .csv is Excel, and you avoid some of the issues mentioned above. Again, just for simple uses.


Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 01/18/2010 :  03:17:29  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by DBConner

As a very simplified solution to write to an 'Excel file' , perhaps you could benefit by "cheating" and using the simple command below to create a CSV text file...

Exec Master..xp_cmdshell 'bcp "Select * from db.dbo.SQLtable" queryout "C:\data\testing.csv" -c -T -t"," -SyourServer\SQL2008'

Default for .csv is Excel, and you avoid some of the issues mentioned above. Again, just for simple uses.





Did you see method 5?
It basically uses the same approach

Your method will not include column names as the first row but method 5 will include it

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

akpaga
Constraint Violating Yak Guru

USA
311 Posts

Posted - 01/29/2010 :  15:48:16  Show Profile  Reply with Quote
hi madhivannan ,
doe your stored procedure export to excel with headers work for a view instead of a table. I am trying but does not work for me...
Go to Top of Page

akpaga
Constraint Violating Yak Guru

USA
311 Posts

Posted - 01/29/2010 :  16:54:41  Show Profile  Reply with Quote
Hi madhivanan

recently we upgraded to sql serv 2008 .And now the excel file that is being generated using your method export to excel with colomn headers is not working any more.

Its justing send a blank excel file thats it.

Do i need to make any changes in it to work for 2008.
Go to Top of Page

vani_r14
Starting Member

New Zealand
24 Posts

Posted - 02/01/2010 :  00:14:40  Show Profile  Send vani_r14 a Yahoo! Message  Reply with Quote
Hi

I am new to all this and am having lots of problems running the query to insert data into excel. I am using the stored procedure shown in page 1.

i have only one database and one table to query and have specified that manually in the procedure.

I have checked that the @file_name variable has the path declared and it has


I ran the procedure to the bit of
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

and get the following results -

User name not provided, either use -U to provide the user name or use -T for Trusted Connection
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL

when i broke it into sections and ran the bit below with the file name set as path

exec master..xp_cmdshell 'bcp " select * from (select ''id'' as id,''pname'' as pname,'') as t" queryout "C:\Documents and Settings\vraghunathan\Desktop\Results\" -c'

I have enabled bcp master..xp_cmdshell from Surface area config manager

Can someone please help

Thanks heaps in advance
Vani

Edited by - vani_r14 on 02/01/2010 00:18:01
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17648 Posts

Posted - 02/01/2010 :  00:20:26  Show Profile  Reply with Quote
add the -T to the bcp command


KH
Time is always against us

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 02/02/2010 :  04:37:51  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by akpaga

hi madhivannan ,
doe your stored procedure export to excel with headers work for a view instead of a table. I am trying but does not work for me...


What did you mean by "does not work"?
Did you get any error?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 02/02/2010 :  04:40:44  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by akpaga

Hi madhivanan

recently we upgraded to sql serv 2008 .And now the excel file that is being generated using your method export to excel with colomn headers is not working any more.

Its justing send a blank excel file thats it.

Do i need to make any changes in it to work for 2008.


You need to give more informations
Did the database exist after migration?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

MattWil
Starting Member

USA
1 Posts

Posted - 02/09/2010 :  10:49:00  Show Profile  Reply with Quote
I'm having a little trouble with an ad hoc query that connects to 2 different instances. The first one runs fine but the second one gives me error 7303. I've found a lot of posts with error 7303 in relation to Linked servers but nothing about ad hoc using Openrowset() Here are my queries. I run SSMS in SQLCMD mode by default to be able to switch between instances.

:Connect SQLSERVER\INST1
use scratch
declare @fdlw datetime
declare @ldlw datetime

set @fdlw = master.dbo.f_Dates('FWLW',getdate(),'',0)
set @ldlw = master.dbo.f_Dates('LWLW',getdate(),'',0)

select *
into #log1
from usersessionlog
where
convert(varchar(5),currenttime, 108) between '06:00' and '19:00'
and convert(varchar(20), currenttime, 110) between @fdlw and @ldlw
order by currenttime

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\temp\excelexport\ExcelExport.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
select * from #log1
drop table #log1
GO
:Connect SQLSERVER\INST2
use scratch

declare @fdlw2 datetime
declare @ldlw2 datetime

set @fdlw2 = master.dbo.f_Dates('FWLW',getdate(),'',0)
set @ldlw2 = master.dbo.f_Dates('LWLW',getdate(),'',0)

select [DateTime],UserID
into #log2
from usagedata
where
convert(varchar(5),[datetime], 108) between '06:00' and '19:00'
and convert(varchar(20), [datetime], 110) between @fdlw2 and @ldlw2
order by [Datetime]

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\temp\excelexport\ExcelExport.xls;HDR=YES', 'Select [DateTime],UserID from [Sheet2$]')
select * from #log2

The result of the first query is:
Connecting to SQLSERVER\INST1...

(1207 row(s) affected)

(1207 row(s) affected)
Disconnecting connection from SQLSERVER\INST1...

The result of the second is:
Connecting to SQLSERVER\INST2...

(458 row(s) affected)
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 17
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Disconnecting connection from SQLSERVER\INST2...

This is on the same server. The file is definitely closed. I can just run the second query and get the same result without running the first. The sheet names are correct and have the column names in the first row.

I've also tried running the second query using MSDASQL and I get

insert into OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\temp\excelexport\ExcelExport.xls',
'SELECT [DateTime],UserID FROM [Sheet2$]')
select [DateTime],UserID from #log2

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver] System resource exceeded.".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".


Any help appreciated.

-Matt
Go to Top of Page

anuragvatsa
Starting Member

India
6 Posts

Posted - 02/11/2010 :  04:00:14  Show Profile  Reply with Quote
4 If you dont want to create an EXCEL file in advance and want to export data to it, use
Hi madhivanan,
Am trying to add some styles on excel sheet generated after exceuting "sp_makewebtask " which will
generate the file with data in tabular format.
So is there any way to add template sort off or some color or styles for eg., Header, columns, background.

av
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 02/11/2010 :  06:01:39  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by MattWil

I'm having a little trouble with an ad hoc query that connects to 2 different instances. The first one runs fine but the second one gives me error 7303. I've found a lot of posts with error 7303 in relation to Linked servers but nothing about ad hoc using Openrowset() Here are my queries. I run SSMS in SQLCMD mode by default to be able to switch between instances.

:Connect SQLSERVER\INST1
use scratch
declare @fdlw datetime
declare @ldlw datetime

set @fdlw = master.dbo.f_Dates('FWLW',getdate(),'',0)
set @ldlw = master.dbo.f_Dates('LWLW',getdate(),'',0)

select *
into #log1
from usersessionlog
where
convert(varchar(5),currenttime, 108) between '06:00' and '19:00'
and convert(varchar(20), currenttime, 110) between @fdlw and @ldlw
order by currenttime

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\temp\excelexport\ExcelExport.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
select * from #log1
drop table #log1
GO
:Connect SQLSERVER\INST2
use scratch

declare @fdlw2 datetime
declare @ldlw2 datetime

set @fdlw2 = master.dbo.f_Dates('FWLW',getdate(),'',0)
set @ldlw2 = master.dbo.f_Dates('LWLW',getdate(),'',0)

select [DateTime],UserID
into #log2
from usagedata
where
convert(varchar(5),[datetime], 108) between '06:00' and '19:00'
and convert(varchar(20), [datetime], 110) between @fdlw2 and @ldlw2
order by [Datetime]

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\temp\excelexport\ExcelExport.xls;HDR=YES', 'Select [DateTime],UserID from [Sheet2$]')
select * from #log2

The result of the first query is:
Connecting to SQLSERVER\INST1...

(1207 row(s) affected)

(1207 row(s) affected)
Disconnecting connection from SQLSERVER\INST1...

The result of the second is:
Connecting to SQLSERVER\INST2...

(458 row(s) affected)
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 17
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Disconnecting connection from SQLSERVER\INST2...

This is on the same server. The file is definitely closed. I can just run the second query and get the same result without running the first. The sheet names are correct and have the column names in the first row.

I've also tried running the second query using MSDASQL and I get

insert into OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\temp\excelexport\ExcelExport.xls',
'SELECT [DateTime],UserID FROM [Sheet2$]')
select [DateTime],UserID from #log2

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver] System resource exceeded.".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".


Any help appreciated.

-Matt


Make sure the file resides at the second instance also

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 02/11/2010 :  06:02:56  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by anuragvatsa

4 If you dont want to create an EXCEL file in advance and want to export data to it, use
Hi madhivanan,
Am trying to add some styles on excel sheet generated after exceuting "sp_makewebtask " which will
generate the file with data in tabular format.
So is there any way to add template sort off or some color or styles for eg., Header, columns, background.

av


I dont think you can do color formation to EXCEL using sql
If you want it, do it from your front end application

Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 02/11/2010 06:04:36
Go to Top of Page

sathiesh2005
Yak Posting Veteran

India
85 Posts

Posted - 03/01/2010 :  06:55:25  Show Profile  Reply with Quote
Very useful post.

Thanks.

Regards,
Sathieshkumar. R
Go to Top of Page

badpupsd
Starting Member

USA
8 Posts

Posted - 03/01/2010 :  11:56:56  Show Profile  Reply with Quote
I am using -->
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\Reports\Morning\Confirmed-0301.xls;HDR=NO',
'SELECT * FROM [Sheet1$]')

I want to use a varable for the file name.

I've tried --> set @TO = "c:\Reports\Morning\Confirmed-" + @Prepend + ".xls" <--
modifying the above 2nd row to --> 'Excel 8.0;Database='+ @TO +';HDR=NO', <--

@TO defined as varchar(100)
(i've also tried defining @TO sysname

It doesn't seem to matter what i do as it doesn't evaluate correctly

My 2nd problem is that dates are not formatted as i want them (they are apparently passed as txt??)
I want them to be --> 3/1/10 08:56 AM <--
I am writing to a formatted .xls sheet so would expect the columns to be formatted correctly.

thanks for any assistance.

Edited by - badpupsd on 03/02/2010 10:50:52
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 03/03/2010 :  01:24:38  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by sathiesh2005

Very useful post.

Thanks.

Regards,
Sathieshkumar. R


Thanks for the feedback

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
Page: of 30 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000