I can't believe I didn't actually google the exact error message. There's a lesson to be learned there. Upon doing so, I learned that similar errors occur in other applications. The cause is non-ASCII codes. I was exporting from SQL in Unicode.
Here is the full solution:
In SQL:
- Right-click on the desired db, choose Tasks > Generate Scripts
- Select the desired database, next
- Leave the default options (I've tried a few things, none worked), next
- Select Tables only (I don't need anything except the tables, their fields, keys, and relationships)
- Select all tables (or 4 tables, it doesn't matter)
- Choose "Script to file", enter a file name
- Under Save as: choose ANSI text (Unicode breaks the crap out of ER/Studio)
- Finish
Then import into ER/Studio. My import has been running for 2 hours so far.
Next up, trying to figure out how to make an ordered list on this site. I'm so full of fail. :)
I was being tasked to do similar task recently. IT is not easy in especially SybaseASE.
Option 1 :
You can write custom tool to BCP out data in files and then grep those files to search for particular text that you want to look for. You can use PERL regex to do as well or use native Unix/Linux commands to parse those files and find the required text that you want to look for.
Option 2 :
Another option (which is not that great - quick and dirty way using TSQL ) that worked for me .
Note: This will take long time (slow and unoptimized) depending on your database size, hardware, workload running on the server as well as database layout.
TEST, TEST and TEST it before hand !!
--- run below script and copy the output and run it in another query window.
set nocount on
declare @searchvalue varchar(255)
select @searchvalue = 'test string to search'---- replace this .. !!
print 'set nocount on'
print 'go'
print 'create table #results (table_name sysname, column_name sysname)'
print 'go"
select
'insert #results select distinct '''
+ object_name(c.id)
+ ''' as table_name, '''
+ c.name
+ ''' as column_name from '
+ object_name(c.id)
+ ' where '
+ c.name
+ ' LIKE ''%'
+ @searchvalue
+ '%'''
+ char(10)
from
syscolumns c, sysobjects o
where
c.usertype in (
-- only look for char, varchar, text etc datatypes as we are not interested in int, datetime etc....
1
,2
,18
,19
,24
,25
,42
)
and o.type ='U' -- only user tables we are interested in
and c.id = o.id
and c.length>=datalength(@searchvalue )
--and object_name(c.id) = 'some table if you want to filter'
print 'select * from #results'
print 'go"
go
Best Answer
Since both Sybase and SQLServer share the same SQL dialect (T-SQL), I'd generate the DDL statements from the existing database (it will require a Sybase server) to build the SQL Server database and the an SSIS task to copy data between databases.
I don't know if it is possible to SQLServer to just open a Sybase file, but, albeit the same origins, I don't believe there's any kind of binary compatibility between inner formats of this two DBMS.