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