Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2007
    Posts
    86

    Unanswered: SSIS with Linked Server to Sybase ASE 15

    I have a linked server from SS05 to ASE 15. It works fine, and I can read,write, insert, delete from my remote Sybase server.

    I'd like to create a packae, that uses SSIS to do this in an Agent job. I know how to do that.

    I started by trying to export data using SSMS / Task/ Export (from SQL server db, to Sybase OLEDB Provider
    -- this fails to conenct .. even though I can query the remote Sybase using a 4-part identifier.

    Anyone have a clue what the problem is ..

    It isn't permissions, as I am sa on both servers.

    thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What makes you think that the SSIS package fails to connect?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Do you have the Sybase client installed on the machine you are running SSMS on?

  4. #4
    Join Date
    Mar 2007
    Posts
    86
    Of course, otherwise how would select from, insert,delete etc work on my remote sybase server ..

    folks please read the post before responding.. thanks.

  5. #5
    Join Date
    Mar 2007
    Posts
    86

    SSIS package fails because

    It's not an SSIS package .. I tested using task / export
    it throws an error
    when I use test connection it throws an error
    all parms are correct as far as I can see ..
    when I get this working, I'll put it in an SSIS package ..

    Please read the post .. I think I articulated the issue pretty clearly.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by stuarta View Post
    Please read the post .. I think I articulated the issue pretty clearly.
    Since the two people that responded didn't understand you, I'm not sure how you can think that you were clear.

    You stated:
    Quote Originally Posted by stuarta View Post
    I can read,write, insert, delete from my remote Sybase server.
    Note that you said nothing about being able to do this from the local Microsoft server.

    You also said:
    Quote Originally Posted by stuarta View Post
    I started by trying to export data using SSMS / Task/ Export (from SQL server db, to Sybase OLEDB Provider
    -- this fails to conenct .. even though I can query the remote Sybase using a 4-part identifier.
    You didn't describe how you knew that it failed to connect until a later posting where you said you got an error message. Even then you didn't post the error message which leaves us free to assume that it was anything.

    Telling us that the Sybase server works is great. Telling us that four part names works would be great if you mentioned that the four part names worked from the Microsoft server.

    So far all that I can concretely tell is that you can't get the linked server working to your satisfaction and that you got an error message of some kind when you tried to access the Sybase server using four part names.

    I'm trying to help, but not making much progress. Please help us to help you.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Mar 2007
    Posts
    86

    PatP

    "I have a linked server from SS05 to ASE 15. It works fine"

    I said "it works fine".. that alone s/b enough for you to shut your pie hole moron!

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You must not know how a linked server works, then. Perhaps you should read up on it a tad?
    Scaling Out SQL Server 2005

  9. #9
    Join Date
    Mar 2007
    Posts
    86

    Linked servers

    I have dozens of linked servers. Some to Oracle, some MS SQL Server to MS SQL Server, and some to MS SQL Server to Sybase 12.5, and ASE 15.5 platforms. They all work for SQL based reference. Linked servers are not hard to setup, whether you're using the GUI to do it, or using sp_addserver (whcih btw is inherited from Sybase, where MS SQL Server was cloned from in the 1990's).

    There appears to be an issue isolated to connectivity when using SSIS / Sybase OLEDB provider.. I've seen a number of folks post on this problem, especially with SS05 using the current Sybase OLEDB provider for ASE 15.5. It's not a linked server issue or select FROM MS SQL SERVER TO A REMOTE SYBASE ASE 15.5 SERVER WOULD NOT WORK. At the native level the linkage is functional, but when attempting to do task/export data, the connection does not recognize the Sybase OLEDB Provider connection properties and throws a meaningless connection failed error.

    I was hoping someone had encountered this specific problem.

    I got around it by using insert/select from ISQL in an Agent job, across the linked server. I'll revisit this when I have time, and after I've migrated to SQL Server 2008.

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I have had all sorts of problems with ASE 15 around name resolution and the sybase client apis like OLEDB not supporting ipv6.

    The only way I have been able to get things to work reliably is to use an ipv4 ip address in the connection string instead of a server name. You might try that.

    also, you are making a mistake calling Pat a moron.

Posting Permissions

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