[dba-SQLServer] Using DTS to create Excel file from a2-stepquery

Major, Joyce Joyce.Major at acsalaska.com
Tue Sep 20 12:29:40 CDT 2005


It is easy to get DTS to create the Excel file on a shared drive.
The issue is getting DTS to recognize a complex query as described.

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
Francisco Tapia
Sent: Tuesday, September 20, 2005 9:17AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Using DTS to create Excel file from
a2-stepquery

yes because it can create the excel file you eant into a server
share..., 
unless someone else has the routine for DTS

On 9/19/05, Major, Joyce <Joyce.Major at acsalaska.com> wrote:
> 
> We only have Reporting Services installed on one test server. I will
> look into the ability to access that. If I can, will it solve my
> issues? I know nothing about the product.
> 
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
> Francisco Tapia
> Sent: Monday, September 19, 2005 7:57AM
> To: dba-sqlserver at databaseadvisors.com
> Subject: Re: [dba-SQLServer] Using DTS to create Excel file from a
> 2-stepquery
> 
> do you have Reporting Services? If you do this might be a lot easier
to
> do
> with RS....
> 
> On 9/16/05, Major, Joyce <Joyce.Major at acsalaska.com> wrote:
> >
> > I have written a query that runs fine from Enterprise Manager/SQL
> Query
> > Analyzer.
> >
> > The first part of the query Selects into a # temp table and the
second
> > part of the query Selects from the # temp table.
> >
> > The user wants this query run on a scheduled basis with the results
> > placed in an Excel file out on the network where they can pick it
up.
> >
> >
> >
> > I created a DTS package with the query as its source and the excel
> file
> > as its destination.
> >
> > When I execute the DTS package, I get the following error:
> >
> > DTSRun OnError: Create Table Results Step, Error = -2147217900
> > (80040E14)
> >
> > Error string: Syntax error in field definition.
> >
> > Error source: Microsoft JET Database Engine
> >
> > Help file:
> >
> > Help context: 5003292
> >
> > Error Detail Records:
> >
> > Error: -2147217900 (80040E14); Provider Error: -537398753 (DFF7F21F)
> >
> > Error string: Syntax error in field definition.
> >
> > Error source: Microsoft JET Database Engine
> >
> > Help file:
> >
> > Help context: 5003292
> >
> >
> >
> > If I create a DTS package only using the first part of the query
> > (removing the INTO # portion), it works.
> >
> > One thing I notice that is different between the 2...the DTS wizard
> > creates a SQL command to create a table that it names Results.
> >
> > In the first scenario (with temp table), the Create Table SQL has no
> > columns....in the second scenario, the SQL command looks like a
> complete
> > Create Table command.
> >
> >
> >
> >
> >
> > Joyce Major
> >
> > (907) 564-1294 Ofc
> >
> > (907) 529-0147 Cell
> >
> > (907) 564-8596 Fax
> >
> > jmajor at acsalaska.com
> >
> >
> >
> >
> >
> >
>
************************************************************************
> ***********
> > This transmittal may contain confidential information intended
solely
> for
> > the addressee. If you are not the intended recipient, you are hereby
> > notified that you have received this transmittal in error; any
review,
> > dissemination, distribution or copying of this transmittal is
strictly
> > prohibited. If you have received this communication in error, please
> > notify
> > us immediately by reply or by telephone (collect at 907-564-1000)
and
> ask
> > to
> > speak with the message sender. In addition, please immediately
delete
> this
> > message and all attachments. Thank you. ACS
> >
> >
> >
> >
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> >
> >
> >
> 
> 
> --
> -Francisco
> http://pcthis.blogspot.com |PC news with out the jargon!
> http://sqlthis.blogspot.com | Tsql and More...
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> 


-- 
-Francisco
http://pcthis.blogspot.com |PC news with out the jargon!
http://sqlthis.blogspot.com | Tsql and More...
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list