Export to Excel - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Export to Excel

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2010-04-09 : 11:09:39

hi, could anyone help me with this following problem.

I need to export a sql table to a password protected excel file everyday.
I was hoping I could create a job that runs everyday to create a new excel file called data_export_yyyymmdd.xls

is this possible ?

I've been trying Exec Master..xp_cmdshell 'bcp "..
but cannot get it to work.

thank you for any help.

jamie
Aged Yak Warrior

542 Posts

Posted - 2010-04-09 : 11:56:56

I am also trying the sp mentioned in a post further down, but get this error :

User name not provided, either use -U to provide the user name or use -T for Trusted Connection

EXEC proc_generate_excel_with_columns 'db_name', 'table_name','c:\testing.xls'

jamie
Aged Yak Warrior

542 Posts

Posted - 2010-04-09 : 12:02:13

IF I add -T into the SP I then get the error :
Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is config

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-12 : 02:32:20

quote:
Originally posted by jamie

IF I add -T into the SP I then get the error :
Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is config

Post the full code you used

Madhivanan

Failing to plan is Planning to fail

jamie
Aged Yak Warrior

542 Posts

Posted - 2010-04-12 : 04:58:52

ALTER procedure [dbo].[proc_generate_excel_with_columns]
(
@db_name varchar(100),
@table_name varchar(100),
@file_name varchar(100)
)

as

--Generate column names as a recordset

declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select
@columns=coalesce(@columns+',','')+column_name+' as '+column_name
from
information_schema.columns
where
table_name=@table_name

select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -T -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -T -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)

--Delete dummy file
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)

GO

Then to Run :

EXEC proc_generate_excel_with_columns 'MyDB', 'MyTable','c:\testing.xls'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-12 : 09:12:13

Remove -T option and use

-SServerName -PPassword

and see if that works

Madhivanan

Failing to plan is Planning to fail

jamie
Aged Yak Warrior

542 Posts

Posted - 2010-04-12 : 09:22:21

Fantastic, it now works !
thank you.
(I'm wondering if its failing because I am not using the default instance name).?

jamie
Aged Yak Warrior

542 Posts

Posted - 2010-04-12 : 09:24:47

Do you also know how to automate password protecting this file ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-12 : 09:55:09

quote:
Originally posted by jamie

Do you also know how to automate password protecting this file ?

I dont think you can set password for excel using sql

Madhivanan

Failing to plan is Planning to fail

jamie
Aged Yak Warrior

542 Posts

Posted - 2010-04-12 : 10:15:23

damn, do you know of any other options ?
ie, if you needed to export data from sql into a file and it needed to be secure. what would you advise ?

thank you for all your help madhivanan.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-13 : 02:47:30

quote:
Originally posted by jamie

damn, do you know of any other options ?
ie, if you needed to export data from sql into a file and it needed to be secure. what would you advise ?

thank you for all your help madhivanan.

You can use front ends like VB6 or .NET to secure it
Search for code samples at .NET sites

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources