Convert .MDF (Master Database file) into ANSI SQL statements

Hi,

We have .MDF (Master Database File). from the Microsoft SQL Server. Is there a way to generate a ANSI sql statements from it. The Goal is to use this .MDF file for other database like (MySQL and Oracle). Once we have ANSI sql statements (e.g. Create Table Test)..
we can use it to create a Database tables on the fly on any Database whether it is Oracle or Mysql or Microsoft. If there is better route then this one please advice me how to do it.

There are tools out there which can do this. I beleive that TOAD can handle this.

You will need the SQL Server Engine installed to do this.

|||Hi,

Thanks for your prompt reply. Using Microsoft SQL Server Management Studio Express.
I was able to Generate Script from the .MDF file. This options creates .sql file but this is TSQL file....Is there a way to convert this file into Ansi SQL file which will work on any database...whether it is (oracle, mysql or MS sql)..

This is the syntax how it look like

USE [RonakPatel]
GO
/****** Object: Table [dbo].[AuditEvents] Script Date: 05/09/2007 11:44:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AuditEvents](
[ID] [nchar](38) NOT NULL,
[AuditDateTime] [datetime] NOT NULL,
[AuditCode] [int] NOT NULL,
[AuditedUserID] [nchar](38) NOT NULL,
[AuditedConstructID] [nchar](38) NOT NULL,
[AuditText] [nchar](38) NOT NULL,
CONSTRAINT [PK_AuditEvents] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I know that GO and SET are not part of Standard SQL i had to remove them inorder for the script to work with my c# application.
|||There are several tools out there which can do the T/SQL to ANSI-SQL conversition for you. When I Googled for "convert T/Sql to ansi-sql" I got several hits.|||Hi,

Thanks for your suggestion mrdenny, I actually found one software..Advent net SwisSQL that does convert any SQL into ANSI SQL..now the next part is I am trying to execute that ansi sql statements using OleDbConnection ..and Server is : MS SQL some how it does not know datatype BLOB...

|||

Help me out here. Isn't ANSI SQL relegated to CRUD operations only and the basic datatypes. And each provider has their propietary extensions for schema creation and control. If I've got that wrong -set me straight.

It seems that there are many parts of the schema that are not ANSI specific, but in fact, provider specific.

You may find a tool that will convert T-SQL schema code to another product schema code (PSQL) -BUT I don't think that either will be ANSI SQL.

|||

RonakPPatel wrote:

Hi,

MS SQL some how it does not know datatype BLOB...

Correct. The BLOB data type isn't a valid data type in Microsoft SQL Server. Most of the vendors have there own data type names and definations. There is no cross platform standard.

|||Hi Arnie,

Here is the link of tool. This will convert a single SQL query or statement into any other format you want (Oracle, MS SQL, MySQL, Sybase, DB2, Ansi SQL etc)...the problem is that ANSI SQL it generates when i use it to execute using the Oledbconnection i am getting weird error about datatypes. But if i use the T-SQL syntax instead of Ansi SQL from this software everything works fine..and it also creates a table...

Site...
http://www.swissql.com/

Download this one
GUI based tool that converts SQL queries from one database dialect to another.

Here is MSSQL Syntext that works

CREATE TABLE automateconstructs11
(
ResourceID varchar (38) NOT NULL ,
ResourceName TEXT ,
ParentID varchar (38) DEFAULT NULL ,
ResourceType NUMERIC (11) NOT NULL ,
CompletionState NUMERIC (11) NOT NULL ,
Notes TEXT ,
CreatedBy varchar (38) NOT NULL ,
CreatedOn datetime NOT NULL ,
ModifiedOn datetime NOT NULL ,
Version NUMERIC (11) NOT NULL ,
VersionDate datetime NOT NULL ,
Empty BINARY (1) NOT NULL ,
Enabled BINARY (1) NOT NULL ,
PRIMARY KEY (ResourceID)
)

Here is MySQL syntext that works...

CREATE TABLE `automateconstructs`
(
`ResourceID` varchar (38) NOT NULL ,
`ResourceName` longtext ,
`ParentID` varchar (38) DEFAULT NULL ,
`ResourceType` int (11) NOT NULL ,
`CompletionState` int (11) NOT NULL ,
`Notes` longtext ,
`CreatedBy` varchar (38) NOT NULL ,
`CreatedOn` datetime NOT NULL ,
`ModifiedOn` datetime NOT NULL ,
`Version` int (11) NOT NULL ,
`VersionDate` datetime NOT NULL ,
`Empty` TINYINT NOT NULL ,
`Enabled` TINYINT NOT NULL ,
PRIMARY KEY (`ResourceID`)
)

Here is Ansi SQL syntaxt that does not work...

CREATE TABLE automateconstructs
(
ResourceID varchar (38) NOT NULL ,
ResourceName BLOB ,
ParentID varchar (38) DEFAULT NULL ,
ResourceType int (11) NOT NULL ,
CompletionState int (11) NOT NULL ,
Notes BLOB ,
CreatedBy varchar (38) NOT NULL ,
CreatedOn TIMESTAMP NOT NULL ,
ModifiedOn TIMESTAMP NOT NULL ,
Version int (11) NOT NULL ,
VersionDate TIMESTAMP NOT NULL ,
Empty bit (1) NOT NULL ,
Enabled bit (1) NOT NULL ,
PRIMARY KEY (ResourceID)
)

|||

This tool is designed to convert a QUERY to ANSI ("single SQL query or statement ") -NOT a CREATION statement. ANSI SQL is QUERY language (DML).

Each Vendor has their own extension to the SQL Language for schema (DDL) and security (DCL). There is NO ANSI standard . Vendors DDL and DCL are vendor specific and not interchangable. (Well, some parts may be -but there is no guarantee.)

You will have to create vendor specific DDL or DCL, and execute the correct version depending upon the Server support.

Convert *= / =* to outer joins (ANSI Join)

Hi,

I'm working on converting *= and =* to 'left outer join' and 'right outer join'.

I noticed the difference in behavior between the =* and the phrase "right outer join" and between *= and 'left outer join'. The result set is different. Here is an example:

select a.au_id, b.title, c.qty

from titleauthor a, titles b, sales c

where (a.title_id =* b.title_id)

and (a.title_id =* c.title_id)

I try to conver the above to:

select a.au_id, b.title, c.qty

from titleauthor a

right outer join titles b

on (a.title_id = b.title_id )

right outer join sales c

on (a.title_id = c.title_id )

The first results into 391 rows in pubs database of sql-server 2000 and the second produces 34 rows. It seems not that straight forward to convert.

The question is: If I want to get 391 row, what should I change/add in the second sql statement?

Thank you. Appreciate your help.

Joan

I think this:

select a.au_id, b.title, c.qty

from titleauthor a, titles b, sales c

where (a.title_id =* b.title_id)

and (a.title_id =* c.title_id)

Is actually not a right join between these tables directly, but actually more this query:

--changed the a b c aliases to just be the table for clarity
select titleauthor.au_id, titles.title, sales.qty
from sales
cross join titles
left outer join titleauthor
on titles.title_id = titleauthor.title_id
and sales.title_id = titleauthor.title_id

The commas can be translated directly to CROSS JOIN, with the where being the same. So in your query, since there is no link between sales and titles, it does a cross join. You can see this by looking at the plan:

Code Snippet

SET SHOWPLAN_ALL ON
GO

select a.au_id, b.title, c.qty
from titleauthor a, titles b, sales c
where (a.title_id =* b.title_id)
and (a.title_id =* c.title_id)

SET SHOWPLAN_ALL OFF
GO

|--Hash Match(Right Outer Join, HASH:([a].[title_id])=([b].[title_id]),
RESIDUAL:([pubs].[dbo].[titleauthor].[title_id] as
[a].[title_id]=[pubs].[dbo].[titles].[title_id] as [b].[title_id]
AND [pubs].[dbo].[titleauthor].[title_id] as
[a].[title_id]=[pubs].[dbo].[sales].[title_id] as [c].[title_id]))
|--Index Scan(OBJECT:([pubs].[dbo].[titleauthor].[titleidind] AS [a]))
|--Nested Loops(Inner Join)
|--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind] AS [b]))

|--Clustered Index Scan(OBJECT:([pubs].[dbo].[sales].[UPKCL_sales] AS [c]))

Note, no join criteria. Hopefully this helps, it is one of the reasons why the syntax was changed
|||

Hi,

Is it because the question is not clear?

Joan

|||You mean the query? It is clear enough, it is just a matter of what you are asking. The ANSI Style is far more clear to express queries as they are going to be/can be expressed.

The old style was ambiguous like this and was based on the concept that you (logically) first cross join each of the tables in the FROM, then for each row check the criteria. Works like a champ for INNER joins, but OUTER joins not so much.|||

Hi Louis,

Thank you so much!

I'll try to fix my query. I might have question later.

Thanks again.

Joan

|||

Hi Louis,

Here I get another example. The old code is as follows:

Select CU.Name, CI.Amount, CI.PolicyItemId, CI.timestamp, CU.CreditSurchargeId, CU.ValueType_ES ,CU.IsDefault, CU.IsModifiable, CU.IsCredit, CP.ObjectCategoryId, CU.Type_ES, CP.DisplayOrder ,CP.Amount, CP.Effectivedateid
from CreditSurchargePolicyLine CP, CreditSurchargeUnit CU, CreditSurchargePolicyLineItem CI
where CP.PolicyLineId = 4
and CP.ObjectSubjectId = 4
and CP.Status_ES = 'A'
and CP.CreditSurchargeId = CU.CreditSurchargeId
and CP.ObjectSubjectId = CU.ObjectSubjectId
and CU.CreditSurchargeId *= CI.CreditSurchargeId
and CI.PolicyItemId = 30153677
and CI.PolicyLineItemId = 1
and CP.ObjectCategoryId in (0, 1)
and CP.CreditSurchargeId <> 79
and CP.CreditSurchargeId <> 78
and CP.CreditSurchargeId <> 83
and CP.effectivedateID = 1

I converted as follows:

Select CU.Name, CI.Amount, CI.PolicyItemId, CI.timestamp, CU.CreditSurchargeId, CU.ValueType_ES, CU.IsDefault, CU.IsModifiable, CU.IsCredit, CP.ObjectCategoryId, CU.Type_ES, CP.DisplayOrder ,CP.Amount, CP.Effectivedateid
from CreditSurchargePolicyLine CP JOIN CreditSurchargeUnit CU ON CP.CreditSurchargeId = CU.CreditSurchargeId
AND CP.ObjectSubjectId = CU.ObjectSubjectId
LEFT OUTER JOIN CreditSurchargePolicyLineItem CI ON (CU.CreditSurchargeId = CI.CreditSurchargeId)
where CP.PolicyLineId = 4
and CP.ObjectSubjectId = 4
and CP.Status_ES = 'A'
and CI.PolicyItemId = 30153677
and CI.PolicyLineItemId = 1
and CP.EffectiveDateId = 1
and CP.ObjectCategoryId in (0, 1)
and CP.CreditSurchargeId <> 79
and CP.CreditSurchargeId <> 78
and CP.CreditSurchargeId <> 83

The old code returns 23 rows, which it should be. But the new code returns 11 rows, which is not correct. But I can't tell what is wrong with the new code.

Thank you!

Joan

|||

This query may fix your problem..

Code Snippet

Select
CU.Name
, CI.Amount
, CI.PolicyItemId
, CI.timestamp
, CU.CreditSurchargeId
, CU.ValueType_ES
, CU.IsDefault
, CU.IsModifiable
, CU.IsCredit
, CP.ObjectCategoryId
, CU.Type_ES
, CP.DisplayOrder
, CP.Amount
, CP.Effectivedateid
from
CreditSurchargePolicyLine CP

JOIN CreditSurchargeUnit CU ON
CP.CreditSurchargeId = CU.CreditSurchargeId
AND CP.ObjectSubjectId = CU.ObjectSubjectId

LEFT OUTER JOIN CreditSurchargePolicyLineItem CI ON
CU.CreditSurchargeId = CI.CreditSurchargeId
And CI.PolicyItemId = 30153677
And CI.PolicyLineItemId = 1

where
CP.PolicyLineId = 4
and CP.ObjectSubjectId = 4
and CP.Status_ES = 'A'
and CP.EffectiveDateId = 1
and CP.ObjectCategoryId in (0, 1)
and CP.CreditSurchargeId <> 79
and CP.CreditSurchargeId <> 78
and CP.CreditSurchargeId <> 83

|||

Thank you ManiD! Thank you all!

|||

You are welcome.

Remember when you use left/right outer join, if you want to apply any filter attach that filter on JOIN condition itself - rather than

at where clause.

-Mani.D

|||

>>Remember when you use left/right outer join, if you want to apply any filter attach that filter on JOIN condition itself - rather than at where clause.<<

In spirit this is usually right, but this isn't quite true. You have to be careful and cognizant about where to put FILTER criteria, but it can go either place. You just have to realize that:

In the JOIN clause, a condition is applied to the joining of the two sets of data. And from the left side of a LEFT join will be returned no matter what (or the right side of a RIGHT join or both sides of a FULL join for that matter

In the WHERE clause, the condition is applied the the set of rows produced from the FROM clause. So if a row was returned in the FROM clause as the result of an LEFT OUTER JOIN, if you then try to filter out the data by comparing data from the right table, all of the values will be NULL. So they will be filtered out unless you realize this.
sqlsql

Convert *= / =* to outer joins (ANSI compliant)

Hi,

I'm working on converting *= and =* to 'left outer join' and 'right outer join'.

I noticed the difference in behavior between the =* and the phrase "right outer join" and between *= and 'left outer join'. The result set is different. Here is an example:

select a.au_id, b.title, c.qty

from titleauthor a, titles b, sales c

where (a.title_id =* b.title_id)

and (a.title_id =* c.title_id)

I try to conver the above to:

select a.au_id, b.title, c.qty

from titleauthor a

right outer join titles b

on (a.title_id = b.title_id )

right outer join sales c

on (a.title_id = c.title_id )

The first results into 391 rows in pubs database of sql-server 2000 and the second produces 34 rows. It seems not that straight forward to convert.

The question is: If I want to get 391 row, what should I change/add in the second sql statement?

Thank you. Appreciate your help.

Joan

I think this:

select a.au_id, b.title, c.qty

from titleauthor a, titles b, sales c

where (a.title_id =* b.title_id)

and (a.title_id =* c.title_id)

Is actually not a right join between these tables directly, but actually more this query:

--changed the a b c aliases to just be the table for clarity
select titleauthor.au_id, titles.title, sales.qty
from sales
cross join titles
left outer join titleauthor
on titles.title_id = titleauthor.title_id
and sales.title_id = titleauthor.title_id

The commas can be translated directly to CROSS JOIN, with the where being the same. So in your query, since there is no link between sales and titles, it does a cross join. You can see this by looking at the plan:

Code Snippet

SET SHOWPLAN_ALL ON
GO

select a.au_id, b.title, c.qty
from titleauthor a, titles b, sales c
where (a.title_id =* b.title_id)
and (a.title_id =* c.title_id)

SET SHOWPLAN_ALL OFF
GO

|--Hash Match(Right Outer Join, HASH:([a].[title_id])=([b].[title_id]),
RESIDUAL:([pubs].[dbo].[titleauthor].[title_id] as
[a].[title_id]=[pubs].[dbo].[titles].[title_id] as [b].[title_id]
AND [pubs].[dbo].[titleauthor].[title_id] as
[a].[title_id]=[pubs].[dbo].[sales].[title_id] as [c].[title_id]))
|--Index Scan(OBJECT:([pubs].[dbo].[titleauthor].[titleidind] AS [a]))
|--Nested Loops(Inner Join)
|--Index Scan(OBJECT:([pubs].[dbo].[titles].[titleind] AS [b]))

|--Clustered Index Scan(OBJECT:([pubs].[dbo].[sales].[UPKCL_sales] AS [c]))

Note, no join criteria. Hopefully this helps, it is one of the reasons why the syntax was changed
|||

Hi,

Is it because the question is not clear?

Joan

|||You mean the query? It is clear enough, it is just a matter of what you are asking. The ANSI Style is far more clear to express queries as they are going to be/can be expressed.

The old style was ambiguous like this and was based on the concept that you (logically) first cross join each of the tables in the FROM, then for each row check the criteria. Works like a champ for INNER joins, but OUTER joins not so much.|||

Hi Louis,

Thank you so much!

I'll try to fix my query. I might have question later.

Thanks again.

Joan

|||

Hi Louis,

Here I get another example. The old code is as follows:

Select CU.Name, CI.Amount, CI.PolicyItemId, CI.timestamp, CU.CreditSurchargeId, CU.ValueType_ES ,CU.IsDefault, CU.IsModifiable, CU.IsCredit, CP.ObjectCategoryId, CU.Type_ES, CP.DisplayOrder ,CP.Amount, CP.Effectivedateid
from CreditSurchargePolicyLine CP, CreditSurchargeUnit CU, CreditSurchargePolicyLineItem CI
where CP.PolicyLineId = 4
and CP.ObjectSubjectId = 4
and CP.Status_ES = 'A'
and CP.CreditSurchargeId = CU.CreditSurchargeId
and CP.ObjectSubjectId = CU.ObjectSubjectId
and CU.CreditSurchargeId *= CI.CreditSurchargeId
and CI.PolicyItemId = 30153677
and CI.PolicyLineItemId = 1
and CP.ObjectCategoryId in (0, 1)
and CP.CreditSurchargeId <> 79
and CP.CreditSurchargeId <> 78
and CP.CreditSurchargeId <> 83
and CP.effectivedateID = 1

I converted as follows:

Select CU.Name, CI.Amount, CI.PolicyItemId, CI.timestamp, CU.CreditSurchargeId, CU.ValueType_ES, CU.IsDefault, CU.IsModifiable, CU.IsCredit, CP.ObjectCategoryId, CU.Type_ES, CP.DisplayOrder ,CP.Amount, CP.Effectivedateid
from CreditSurchargePolicyLine CP JOIN CreditSurchargeUnit CU ON CP.CreditSurchargeId = CU.CreditSurchargeId
AND CP.ObjectSubjectId = CU.ObjectSubjectId
LEFT OUTER JOIN CreditSurchargePolicyLineItem CI ON (CU.CreditSurchargeId = CI.CreditSurchargeId)
where CP.PolicyLineId = 4
and CP.ObjectSubjectId = 4
and CP.Status_ES = 'A'
and CI.PolicyItemId = 30153677
and CI.PolicyLineItemId = 1
and CP.EffectiveDateId = 1
and CP.ObjectCategoryId in (0, 1)
and CP.CreditSurchargeId <> 79
and CP.CreditSurchargeId <> 78
and CP.CreditSurchargeId <> 83

The old code returns 23 rows, which it should be. But the new code returns 11 rows, which is not correct. But I can't tell what is wrong with the new code.

Thank you!

Joan

|||

This query may fix your problem..

Code Snippet

Select
CU.Name
, CI.Amount
, CI.PolicyItemId
, CI.timestamp
, CU.CreditSurchargeId
, CU.ValueType_ES
, CU.IsDefault
, CU.IsModifiable
, CU.IsCredit
, CP.ObjectCategoryId
, CU.Type_ES
, CP.DisplayOrder
, CP.Amount
, CP.Effectivedateid
from
CreditSurchargePolicyLine CP

JOIN CreditSurchargeUnit CU ON
CP.CreditSurchargeId = CU.CreditSurchargeId
AND CP.ObjectSubjectId = CU.ObjectSubjectId

LEFT OUTER JOIN CreditSurchargePolicyLineItem CI ON
CU.CreditSurchargeId = CI.CreditSurchargeId
And CI.PolicyItemId = 30153677
And CI.PolicyLineItemId = 1

where
CP.PolicyLineId = 4
and CP.ObjectSubjectId = 4
and CP.Status_ES = 'A'
and CP.EffectiveDateId = 1
and CP.ObjectCategoryId in (0, 1)
and CP.CreditSurchargeId <> 79
and CP.CreditSurchargeId <> 78
and CP.CreditSurchargeId <> 83

|||

Thank you ManiD! Thank you all!

|||

You are welcome.

Remember when you use left/right outer join, if you want to apply any filter attach that filter on JOIN condition itself - rather than

at where clause.

-Mani.D

|||

>>Remember when you use left/right outer join, if you want to apply any filter attach that filter on JOIN condition itself - rather than at where clause.<<

In spirit this is usually right, but this isn't quite true. You have to be careful and cognizant about where to put FILTER criteria, but it can go either place. You just have to realize that:

In the JOIN clause, a condition is applied to the joining of the two sets of data. And from the left side of a LEFT join will be returned no matter what (or the right side of a RIGHT join or both sides of a FULL join for that matter

In the WHERE clause, the condition is applied the the set of rows produced from the FROM clause. So if a row was returned in the FROM clause as the result of an LEFT OUTER JOIN, if you then try to filter out the data by comparing data from the right table, all of the values will be NULL. So they will be filtered out unless you realize this.

Conversion of non Ansi standard queries to ANSI Standard queries

Hi,
In our company we are trying to support SQL Server 2005 in 90 mode. Our
application consists around 120 Stored procedure written with non ansi
standard format joins (*=), is there any tools to convert them or any other
quicky method to do it.
Thanks in advance.
Cheers
RajeshIn article <D05276DF-6E55-49D4-A35E-03ECC8B953A4@.microsoft.com>, =?Utf-
8?B?UmFqZXNoIFY=?= <Rajesh V@.discussions.microsoft.com> says...
> Hi,
> In our company we are trying to support SQL Server 2005 in 90 mode. Our
> application consists around 120 Stored procedure written with non ansi
> standard format joins (*=), is there any tools to convert them or any other
> quicky method to do it.
> Thanks in advance.
> Cheers
> Rajesh
>
Not sure about the available tools other than search and replace via any
good text editor, but another question is the ambiguity of old syntax
outer joins which may produce different results when converted to ANSI
standard syntax.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||On Wed, 26 Sep 2007 01:44:01 -0700, Rajesh V <Rajesh
V@.discussions.microsoft.com> wrote:
>Hi,
> In our company we are trying to support SQL Server 2005 in 90 mode. Our
>application consists around 120 Stored procedure written with non ansi
>standard format joins (*=), is there any tools to convert them or any other
>quicky method to do it.
Hi Rajesh,
No automated tools that I know of. In similar cases in the past, I have
found that if you assign one person to the task, he or she will build up
routine quickly, so that once (s)he is past the learing curve, the
process of replacing the non-standard code becomes pretty fast.
Don't forget to reward the poor guy/gal with a day off or a bonus after
completing such an unrewarding and mind-numbing task!!
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis