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.
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
Method 1:
You can create a
Linked serverat SQL server pointing to SYBASE and then create a SQL server agent job that does the task of refreshing the data from SYBASE to SQL with help of OPENQUERY. This is better explained in the article here.Method 2:
If you are comfortable at creating SSIS package, then you can create one, in case method 1 cannot be used with security point in concern, to fetch the data from SYBASE to SQL server. You can follow the steps as explained in Extracting data from Sybase SQL Anywhere using SSIS through ODBC.
Method 3:
As you said, you have the access to client server, then you may give a try with option of extracting the data from sybase DB tables with help of writing few line codes using bcp utility into csv or text files on the sybase server or copy that onto SQL server. Once the data has been exported onto SQL server you may import with various option available to import the same text/csv file into SQL server.