CODEWITHSUNDEEP
sql-serversybasessms
user3181645
user3181645

Reputation: 11

Query Advantage Sybase from SQL Server Management Studio 2012

I have a remote site using Advantage Sybase as a backend, and I need to query the data preferably from SQL Server Management Studio 2012.

I can see the tables within a Sybase Data Architect app I downloaded, but now I need to query from SQL Server Management Studio 2012 Can anyone help?

Upvotes: 1

Views: 2815

Answers (2)

glenn garson
glenn garson

Reputation: 446

Yes it is possible. I hope this gets you started.

The word you want to search for is "LINKED" server.

Linked servers allow you to do what you are trying to do.

I have set up Linked servers to 'Active Directory', 'MySQL', other MS SQL Servers, and "Advantage Databases" I have not set up a linked server on SQL SERVER 2012, But I have the steps here for doing so from SQL SERVER 2014

1] SQL Server: 2014 SP2

2] The OLEDB Driver

Driver I recently downloaded from the Advantage Database Site:

http://devzone.advantagedatabase.com/dz/content.aspx?key=20&Release=16

Advantage OLE DB Provider Release 10.1_64bit File: adsoledb_x86_64.exe Signed by iAnywhere Solutions, Inc Product Version: 10.10.0049

To Install:

Run as administrator Installed to: C:\Program Files\Advantage 10.10\ (x) Complete Anyone who uses this computer (all users)

3] Configure the Provider

Object Explorer => {the Server} => Server Objects => Linked Servers => Providers

"Advantage OLE DB Provider" now shows up under 'Providers' in the Object Explorer of SQL

Double-Click the object => check: "Allow InProcess"

4] Run Script to create the LINKED server object:

"TheNameYouWantToAppearInSSMS" is the name that will show up in the Object Explorer (SSMS) under: Server Objects => Linked Servers

The "sp_dropserver" is in case you are testing, and want to easily delete a previously created LINKed server

        sp_dropserver N'TheNameYouWantToAppearInSSMS', 'droplogins';  
        go
        EXEC master.dbo.sp_addlinkedserver
            @server = N'TheNameYouWantToAppearInSSMS'
            ,@srvproduct=N'Advantage OLE DB Provider' 
            ,@provider=N'Advantage OLE DB Provider'
            ,@datasrc=N'\\10.0.0.48:2211\TheShareName\SomeFolderName\TheADD_FileName.add'           
            ,@provstr=N'ServerType=ADS_REMOTE_SERVER; TableType=ADS_ADT;'   
        EXEC master.dbo.sp_addlinkedsrvlogin 
            @rmtsrvname=N'TheNameYouWantToAppearInSSMS',
            @useself=N'False',
            @locallogin=NULL,   
            @rmtuser='yourUser',
            @rmtpassword='yourPassword'     
        GO  

        --https://blogs.msdn.microsoft.com/dataaccesstechnologies/2010/08/19/permissions-needed-to-set-up-linked-server-with-out-of-process-provider/

        EXEC master.dbo.sp_serveroption @server=N'TheNameYouWantToAppearInSSMS', @optname=N'rpc', @optvalue=N'true'
        GO

        EXEC master.dbo.sp_serveroption @server=N'TheNameYouWantToAppearInSSMS', @optname=N'rpc out', @optvalue=N'false'
        GO


    -- To SELECT you have to use OPENQUERY:

    select * from
    openquery(TheNameYouWantToAppearInSSMS,'select * from SomeTableInAdvantageDatabase')

Then build some views over those SELECT statements so that you can conveniently query the Advantage database from SSMS

Upvotes: 1

dom
dom

Reputation: 651

As far as i know this is not possible even if Sybase and MSSQL come from the same roots.

See also my answer to another topic: SSMS connection to Sybase ASE

Upvotes: 0

Related Questions