Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2005
    Posts
    119

    Unanswered: Query data from SYBASE ASE DB then insert to MSSQL Server 2005 tables

    I will be importing data from SYBASE ASE database then insert the result set to a MSSQL Server 2005 database.

    I'll be working on MS SQL Server 2005. What I'm thinking is to create a stored procedure, query the data from SYBASE ASE database then insert those data to SQL tables. IS there a code/syntax (sort of openrowset) where I can query the data directly from SYBASE ASE inside my SQL stored procedure?

    Please help how will i do this.

    Thanks a lot.

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    try to use a linked server (sp_addlinkedserver)

  3. #3
    Join Date
    Oct 2005
    Posts
    119
    how is this?

    1.) Will i create an ODBC connection to SYbase? The info i have are the servername, Database Name, username & password of Sybase.

    2.) How will i use the sp_addlinkedserver. Can you show me the syntax pls.

    I don't know how to start. I'm a newbie in SQL2005.

    Thanks a lot.

  4. #4
    Join Date
    Oct 2005
    Posts
    119
    I created an ODBC connection successfully.

    But when i was trying to create a linked server...the error "Msg 15247, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 36
    User does not have permission to perform this action." appears.

    I dont know if it's bcoz of my access rights to the SQL server or there is something wrong w/ my sntax. Here's my code:

    EXEC sp_addlinkedserver
    @server = 'SybaseSvr',
    @srvproduct = '',
    @provider = 'MSDASQL',
    @datasrc = 'DataSourceName' --this is the datasource name i defined in ODBC
    GO

    SELECT *
    FROM OPENQUERY(SYBASESvr, 'SELECT * FROM SybaseTableName')
    GO

  5. #5
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    sp_addlinkedserver requires ALTER ANY LINKED SERVER permission.

  6. #6
    Join Date
    Oct 2005
    Posts
    119
    Quote Originally Posted by aflorin27
    sp_addlinkedserver requires ALTER ANY LINKED SERVER permission.
    i dont understand. can u explain further pls. thanks.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm not sure how to expand on what aflorin27 wrote, but I can add some links that might help:

    Your SQL login must have at least the ALTER ANY LINKED SERVER permisson in order to execute the sp_addlinkedserver stored procedure.

    You'll have to explain what isn't clear in order for for me to help you more than this because I don't understand what help you need.

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

Posting Permissions

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