I have an ASP.NET app that uses a SQL Server database. I now need to pull data from Sybase ASE into that SQL Server database for my app to consume, and I'm not having any success with my ideas.
Has anyone done this? Any ideas/suggestions/tips?
-
More specifics: This is not a conversion. I want the data in Sybase to be reflected in the SQL Server db in real time, or close to it. Almost like doing SSIS, I imagine (which is something else I've tried with no success). The source server is always reachable by the destination server.theog– theog2009-04-09 20:47:46 +00:00Commented Apr 9, 2009 at 20:47
Add a comment
|
4 Answers
You can configure a linked server from SQL Server to Sybase. It should be fairly vanilla using the Sybase provider on the MS side.
1 Comment
Matt Rogish
I've done this before -- both ways (ASE to SQL Server and vice versa). Works like a charm
Okay, I've finally (through lame trial and error) found out how to link my Sybase ASE (12.5) server to my SQL Server (2008) which will allow the integration I want. Here's roughly how I did it:
- Logged in to Sybase ASE OLE DB Configuration Manager (this is like the Sybase version of Windows' ODBC Data Sources) and added an OLE DB data source. I believe you must be an admin on the PC to do this.
- In SQL Server 2008 Management Studio, went to Server Objects > Linked Servers. Right click and select "New Linked Server".
- In the Linked Server Properties, I set the following properties:
- General:
- --Linked server: the name of your linked server as you want it to appear in your linked server list
- --Provider: Select Sybase ASE OLE DB Provider from the dropdown list.
- --Product name: The exact name of the OLD DB data source you just created in Sybase ASE OLE DB Configuration Manager.
- --Data source: Same as Product name.
- --Provider string: I left this blank
- --Location: I left this blank
- --Catalog: The default database (master or whatever) to log on to.
- Security:
- --You need to map a valid SQL Server logon to a valid Sybase logon. I did not use impersonation (which does a credentials pass-thru).
- --I chose my connection Be made without using a security context.
- Server Options:
- --All the defaults worked for me.
Throughout, the standard SQL Server help worked fairly well as a guide. Though not always true, F1 was my friend here.
I can now do distributed queries, DTS or SSIS packages, and use SSRS. This takes a lot of the suck out of Sybase ASE.
Of course the above can be done via the command line using sp_linkserver, but the GUI is more comfortable for a lowly dev like me.
Comments
Use Management Studio or Enterprise Manager to import the data using the data importation wizard. That should be it, just make sure you pick the right data provider in the wizard and you should be good to go.
If you want this to be a live feed create a small windows service to manage the exchange of information. It should be relatively simple to do, just a little bit of leg work on your end. If you are adverse to that there are plenty of off the shelf solutions that can do this for you.
1 Comment
theog
Yeah, sorry Al, I should have specified that this is intended to be a live feed of sorts, not a one-time data conversion. But thanks anyway!
The question is a little vague on specifics:
- Is this a one time conversion or part of a repeated process.
- Is the source machine "reachable" from your destination machine (can you connect the two or do you need to read in files)
With most conversions there are two parts:
- Physically getting data from the source into the destination.
- Mapping data from the source to the destination tables.
It is hard to make any recommendations without more info. What would be fine for a one time conversion would not work if you need to read in data all day every day. Also, if the source database can not be connected to and you have to pass files, they methods change.
