Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    15

    Unanswered: SQL Server to Sybase Conversion - Basic Problem

    I am currently looking at converting a SQL Server System over to Sybase. Syntax is much the same however I have come up against a problem which I have not yet been able to figure out.

    In my existing application I have multiple databases on the same server. In SQL Server if you want to references on database from the other all you need do is refer to the table with the prefix of the database name.

    i.e. While using proc in Database1, SELECT * FROM Database2..table2

    This doesn't seem to work in Sybase. Is there any equivalent? I saw something about CIS and having to set up proxy tables to remote queries, however this query isn't remote - it is local and native to the server.

    Any help would be appreciated.

    Thanks
    David

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443

    Re: SQL Server to Sybase Conversion - Basic Problem

    Originally posted by davidfinnegan
    I am currently looking at converting a SQL Server System over to Sybase. Syntax is much the same however I have come up against a problem which I have not yet been able to figure out.

    In my existing application I have multiple databases on the same server. In SQL Server if you want to references on database from the other all you need do is refer to the table with the prefix of the database name.

    i.e. While using proc in Database1, SELECT * FROM Database2..table2

    This doesn't seem to work in Sybase. Is there any equivalent? I saw something about CIS and having to set up proxy tables to remote queries, however this query isn't remote - it is local and native to the server.

    Any help would be appreciated.

    Thanks
    David
    Hi David,

    Are you converting to ASE or ASA ?

    SELECT * from Database2..table2 should work in ASE as long as these databases are under a single ASE instance. Make sure the user has the right permissions.

    Thanks,
    Vishi.

  3. #3
    Join Date
    Aug 2003
    Posts
    15

    Re: SQL Server to Sybase Conversion - Basic Problem

    Hi Vishi,

    I am using Sybase 12.5, ASE (I think 6?).

    I start Sybase Central, open database connection to database A. I then open database connection to database 2. The GUI then looks quite like Entrerprise Manager with the first 2 nodes being the built in options and the next one being server name with the node of Database1 and Database2 underneath. If I select View Data option on table in either database I cannot refer to a table in the other database.

    In fact a piculiar thing I noticed is that if I issue query
    SELECT * From AnyDB..tableA

    Even if AnyDB doesn't exist, so long as the tableA exists in the current DB it will return records and doesn't complain about AnyDB not existing.

    The one thing that has me confused is that I am sure that when I first looked at Sybase a few weeks ago one of the first things I tried to do was query a table in a separate database and I think it worked then.

    It still doesn't seem to work now!

    Any further help would be appreciated.

    Thanks
    David

  4. #4
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Hi David,

    Hmm.. I am more of a unix guy working on shell scripts and isql sessions than the GUI tools.

    Maybe its a Sybase Central issue.

    Try opening a normal isql session and do the following

    use DB1 /* so that you are on a different db */
    go
    SELECT * from DB2..Table2
    go

    Thanks,
    Vishi.

  5. #5
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Yeah, this can most definitly work in ASE12.5. I think it even works in ASE11!

    It does seem to be some sort of a weird Sybase Central thing. When I get into work I'll test it on my server.
    Thanks,

    Matt

  6. #6
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208

    Re: SQL Server to Sybase Conversion - Basic Problem

    From what uou've explained here. I'm certain you are using ASA and not ASE. You see, Sybase has 2 totally different database products. ASE stands for Adaptive Server Enterprise and the other ASA, A -> Anywhere.

    The concept of databases is slightly different in ASA to that of ASE. You can't jump between databases using the "use <database> syntax.

    If you do a SELECT@@version in the ISQL session. If you get something like 6.0.4.2443 etc.. then it is ASA that you are using. If you get a long string like Adaptive Server Enterprise 12.5/C/P/EBF#11034/blah..blah..., then this is ASE.

    Cheers,

    Willy


    Originally posted by davidfinnegan
    Hi Vishi,

    I am using Sybase 12.5, ASE (I think 6?).

    I start Sybase Central, open database connection to database A. I then open database connection to database 2. The GUI then looks quite like Entrerprise Manager with the first 2 nodes being the built in options and the next one being server name with the node of Database1 and Database2 underneath. If I select View Data option on table in either database I cannot refer to a table in the other database.

    In fact a piculiar thing I noticed is that if I issue query
    SELECT * From AnyDB..tableA

    Even if AnyDB doesn't exist, so long as the tableA exists in the current DB it will return records and doesn't complain about AnyDB not existing.

    The one thing that has me confused is that I am sure that when I first looked at Sybase a few weeks ago one of the first things I tried to do was query a table in a separate database and I think it worked then.

    It still doesn't seem to work now!

    Any further help would be appreciated.

    Thanks
    David

  7. #7
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    If you are using ASA, go here:
    http://download.sybase.com/pdfdocs/awg0800e/dbugen8.pdf

    Skip to page 480 (PDF page 480, documentation page 462) and it'll tell you how to do it.
    Joining tables from multiple local databases
    An Adaptive Server Anywhere server may have several local databases
    running at one time. By defining tables in other local Adaptive Server
    Anywhere databases as remote tables, you can perform cross database joins.
     For more information about specifying multiple databases, see "USING
    parameter value in the CREATE SERVER statement" on page 478.
    For example, if you are using database db1 and you want to access data in
    tables in database db2, you need to set up proxy table definitions that point to
    the tables in database db2. For instance, on an Adaptive Server Anywhere
    named testasa, you might have three databases available, db1, db2, and db3.
    ♦ If using ODBC, create an ODBC data source name for each database
    you will be accessing.
    ♦ Connect to one of the databases that you will be performing joins from.
    For example, connect to db1.
    ♦ Perform a CREATE SERVER for each other local database you will be
    accessing. This sets up a loopback connection to your Adaptive Server
    Anywhere server.
    CREATE SERVER local_db2
    CLASS ’asaodbc’
    USING ’testasa_db2’
    CREATE SERVER local_db3
    CLASS ’asaodbc’
    USING ’testasa_db3’
    Alternatively, using JDBC:
    CREATE SERVER local_db2
    CLASS ’asajdbc’
    USING ’mypc1:2638/db2’
    CREATE SERVER local_db3
    CLASS ’asajdbc’
    USING ’mypc1:2638/db3’
    ♦ Create proxy table definitions using CREATE EXISTING to the tables
    in the other databases you want to access.
    CREATE EXISTING TABLE employee
    AT ’local_db2...employee’
    Example
    Thanks,

    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •