[dba-SQLServer] Using DTS to create Excel file from a 2-step query

Major, Joyce Joyce.Major at acsalaska.com
Fri Sep 16 20:27:39 CDT 2005


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




More information about the dba-SQLServer mailing list