Solved

ms sql stored procedure

Posted on 2016-11-03
22
102 Views
Last Modified: 2016-11-15
hi can you show me how to create a stored procedure that export data with the header into ms excel and schedule to run
daily at 9:00 pm ?

Assume the select query is like select firstname from person

Thanks
0
Comment
Question by:ITsolutionWizard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
  • +4
22 Comments
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41873414
you probably can do it using SQL Server Integration Services (SSIS).

as a start, you can read this article:

Using SSIS to Export Data to Excel
http://knowlton-group.com/using-ssis-to-export-data-to-excel/
0
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 41873415
from:
https://www.simple-talk.com/blogs/sending-query-results-to-excel-through-e-mail/
declare @qry varchar(8000)
declare @column1name varchar(50)
-- Create the column name with the instrucation in a variable
SET @Column1Name = '[sep=,' + CHAR(13) + CHAR(10) + 'customerid]'
 
-- Create the query, concatenating the column name as an alias
select @qry='set nocount on;select customerid ' + @column1name + 
             ' ,companyname, contactname, country from customers'
 
-- Send the e-mail with the query results in attach
exec msdb.dbo.sp_send_dbmail @recipients="Your email",
@query=@qry,
@subject='Client list',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'result.csv',
@query_result_separator=',',@query_result_width =32767,
@query_result_no_padding=1

Open in new window


You would then use SQL Agent to schedule a task to run at 9:00 PM.
0
 
LVL 1

Author Comment

by:ITsolutionWizard
ID: 41873423
If i do not want to use sql agent to schedule, what can i do?
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 41873432
You need something to kick it off.  Sql Agent makes sense.  If not it'll need to be a windows service or a program and a scheduled task.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41873433
You can create a Windows Service in that case.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 41873436
If i do not want to use sql agent to schedule, what can i do?
Use a different scheduling tool.

What do you have available? If we don't know what you have, we can only make random suggestions.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41873441
Create a SP in that perform operations you want to do and call it via SQL Job.

SQL Job you can schedule , you can also get alert on completion and you will also get alert in case it fails.
So tracking in this case will be very easy.
0
 
LVL 1

Author Comment

by:ITsolutionWizard
ID: 41873448
Again, I do not want to use SQL Job and window service.
Is it a way I can use stored procedure straight forward?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41873450
In that case you can run it manually !!
0
 
LVL 1

Author Comment

by:ITsolutionWizard
ID: 41873475
that does not solve my problem...
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41873478
if you want a piece of codes or a program to run automatically on a designated timing in a machine, it means you need to automate by allowing another listener program to trigger it.

How to automate it? I think some of us already given you suggestions here.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41873481
0
 
LVL 1

Author Comment

by:ITsolutionWizard
ID: 41873490
no
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41874019
If you can't or don't want to use SQL agent or windows scheduler the only thing left is for you to build one. Basically the simpllest way would be to create a VB script that will run in an infinite loop, check every time what time it is and if the time is right execute the stored procedure from that script. In that case you don't even need a stored procedure as you can manipulate the results and save them into an excel file using the VB script itself.

Execute from vb script:

https://www.experts-exchange.com/questions/28310743/Run-a-SQL-Stored-Procedure-from-a-vbs-File-and-return-a-parameter.html

Write to excel:
http://www.geekshangout.com/an-example-of-using-a-vbs-script-to-create-populate-and-format-an-excel-document/
0
 
LVL 1

Author Comment

by:ITsolutionWizard
ID: 41875098
so you are telling me to use classic asp to get this task completed?
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 41875870
Is there any specific reason you can't use sql agent?  It makes the most sense in terms of scheduling.
0
 
LVL 1

Author Comment

by:ITsolutionWizard
ID: 41875917
Our sql agent is not working and we don't want to touch it
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41877133
@Jim Horh:
Gentlemen - Please refrain from posting links-only or links-mostly answers.  Thanks in advance.

Three things:

1. My post is not at all "links-only or links-mostly". I give explanations what the alternatives are considering the conditions

2. One of the links is to experts-exchange and the other is just for a standard procedure to use vbScript to write to Excel!

3. I will not waste my time to just re-type or copy-paste things for things that are common knowledge. I am aware of the EE rules and I am not breaking them. The question was not about how to write in Excel using vbScript so I merely made a suggestion based on what the answers evolved to...
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41877208
Zberteoc - The comment was intended for multiple experts, as I'm counting four comments with links and various levels of completeness.  And please spell my name correctly.

Thanks in advance.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41877235
Sorry for misspelling your name, which was obviously unintended,  but I am surprised to see that you make a case of it...

Do links to to https://technet.microsoft.com or even other sites that are not EE competition but simply articles describing a procedure not allowed either? I mean there should be at least some tolerance in situations like these. We are giving our answers benevolently here, for free but at time cost to us, so can we at least have the right to not waste our time reinventing the wheel?

I think we are making little bit of a big deal about this, here!
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41878779
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question