Francisco Tapia
fhtapia at gmail.com
Tue Sep 20 21:40:24 CDT 2005
Well then what if you were to use a variable table... instead of using a SELECT * INTO #Table, try declaring the variable table first.. Declare @T TABLE(Field1 Varcar(8), Field2 INT) then just INSERT INTO @T and finally select. When playing with exporting data sometimes using a variable table (virtual table) helps. On 9/20/05, Major, Joyce <Joyce.Major at acsalaska.com> wrote: > > 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 > > _______________________________________________ > 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...