Hi Folks … I have a scenario where one of the database is moved from ORACLE DB
to SQL SERVER DB.
There are many sql scripts where (+) is used for outer joins.
My concern is does SQL SERVER understand (+) the same way as ORACLE dose.
Will I have to rewite all my scripts ?
If NO then dose TOAD have any tool/option where by these codes are run as is i.e
with (+) symbal and it runs fine against SQL SERVER database ?
Most probably by internally converting those codes in ANSI SQL format i.e. LEFT
/RIGHT/FULL outer join.
Regards,
Shiva
The (+) is Oracle specific syntax and your queries will need to be re-written.
Toad can help!
The Query Builder can re-write these using the proper ANSI syntax and I believe
there is an Action that can be used for doing this en masse.
The Toad SQL Query Builder can convert from/to ANSI join syntax for existing
statements – try it out J
Thanks Jeff … I found that option in Toad and it does Oracle to ANSI format (
and otherway ) conversion very nicely.
But it is very manual.
I have to copy each code in Query Builder and then hit the “ANSI Syntax” option.
Is there a way to convert these SQL files lying in a directly by some command ?
Much better if there was a tool like TOAD which would accept Oracle SCRIPTS (
with + ) and talk to SQL SERVER DB .
Regards,
Shiva
Utilities > Automation Designer
You can now build a Toad macro/widget/app/thingy that allows you to point to
multiple files and have them batch re-written in ANSI join syntax. Just pick the
output directory and you’re good to go.
If you put your code into the same file, you could run this via command-line as
well (after you define the action)
Toad.exe –a MyActionName
Jeff …this is wonderful.Thanks a bunch.
However I wish Toad had that option to accept ORACLE SYNTAX while talking to no
Oracle Database.
Maybe Toad team can have this option added in next version.
Regards,
Shiv a
Toad only connects to Oracle…or are you asking for Toad for Data Analysts
(which can connect to SQL or Oracle) to accept Oracle Join Syntax?
I am looking for any Tool which can connect to SQL SERVER ( DB other than Oracle
) but accept Oracle Join Syntax.
If Toad for Data Analysts does this then I am very happy.
This will save me from all this conversion when one of my DB is migrated from
Oracle to SQL Server.
Regards,
Shiv a
It is not the GUI that accepts the syntax but the server.
So The oracle join syntax only works with Oracle.
If you want a GUI that accepts Oracle syntax, CONVERTS it to SQL SERVER and then
sends it to SQL SERVER I wish you good luck.
I believe I saw something on the Toadbeta list about the latest version of Toad
(possibly the BETA) converting the (+) syntax to INNER/OUTER join syntax.
The Toad for Oracle query builder has had the capability to convert from + to
ANSI for a long time now.
As for the other question – our toad for data analysts supports a syntax
that works across all databases and it works out the platform specific details.
You’ll need to download, install and read-up on how it does that. For
example it can do a cross database join – i.e. join oracle table to sql
server table. So it knows all about the various syntaxes J
Hello! I am also looking for a toll for convering old-style MSSQL 2005 syntax to 2012 syntax
for example
RAISERROR @errno @errmsg
into
raiserror(@errmsg,@errno,1)
and
g.klas1_type_id =* a.klas1_type_id
into LEFT OUTER JOIN ON g.klas1_type_id = a.klas1_type_id
I hope that TODD Query Builer can help? but after installation ToadforSQLServer_Freeware_6.6.0.963.zip
I’ve found that Query Builder Is not enabled (not licensed)
Gow can I try this feature? I have a lot of allstylescripts to convert
Please help!
