Move a Sybase db to SQL Server 2000

[LucieLastic]

hi

We are going to move all our databases which are currently in Sybase Adaptive Server 12.5 to MS SQL Server 2000. I need to find out how we can do this and if there are common export/import formats. Has anyone ever done anything like this ?

Grateful of any help.

many thanks in advance
lou

 

[nigelrivett]

Yes and it involved recoding all the sybase sql which was a pain - took the oppertunity to redesign a lot of it.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[LucieLastic]

How exactly did you do it Nigel?

 

[nigelrivett]

Created the tables in sql server.
Exported data from sybase to files (I didn't do this bit).
imported the files using bcp.
Looked at all the code and rewrote it is t-sql (included a lot of shell scripts and c routines).



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[LucieLastic]

hi nigel

I'll have a look at the bcp stuff. Luckily we don't have many scripts, only a couple of triggers.

Any chance of you finding out how the other person produced the files from Sybase? I think date formats may be a problem with the transfer - did you encounter any other problems?

lou

 

[nigelrivett]

Afraid I don't have contact with that company anymore.

I bcp'd into staging tables first then inserted into the main table doing any convertion (including dates) on the way.

Put everything into SPs which could be run from a job as the sybase system was to keep running for a while. It would dump out daily files after the initial import which would be imported to sql server. Both systems would then carry out processing for comparison.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[LucieLastic]

hi Nigel

I've successfully created all the tables and moved the data in to SQLServer for one of our large databases. Now I want to add all the constraints, PKs etc but the DDL that Sybase has produced is confusing me, eg

exec sp_primarykey 'Product.dbo.AccType', 'accTypeID'

create unique nonclustered index XPKAccType on Product.dbo.AccType(accTypeID)
go

I want to create a PK for this table but SqlServer will create a clustered index not a logical entry like Sybase(sp_primarykey), but I need to add the nonclustered index SPKAccType. Is the best not to add the PK constraint and just add the non clustered one? but then I could have referential integrity problems later, couldn't I?

Not sure what to do here. It may be that I don't know enough about the constraint commands yet and getting flustered.

Thanks for any help
lou