I am using SQL to extract data to a text file(and I'm new to this). I want to
end up with one row from a table that stores data in two rows:
Table looks like this:
Location LocationTYpe Product
1111 Bottle AAA
2222 Case AAA
I want my file to look like this:
Product Case Location Bottle Location
AAA 2222 1111
I can do this using multiple Selects into temporary tables AND it works in
SQL Analyzer BUT when I include the same code in a DTS Package it fails.
Either something is wrong with the package OR (I think) DTS can't handle
multi Selects or temporary tables. Any help would be greatly appreciated.
--
Stan Gosselin

So DTS is not so friendly towards temp tables and the like but this query can easily be rewritten

SELECT
B.Location as BottleLocation,
C.Location as CaseLocation,
B.Product
FROM
(SELECT * FROM Table WHERE LocationType = 'Bottle') B
JOIN
(SELECT * FROM Table WHERE LocationType = 'Case') C
ON
B.Product = C.Product



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Stan" <Stan (AT) discussions (DOT) microsoft.com> wrote

DTS have many problems with sql query, a good way to never have surprise is
to put your SQL code into Stored Procedure
DTS does not excute your SQL code like the query anylyzer, it execute the
sql code vis a Systeme Stored procedure (sp_execpreparedsql or something
like that)



"Stan" <Stan (AT) discussions (DOT) microsoft.com> wrote

In message <OvUDG2mcFHA.2664 (AT) TK2MSFTNGP15 (DOT) phx.gbl>, Cymryr
<Cymryr (AT) hotmail (DOT) com> writes
For info
I believe it uses the OLE-DB parameterised query support, which does
indeed use procedures to prepare, execute then remove. It gives the
benefit of cached plans if calling the same overall statement several
times, but to be honest the only time this makes sense is for lookups.
It also has some benefits around SQL injection as parameters are not
just string concatenated.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org