Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Unanswered: Problem querying linked server

    I have a problem querying a linked server (Oracle) from my SQL server 2000. I am able to query it normally but when I try to do it through a stored procedure i get the following message


    Msg 7399, Sev 16: OLE DB provider 'MSDAORA' reported an error. Authentication failed. [SQLSTATE 42000]
    Msg 7312, Sev 16: [SQLSTATE 01000]
    Msg 7300, Sev 16: OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.]. [SQLSTATE 01000]

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Re: Problem querying linked server

    Can anybody help me ????

    Originally posted by Enigma
    I have a problem querying a linked server (Oracle) from my SQL server 2000. I am able to query it normally but when I try to do it through a stored procedure i get the following message


    Msg 7399, Sev 16: OLE DB provider 'MSDAORA' reported an error. Authentication failed. [SQLSTATE 42000]
    Msg 7312, Sev 16: [SQLSTATE 01000]
    Msg 7300, Sev 16: OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.]. [SQLSTATE 01000]

  3. #3
    Join Date
    Jul 2003
    Posts
    45

    Re: Problem querying linked server

    Looks like you may have a problem with your access rights on either one of the servers........
    If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Well,

    I am able to query the server when I am in the sql query analyzer.
    I.E.
    SELECT * INTO ABCD FROM TESTSVR..USER.ABCD

    This works perfectly and i get the result


    When I try to run this as a stored procedure

    CREATE procedure sp_TransferData @server varchar(100),@userid varchar(30)
    as
    declare
    @tablename varchar (30),
    @fieldnametemp varchar(100),
    @query varchar (2500)
    declare tablenames cursor for
    select distinct table_name from TABLES

    open tablenames
    FETCH NEXT FROM tablenames into @tablename
    WHILE @@FETCH_STATUS = 0
    begin
    declare @fieldname varchar(2000)
    select @fieldname = ''
    declare fieldname cursor for
    select field_name from tables where table_name = @tablename
    open fieldname
    FETCH NEXT FROM fieldname into @fieldnametemp
    WHILE @@FETCH_STATUS = 0
    begin
    select @fieldname = @fieldnametemp + ',' + @fieldname
    fetch next from fieldname into @fieldnametemp
    end
    CLOSE fieldname
    DEALLOCATE fieldname
    select @fieldname = left(@fieldname,len(@fieldname)-1)
    select @query = 'select '+ @fieldname + ' into ' + @tablename + ' from ' + @server + '..' + @userid + '.' + @tablename + ''')'
    select @query

    execute (@query)
    print 'Table Processed'

    fetch next from tablenames into @tablename
    end

    CLOSE tablenames
    DEALLOCATE tablenames

    the error crops up .... can somebody suggest a way around

Posting Permissions

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