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

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