How can we make sure that DTS works across databases. For example in one of the Transform Data tasks I have a query which has to make use of outer joins. The strategy we has used is to keep our SQLs to ANSI standards. But I have still not found a solution to make outer joins which works across Oracle 8.1.7, 9i and SQL server 2000?

There will be a certain amount of compliance between the maor database
vendors but both MS and Oracle implement their relative expensions.

What statement are you implementing exactly.?

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Break it" <anonymous (AT) discussions (DOT) microsoft.com> wrote

joins. The strategy we has used is to keep our SQLs to ANSI standards. But I
have still not found a solution to make outer joins which works across
Oracle 8.1.7, 9i and SQL server 2000?

I was trying to implement a SQL
SELECT * FROM table_A A LEFT OUTER JOIN table_B B ON A.id = B.id

This ANSI SQL would work with SQL server, but not with Oracle 8.1.7.

Is there any way I can dynamically replace the SQL on the "Transform Data Task"? What I am hinting is that depending on the database type I will dynamically replace the SQL query to be Oracle specific or SQL server specific.

Thanks Allan

Yes you can dynamically replace the SourceSQLStatement property of the
DataPump task

Global Variables and SQL statements in DTS
(http://www.sqldts.com/Default.aspx?205)

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Break It" <anonymous (AT) discussions (DOT) microsoft.com> wrote

dynamically replace the SQL query to be Oracle specific or SQL server
specific.

Thanks Allan. This worked