1

Topic: Automatic export query from ms sql server into excel on specific time

All greetings!
Can help with such question, it is necessary that automatic it was exported select query in excel every day to two o'clock in the morning
And that excel it was exported to a server folder or through ftp
How it can be implemented?

2

Re: Automatic export query from ms sql server into excel on specific time

SSRS+ On the report

3

Re: Automatic export query from ms sql server into excel on specific time

Very Pavlovny dream;
And it is possible to explain in more details?
I new in this question therefore will not understand

4

Re: Automatic export query from ms sql server into excel on specific time

Urzi;
1. SSRS .
2. a subscription .

5

Re: Automatic export query from ms sql server into excel on specific time

Very Pavlovny dream;
Whether and it is possible to create SSIS package and after this SSIS Package to cause Job th?
And as well as where it is possible to create SSIS Package?
Thanks in advance!

6

Re: Automatic export query from ms sql server into excel on specific time

Urzi wrote:

And whether it is possible to create SSIS package and after this SSIS Package to cause Job th?

Yes.

Urzi wrote:

And as well as where it is possible to create SSIS Package?

In MS a Visual Studio.
Functionality SSRS is insufficient for such task  (import to an ex-fur-tree, insufficient productivity if it is important is inconvenient), on the other hand, is superfluous (web-access to reports, mailing etc.) is not necessary

7

Re: Automatic export query from ms sql server into excel on specific time

alexeyvg;
And there are other methods to carry out my task? If SSIS Package it is insufficient

8

Re: Automatic export query from ms sql server into excel on specific time

Urzi wrote:

And there are other methods to carry out my task? If SSIS Package it is insufficient

Describe normally the task.
For the SSIS-package just suffices for normal export to an ex-fur-tree.
And for pastes and  just right SSRS.
It is necessary to guess, than all it did not suit you.

9

Re: Automatic export query from ms sql server into excel on specific time

o-o;
The task: every day at night at 2:00 the automatic the data with MS a SQL Server in Excel a file and that this Excel the file was saved in one of server folders (something like 1.1.1.1\ExcelFiles) was deduced
What for pastes with SSRS?

10

Re: Automatic export query from ms sql server into excel on specific time

Urzi wrote:

alexeyvg;
And there are other methods to carry out my task? If SSIS Package it is insufficient

In  to bring a call sqcmd/bcp and your request about a data sampling
As an output file to use comma-separated or tab-separated a file which is perfectly recognized Excel th
For example, so:

sqlcmd-S the server-E-Q "set nocount on; select name, database_id from sys.databases"-o \\the server \folder \fajl.csv-s ","-W

11

Re: Automatic export query from ms sql server into excel on specific time

Urzi wrote:

the Task: every day at night at 2:00 the automatic the data with MS a SQL Server in Excel a file and that this Excel the file was saved in one of server folders

was deduced

wrote:

SSIS Package it is insufficient

So what exactly the SSIS-package did not approach?

12

Re: Automatic export query from ms sql server into excel on specific time

Urzi wrote:

alexeyvg;
And there are other methods to carry out my task? If SSIS Package it is insufficient

The most trivial way:
xcel can load in itself request and save result. FillFromRecordset
To launch xcel it is possible job th or the scheduler.
And  singers SSIS have a rest.

13

Re: Automatic export query from ms sql server into excel on specific time

aleks222;
How to make it: to Launch xcel it is possible job th or the scheduler.

14

Re: Automatic export query from ms sql server into excel on specific time

Urzi wrote:

aleks222;
How to make it: to Launch xcel it is possible job th or the scheduler.

At it  and SSIS for the same reasons did not approach:
It should also be launched 8-o
So matter is not in SSIS, and in skillful pens

15

Re: Automatic export query from ms sql server into excel on specific time

Schedule a Task
How to: Create a Job (SQL Server Management Studio)

16

Re: Automatic export query from ms sql server into excel on specific time

Urzi wrote:

alexeyvg;
And there are other methods to carry out my task? If SSIS Package it is insufficient

Yes it is possible though a program on With to write, if the heads forbid to use SSIS.
But other ways it is more difficult.
I even met a variant, to make request on T-SQL which forms  a file (Msec-office files is XML)

17

Re: Automatic export query from ms sql server into excel on specific time

alexeyvg wrote:

I even met a variant, to make request on T-SQL which forms  a file (Msec-office files is XML)

On T-SQL such, maybe, and search, and c c for xml + xslt on the client, and on an output to receive Excel xml 2003 - trifling, in general, business. Well, certainly, for those who knows, how.

18

Re: Automatic export query from ms sql server into excel on specific time

Very Pavlovny dream;
Do not prompt as it to make?

19

Re: Automatic export query from ms sql server into excel on specific time

So what exactly the SSIS-package did not approach? [/quote]
Approached, but with access the problem quits Code: 0xC0011007 this error

20

Re: Automatic export query from ms sql server into excel on specific time

Very Pavlovny wrote:

On T-SQL such, maybe, and search, and c c for xml + xslt on the client, and on an output to receive Excel xml 2003

it is clear, not absolutely alone T-SQL, but in general approximately so.

Urzi wrote:

do not prompt as it to make?

Well, study a format  a file, and write a program. This uneasy business what here fast to write  :-)

Urzi wrote:

it is passed...
Approached, but with access the problem quits Code: 0xC0011007 this error

With access where? To a sequel? Probably to solve this unpretentious problem easier, than to select on the present a difficult way.

21

Re: Automatic export query from ms sql server into excel on specific time

alexeyvg wrote:

it is passed...
With access where? To a sequel? Probably to solve this unpretentious problem easier, than to select on the present a difficult way.

When not in a state  completely an error;
It is already huge problem

22

Re: Automatic export query from ms sql server into excel on specific time

o-o;
Here such error:
Message
Executed as user:-/user/- $. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) OF MICROSOFT CORPORATION 2010. All rights reserved. Started: 2:29:00 PM Error: 2017-10-10 14:29:00.08 Code: 0xC0011007 Source: {F5028092-CA1D-466F-A246-931189760856} Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted. End Error Error: 2017-10-10 14:29:00.08 Code: 0xC0011002 Source: {F5028092-CA1D-466F-A246-931189760856} Description: Failed to open package file "C:\Program Files\Microsoft SQL Server\100\DTS\Packages\ExcelTest3.dtsx" due to error 0x80070005 "Access is denied." . This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. End Error Could not load package "C:\Program Files\Microsoft SQL Server\100\DTS\Packages\ExcelTest3.dtsx" because of error 0xC0011002. Description: Failed to open package file "C:\Program Files\Microsoft SQL Server\100\DTS\Packages\ExcelTest3.dtsx" due to error 0x80070005 "Access is denied.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. Source: {Source-code} Started: 2:29:00 PM Finished: 2:29:00 PM Elapsed: 0.016 seconds. The package could not be found. The step failed.

23

Re: Automatic export query from ms sql server into excel on specific time

Unable to load the package as XML because of package does not have a valid XML format

24

Re: Automatic export query from ms sql server into excel on specific time

o-o;
What does it mean?

25

Re: Automatic export query from ms sql server into excel on specific time

Urzi wrote:

o-o;
What does it mean?

Means nothing.
Means
Failed to open package file "C:\Program Files\Microsoft SQL Server\100\DTS\Packages\ExcelTest3.dtsx" due to error 0x80070005 "Access is denied.".
Here all is written.
. SSIS - not for nervous.