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.