Hi

I have a (longish - 3500 lines) SQLServer script that contains the
definition of a data structure I want to port to SQLAnywhere (v10). It
basically consists of CREATE TABLE and CREATE INDEX statements with some
"If exist" constructs to check the tables don't exist already - they
won't in my case and I could get rid of those statements fairly easily.

The actual content seems entirely consistent with SQLAnywhere but the
minor syntax differences (no semi-colons, GO statements etc) don't seem
amenable to fixing even with a regex script. e.g:


/* AB Group Headings */

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ABGroupHeadings]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[ABGroupHeadings]
GO

CREATE TABLE [dbo].[ABGroupHeadings] (
[ABCode] [varchar] (2) NULL ,
[LanguageCode] [varchar] (3) NULL ,
[RecordType] [varchar] (1) NULL ,
[ClassGroupCode] [varchar] (2) NULL ,
[Description] [varchar] (40) NULL
) ON [PRIMARY]

CREATE UNIQUE INDEX [0] ON ABGroupHeadings (ABCode, LanguageCode,
RecordType, ClassGroupCode)
GO

Is there any tool I can run this script in directly against a
SQLAnywhere database or do I need to set up a SQLSever, run it there and
then migrate the resulting database?

Thanks

Justin Willey

Justin,

I can't say for sure if this will work "AS-IS" against the SQL Anywhere
database, however SQL Anywhere does support Transact-SQL compatibility:
http://dcx.sybase.com/index.php#http...2Fug-tsos.html

Have you tried running your script against a SQL Anywhere database?


Justin Willey wrote:

Justin,

in addition to Kory's remarks:

We have used nearly identical scripts in Transact-SQL syntax for MS SQL
Server (6.5 - 2000) and SQL Anywhere for years, and the missing
semicolons and the "go" batch delimiters should work with SQL Anywhere
just fine.

The most important minor differences I remember were:
a) A MS float (i.e. without precision) is a 64 bit float whereas a SA
float is 32 bit, and so a SA double corresponds to a MS float.
b) We stripped all segment/filegroup location descriptions, i.e the
CREATE TABLE ...(...) "on PRIMARY" parts.

So a simple text replace should do the trick.


HTH
Volker


Justin Willey wrote:

Hi

Thanks Kory & Volker. I think I was getting too hung up on the GO etc
business! It seems that if I can lose the "ON PRIMARY" as Volker
suggests and also get rid of the square brackets round the data type
names then it will run.

Many thanks

Justin

Kory Hodgson (Sybase iAnywhere) wrote:

Hi Volker

Thanks for the tip about FLOAT.

One issue further I'm now finding is dealing with a statement like this:

ALTER TABLE [dbo].[ABZSegments] ADD
CONSTRAINT [DF_ABZSegments_ClosedSegment] DEFAULT (0) FOR [ClosedSegment],
CONSTRAINT [DF_ABZSegments_ReportingLevel] DEFAULT (0) FOR
[ReportingLevel],
CONSTRAINT [DF_ABZSegments_SegmentOnHold] DEFAULT (0) FOR [SegmentOnHold]
GO

As far as I can see SQLA doesn't support adding default values as a
constraint. However I suspect I can regex this.

Justin

Volker Barth wrote:

Or just replace "ON PRIMARY" with "ON system" (or the appropriate
dbspace name). In that respect, MS SQL and SA use different terms
(segement/filegroup vs. dbspace) with a similar function, methinks

Justin Willey wrote: